How to check if a database is being used?????
-
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
-
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
-
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
-
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
-
dean_vr (7/15/2010)
list of dbs in use since last rebootselect 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.
-
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
-
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
-
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
-
Activity Monitor Is Anotherone
-
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 DISTINCTdatabase_name = DB_NAME(resource_database_id)FROM sys.dm_tran_locks TLJOIN sys.dm_exec_sessions SON S.session_id = TL.request_session_idWHERE 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;
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply