Terminate SQL Server connections before you perform maintenance

If there are active connections open on your database, some types of maintenance may not run. Learn how to ensure that connections are closed before maintenance starts.

SQL administrators typically set up a nightly or weekly maintenance plan for their databases. The details of a

maintenance plan will vary depending on what's needed for a particular database, but it usually consists of making a backup, checking the database and its indexes for integrity and reclaiming slack space within the database's physical files.

The best time to run a maintenance cycle is when the database is receiving little or no traffic, something that can be determined by analyzing system logs. But if the database gets traffic continually around the clock, it may be hard for the maintenance cycle to run interrupted. SQL Server's standard behavior is to not run maintenance if there are active connections to the database; and if there are active connections being held open, some types of maintenance may not run (such as CHECKDB).

One way to ensure that all connections to the database are closed before maintenance starts is to use an ALTER DATABASE command in your maintenance plan:

ALTER DATABASE <database > SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

where <database > is the name of the database to be optimized. This command closes all existing connections to the database and allows only users with special privileges (such as SA) to connect. You can insert this instruction as a step at the beginning of the maintenance plan, and then at the end, insert another command to allow conventional connections once again:

ALTER DATABASE <database > SET MULTI_USER

Note that if a majority of the users for this particular database are connecting as privileged users or as SAs, you may want to use the command ALTER DATABASE <database > SET SINGLE_USER WITH ROLLBACK IMMEDIATE to limit connections, rather than RESTRICTED_USER, which allows only a single connection (namely, the process used to run the maintenance cycle).

Also note that if you edit the maintenance plan, you may get a spurious error message from the SQL Server Enterprise Manager that says you should not add steps to jobs created by maintenance plans. But the maintenance plan itself should run correctly.


Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators – please share your thoughts as well!


This was first published in March 2005

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close