November 22, 2012 at 2:39 am
Hi,
I have a SQL Server 2000 box that houses several databases, some of which are probably no longer in use. I'd like to clean things up by first taking them offline, and then later removing them all together. The problem is that I don't know how to tell which of these are still being actively used (outside sources may or may not be connecting to them, using them, etc.)
Is there a way to tell the time of the last activity on each database? I know that SQL Server keeps records of some things in sys tables, but I'm not sure what exactly is stored there.Can any one help .......:-)
November 22, 2012 at 2:42 am
No, not conclusively.
You can check the index usage stats for a DB, if there's nothing in there then the DB hasn't been used since the last time the server was started, but that's about as good as you can get.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2012 at 2:45 am
how can i know if the sql server restarted then also how we can retrieve when database was last accessed .
November 22, 2012 at 2:56 am
No,You can not check the index usage stats for a DB in SQL server 2000 ,as there is no DMV's in SQL server 2000 .
You can use the Profiler, to know the which databases are active on server.
Run it for certain period ,to ensure that databases is not being used
-----------------------------------------------------------------------------
संकेत कोकणे
November 22, 2012 at 3:01 am
Not sure which version of SQL server you are using exactly 😉
its different in post and different in Subject line
-----------------------------------------------------------------------------
संकेत कोकणे
November 22, 2012 at 3:09 am
hi ,
we are using sql server 2008 r2
November 22, 2012 at 3:28 am
to get when your SQL server restarted last time
SELECT[sqlserver_start_time]
FROM[sys].[dm_os_sys_info]
To get when databases accessed last time , use the script provided by Lowell in below thread
http://www.sqlservercentral.com/Forums/Topic1280879-1550-1.aspx
-----------------------------------------------------------------------------
संकेत कोकणे
November 22, 2012 at 3:36 am
Hi,
to find out last time server was restarted on SQL Server 2008, you can use:
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
Then, to figure when the indexes were last used (i.e. to get an idea if the DB is actually used), you can use the DMV sys.dm_db_index_usage_stats which will show you the following information:
- last_user_seek: Time of last user seek
- last_user_scan: Time of last user scan.
- last_user_lookup: Time of last user lookup.
- last_user_update: Time of last user update.
For details, see http://msdn.microsoft.com/en-us/library/ms188755(v=sql.105).aspx
Alternatively, you could also take the DB offline and then wait for the cries of despair coming in from users ... 😛
HTH,
B
November 22, 2012 at 3:44 am
hi,
I am not getting all databases names in the server from sys.dm_db_index_usage_stats because sql server was restarted last week.
November 22, 2012 at 4:31 am
nhimabindhu (11/22/2012)
hi,I am not getting all databases names in the server from sys.dm_db_index_usage_stats because sql server was restarted last week.
The ones you aren't getting haven't been used since the restart. That's the best you can tell as for usage if you don't have custom logs of any form
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply