August 30, 2011 at 9:37 pm
All,
In SQL Server 2005, Can any one tell me how to find out how long the database is not active?.This will help to decommision the database.
Thank You,
sk
August 31, 2011 at 7:53 am
You can look at queries in the cache to see if the database has been accessed recently: sys.dm_exec_query_stats. But that's only as good as the queries in cache. If the queries against that database have aged out of cache or their plans weren't cached for some reason, then the access could still be there.
Other than that, the only way to know is to monitor the system regularly, say with a server-side trace, to see what calls are made.
There's no built in function that will show you the last time a database was accessed.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 31, 2011 at 10:09 am
Run SQL Server Profiler and filter the database name to the one you are interested in. Monitor it for as long as you see fit to determine usage. When I decommission a database, I do not drop it right away... I take it offline so that it can easily be put back online in case of emergency. Always keep a backup as well, unless you are absolutely sure that it will not be needed such as a deprecated test database. However, I would assume that if you are worried about access, you may want to have a backup saved.
Thanks,
Jared
Jared
CE - Microsoft
August 31, 2011 at 10:25 am
jared-709193 (8/31/2011)
Run SQL Server Profiler and filter the database name to the one you are interested in. Monitor it for as long as you see fit to determine usage. When I decommission a database, I do not drop it right away... I take it offline so that it can easily be put back online in case of emergency. Always keep a backup as well, unless you are absolutely sure that it will not be needed such as a deprecated test database. However, I would assume that if you are worried about access, you may want to have a backup saved.Thanks,
Jared
But, running the Profiler GUI against a production server is somewhat risky. The GUI works differently with event queues in the server and can negatively impact the server. That's why I always recommend setting up a server side trace and not using the GUI.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 31, 2011 at 10:46 am
Grant Fritchey (8/31/2011)
But, running the Profiler GUI against a production server is somewhat risky. The GUI works differently with event queues in the server and can negatively impact the server. That's why I always recommend setting up a server side trace and not using the GUI.
Grant, I see your point. To the user with the question, here is a link with a comparison of Server Side and Profiler with a sample script for the Server Side trace: http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx
I could make a case for SQL Profiler, but each companies' set up is different of course (do you have multiple servers running multiple databases, multiple databases on same server, distributed servers to balance the load, etc.). Your safest bet is to follow Grant's advice, unless you know for sure that you will not cause an impact on production.
Thanks,
Jared
Jared
CE - Microsoft
August 31, 2011 at 10:49 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply