MSDB Scripts

Many times, you need to get informations from MSDB database.
I’m going to share some simple scripts that can help you.

Increase SQL Agent History Retention
If you have many jobs that run frequently, you may want to increase the retention to use it later on to troubleshoot.

Use the following script:

USE [msdb]
GO
EXEC [dbo].[sp_set_sqlagent_properties]
      @jobhistory_max_rows=10000,
      @jobhistory_max_rows_per_job=1000

Delete old entries in the backup and restore history tables in msdb database
To delete old entries in the backup and restore history tables in msdb database, you can use a stored procedure sp_delete_backuphistory.

Following Stored Procedure can be executed with parameter which takes days of history to keep. In following example 30 is passed to keep history of month.

USE [msdb]
GO
DECLARE @Delete_Date [datetime]
SET @Delete_Date = GETDATE() - 30
EXEC sp_delete_backuphistory @Delete_Date

To delete all information about the specified database

USE msdb;
GO
EXEC sp_delete_database_backuphistory @database_name = 'AdventureWorks';

Search SQL Server Agent Job Steps or Stored Procedure
Search a specific Job key word in the steps of all jobs

USE [msdb]
GO
SELECT    j.job_id,
     s.srvname,
     j.name,
     js.step_id,
     js.command,
     j.enabled
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js
     ON   js.job_id = j.job_id
JOIN master.dbo.sysservers s
     ON   s.srvid = j.originating_server_id
WHERE     js.command LIKE N'%your_key_word_here%'
 Image2
Advertisements

Danilo Braga is a Database Administrator with over nine years of experience. He is a Microsoft Certified IT Professional (MCITP) Database Administrator and Developer. His experience includes solutions for management, assessment and implementation of SQL Server environments making use of best practices, performance tuning, and high availability.

Tagged with: , ,
Posted in Administration, Scripts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: