Five Tips for SQL DBAs to Work Efficiently in Production Environment

Working efficiently in a production environment where even a minor change can have a profound impact on performance, there are some tips you can follow to ensure the best possible database performance. In this article, we will discuss five tips to make the job of an SQL Server DBA easier in a production environment.

1. Use the Maintenance Cleanup Task Utility To Delete Old Backups

While backups are an important part of an effective recovery plan, frequent backups can cause issues if the available storage space is limited. In such a situation, deleting old backups can help clear the storage space. The Maintenance Cleanup Task feature (in SQL Server 2005 and later versions) available in the Maintenance Plan Wizard can help remove obsolete database backup files.

Using Maintenance Cleanup Task, you can remove backup of all types (i.e., Full, Differential, and Transaction Log) from the given location. However, you can only delete one type of file in each Maintenance Cleanup Task. In other words, you cannot remove transaction log files (.trn) and full or differential backup files (.bak) in a single task. You need to create two tasks to purge the files.

To delete the old SQL backup files created using Maintenance Cleanup Task, do the following:

• Expand Management in SQL Server Management Studio (SSMS).
• Right-click on Maintenance Plans and select New Maintenance Plan. Specify a name for the plan like "DeleteOldBackups" and click OK.
• On the Maintenance Plan Designer window, drag and drop Maintenance Cleanup Task from the Toolbox.
• On the "Maintenance Cleanup Task" window, select backup files, and specify the backup folder and file extension of the file type you want to delete. For instance, specify "trn" if you need to purge transaction log backups and "bak" to delete the full database backup. Finally, select the time after which you want to delete the backup files.
• Now the DeleteOldBackups task will get listed under Maintenance Plans. Right-click on it and click Execute. When you open your backup folder, you can see that the backup files older than two weeks have been deleted.

Note: You may also create a new job schedule to run the maintenance plan of deleting old backup files weekly.

For more information about Maintenance Cleanup Task, see Microsoft’s guide.

2. Change the Maintenance Plan Owner to SA

When a maintenance plan is created in SQL Server, a user logged into the server is the plan's owner. If the owner's account is locked or removed, the maintenance plan for scheduled jobs (like backup, etc.) will fail. By changing the owner of the maintenance plan to another account (besides your domain account), you will ensure that the jobs will run, even if your account is locked or deleted. However, you may need to manually change the job owner when changes are made to a job or maintenance plan. To resolve this, set the maintenance plan owner to SA.

Before changing the maintenance plan owner, it is important to find the current maintenance plan and its owner. For this, execute the following T-SQL query:

Use MSDB

GO

SELECT *FROM dbo.sysdbmaintenanceplans

Now run the below code to set the maintenance plan owner to "sa". In our case, we will replace "Login_name" with the owner name returned using the above query.

Use MSDB

GO

UPDATE msdb.dbo.sysssispackages

 SET OWNERSID = SUSER_SID('sa')

WHERE NAME = 'Login_name'

3. Run DBCC CHECKDB for Large Databases Using Different Strategies

Checking database integrity using a basic DBCC CHECKDB command for large databases can take a longer time to complete. To run the CHECKDB command in significantly less time, try using a different strategy to run the command against databases of a large size. 

Essentially, to prevent DBCC CHECKDB from exceeding the allocated maintenance period, implement solutions discussed in the blog by Paul S. Randal: "CHECKDB From Every Angle: Consistency Checking Options for a VLDB". The blog discusses using a backup to restore the SQL database on another server and running DBCC CHECKDB against that server, as one solution. Also, you can reduce the time to run DBCC CHECKDB by using the PHYSICAL_ONLY option.

4. Query Multiple Servers Simultaneously 

It is not easy to query thousands of SQL Servers individually in a production environment. But by creating a local server group or a Central Management Server and one or more registered servers, you can execute queries against multiple servers simultaneously. To do so, in SQL Server Management Studio (SSMS), go to Registered Servers, right-click a server group (i.e., Local Server Groups or Central Management Server), and select New Query.

In the query editor window, execute the below query:

USE master  

GO  

SELECT * FROM sysdatabases;  

GO    

5. Use a Professional Solution for Business Continuity

The primary responsibility of a production DBA is to keep the SQL Server running smoothly and ensure data availability. However, a disaster can happen when you least expect it and hamper business continuity. You may have invested in a disaster recovery (DR) solution to deal with such unplanned events.

While DR is an important component of a business continuity plan, per Nationwide's study: a majority of small-business owners (68 percent) don’t have a written DR plan. If a DR plan doesn’t exist, a third-party solution such as a SQL recovery tool can help in extracting data from a database damaged due to hardware failure, ransomware attack, software bug, etc.

Wrapping Up

This article discussed the top five tips to make the job of a SQL Server production DBA easier. These tips included deleting old backups to free up storage space and changing maintenance plan owner to "sa" to ensure that jobs (like backup or restore) will continue running. It also explained the tips on running DBCC CHECKDB against large databases and querying multiple servers simultaneously. Lastly, it discussed how using a third-party SQL Recovery Tool can come in handy when everything else fails to bring the database online.