Long Running Jobs Alert

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



DECLARE @INT INT
 SELECT jobs.name 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 jobs.name 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 @tableHTML  NVARCHAR(MAX)  
declare @NumStDevs int = 2

SET @tableHTML = 
   N'

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

' + 
   N'' + 
   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  
 ) AS NVARCHAR(MAX) ) + 
  N'

Job Name	start_execution_date	Current_Status	Duration[DD:HH:MM:SS]

' ; 
 

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

 drop table #jobhist
 drop table #jh

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:

DBCC OPENTRAN

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 or C:\Program Files (x86)\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"

Refer: https://support.microsoft.com/en-in/kb/956013

Result:_

Limiting No Of Rows and Sizing the bufferon SSIS

Adding Columns to Replicated Tables

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

Moving Database Files on the secondary server in Log Shipping

http://blogs.technet.com/b/mdegre/archive/2009/09/25/moving-database-files-on-the-secondary-server-in-log-shipping.aspx

 

 

You are using Microsoft SQL Server Logshipping for one of your VLDB (very large database). You would like to move data files on another disk on the secondary server without reconfiguring log shipping.

As you cannot use the detach/attach database, you could follow the way below :

This solution is inspired of the documentation "Moving Database Files" http://technet.microsoft.com/en-us/library/ms345483.aspx


Step 1 : If your secondary is with the option "Standby Mode", you must change by "no recovery mode". And perform a transaction (insert, delete or update)
After, you have to manually run the job of backup, Then the jog of copy and the restore job.

Step 2 : Collect the logical name :

SELECT
  name as logicalname, physical_name as filename
FROM
  sys.master_files
WHERE
  database_id = DB_ID('yourDatabaseName');

Step 3 : Move the file on the path expected :

ALTER DATABASE yourDatabaseName

MODIFY FILE( NAME = logicalname, FILENAME = 'M:\newpath.mdf')

Step 4 : Stop SQL server service, move the database file and start SQL server Service

Step 5 : Check that the update is ok with the query below :

SELECT
  name as logicalname, physical_name as filename
FROM
  sys.master_files
WHERE
  database_id = DB_ID('yourDatabaseName');


You can follow the same way to add a file to a log shipped database. 

user is not able to access the database "msdb" under the current security context.

Symptoms when the guest user is disabled in the msdb database

USE msdb;
SELECT prins.name AS grantee_name, perms.*
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS prins
ON perms.grantee_principal_id = prins.principal_id
WHERE prins.name = 'guest' AND perms.permission_name = 'CONNECT';

GO

If you receive a result set that resembles the following, the guest user has the necessary permissions. 

 

If you receive either an empty result set or if the state_desc shows DENY in the result set that is mentioned here, the guest user is disabled in the msdb database. You may receive error 916 when you connect to a database.

How to resolve the issue

USE msdb;
GRANT connect TO guest;
GO

http://support.microsoft.com/kb/2539091/en-us