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

I need to find when all the databases on SQL instance last modified Expand / Collapse
Author
Message
Posted Tuesday, January 21, 2014 8:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:11 PM
Points: 22, Visits: 295
I used below query but i have to query each and every database in the instance to get the information.

SELECT max(last_user_update) last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'YOUR_DBNAME_HERE')

But the above query is returning null for many of the databases.

Can anyone please let me know why its resulting null.

Also i would appreciate if only one query is provided to find all the last modified date of all the databases in one SQL instance.
Post #1533130
Posted Tuesday, January 21, 2014 8:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
That DMV only tracks data since the last time SQL was started. If a DB has had no data modifications since the last time the server was started, that query will return null for that database.

SQL doesn't track modifications across server restarts, the best you'll get is when it was modified since the last time SQL started.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1533139
Posted Tuesday, January 21, 2014 8:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:11 PM
Points: 22, Visits: 295
So what is the way to find when the databases are last modified in a SQL instance.

Post #1533146
Posted Tuesday, January 21, 2014 8:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
SQL doesn't track modifications across restarts of the service. If you need to track that, you'll have to put some custom monitoring in place going forward.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1533151
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse