Monday, July 14, 2014

Long Running Jobs Alert

Copy the below code and schedule the job on the server :-

 SELECT AS [Job_Name]
 , CONVERT(VARCHAR(23),ja.start_execution_date,121)  AS [Start_execution_date]
 , ISNULL(CONVERT(VARCHAR(23),ja.stop_execution_date,121), 'Is Running') AS [Stop_execution_date]
 ,DATEDIFF(SECOND,CONVERT(VARCHAR(23),ja.start_execution_date,121),ISNULL(CONVERT(VARCHAR(23),ja.stop_execution_date,121),GETDATE())) as Duration
 into #jobhist
 FROM msdb.dbo.sysjobs jobs
 LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = jobs.job_id
 AND ja.start_execution_date IS NOT NULL
 --where not like 'repli%' and name not like '%mirr%' and name not like '%distribu%'and name not like '%subscript%' and name not like '%sys%' and name not like '%pub%'

 select *
 , convert(varchar(10), (Duration/86400)) + ':' +
convert(varchar(10), ((Duration%86400)/3600)) + ':'+
convert(varchar(10), (((Duration%86400)%3600)/60)) + ':'+
convert(varchar(10), (((Duration%86400)%3600)%60)) as 'DD:HH:MM:SS'
into #JH
 from #jobhist where stop_execution_date='Is Running' and start_execution_date is not null

declare @NumStDevs int = 2

SET @tableHTML = 

' + @@SERVERNAME + ': Long Running Jobs Alert :

' + 
   N'' + 
   ' + 
   N'' + 
   N' ' + 
   CAST ( ( SELECT td = job_name, '', td = start_execution_date, '', td = stop_execution_date, '', td = [DD:HH:MM:SS] 
      FROM #jh where Duration>(360*24) /* for 1 day */
           FOR XML PATH('tr'), TYPE  
Job Namestart_execution_dateCurrent_StatusDuration[DD:HH:MM:SS]
' ; 

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'DBPROFILENAME',
    @subject = 'LongRunning Jobs',
    @body = @tableHTML,
    @body_format = 'HTML' ;

 drop table #jobhist
 drop table #jh

Sunday, July 6, 2014

Shrink Transaction log DB Replication

First check what is causing your database to not shrink by running:
SELECT name, log_reuse_wait_desc FROM sys.DATABASES

If you are blocked by a transaction, find which one with:

Kill the transaction and shrink your db.

If the cause of the blocking is 'REPLICATION' and you are sure that your replicas are in sync, you might need to reset the status of replicated transactions. To see the status of what the database still think needs to be replicated use:
DBCC loginfo

You can reset this by first turning the Reader agent off (I usually just turn the whole SQL Server Agent off), and then run that query on the database for which you want to fix the replication issue:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, 
 @reset = 1

Exec sp_replflush

Close the connection where you executed that query and restart SQL Server Agent (or just the Reader Agent). You should be all set to shrink your db now.

Cannot connect to WMI provider. SQL Server Configuration Manager

1. Copy sqlmgmproviderxpsp2up.mof to the path C:\Program Files\Microsoft SQL Server\110\Shared
2. Open CMD in elevated privilages
3. C:\Windows\system32>mofcomp "C:\Program Files\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof"


Thursday, June 19, 2014

Limiting No Of Rows and Sizing the bufferon SSIS

Adding Columns to Replicated Tables

sp_repladdcolumn @source_object =
   , @column =  'newcol'
   , @typetext = 'INT'
   , @publication_to_add = '       of publication authors is
      included in>'