To find SQL Server is actually used or not?

Posted on

Question :

I have an SQL Server 2012 and a 2008R2 running in my environment. I need to decommission the server if the server is not actually being used.

I did go through this and this where connection count is one of the parameters to check the same, and I did run that and the result was 0 but is that concrete enough for to me do away with this server? Thing is I do not have any ownership details of this server to even proceed further, so I will have to see in the server itself.

There are chances the database could have been restarted sometime back, which I’m not sure. Auditing and logon triggers have been disabled at present. Should I enable them? If yes, which auditing specifically I will have to enable? And what logon trigger?

There aren’t many databases in the instance, about 3-4 only.
What can be done to come to a concrete conclusion to stop this instance from running?

Answer :

SQL Server can log failed logins, successful logins, both or none to the SQL server log. Check your current setting and if it is not collecting both failed and successful logins change it so that it is. Once it’s been running that way for a few weeks (preferably over a month end & quarter end) check to see if there has been any activity. If no one is logging in then you can run a final backup of your databases (you should probably include the master and msdb databases as well as the user databases). Move those backups to long term storage and shut down the instance. If no one complains after a while then you can de-com.

Under the server properties:

enter image description here

Log the connections to a database using a SQL Server Agent job

Auditing successful and failed logins works to some degree–but those audits don’t tell you what database for which the user is trying to connect (or much anything else). I prefer logging more details than that by gleaning information from sysprocesses that can give you the database, user, application name, hostname and other more valuable information.

I haven’t used this code in a while, but this should suit your needs with perhaps a little tailoring. It monitors each connection to any database on a per minute basis/24×7 by spid, database, user, application and hostname. If any of those variables changes, it will log a new entry. What I didn’t want to happen is have connection bloat by having a bunch of connections logged that was just a continuation of the same connection/spid. I created a database dbadmin to put all this in–so change this, in addition to anything else, if you need to. I wish you the best in resolving your issue.

use dbadmin
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DBAccess]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DBAccess]
GO

