Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We're looking to decommission a SQL Server instance which has a couple databases still remaining on it.

How can I tell if they are still being used by users or a web application?

I found a forum thread which had a T-SQL query you could run to retrieve the last query date. It seems to work but I want to know if this information is valid enough to drop databases. Is it?

If you have alternative methods that would help as well.

share|improve this question
    
Lots of great discussion below but also see this blog post. –  Aaron Bertrand Jun 19 at 21:01

7 Answers 7

up vote 25 down vote accepted

You would have to be concerned with items that have been purged from the cache and that you have missed, or for databases that have infrequent usage.

Rather than dropping the databases out of hand put them either OFFLINE to prevent access without dropping them or in RESTRICTED_USER mode to limit access. Doing this you can leave them in that state for a month or two to check and see if there is occasional usage.

You could also look to use a server side profiler trace filtering on that database.

share|improve this answer
17  
Rule #1 of being a DBA: Never make any change that you can't back out of quickly if you have to. –  Gaius Apr 4 '11 at 9:13

The next solution shows temporary total, clean and dirty pages in MB for particular databases under your instance (found on internet and modified a bit):

SELECT
    (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
    COUNT(*) *8/1024 AS [TotalPages in MB],
    SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) *8/1024 AS [CleanPages in MB],
    SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) *8/1024 AS [DirtyPages in MB]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY DB_NAME(database_id)

or

select value [DBid],attribute, last_execution_time ,text
from
sys.dm_exec_query_stats
cross apply
sys.dm_exec_plan_attributes(plan_handle)
cross apply
sys.dm_exec_sql_text(plan_handle)
where  attribute = 'dbid' 
order by last_execution_time desc

or

select value [DBid],attribute, last_execution_time ,text
from
sys.dm_exec_query_stats
cross apply
sys.dm_exec_plan_attributes(plan_handle)
cross apply
sys.dm_exec_sql_text(plan_handle)
--where dbid=8
where 
      text like '%idAdministrator%' and
      attribute = 'dbid' 
      and value>= 5 -- dbid >=5 for user databases but include resource database which
                     --you can exclude by its numer I don't remember at the moment
order by last_execution_time desc
share|improve this answer
    
Could you clarify how does this solve the original problem? –  dezso Nov 6 '12 at 21:43

The following query shows DBs which have had no usage since the last restart, without relying on query plans being held in the cache, as it shows user IO against the indexes (and heaps). This is sort of along the lines of using virtual file stats, but the DMV used here excludes IO activity from backups. No need to keep a profiler trace running, no triggers or auditing required. Of course, if you restart your SQL server frequently (or you attach/shutdown databases often) this might not be the way to go :-)

Having said that, still agree that even if this query seems to confirm that a DB can be dropped, definitely do the OFFLINE/detach or deny user access for some time, plus any due diligence of asking around before actually dropping!

select [name] from sys.databases 
where database_id > 4
AND [name] NOT IN 
(select DB_NAME(database_id) 
from sys.dm_db_index_usage_stats
where coalesce(last_user_seek, last_user_scan, last_user_lookup,'1/1/1970') > 
(select login_time from sysprocesses where spid = 1))
share|improve this answer

Another two alternatives are:

  1. Create triggers on the DB that will notify you (or store to tables) of any activity.
  2. Enable auditing on the DBs.

    • Depends on your DB version.
share|improve this answer

I've worked at a place that had a large number of orphaned and semi-orphaned databases. It was hard to tell if they really were orphaned as many tasks were seasonal or annual - so that website only runs for 3-4 months per year (as an example, W2 forms need to be electronically filed 1/31, so the website processing these only ran from mid January to the end of April).

What was done was a combination of:
* ask every developer if they were using some database or the other (these emails would go out monthly or whenever backups were taking too long).
* take the database offline and see who complains.
* rename the server to see who complains.

Since the pointy haired boss only was willing to allow "full and complete" documentation, a wiki was expressly forbidden, and staff reductions lead to a dramatic decline in documentation that met the standard.

If it were up to me, there would be a wiki page per server with contact names for each database (and maybe a brief description of what the database is for). Any database undocumented on the wiki would be fair game for deletion.

We had one large financial client that was still using SQL Server 2000 as late as 2009, so we had to keep one SQL Server 2000 instance running until that client finally moved to SQL Server 2005.

share|improve this answer

These are the methods I have used in the past:

  1. Take database offline/ Detach
  2. DENY user/login access
  3. Profiler trace

The trouble is this: how long do you wait before you are certain that nobody is going to access the data? For financial data, you have some items run daily, weekly, monthly, quarterly, semi-annual, and annual. But is a year long enough? I have also seen requests to have data kept available for at least 7 years, and in one case I was told that the data in one system needed to be there forever, even though nobody was using it.

The best advice is this: whatever you do to turn off access, make sure you can turn it back on immediately. I found that the detatch worked best for this. I would simply script out the reattach and instruct my team "if anyone ever asks where it is, run this script". That gave us the best chance to put things back as quickly as possible.

share|improve this answer
    
I've thought about that, whether the amount of time I monitor the database use was long enough. Do you just take the nature of the SQL Server and make that judgement call? –  jsauni Apr 5 '11 at 8:17
    
yeah, at some point you get everyone to agree to pull the plug, and to understand that if some rogue process fails you will need to put things back quickly. as long as they are OK with an outage, and you get things back online quickly, you should be fine. but it isn't easy to get everyone to agree! –  SQLRockstar Apr 5 '11 at 12:11

I agree with Nic with his advice. If you need to be sure, then you would have to go with Profiler (Service side trace) because some of the SQL queries won't be cached or for any reason the procedure cache could be purged.

I would normally check the virtual file stats information also to see if there are any reads or writes happening at the OS file level. Even if the database is NOT active you will still see a small reads/writes if you are taking log backups, full backups etc... but that will also give you an idea of read/write activity on that database.

Before dropping any database, I would make sure you have at-least 2 or 3 readable backups (test them) in separate locations. You never know when you need them.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.