MSDB Clean up

MSDB Clean up

-- Declare Local Variables 
declare @mindate datetime, 
	@oldest_date datetime, 
	@sql varchar(8000), 
	@currdate datetime, 
	@oldestdate datetime 
	-- Find out the oldest date from the Backup Set table 

select @mindate = min(backup_start_date) from msdb..backupset  

set @currdate = @mindate + 7  
set @oldestdate = '2013-09-01 00:00:00.000' 
-- Modify this to the date till which you want your msdb history purged 

-- Begin a while loop to generate the commands to purge the MSDB entries 

while (@currdate <= @oldestdate)  
begin 
set @sql = 'EXEC msdb..sp_delete_backuphistory ''' + 
	cast(@currdate as varchar(20)) + ''''  
print @sql  

set @sql = 'EXEC msdb..sp_purge_jobhistory @oldest_date = ''' + 
	cast(@currdate as varchar(20)) + '''' 
print @sql  
print char(13)  

-- Optional if you are running out of space in MSDB 
--print 'use msdb' + char(13) + 'checkpoint' 
-- Increment value and move on to the next date 
set @currdate = @currdate + 7 
-- The time interval can be modified to suit your needs end 
end 
-- End of while loop 

set @sql = 'EXEC msdb..sp_delete_backuphistory ''' + 
	cast(@oldestdate as varchar(20)) + '''' 
print @sql  
set @sql = 'EXEC msdb..sp_purge_jobhistory @oldest_date = ''' + 
	cast(@oldestdate as varchar(20)) + '''' 
print @sql

Source

Cheers!

Sanjay

 

 

 

 

Currently blocking transactions

Currently blocking transactions

SELECT r.session_id                                AS spid, 
       r.cpu_time, 
       r.reads, 
       r.writes, 
       r.logical_reads, 
       r.blocking_session_id                       AS BlockingSPID, 
       LEFT(Object_name(st.objectid, st.dbid), 50) AS ShortObjectName, 
       LEFT(Db_name(r.database_id), 50)            AS DatabaseName, 
       s.program_name, 
       s.login_name, 
       Object_name(st.objectid, st.dbid)           AS ObjectName, 
       Substring(st.text, ( r.statement_start_offset / 2 ) + 1, ( 
       ( CASE r.statement_end_offset 
       WHEN -1 THEN Datalength(st.text) 
       ELSE r.statement_end_offset 
                                                                   END - 
         r.statement_start_offset ) / 2 ) + 1)     AS SQLText 
FROM   sys.dm_exec_requests r 
       JOIN sys.dm_exec_sessions s 
         ON r.session_id = s.session_id 
       CROSS apply sys.Dm_exec_sql_text (sql_handle) st

Source : Unknown

Cheers!
Sanjay

 

SQL Agent Job failures Review

SQL Agent Job failures

--Variable Declarations 
DECLARE @PreviousDate datetime 
DECLARE @Year VARCHAR(4) 
DECLARE @Month VARCHAR(2) 
DECLARE @MonthPre VARCHAR(2) 
DECLARE @Day VARCHAR(2) 
DECLARE @DayPre VARCHAR(2) 
DECLARE @FinalDate INT 

-- Initialize Variables 
SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days  
SET @Year = DATEPART(yyyy, @PreviousDate)  
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate)) 
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2) 
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate)) 
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) 
SET @FinalDate = CAST(@Year + @Month + @Day AS INT) 

-- Final Logic 
SELECT   j.[name], 
         s.step_name, 
         h.step_id, 
         h.step_name, 
         h.run_date, 
         h.run_time, 
         h.sql_severity, 
         h.message, 
         h.server 
FROM     msdb.dbo.sysjobhistory h 
         INNER JOIN msdb.dbo.sysjobs j 
           ON h.job_id = j.job_id 
         INNER JOIN msdb.dbo.sysjobsteps s 
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_status = 0 -- Failure 
         AND h.run_date > @FinalDate 
ORDER BY h.instance_id DESC

Source:

Cheers!

Sanjay

Unreplicated transaction count

With MaxXact (ServerName, DistAgentName, PublisherDBID, XactSeqNo)
 As (Select S.name, DA.name, DA.publisher_database_id, max(H.xact_seqno)
 From distribution.dbo.MSdistribution_history H with(nolock)
 Inner Join distribution.dbo.MSdistribution_agents DA with(nolock) 
	On DA.id = H.agent_id
 Inner Join master.sys.servers S with(nolock) 
	On S.server_id = DA.subscriber_id
 Group By S.name, DA.name, DA.publisher_database_id)
 Select MX.ServerName, MX.DistAgentName, MX.PublisherDBID, 
	COUNT(*) As TransactionsNotReplicated
 From distribution.dbo.msrepl_transactions T with(nolock)
 Right Join MaxXact MX On MX.XactSeqNo < T.xact_seqno 
	And MX.PublisherDBID = T.publisher_database_id
 Group By MX.ServerName, MX.DistAgentName, MX.PublisherDBID;

With MaxXact (ServerName, DistAgentName, PublisherDBID, XactSeqNo)
 As (Select S.name, DA.name, DA.publisher_database_id, max(H.xact_seqno)
 From distribution.dbo.MSdistribution_history H with(nolock)
 Inner Join distribution.dbo.MSdistribution_agents DA with(nolock) 
	On DA.id = H.agent_id
 Inner Join master.sys.servers S with(nolock) 
	On S.server_id = DA.subscriber_id
 Group By S.name, DA.name, DA.publisher_database_id)
 Select MX.ServerName, MX.DistAgentName, MX.PublisherDBID, 
	COUNT(*) As CommandsNotReplicated
 From distribution.dbo.MSrepl_commands C with(nolock)
 Right Join MaxXact MX On MX.XactSeqNo < C.xact_seqno And 
	MX.PublisherDBID = C.publisher_database_id
 Group By MX.ServerName, MX.DistAgentName, MX.PublisherDBID;

Source:

Cheers!
Sanjay

 

Index defragmentation Maintenance

Index Defragmentation Maintenance:

Source:
http://ola.hallengren.com/
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

 
 EXECUTE dbo.IndexOptimize
 @Databases = 'USER_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,
			INDEX_REBUILD_ONLINE,
			INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y'

Note: before executing the scripts the SQL objects needs to be installed.

Cheers!
Sanjay

 

Transaction Log Maintenance – Part 5 – Shrink File

Transaction Log Maintenance – Part 5 – Shrink File

The previous parts dealt with transaction log maintenance, now comes the fun part, actual shrinking of the log file. Start  with checking the log_reuse_wait status:

select name as [db] ,
       log_reuse_wait, 
       log_reuse_wait_desc columns 
from sys.databases

Mostly the status is nothing, for couple of database the status is LOG_BACKUP for which initiate transaction log backup couple of time or worst case a full backup followed by transaction log backup, the status returns to nothing.  Some time the status is REPLICATION, for this review the log reader agent for any errors – if not just stop the agent and start it again the status gets changed to nothing.

Before we get to shrinking the file, would it be nice to see how much of shrinking we did, for reviewing  the current state…

dbcc sqlperf(logspace)

Finally shrink the transaction log files….

use [db]
dbcc shrinkfile(2,1)

Factors to be considered for shrink file operation:

1) Shrunk file will again have to grow during normal operations, and growing is an expensive operation.

2) Heavy shrinking and growing of database files will fragment your file system, which will further hurt performance.

Cheers!

Sanjay

 

 

 

 

Transaction Log Maintenance – Part 4 – Active transactions

Transaction Log Maintenance – Part 4 – Active transactions

When it is known that there are active transactions, what are those transactions ?  How much of them are there ?  – these are the questions which pop up, the below script is good start to identify the active transactions.

declare @loginfo table
      (
_field varchar(50),
_size  varchar(50),
_start varchar(50),
_fseq  varchar(50),
_stat  varchar(50),
_pari  varchar(50),
_lsn   varchar(50))
insert into @loginfo
execute('DBCC LOGINFO(db)')
select  *  from @loginfo where _stat = 2

declare @loginfo table
      (
_field varchar(50),
_size  varchar(50),
_start varchar(50),
_fseq  varchar(50),
_stat  varchar(50),
_pari  varchar(50),
_lsn   varchar(50))
insert into @loginfo
execute('DBCC LOGINFO(db)')
select  count (*)  from @loginfo where _stat = 2

Cheers!
Sanjay

Transaction Log Maintenance Part 3 – Transaction Log Size and % utilization

Transaction Log Maintenance Part 3 – Transaction Log Size and  % utilization:

Review of the size of the transaction log and the percentage of its utilization has directly relations ship between disk space required.  When the percentage utilization is very high the size of log start growing.  This script should provide a quick status on transaction log and % utilization.

dbcc sqlperf(logspace)

 

Cheers!
Sanjay