How to check if a database is being used?????

Question

Post reply

How to check if a database is being used?????

Viewing 15 posts - 16 through 30 (of 32 total)

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

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

You could use a server level DDL trigger in combination with a database set AUTO_CLOSE on.

(check BOL DDL Statements with Server Scope (ALTER_DATABASE) )

and just register if it is being opened / closed (you may even find out Who/What is opening it.

I cannot find i right now, but I think you can also use sqlserver events to capture a db-open event.

First check it in a DEV environment, to see what effects it causes !!

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Don't see an open event, but there is an event that snags object access:

http://msdn.microsoft.com/en-us/library/ms175013%28v=SQL.100%29.aspx

tony.turner Right there with Babe Points: 783 More actions · Answer 3

At the Windows level, rather than the SQL level, it is possible to do auditing by file. You would be able to track Open/Close, and view the results in your Windows Security Event Log. Could expand on the subject if you need additional detail

Angelindiego SSCertifiable Points: 6048 More actions · Answer 4

expand away....grasshopper!!

Thank you!!,

Angelindiego

dean_vr SSC Rookie Points: 27 More actions · Answer 5

list of dbs in use since last reboot

select a.name, a.dbid, max(last_user_seek), max(last_user_scan)

from sys.sysdatabases a

left outer join sys.dm_db_index_usage_stats b on a.dbid = b.database_id

group by a.name, a.dbid

Angelindiego SSCertifiable Points: 6048 More actions · Answer 6

Angelindiego

SSCertifiable

Points: 6048

July 15, 2010 at 8:25 am

#1193515

SWEET!! Thank you!!

Thank you!!,

Angelindiego

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

dean_vr (7/15/2010)
list of dbs in use since last reboot
select a.name, a.dbid, max(last_user_seek), max(last_user_scan)
from sys.sysdatabases a
left outer join sys.dm_db_index_usage_stats b on a.dbid = b.database_id
group by a.name, a.dbid

That may be of limited value if you have regular maintenance jobs for a database, like DBCC checks or reindexing. However, you can see if there has been any access since the last job ran.

Angelindiego SSCertifiable Points: 6048 More actions · Answer 8

this works for me and thank you again!!

Thank you!!,

Angelindiego

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 9

Hi

You can also use Performance Dashboards reports and check I/O statistics of the databases to check the number of reads and writes for the databases.

Thank You,

Best Regards,

SQLBuddy

Angelindiego SSCertifiable Points: 6048 More actions · Answer 10

can you give me more details on your suggestion please???? thank you!!

Thank you!!,

Angelindiego

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 11

when i run the script i get:

Warning: Null value is eliminated by an aggregate or other SET operation.

what does that mean ?

thanks

Jim

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 12

Hi

Check these for installing and using SQL Server Performance Dashboard reports

http://www.mssqltips.com/tip.asp?tip=1553

http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p1.aspx

Thank You,

Best Regards,

SQLBuddy

Angelindiego SSCertifiable Points: 6048 More actions · Answer 13

Angelindiego

SSCertifiable

Points: 6048

July 15, 2010 at 12:23 pm

#1193699

Yeah! Again, Thank you!!!

Thank you!!,

Angelindiego

kamran85 Newbie Points: 8 More actions · Answer 14

kamran85

Newbie

Points: 8

July 17, 2010 at 1:53 am

#1194479

Activity Monitor Is Anotherone

Paul White SSC Guru Points: 150467 More actions · Answer 15

It's not exactly what you need, but interesting and handy anyway:

My favourite way to see which databases are in use by any user sessions (even if they just have it as the context database, without running anything):

SELECT DISTINCT

database_name = DB_NAME(resource_database_id)

FROM sys.dm_tran_locks TL

JOIN sys.dm_exec_sessions S

ON S.session_id = TL.request_session_id

WHERE TL.resource_type = N'DATABASE'

AND TL.request_mode = N'S'

AND TL.request_owner_type = N'SHARED_TRANSACTION_WORKSPACE'

AND S.is_user_process = 1;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi