SQL Integrity Checks

The job failed "Integrity Checks Job for DB Maintenance Plan 'DB Maintenance Plan1' sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." That's the job history. The history against the Maintenance plan (I only learned about this more detailed history last week, and I think it's fab), said "[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode."

There are all sorts of ways of dealing with this.

Firstly, you can untick the box that says "Attempt to repair any minor problems" within the database maintenance plan. This is the same as changing the end of the SQL statement in the scheduled job to read "-CkDB" instead of "-CkDBRepair" If the job fails, this will flag up that there are errors within the database. Run DBCC CHECKDB at some point when you get in, and investigate from there. Databases need to be in single user mode to repair themselves. Sometimes, this is not possible (for example, SQL boxes that support websites with 24/7 availability) and you need scheduled downtime.

Secondly, you can ensure that users are not logged in. In one case, I shifted the job to run at 23:45 instead of at 19:30 - there were a couple of extremely enthusiastic users who were still logged in at 19:30.

How did I find out who they were? Script... On all servers, I find that an admindb is very helpful. So, I created a new table:


USE [admindb]
GO
/****** Object: Table [dbo].[tbl_sweetsql_usersloggedin] Script Date: 07/17/2009 10:15:11 ****** (C) Sweet SQL Lass/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_sweetsql_usersloggedin](
[loginame] [varchar](50) NOT NULL,
[logintime] [datetime] NULL,
[currenttime] [datetime] NULL,
[hostname] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

and then ran the following as a scheduled job:

USE [master]
GO
/****** Script Date: 07/17/2009 10:15:11 ****** (C) Sweet SQL Lass/
INSERT INTO
admindb..tbl_sweetsql_usersloggedin
SELECT loginame,
login_time,
getdate(),
hostname
FROM
sysprocesses
WHERE dbid = 7

(use
SELECT *
FROM sysdatabases
to find out what the dbid is).

The above code works on SQL 2000. For SQL 2005/8, use sys.sysprocesses and sys.databases views to get the same information. This is published under a creative commons license. (c) Sweet SQL Lass.

If the users are really lazy, and just didn't log out, and you want to repair things in the middle of the night, then you may need to use some SQL code to deal with this and throw them out of the database. There is plenty out there (and I have a version a colleague produced, and am seeking permission to publish, because it is rather nifty). Personally, I wouldn't recommend this. At the very least, warn your users that they will get logged out.

Heck. Microsoft doesn't recommend automatically repairing your database as part of an Integrity Check job. So who am I to go against that idea?

Finally, don't forget - log the information for your Maintenance Jobs to a Report File. It makes life much easier if you've done something really daft, like drop a database from the server without bothering to remove it from the Maintenance Job first.