Wednesday, November 12, 2014

Who is using this database? Is it in use at all?

This is a question we get as SQL Server DBAs all the time:  is this database in use?  Most of us are in the habit of putting this question back on the application owners, or of using a simple scream test to see if it’s important to anyone. 
Until SQL Server 2008, there weren’t a lot of great options:  triggers would work, but they have a lot of downsides, and they’re reasonably resource-intensive for busy servers.
Extended events offer a much better solution, and we’ll use this question as a way to ease into their use; this will be a beginner’s introduction to SQL Server extended events (on both SQL 2008 R2 and SQL 2012).  We’ll end with a pretty cool (and portable) solution to the question, “is this database in use?”