if a database in use

Question

if a database in use

sqlfriend

SSC Guru

Points: 52781
More actions
April 1, 2011 at 12:08 pm

#236190

Is there a way to find out if a database is no longer used for some period of time?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 1

annasql (4/1/2011)
Is there a way to find out if a database is no longer used for some period of time?
Thanks

Run profiler for 3 to 4 business days. Make sure u use the db_name or dbid for filter and then look for any connections.

HTH,

Thanks

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 2

annasql (4/1/2011)
Is there a way to find out if a database is no longer used for some period of time?
Thanks

Run profiler for 3 to 4 business days. Make sure u use the db_name or dbid for filter and then look for any connections.

HTH,

Thanks

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

Is there an easier way, for example to see some modified date some where to check?

Lowell SSC Guru Points: 323518 More actions · Answer 4

you can use some DMV's to see what has been used since the last time the server was started:

if it's not on the lsit, it has not been accessed...but be careful... pay attention to the dates...try taking the database offline for a few weeks before you ever really drop it. some databases are not necessarily accessed every day...think end of month reports, people on vacation and maternity leave, etc.

--modified (barely) from a post by Gianluca Sartori

--http://www.sqlservercentral.com/Forums/Topic1086402-391-1.aspx#bm1086799

DECLARE @accessStats TABLE (

db_name sysname,

last_access datetime

)

INSERT @accessStats

EXEC sp_msForEachDb '

SELECT ''?'', MAX(last_access) AS last_access

FROM (

SELECT DB_NAME() AS db_name,

MAX(last_user_seek) AS last_user_seek,

MAX(last_user_scan) AS last_user_scan,

MAX(last_user_lookup) AS last_user_lookup,

MAX(last_user_update) AS last_user_update

FROM [?].sys.dm_db_index_usage_stats AS s

) AS src

UNPIVOT (

last_access

FOR access_type IN (

last_user_seek,

last_user_scan,

last_user_lookup,

last_user_update)

) as u

GROUP BY db_name

'

SELECT

stat.*,

ServerRestarted.ServerRebootedOn

FROM @accessStats stat

CROSS JOIN

(SELECT

CREATE_DATE As ServerRebootedOn

FROM sys.databases

WHERE name='tempdb') ServerRestarted

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

Thanks, I will give it a try.

And I will pay attention to other factors that databases may not be used for some time.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 6

MysteryJimbo

SSC-Insane

Points: 24203

April 3, 2011 at 7:07 am

#1306810

Switch it to offline and see who shouts? 🙂

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

I would first start with enabling auto close for that database and see when it goes offline and if it comes back online.

Of course you need to take into account your backup jobs or your admin stuff that may need an open database.Double check these are disabled for your db.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me