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

How to find last accessed date time of a SQL Server 2008 database? Expand / Collapse
Author
Message
Posted Tuesday, February 7, 2012 3:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:17 AM
Points: 117, Visits: 281
How we can find last accessed date time of a SQL Server 2008 database apart from using the DMV sys.dm_db_index_usage_stats because it will show result only for database tables having indexes.
Any audit or trace option...?


Regards
Vivek
Post #1247928
Posted Tuesday, February 7, 2012 6:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 36,941, Visits: 31,443
My q2uestion would be.... why do you have tables that people access that have no indexes?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1248661
Posted Tuesday, February 7, 2012 7:56 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 28, 2014 12:12 PM
Points: 341, Visits: 804

Found this..

http://www.sanssql.com/2008/10/when-was-my-database-last-used.html


Post #1248679
Posted Tuesday, February 7, 2012 8:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 23, 2012 7:55 AM
Points: 75, Visits: 110

Check this post.
http://www.sqlservercentral.com/Forums/Topic517209-5-2.aspx#bm688879


java
Post #1248681
Posted Tuesday, February 7, 2012 11:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:17 AM
Points: 117, Visits: 281
We are in the process of retiring unused DBs. These databases and tables were created few years back.
Also, its not necessary that a table should have index.
Post #1248727
Posted Wednesday, August 29, 2012 9:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 12:15 PM
Points: 1, Visits: 36
sp_MSforeachdb @command1='use [?];
SELECT DB_NAME() as DatabaseName,
--s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
last_execution_time,
execution_count,
plan_generation_num,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY last_execution_time desc--,s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset
'
Post #1351721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse