-> There are two SQL Server Agent jobs scheduled. Job1 has 6 steps and Job2 as 1 step.

-> Job1 details below,

Job Step	Step Details
Step1	Changes Maxdop to 0.
Step2	Starts Job2.
Step3	Run reindexing for 3 Indexes.
Step4	Checks if Job2 is completed and waits for the Job2 to complete before proceeding to next step.
Step5	Changes Maxdop to 1.
Step6	Executes TSQL to create views.

-> Job2 details below,

Job Step	Step Details
Step1	Run reindexing for 10 Indexes

-> Step4 of Job1 executes below TSQL that checks if Job2 has completed or not and wait for Job2 to complete.

DECLARE @JOB_NAME SYSNAME = N'JOBNAME';
DECLARE @JOB_NAME1 SYSNAME = N'JOBNAME';

Job_Status:
IF NOT EXISTS(
select 1
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
where
activity.run_Requested_date is not null
and activity.stop_execution_date is null
and job.name IN (@JOB_NAME,@JOB_NAME1)
)
BEGIN
PRINT 'Job not running'
END
ELSE
BEGIN
PRINT 'Job is running';
waitfor delay '00:01:00'
goto Job_Status;
END

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

-> Data archival on a SQL Server database is a common activity that needs to be performed by a SQL Server database administrator.

-> A report that contains before and after row count post Data Archival with the row count difference will be of much help.

-> Download  Archival_Report_Job.sql and execute the query using SQL Server Management Studio on database server where Archiving will be performed.  Remember to change the database name to appropriate database where Archiving will be performed in the downloaded script. This will create a job called Archival_Report and the job can be found within the jobs folder under SQL Server Agent. This will be an one time activity and should be performed on database server during initial setup.

-> The first step on job Archival_Report inserts data related to table size into table Archival_Table_Details.

-> The second step on job Archival_Report deletes data older than 7 years from table Archival_Table_Details.

-> SQL Server agent job “Archival_Report” should be executed before the start of data archival either manually or by executing below code from application scheduler agent,

sqlcmd -SSQLServerInstance -E -Q"Exec msdb..sp_start_job N'Archival_Report'"

-> Wait for data archival to complete. Once the Data Archival is complete. SQL Server agent job “Archival_Report” should be executed once again either manually or by executing below code from application scheduler agent,

sqlcmd -SSQLServerInstance -E -Q"Exec msdb..sp_start_job N'Archival_Report'"

-> Execute below query on the context of Archived database on database server where Archiving was performed. Remember to change the database name to appropriate database where Archiving will be performed in the below script. Archival report will be displayed in the output tab as part of query window in SQL Server Management Studio.

use [JBDB]
GO
;with Archival_Report_CTE as (
select TableName, [Time],[# Records],[Table_used_Space GB],row_number() Over(Partition by TableName order by Time DESC ) RowNumber
from Archival_Table_Details)

Select
Max(Case when RowNumber=1 then Cast([Time] as date) else null End) Time,
TableName
,Max(Case when RowNumber=2 then [# Records] else null End) [Before # Records]
,Max(Case when RowNumber=2 then [Table_used_Space GB] else null End) [Before_Table_used_Space GB]
, Max(Case when RowNumber=1 then [# Records] else null End) [After # Records]
, Max(Case when RowNumber=1 then [Table_used_Space GB] else null End) [After_Table_used_Space GB]
,Max(Case when RowNumber=2 then [# Records] else null End)-Max(Case when RowNumber=1 then [# Records] else null End) [# Records Difference]
From Archival_Report_CTE
where RowNumber<2 and TableName NOT LIKE '%Archival_Table_Details%'
Group by TableName

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

-> It becomes mandatory to find which databases are being used on a SQL Server instance when planning for below,

<> SQL Server Migration.
<> Consolidating several SQL Server instances to fewer.
<> Downsizing a Database Server and much more.

->  There are many ways this can be achieved. The best way according to me is to use profiler trace and follow below procedure,

-> Schedule the below script as a SQL agent job around 12:00 AM on each server that requires monitoring,

-- Start trace

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1024
declare @filename nvarchar(255)
set @filename = '\\SERVER1\JBS$\SERVERNAME\'
declare @filename1 nvarchar(255)
set @filename1 = 'JBS_DB_In_use_Trace'+convert(varchar(25),getdate())
set @filename1 = REPLACE(@filename1,' ','_')
set @filename1 = REPLACE(@filename1,':','_')
set @filename1 = @filename + @filename1

if exists(select * FROM ::fn_trace_getinfo(default) where CONVERT(sysname, value) like '%'+@filename+'%')
begin
goto error
end
else
begin
exec @rc = sp_trace_create @TraceID output, 2, @filename1, @maxfilesize, NULL
if (@rc != 0) goto error
end

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1024
exec sp_trace_setevent @TraceID, 10, 8, @on
--exec sp_trace_setevent @TraceID, 10, 1, @on --Textdata
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
--exec sp_trace_setevent @TraceID, 12, 1, @on --Textdata
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 15, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 1c3d4c0b-4415-4b1f-9cea-3bff55b961bc'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'master'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'msdb'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'model'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'tempdb'
exec sp_trace_setfilter @TraceID, 35, 0, 1, NULL
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
Go

-> Schedule the below script as a sql agent job at 11:59:59 PM on each server that requires monitoring.

--Stop Trace
declare @TraceID int
declare @filename nvarchar(255)
set @filename = '\\SERVER1\JBS$\SERVERNAME\'
set @TraceID = (select TraceID FROM ::fn_trace_getinfo(default) where CONVERT(sysname, value) like '%'+@filename+'%')
if (@TraceID IS NOT NULL)
begin
EXEC sp_trace_setstatus @traceid = @TraceID, @status = 0;
EXEC sp_trace_setstatus @traceid = @TraceID, @status = 2;
end

-> Allow this trace to run for appropriate days. In my case it was run for 40 days.

-> Once the traces are run for appropriate days. Disable/Delete the two (2) jobs from all servers that was previously created.

-> Copy created trace files to Test/Development server for processing the trace files.

-> Insert data onto table from the created trace files using below command,

SELECT HostName,ApplicationName,LoginName, SPID,EndTime, Databasename Into [JB_TraceTable]  FROM fn_trace_gettable('\JBS\\JBS_DB_In_use_TraceMar_15_2018__12_00AM.trc', default)

insert into [JB_TraceTable] SELECT HostName,ApplicationName,LoginName, SPID,EndTime, Databasename FROM fn_trace_gettable('\JBS\\JBS_DB_In_use_TraceMar_16_2018_12_00AM.trc', default)
insert into [JB_TraceTable] [JB_TraceTable] SELECT HostName,ApplicationName,LoginName, SPID,EndTime, Databasename FROM fn_trace_gettable('\JBS\\JBS_DB_In_use_TraceMar_17_2018_12_00AM.trc', default)
insert into [JB_TraceTable] SELECT HostName,ApplicationName,LoginName, SPID,EndTime, Databasename FROM fn_trace_gettable('\JBS\\JBS_DB_In_use_TraceMar_18_2018_12_00AM.trc', default)
.
.
.
insert into [JB_TraceTable] SELECT HostName,ApplicationName,LoginName, SPID,EndTime, Databasename FROM fn_trace_gettable('\JBS\\JBS_DB_In_use_TraceApr_27_2018_12_00AM.trc', default)

-> Once the data is loaded into the table. Use below query to get the contents from the loaded table,

select distinct Applicationname,databasename,loginname from [JB_TraceTable] where loginname='login' order by databasename

-> This uses a lightweight server side trace and doesn’t collect unnecessary data.

-> All rows returned indicates in the output indicates what databases are being used on each of the server. Databases not in the output are databases that are not used within the time frame the traces were run.

-> Remember to check the output for Applicationname and Loginname. SQL Server agent job for database maintenance such as backups, Index maintenance and integrity checks should not be considered as user queries.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.