Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: 2562 | Views in the last 30 days: 22
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? ...





Script to check for last updates in database objects

Script to check for last updates in database objects


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones