I recently had to track down a rogue process that was failing to log in on one of my servers. It took a bit of fiddling, but eventually I found that you can trace login failed due to missing database errors with Profiler. The trick is to look for User Error Messages where TextData Like ‘Cannot open database%’.
Here are the steps if you want to try this yourself:
1. Open Profiler
2. Specify a server name
3. Use the Blank template
4. Place a check in the box next to User Error Message in the Errors and Warnings group
5. Scroll right and click the column header for TextData
6. Expand Like and type Cannot open database%
7. Click Run
Once you have the trace up and running you just have to wait for the process to try to log in again and you will have plenty of information to go do some finger pointing. If you are like me and like to test your traces to make sure they are working or just want to try this out before you need it in real life, here is a PowerShell script to simulate the failures:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | cls [string] $serverName $serverName = '<server_name>' #Make sure to enter your server name here $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=$serverName;Database=$databaseName;Integrated Security=True;Network Library=dbmssocn;" $SqlConnection.close() $SqlConnection |
I hope you find this information useful. Please make sure you are comfortable with the process described above before trying it anywhere important. The steps above have worked well for me but your results may vary. I offer no warranty beyond a sympathetic ear should you run into trouble.
