SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Check if someone use a database or not

By Robin Back,

I manage quite a few hundred databases across the company. Time to time I get a question if I can check wheather a database is beeing used or not, and if it is, by whom?

There are probably a 1000 ways to do this, but I've created a script for creating a scheduled job that runs in tempdb and checks the database i want to know about. I've rewritten it a bit, since I also had a part that send a signal to our ControlCenter when someone used the database (this is skipped in this script).

The script creates three objects. One table and one stored procedure on tempdb and a scheduled job that the script start automaticlly.

It's written so that you can check more then 1 database on a server at the same time.

If either the sproc or the table created in tempdb already exists when the script is run, it will delete them without notice. The scheduled job created will not delete the objects in tempdb, since this will be deleted whenever the sql-server is restarted.
You can then modify the created job to report to you or someone else whenever your criteria has been met. Use for example net send operator or just send a mail.

Good luck!

Total article views: 2571 | Views in the last 30 days: 20
Related Articles

script for Checking database online or not

script for Checking database online or not


How to change TempDB system Database files location?

Steps to change TempDB system Database files location :- 1) Check current file location of TempDB ...


Should I check tempdb for corruption?

You all know that checking our databases for corruption regularly is a must. But what about tempdb? ...





TempDB database is Full and Optimization

What is TempDB and best practice for TempDB TempDB is the system database and it is per instance. I...