CREATE TABLE [dbo].[DBAccess] (
    [dbname] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [hostname] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [program_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [loginame] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [spid] int  null,
    [last_batch] datetime null,
    [insertTime] [datetime] NULL DEFAULT GETDATE()
) ON [PRIMARY]
GO


USE [msdb]
GO

/****** Object:  Job [_DBAccess]    Script Date: 5/5/2016 11:07:31 AM ******/
EXEC msdb.dbo.sp_delete_job @job_name=N'_DBAccess', @delete_unused_schedule=1
GO

/****** Object:  Job [_DBAccess]    Script Date: 5/5/2016 11:07:31 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 5/5/2016 11:07:31 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'_DBAccess', 
        @enabled=1, 
        @notify_level_eventlog=2, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Run TSQL]    Script Date: 5/5/2016 11:07:31 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run TSQL', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=1, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'insert  into DBAccess(dbname, hostname, program_name, loginame, spid, last_batch)
    select distinct db_name(dbid)as dbname,hostname, program_name, loginame, spid, last_batch from master..sysprocesses  s where loginame not in(''sa'') and
    not exists(select * from dbaccess where last_batch=s.last_batch and spid = s.spid and dbname=db_name(s.dbid) and hostname= s.hostname and program_name=s.program_name and loginame = s.loginame )', 
        @database_name=N'dbadmin', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run Every Minute', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=4, 
        @freq_subday_interval=1, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20160122, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

There aren’t many databases in the instance, about 3-4 only. What can be done to come to a concrete conclusion to stop this instance from running?

You can use a server side trace and load the data into table

(Full script with server side trace, table creation and load data from trace can be found on my gist.github)

/**************************************************************************************
Author:     KIN SHAH
Date    :   10/10/2015
Adapt the script as per your env --> places to change ---- CHANGE HERE !!
This script will create 
    - a trace file that will rollover after 1 hr
    - the trace file will capture apps/users/host machines that are connecting using "SA" account as well as
    - can be used for server migrations - as it will capture who is connecting to your database server instance.
Disclaimer
The views expressed on my posts on this site are mine alone and do not reflect the views of my company. All posts of mine are provided "AS IS" with no warranties, and confers no rights.

The following disclaimer applies to all code, scripts and demos available on my posts:

This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. 

I grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: 

(i)     to use my name, logo, or trademarks to market Your software product in which the Sample Code is embedded; 
(ii)    to include a valid copyright notice on Your software product in which the Sample Code is embedded; and 
(iii)   to indemnify, hold harmless, and defend me from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
************************************************************************************/




/****** Object:  StoredProcedure [dbo].[usp_profiler_trace_sa]    Script Date: 10/10/2015 3:29:40 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO



CREATE procedure [dbo].[usp_profiler_trace_sa]
as
 --04232013 Kin : Capture login, hostname, appname activities for migrating the server ....
 --             : Capture sa login activities. Keep trace files in /trace/server_name/test folder 
set nocount on

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
declare @FilePath varchar(256)
declare @FileName nvarchar(128)
declare @Duration int
declare @Servername varchar(128)
declare @instance_name varchar(128)
declare @Drive varchar(1)
declare @path varchar(200)

set @Servername = replace(@@SERVERNAME, '', '$')
-- Set trace time in minutes
set @Duration = 61
set @DateTime = dateadd(minute,@Duration, getdate())
-- Set filepath and filename to save
set @FilePath = 'D:trace' --- CHANGE HERE !!
print @FilePath
-- Check if trace path exists, and create trace path if not exists
declare @result int, @result2 int
declare @SQLText varchar(8000)
set @SQLText = 'cd '+@FilePath
exec @result = master..xp_cmdshell @SQLText,no_output
if @result <> 0 -- backup path does not exist
begin
    set @SQLText = 'mkdir '+@FilePath
    exec @result2 = master..xp_cmdshell @SQLText,no_output
    if @result2 <> 0
    begin
        RAISERROR ('Failed to create trace directory', 16, 1)
    end
end
--create specific folder for each instance under trace folder
select @instance_name = CONVERT(nvarchar(100), SERVERPROPERTY('instancename'))
select @path = case when @instance_name is null then @FilePath else @FilePath+@instance_name+'' end
select @SQLText = 'cd '+@path
exec @result = master..xp_cmdshell @SQLText, no_output
if @result <> 0 -- path does not exist
begin
    set @SQLText = 'mkdir '+@path
    exec @result = master..xp_cmdshell @SQLText, no_output
    if @result <> 0 
        RAISERROR('Failed to create trace instance directory', 16, 1)
end
--create "test" under instance folder
select @instance_name = 'test'  --- CHANGE HERE !!
select @path = @path+@instance_name
select @SQLText = 'cd '+@path
exec @result = master..xp_cmdshell @SQLText, no_output
if @result <> 0 -- path does not exist
begin
    set @SQLText = 'mkdir '+@path
    exec @result = master..xp_cmdshell @SQLText, no_output
    if @result <> 0 
        RAISERROR('Failed to create test directory', 16, 1)
end
set @FileName = @path + @Servername + '_' +replace(replace(replace(convert(varchar(20),getdate(),120),':',''),' ',''),'-','')
-- Set maximum file size
set @maxfilesize = 5000  --- CHANGE HERE if you want a larger size, but its not advisable !!
exec @rc = sp_trace_create @TraceID output, 0, @FileName, @maxfilesize, @DateTime
print @rc
if (@rc != 0) 
    raiserror ('Failed to run profiler' , 1, 16)
declare @on bit
set @on = 1
declare @events varchar(1000),@eleft int, @eright int
declare @columns varchar(1000), @cleft int, @cright int
declare @eventid int, @columnid int
-- Set events to trace
--set @events = '10,12,14,15,17,19,25,33,34,35,36,37,38,53,55,59,60,61,67,69,79,80,81'
set @events = '11,13,16,19,33,37,61,162'
-- Set columns to record
--set @columns = '1,3,6,8,9,10,11,12,13,14,15,16,17,18,21,22,24,27,28,30,31,34,35,40'
set @columns = '1,6,8,10,11,12,14,27,35,40'
set @eleft = 1
set @eright = charindex(',',@events)
while @eleft > 0
begin
    set @cleft = 1
    set @cright = charindex(',',@columns)
    set @eventid = convert(int,substring(@events, @eleft, @eright - @eleft))
    while @cleft > 0
    begin
        set @columnid = convert(int,substring(@columns, @cleft, @cright - @cleft))

        --print str(@eventid) + ' , ' + str(@columnid)
        exec sp_trace_setevent @TraceID, @eventid, @columnid, @on
        if ((@cright + 1) > len(@columns)) begin set @cleft = 0 end else set @cleft =@cright + 1
        if (charindex(',',@columns,@cleft) > 0) begin set @cright = charindex(',',@columns,@cleft) end else set @cright = len(@columns)+1
    end
    if ((@eright + 1) > len(@events)) begin set @eleft = 0 end else set @eleft =@eright + 1
    if (charindex(',',@events,@eleft) > 0) begin set @eright = charindex(',',@events,@eleft) end else set @eright = len(@events)+1
end
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
--only filter sa session
--exec sp_trace_setfilter @TraceID, 11, 0, 0, N'sa'
--Database name is not null
exec sp_trace_setfilter @TraceID, 35, 0, 1, NULL
/*
reference: 
http://msdn.microsoft.com/en-us/library/ms174404.aspx
http://msdn.microsoft.com/en-us/library/ms186265.aspx
*/
exec sp_trace_setstatus @TraceID, 1
select TraceID=@TraceID
set nocount off
GO

Leave a Reply

Your email address will not be published.