Time Matters 9/10/11 Database Corruption Part II

So if your reading this your in bad shape, just kidding. However if you think your database is or may be corrupted, there are some general steps you can take to resolve the issue. For many this can be an overwhelming issue, however there are steps that even the novice IT consultant can follow which often will easily resolve the issue. As I mentioned in the previous article, Time Matters Database Corruption Part 1 if you are uncomfortable with any of the steps in this article or do not know what SQL Server is, or what SQL Server Management Studio is you should contact a professorial. So the first thing you are going to want to do is run DBCC CHECKDB. Ideally you should do this from the SQL command prompt but I find that if you can access the program you are good to do this from the Management Studio. If you don’t know how to access or don’t have SQL Server Management Studio you can read my Time Matters 11 Installation Guidelines article where I outline some of the steps and have some links to download the installer.

So again run the following command, it may take several minutes.

use TimeMatters10
GO
DBCC CHECKDB
GO

So now you have to interpret the results. If there is no red text then take a deep breadth and try to think about what else could be cause your issue (whatever that may be). If there is Red text in the output this is the area you want to focus on. If there is just about nothing but red text, contact us or another proffesional Database Administrator. I generally find that there are three major fixes for corruption that I end up having to implement (of course there are more then this, however these are the most common).

• 1. An index has become corrupted. (rebuild the Index).
• 2. There is some form of foreign or primary key violation.
• 3. There is IO corruption.

The output from CHECKDB is generally pretty descriptive however sometime you will get system identifiers rather then a name, such as “object 1269579561″. You can easily query the identifiers name like this:

select Object_Name(1269579561);

 

Scenario 1

If you get an error like I/O error on index such and such, this means that the index has been corrupted. This is the most common type of corruption I see and it also generally the easiest to fix. Figure out what index is corrupted (generally you can ascertain this directly from the output of DBCC CHECKDB) and the run the following command: (substituting in your table and index).

USE TimeMatters10
GO
DBCC DBREINDEX ('TimeMatters10.lntmuser.yourtable', 'your_index', 80)
GO

 

The “80″ on the end is the fill factor for the index you can learn more about this in the MSDN Fill Factor Overview.

Scenario 2, 3

If you are getting error’s about key – primary or foreign – violation or I/O corruption, there are two steps that the novice can take, run DBCC CHECKDB with no data loss, or run DBCC CHECKDB with Data loss.

Attempt to repair without dataloss:

ALTER DATABASE TimeMatters10
SET SINGLE_USER
GO
DBCC checkdb ('TimeMatters10', REPAIR_REBUILD);
GO

 

Attempt to repair with data loss. Only use this script after doing a verified backup or baseline:

ALTER DATABASE TimeMatters10
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
DBCC checkdb ('TimeMatters10', REPAIR_ALLOW_DATA_LOSS);
GO

 

Other Considerations

To run some of the above scripts you may have to put the database into “single user mode” and/or force close the connections to the database, here are the scripts:

Create procedure to kill connections.

USE MASTER
GO
CREATE PROCEDURE dbo.clearDBUsers
@dbName SYSNAME
AS
BEGIN
SET NOCOUNT ON

DECLARE @spid INT,
@cnt INT,
@sql VARCHAR(255)

SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)
AND spid != @@SPID

PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'

WHILE @spid IS NOT NULL
BEGIN
PRINT 'About to KILL '+RTRIM(@spid)
SET @sql = 'KILL '+RTRIM(@spid)
EXEC(@sql)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)
AND spid != @@SPID
PRINT RTRIM(@cnt)+' processes remain.'
END
END
GO

Call the procedure like: 

dbo.clearDBUsers 'TimeMatters10' 

 

Put Database in Single User Mode:

USE master;
GO
dbo.clearDBUsers 'TimeMatters10'
ALTER DATABASE TimeMatters10
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

 
Put Database back in Multi User Mode:

USE master;
GO
ALTER DATABASE TimeMatters10
SET MULTI_USER;
GO

 

Feel free to contact us if you are having any issues with your Time Matters or with your SQL Databases.

David Wetherell
consult@nyc-software.com