Time Matters Database Corruption
Time Matters Database Corruption part II. So if you’re reading this, you’re 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 professional. I take no responsibility for the utilization of the following scripts. 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 offer 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 breath and try to think about what else could be causing 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 than 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 sometimes you will get system identifiers rather than a name, such as “object 1269579561”. You can easily query the identifiers name like this:
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’s 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 then 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 errors 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
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:
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.