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
