Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Inputs Required!! Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 1:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Hey Jason, thanks for your valuable inputs. I shall have these incorporated and tested soon.

Thanks


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1412247
Posted Monday, January 28, 2013 2:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
No problem, let me know how it turns out.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1412260
Posted Monday, January 28, 2013 2:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Jason-299789 (1/28/2013)
No problem, let me know how it turns out.


Cool! This works well. Here is the modified (few compilation issues rectified) and tested version.

;WITH Cte_Src AS (
SELECT
Db_name(database_id) databasename
,Max(x.Last_Date) Last_Access_time
,Getdate() curr_time
FROM
sys.dm_db_index_usage_stats
CROSS APPLY(VALUES(last_user_update)
,(last_user_lookup)
,(last_user_scan)
,(last_user_seek)) x (last_date)
WHERE last_date IS NOT NULL
AND Db_name(database_id) NOT IN ('msdb','master','tempdb')
GROUP BY database_id
)
MERGE master..AuditTable AS tgt
USING Cte_Src AS src
ON tgt.databasename = src.databasename
WHEN MATCHED AND src.Last_Access_time != tgt.Last_Access_time
THEN
UPDATE SET
tgt.last_access_time = src.last_access_time
, tgt.no_of_days = DateDiff(d,src.last_access_time, src.curr_time)
, tgt.last_upd_dtm = src.curr_time
WHEN NOT MATCHED BY TARGET
THEN INSERT (DatabaseName,last_access_time,no_of_days,last_upd_dtm)
VALUES ( src.DatabaseName,src.last_access_time,DateDiff(d,src.last_access_time, src.curr_time),GETDATE());

Thanks!


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1412267
Posted Monday, January 28, 2013 3:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 29, 2013 9:13 PM
Points: 20, Visits: 68
Thanks people, with the similar piece of code it has solved my probelm....
Post #1412309
Posted Monday, January 28, 2013 3:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
sainatth.wagh (1/28/2013)
Thanks people, with the similar piece of code it has solved my probelm....


Wonderful! Glad to know that.

All glories to Jason as well for optimizing my code


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1412312
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse