Unused Databases, Last accessed DB

Question

Post reply

Unused Databases, Last accessed DB

mahisusan

Old Hand

Points: 308
More actions
March 30, 2009 at 7:12 am

#130235

Hi,
How do i find the unused databases or last accessed datetime of databases. I want to find it and drop all the databases which are not accessed for past 3 months.
Please help me.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

I assume you are talking about a wild, non-secure development box.

Shutdown the suspected databases, wait three month, if nobody comes crying foul take a full backup and drop it.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

mahisusan Old Hand Points: 308 More actions · Answer 2

😎

My Manager asked me to check and drop the databases which are not used for past 3 months.

How do i do it?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

Set up a profiler trace and filter it for the databases you need to drop and check whether you see any connections for these databases? Do this activity for good amount of time and then decide accordingly.

MJ

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 4

What I do on servers that I suspect the databases/logins are not being used is setup a DBManagement database. That gets populated with connection information every min by SQL Agent jobs so I can later run queries against the tables in DBManagement to see what is in use and what is not.

Not recommend on heavy used servers; as the job running every min would be too much performance impact; question is how often you think is enough?

Thanks.

Mohit.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

yehuda klein SSC Rookie Points: 33 More actions · Answer 5

In sql 2005/2008 and the service us running for a long time

you can run this script, and you can get an idia of wht is hapenning:

--time sql service started

select last_batch from sys.sysprocesses where spid=1

---

SELECT db_name(d.database_id),MAX(last_user_SCAN),MAX(last_user_Seek),MAX(last_user_lookup),max(last_user_update)

from sys.databases d

left join sys.dm_db_index_usage_stats i on d.database_id=i.database_id

group by db_name(d.database_id)

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

A somewhat non-intrusive method is to set the databases to 'AUTO CLOSE'.

Then all you have to do is scan the errorlog to see which ones are used !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Carlton Leach SSCarpal Tunnel Points: 4202 More actions · Answer 7

In SQL 2008 u could log/audit any statements run across the suspected DB's.

Or just.

You could use a trigger on the connection event.

But the auto-close idea could be a Q and D fix.

Carlton..

James O. SSC-Addicted Points: 472 More actions · Answer 8

yehuda klein (3/31/2009)
SELECT db_name(d.database_id),MAX(last_user_SCAN),MAX(last_user_Seek),MAX(last_user_lookup),max(last_user_update)
from sys.databases d
left join sys.dm_db_index_usage_stats i on d.database_id=i.database_id
group by db_name(d.database_id)

Thanks for this script yehuda; it gives me what I need. 🙂

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

rudy komacsar - Doctor "X" (3/31/2009)
A somewhat non-intrusive method is to set the databases to 'AUTO CLOSE'.
Then all you have to do is scan the errorlog to see which ones are used !

Best solution I've ever read on this forum.

However it assumes you don't need the hd space right away. In that case backup and send to dvds or where you can for quick recovery then drop from server. Just make sure you tell your boss that recovery time will be in the hours range rather than seconds (or ms with Rudy's solution).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

yehuda klein (3/31/2009)
SELECT db_name(d.database_id),MAX(last_user_SCAN),MAX(last_user_Seek),MAX(last_user_lookup),max(last_user_update)
from sys.databases d
left join sys.dm_db_index_usage_stats i on d.database_id=i.database_id
group by db_name(d.database_id)

NB. That DMV is flushed when the DB is started. Offline/online, detach/attach, close/open, restore, restart of SQL all will set the seeks and scans dates to null.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

francesco.mantovani SSC Eights! Points: 840 More actions · Answer 11

If the last_access column is null then no reads or writes have occurred:

WITH cte AS (
SELECT database_id, dt, op 
FROM sys.dm_db_index_usage_stats
UNPIVOT (dt for op in (last_user_lookup, last_user_scan, last_user_seek, last_user_update)) b)
SELECT d.name DB, MAX(c.dt) last_access, MAX(i.sqlserver_start_time) sqlserver_start_time, GETUTCDATE() captured
FROM sys.databases d 
LEFT JOIN cte c ON d.database_id=c.database_id
CROSS JOIN sys.dm_os_sys_info i
WHERE d.database_id>4
GROUP BY d.name 
ORDER BY d.name;

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 12

francesco.mantovani wrote:

If the last_access column is null then no reads or writes have occurred:

WITH cte AS (
SELECT database_id, dt, op 
FROM sys.dm_db_index_usage_stats
UNPIVOT (dt for op in (last_user_lookup, last_user_scan, last_user_seek, last_user_update)) b)
SELECT d.name DB, MAX(c.dt) last_access, MAX(i.sqlserver_start_time) sqlserver_start_time, GETUTCDATE() captured
FROM sys.databases d 
LEFT JOIN cte c ON d.database_id=c.database_id
CROSS JOIN sys.dm_os_sys_info i
WHERE d.database_id>4
GROUP BY d.name 
ORDER BY d.name;

Heh... especially in the first several milli-seconds or maybe even days after you've done a restart of the SQL Server Service or the box it's on. For that matter, you could have important databases that are used only once per month or even less.

I strongly recommend that if you use this method to determine if a database is still used or not that 1) you make damned sure that you have a viable, quickly restore-able backup that you've tested for restore-ability and 2) that your resume is up to date and 3) that you stand a chance of actually getting a job after you use this method to drop supposedly unused databases. 😉

Also... while I always appreciate someone adding to a more than a decade old thread with additional information, you might not be aware of the fact that this thread was last active more than a decade ago prior to your post on it.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)