Using Extended Events To Determine Whether The Database Is Being Used

If you have consolidated your databases, you may want to identify and remove unused databases after some time. In such a case, we need to find out if there is a session in the database. We can find out if there is a session in the database by using the Extended Events.

Right click on Session in Management-> Extended Events on SSMS and click on the New Session Wizard.

On the next screen we give a name to the session as follows. I do not select start at event session at server startup. Because I don’t want this session to start automatically when the server starts. Click Next to proceed. On the next screen, select “Do not use template” and click next.

In the screen that appears, write “completed” in the “Event Library” section, and select the following events by double-clicking.

The selected events will be listed in the selected events section. Click Next to proceed.

On the next screen, select the following options.

• Client hostname
• Client app name
• Database name
• Sql text
• User name

On the next screen we will create the filter as below.

You may not see every condition in here, but you can see every condition after you create the extended event session.

You can filter by the following way after you create Extended Event Session.

Right-click on Session and click Properties. Then add filters by clicking Configure on Events.

If you want to create a filter after you create the Extended Events Session, you must set a separate filter for each of the selected events.

On the next screens, click next next and finish to complete the process. Then start the session as follows.

Right-click on the session and click “Watch live data” to see if there is a session in the database. If no records return, this means that the database is not being used. But to make sure the database is not being used, you need to keep this session open for a while. Keep in mind that an application may be running in only a certain period of the year.

If you want to filter in the result set, you can do this by right-clicking in the upper-left corner by clicking “Filter by this Value”.