Check if someone use a database or not

Total article views: 1532 | Views in the last 30 days: 63

 Rate this |   Join the discussion |  Briefcase

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: 1532 | Views in the last 30 days: 63