SQL Server - Information

Asked By prathap chowdary on 23-Feb-12 12:57 AM
Hi,

This is Prathap..........

How to check the last access of database.......like date and time.......

Please help me
Suchit shah replied to prathap chowdary on 23-Feb-12 01:05 AM
To know the last access data and time of database use below query in your SSMS


SELECT name, last_access =(
  
select X1= max(LA.xx)
  
from ( select xx =
  
max(last_user_seek)
  
where max(last_user_seek)is not null
  
union all
  
select xx = max(last_user_scan)
  
where max(last_user_scan)is not null
  
union all
  
select xx = max(last_user_lookup)
  
where max(last_user_lookup) is not null
  
union all
  
select xx =max(last_user_update)
  
where max(last_user_update) is not null) LA)
  
FROM master.dbo.sysdatabases sd
 
left outer join sys.dm_db_index_usage_stats s
 
on sd.dbid= s.database_id
 
group by sd.name
Web Star replied to prathap chowdary on 23-Feb-12 01:14 AM
See this article, you can get all info about all database by using dm_db_index_usage_stats system table

http://dinesql.blogspot.in/2010/05/when-was-my-sql-server-database-last.html 

hope this helps you
Somesh Yadav replied to prathap chowdary on 23-Feb-12 03:54 AM
Hi,

If a user wants to finds out when was the last table updated he can query dynamic management view (dmv) – sys.dm_db_index_usage_stats and easily figure out when was the table updated last. Let us comprehend this example by creating a table and updating it. We can use dmv to determine when it was updated last.

USE AdventureWorks
GO
CREATE TABLE Test
(ID INT,
COL VARCHAR(100))
GO
INSERT INTO Test
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
GO

Now we have created a table and populated it with data. Next, we will run the following query to find out when it was last updated.

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks')
AND OBJECT_ID=OBJECT_ID('test')

Running query provides accurate details of when was  the table last updated. If WHERE condition is entirely removed it will provide details of the entire database.

Hope it helps you.