How to find last accessed date time of a SQL Server 2008 database?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » How to find last accessed date time of a SQL...

How to find last accessed date time of a SQL Server 2008 database?

Rate Topic

Display Mode

Topic Options

Author

Message

sqldba4u

Posted Tuesday, February 07, 2012 3:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:13 AM
Points: 108, Visits: 233

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

Jeff Moden

Posted Tuesday, February 07, 2012 6:21 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 32,923, Visits: 26,811

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1248661

SQLback

Posted Tuesday, February 07, 2012 7:56 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 8:28 AM
Points: 340, Visits: 744

Found this..

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

Post #1248679

sumitagarwal781

Posted Tuesday, February 07, 2012 8:00 PM

SSC 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

sqldba4u

Posted Tuesday, February 07, 2012 11:57 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:13 AM
Points: 108, Visits: 233

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

bassman7463

Posted Wednesday, August 29, 2012 9:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 10:25 AM
Points: 1, Visits: 30

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 »

Permissions