Feeds:
Posts
Comments

Archive for the ‘SQL Server Agent’ Category

SQL Server – Automating Backup Files Cleanup Task Using Maintenance Plans

Posted in Backup, Disk Space, SQL Server Agent, tagged , , , on August 7, 2013| 17 Comments »

Cleaning/deleting the old backup files is equally important as taking the backups. Doing so we can (i) Avoid overhead cost for the storage space, and (ii) Ensure and retain enough space on the disk for the next successful backup

Using ‘Maintenance Plans’ this task will be a cake walk for the DBAs who are having hard time deleting the old backup files manually!

Below is the step by step process with screenshots showing how to setup and Automate backup files cleanup task using Maintenance Plans in SQL Server.

Step 1: Connect to the SQL Server instance, expand ‘Management’ folder, right click on ‘Maintenance Plans’ and select ‘Maintenance Plan Wizard’

BackUp Clean - 1

Step 2: ‘Maintenance Plan Wizard’ pops out, click Next

BackUp Clean - 2

Step 3: On the ‘Maintenance Plan Wizard’ give a Name and Description. To schedule this as a job, select Change under ‘Schedule’

BackUp Clean - 3

Step 4: ‘New Job Schedule’ pops out. Define a schedule. In my case I gave it to run Daily at 12:00:00 AM. Click ‘ok’ when done.

BackUp Clean - 4

Step5: Make sure everything is correct and click Next

BackUp Clean - 5

Step 6: Select the Maintenance Cleanup Task option and click Next

BackUp Clean - 6

Step7: Here we only have one task, so nothing to order/re-order. Click Next

BackUp Clean - 7

Step 8: This window is the heart for this task. Under ‘Delete files of the following type’ select Backup files. Under ‘File location’ select Search folder and delete files based on an extension. Give the path of the backup files location in Folder and “bak” (for native backups) in File extension. For Light Speed Backups you need to mention ‘lsb’.

Under ‘File age’ check the option Delete files based on the age of the file at task run time. Under ‘Delete files older than the following’ select a retention policy. I left the default 4 weeks. Click Next

BackUp Clean - 8

Step 9: The wizard will write a report to a text file to the defined Folder location. You can leave the default location or chose a location of your choice. Click Next

BackUp Clean - 9

Step 10: Click Finish

BackUp Clean - 10

This should create the Maintenance Plan ‘Delete old backup files’ as shown in the screenshot below

BackUp Clean - 11

Note: SQL Agent Services must be running in order to execute the above discussed Maintenance Plan.

Advertisement

Read Full Post »

SQL Server – Understanding ‘Agent XPs’ Server Configuration Option

Posted in Agent XPs, SQL Server Agent, tagged , , on September 19, 2012| Leave a Comment »

Today we got an escalation where on one of the servers ‘Agent XPs’ was in a disabled state. And when looked at SQL Server Agent node, it is displayed in Red which basically indicates SQL Agent Service is not running, however when checked the Agent Services are actually running, which is misleading!

AgentXps

So here is some basic information I gathered about Agent XPs option – only found in SQL Server 2005 and above versions

Summing up from MSDN BOL:

  • When Agent XPs is not enabled (which makes Agent extended stored procedures are not enabled), SSMS does not display the contents of the SQL Server Agent node regardless of the SQL Server Agent service state.
  • Hence, in order to enable the SQL Server Agent extended stored procedures on the server, Agent XPs should be enabled
  • When SQL Server Management Studio tool is used to start the SQL Server Agent service, these extended stored procedures are enabled automatically, which should enable Agent XPs server configuration option.

It is not very clear from BOL what makes Agent XPs setting enable/disable, so I dug more into this and here is some brief notes:

  • I Installed a local instance (SQL 2012) on my machine and did a few test scenarios which confirms that Agent XPs option will be enabled automatically when SQL Agent Services are started, either with Manual mode or Automatic mode.
  • And whenever the SQL Agent service is started or stopped regardless of the SQL Agent start mode (Manual/Automatic), it will enable or disable the Agent XPs respectively.
  • Hence, if SQL Agent is stopped and don’t restart – it will disable the Agent XPs option(even though we enabled the Agent XPs prior to stopping SQL Agent)
  • When the Agent XPs option is disabled, we cannot access any SQL Agent nodes or even its properties from SSMS.
  • Vice-versa, when the Agent XPs option is enabled, we can access SQL Agent nodes and properties from SSMS whether or not SQL Agent services are running or stopped
  • And, from the above observations, it is clear says that Agent XPs option disabled doesn’t mean that SQL Agent services are stopped

Finally from my understanding most of the time Agent XPs option shouldn’t be an issue since whenever the SQL Agent services are started the Agent XPs option will be automatically enabled.

If this option is disabled, either the SQL Agent services never started or Agent XPs option might have disabled due to some reasons.. such as security patching on the servers (which can be enabled anytime using a simple script which doesn’t need Agent/Server restart).

--To check if Agent XPs option is enabled or not 
--(show advanced options must be enabled for this)
EXEC SP_CONFIGURE 'Agent XPs'
GO 
--To enable show advanced options
EXEC SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE
GO
--To see advanced options
EXEC SP_CONFIGURE 'show advanced options'
GO
--To enable Agent XPs option
EXEC SP_CONFIGURE 'Agent XPs', 1
GO
RECONFIGURE
GO

Read Full Post »