Tip

Terminate SQL Server connections before you perform maintenance

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.