| Author |
Topic |
|
kumarich1
Yak Posting Veteran
99 Posts |
 Posted - 2008-04-30 : 14:14:27
|
| How to determine if a database is being used by an application or not? I am trying to delete some databases which are not used by any application. Please advise how I can determine that a database is not being used by any application. I am thinking I can use profiler to find out this. Can any one advise any better method. Thanks in advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-30 : 14:15:30
|
| SQL Profiler, Activity Monitor in SSMS, sp_who/sp_who2, DateTime columns with default of GETDATE(), ...Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-04-30 : 15:22:10
|
| What are you trying to accomplish? Are you trying to just kill the connections to drop the database or are you trying to find out what applications are accessing your databases?If the former then who cares how many connections are in the database, just set it to single user mode before you drop the database:ALTER DATABASE <datbasename> SET SINGLE_USER WITH ROLLBACK IMMEDIATEDROP DATABASE <databasename>All this will do is force any connections on that database to be killed and allow your command to complete. |
|
|
|
kumarich1
Yak Posting Veteran
99 Posts |
Posted - 2008-04-30 : 15:59:53
|
| I am not trying to kill connections and drop database. Before dropping any database I want to check if any application is using this database. If there are any connections then it is confirmed that database is being used and I should not drop it , I think I can monitor this by using profiler.Thanks |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-30 : 17:32:32
|
| Monitor with SQL profiler . |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-04-30 : 21:33:02
|
Once you get to the point where you are fairly certain the database is not being used, set it offline for a few weeks before you drop it, and wait to see if anyone screams. You can bring it back online in a few seconds if it turns out it is being used.Or, you can just do it now and wait to see who screams.  alter database MyDatabase set offline with rollback immediate. CODO ERGO SUM |
|
|
|
arunsqladmin
Yak Posting Veteran
74 Posts |
Posted - 2008-05-01 : 03:47:37
|
| u can try to check the data in the tables.. there might be few databases which would have been created for testing or a logical backup and which do not have any user created at all using enterprise manager. |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-05-01 : 13:45:23
|
| Just monitoring connections made to a database may not catch it all. I do not think it will catch any query made from the context of another database using the 3 part naming qualifier of an object for example. If you suspect that a database can be taken out, I think Michael has the best suggestion. |
|
|
|
|
Database is being used