{"id":386,"date":"2014-03-10T11:15:57","date_gmt":"2014-03-10T16:15:57","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=386"},"modified":"2014-03-10T11:15:57","modified_gmt":"2014-03-10T16:15:57","slug":"identify-sql-server-databases-that-are-no-longer-in-use","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/identify-sql-server-databases-that-are-no-longer-in-use\/","title":{"rendered":"Identify SQL Server databases that are no longer in use"},"content":{"rendered":"

I have come across this problem a few different times in my career. I’ll change jobs and they’ll be numerous database servers that I inherit that I know nothing about. It’s a process to learn what each server does, what applications use them, and what databases are no longer used and can be removed.<\/p>\n

There is no “tried and true” method to knowing if a database is truly no longer used, but I have three different suggestions that may help with your research. These suggestions are all based around capturing user connections.<\/p>\n

SQL Server User Connection Count<\/h2>\n

One suggestion to finding orphan databases is to get connection counts. In most cases, if a database has zero user connections over a long period of time, it may be time to look into removing this database. The following query will capture server name, database name, number of connections, and time the query was executed and it will also filter out system databases because they are needed:<\/p>\n

\n
SELECT @@ServerName AS server\r\n ,NAME AS dbname\r\n ,COUNT(STATUS) AS number_of_connections\r\n ,GETDATE() AS timestamp\r\nFROM sys.databases sd\r\nLEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid\r\nWHERE database_id NOT BETWEEN 1 AND 4\r\nGROUP BY NAME<\/pre>\n<\/div>\n
\"SQL<\/a><\/p>\n
I’m using a server named BUADMIN for this example. As you can see I have 3 active connections to the database SQLCensus. This is a good indication that this database is in use. MonitorDB and SSISDB have 0 connections, so I may need to monitor them further. The easiest way to monitor these databases is to create a stored procedure using this query so I can schedule it. You can also put this query directly into a SQL Server Agent Job and set a schedule.<\/p>\n
Before setting a schedule, you will need to create a table that will hold the results. To create a table using the following code:<\/p>\n
\n
SET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nCREATE TABLE [dbo].[Connections](\r\n [server] [nvarchar](130) NOT NULL,\r\n [name] [nvarchar](130) NOT NULL,\r\n [number_of_connections] [int] NOT NULL,\r\n [timestamp] [datetime] NOT NULL\r\n) ON [PRIMARY]\r\nGO<\/pre>\n<\/div>\n
Next, create a stored procedure that will INSERT the results into the table:<\/p>\n
\n
SET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nCREATE PROCEDURE usp_ConnectionsCount \r\nAS\r\nBEGIN\r\n SET NOCOUNT ON;\r\nINSERT INTO Connections \r\n  SELECT @@ServerName AS server\r\n ,NAME AS dbname\r\n ,COUNT(STATUS) AS number_of_connections\r\n ,GETDATE() AS timestamp\r\nFROM sys.databases sd\r\nLEFT JOIN master.dbo.sysprocesses sp ON sd.database_id = sp.dbid\r\nWHERE database_id NOT BETWEEN 1\r\n  AND 4\r\nGROUP BY NAME\r\nEND<\/pre>\n<\/div>\n
Once the stored procedure is created you can create a SQL Server Agent Job and set it to run on a schedule. I’ll set it to run every 10 minutes.<\/p>\n
Let this run a few days, a few months or however long you think is appropriate and then go back and examine the results. Once you are happy with the timeframe chosen, use the following query to select the MAX number of connections per database:<\/p>\n
\n
SELECT NAME\r\n ,MAX(number_of_connections) AS MAX#\r\nFROM Connections\r\nGROUP BY NAME<\/pre>\n<\/div>\n
\"SQL<\/a><\/p>\n
From here you will be able to determine if any databases have not had a user connection in the timeframe specified.<\/p>\n
Detailed SQL Server Connection Information<\/h2>\n
The above suggestion is good if you just need connection counts. However, sometimes a count isn’t good enough. Sometimes you need to know exactly what is connecting. This suggestion helps in that aspect.<\/p>\n
It’s basically setup the same way, create a stored procedure, insert data into a table, set a schedule and examine the results.<\/p>\n
The following query gives you more information:<\/p>\n
\n
SELECT @@ServerName AS SERVER\r\n ,NAME\r\n ,login_time\r\n ,last_batch\r\n ,getdate() AS DATE\r\n ,STATUS\r\n ,hostname\r\n ,program_name\r\n ,nt_username\r\n ,loginame\r\nFROM sys.databases d\r\nLEFT JOIN sysprocesses sp ON d.database_id = sp.dbid\r\nWHERE database_id NOT BETWEEN 0\r\n  AND 4\r\n AND loginame IS NOT NULL<\/pre>\n<\/div>\n
\"SQL<\/a>
\nClick here to view the rest of this post.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
I have come across this problem a few different times in my career. I’ll change jobs and they’ll be numerous database servers that I inherit that I know nothing about. It’s a process to learn what each server does, what applications use them, and what databases are no longer used and can be removed. There […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[87],"tags":[88,105],"class_list":["post-386","post","type-post","status-publish","format-standard","hentry","category-auditing-and-compliance","tag-logon-trigger","tag-monitoring"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/386","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=386"}],"version-history":[{"count":1,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/386\/revisions"}],"predecessor-version":[{"id":390,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/386\/revisions\/390"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=386"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=386"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=386"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}