Managing and Configuring the Database

In this section, I'll continue the discussion I started in Chapter 2, and discuss more advanced options you can apply to your database and server. I'll also show you some simple shortcuts for performing some administrative tasks.

Monitoring Growth

One of the greatest ease-of-use features that was added into SQL Server starting with version 7.0 is the capability of SQL Server to automatically increase the size of a database without user intervention. This feature does have its drawbacks, however.

The autogrow process is triggered when a database runs out of space and is configured to grow by a percentage or megabyte amount. As you can imagine, this is generally triggered during your peak database times. Your clients notice a real slowdown, because the process of expanding a database is very CPU intensive. Table 7-1 shows the amount of time, at 10 percent growth, it takes to expand a database's size on a dual processor machine. These numbers can vary, depending on the hardware.

Table 7-1: Sample Time Calculations for Database Growth

Total Size of Database	Number of MB Increase	Time to Grow
1000MB	100MB	6 seconds
1100MB	110MB	7 seconds
1210MB	121MB	8 seconds
1331MB	133.1MB	9 seconds

Tip 

This is where dual processors really help. On a single processor machine, a CPU I tested was at 100 percent utilization for 38 seconds, in order to grow the database 100MB. The dual processor only used between 4 and 10 percent of the CPU.

To avoid this slowdown, keep an eye on your databases and schedule alerts to trigger e-mail when a database is 85 percent full. You can then manually expand the database during off-peak hours.

Disconnecting Users

A common problem that DBAs experience is the need to disconnect users from a database. You generally have to disconnect a user during maintenance of a database or when you restore a database. When you restore a database, for example, all users in the database except the user issuing the command must be disconnected before the restore can proceed. Microsoft has added a feature in SQL Server 2000 to give you the ability to disconnect all users in a database before placing it in single user mode.

Before you can restore a database, you have to run sp_who in Query Analyzer to find out who's connected to the database. Then you must use the KILL command to disconnect the users. The KILL command is accompanied by the SPID for the user, which is the unique ID assigned to the user when he or she first connects. To issue the KILL command, use the syntax KILL <SPID>.

On a busy system, it takes a long time to issue the command for each user, and by the time you issue the command, ten more users have probably connected. To solve the problem, you can place the database in single user mode, unplug it from the network, or run the usp_killusers procedure. The usp_killusers is a stored procedure that I developed to loop through everyone connected in a given database and then issue a KILL statement for each user's SPID in that database. The most appropriate place to compile this stored procedure would be the Master database. To create the procedure, use the following code:

CREATE PROCEDURE USP_KILLUSERS @dbname varchar(50) as SET NOCOUNT ON DECLARE @strSQL varchar(255) PRINT 'Killing Users' PRINT '-----------------' CREATE table #tmpUsers(  spid int,  eid int,  status varchar(30),  loginname varchar(50),  hostname varchar(50),  blk int,  dbname varchar(50),  cmd varchar(30)) INSERT INTO #tmpUsers EXEC sp_who DECLARE LoginCursor CURSOR READ_ONLY FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname DECLARE @spid varchar(10) DECLARE @dbname2 varchar(40) OPEN LoginCursor FETCH NEXT FROM LoginCursor INTO @spid, @dbname2 WHILE (@@fetch_status <> -1) BEGIN       IF (@@fetch_status <> -2)       BEGIN       PRINT 'Killing ' + @spid       SET @strSQL = 'KILL ' + @spid       EXEC (@strSQL)       END       FETCH NEXT FROM LoginCursor INTO  @spid, @dbname2 END CLOSE LoginCursor DEALLOCATE LoginCursor DROP table #tmpUsers PRINT 'Done' go

Note 

You can download this procedure from http://www.sqlservercentral.com/experienceddba/.

To execute the stored procedure, you must pass it a database name from which you want to disconnect the users. You can also modify the stored procedure to disconnect all the users connected to your system by removing the WHERE clause and the @dbname parameter. The command to execute the procedure in the Northwind database is

USP_KILLUSERS 'Northwind' 

If you are connected to the database you're trying to disconnect users from, you'll receive the following error when the cursor tries to disconnect you:

Server: Msg 6104, Level 16, State 1, Line 1 Cannot use KILL to kill your own process.

If you execute the stored procedure against the Master database, you may experience the following error as the procedure tries to disconnect system processes (this will not harm your system):

Server: Msg 6107, Level 14, State 1, Line 1 Only user processes can be killed.

Model Database

Do you have a standard way each database is configured on your server? Tired of configuring each database after creation? You can use the Model database to reduce your workload and lower the risk of human error when configuring databases.

In SQL Server, the Model database is used as a template when a new database is created. You can make an adjustment to the Model database, and any databases from that point forward will show the Model database's adjusted properties. If, for example, you always want new databases to have the Torn Page Detection option on, you can configure the Model database to reflect this, and every database created from that point on will have the option checked.

This also applies to any other type of objects you create in the Model database. If your company creates a product that expects a certain user-defined function or data type in each database, create the objects in the Model database and these objects will automatically be transferred when you create a new database. This reduces the risk of an administrator misconfiguring a database or forgetting a step in the installation process. The configuration also applies to the size of the database. If your base Model database is 15GB, then your newly created databases will be 15GB.