Database Integrity Check - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Database Integrity Check

Author

Topic

psychotic_savage
Starting Member

25 Posts

Posted - 2010-11-11 : 03:04:06

Hi All.

Recently I've had an issue where the built in Database Integrity Check Maintenance task was causing issues as the writes to the TempDB where locking resources for longer than my poll period for mirroring. Thus causing a failover. For now I have removed the integrity check task to alleviate the failover during the maintenance tasks. I have written the following to replace the built in Integrity task and included the WITH PYSICAL_ONLY parameter on the DBCC command.
Your thoughts on the below are appreciated.

SET NOCOUNT ON
-- Declarations.
DECLARE @db as sysname;

DECLARE @command nvarchar(1000);

DECLARE @errormessage as nvarchar(4000);
DECLARE @notification as nvarchar(4000);

-- Initiate the compounding variables.
SET @errormessage = ''
SET @notification = ''

-- Declare the cursor for the list of Databases to be used.
DECLARE DatabaseList CURSOR FAST_FORWARD FOR

SELECT [name] FROM sys.databases
-- To exclude any DB's from the check fill in below
WHERE [name] NOT IN ('')
AND state_desc = 'ONLINE'
ORDER BY [name]

-- Open DatabaseList Cursor.
OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @db
WHILE (@@fetch_status = 0)
BEGIN
PRINT N'Database: ' + @db;
Begin try
SET @command = N'USE ' + @db + N';
DBCC CheckDB WITH PYSICAL_ONLY;'
EXEC (@command);
PRINT N'Executed: ' + @command;
SET @notification = @notification + ' DBCC CheckDB on ' + @db + ' completed / '
end try
BEGIN CATCH
SELECT @errormessage = @errormessage +cast(ERROR_MESSAGE() as nvarchar(1000)) +' on ' +@db
END CATCH
FETCH NEXT FROM DatabaseList INTO @db

END

-- Close and deallocate the DatabaseList cursor.
CLOSE DatabaseList;
DEALLOCATE DatabaseList;

-- Populate and send the notification Email Success\failure.
set @notification = 'There where no Issuses. The following where Databases Integrity where checked: ' + @notification
If @errormessage = ''
EXECUTE msdb.dbo.sp_notify_operator @name=N'SQLOperator',@subject=N'Integrity Check completed successfully',@body = @notification
else
EXECUTE msdb.dbo.sp_notify_operator @name=N'SQLOperator',@subject=N'Integrity Check failed',@body = @errormessage
-- End

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-11 : 04:37:30

The only thing that CheckDB uses TempDB for is checking indexed views. Check that your IO subsystem is handling the required load.

By running only physical checks you could be missing quite a lot of corruptions. I recommend that you find some way to run full checkDB from time to time, even if it's on a restored backup on another server.

--
Gail Shaw
SQL Server MVP

psychotic_savage
Starting Member

25 Posts

Posted - 2010-11-11 : 04:55:29

I know the Server cant handle the IO it needs to do and it is getting a much beefier server thrown at it in a few weeks.

Until then I do need a temp fix.
I am busy writing a script to restore the DB's to my Data collection server, then run a full integrity check there. Total DB's to run the check on will be around 15 so I think it will run pretty much all day.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-11 : 06:01:16

quote:
Originally posted by psychotic_savage

I know the Server cant handle the IO it needs to do and it is getting a much beefier server thrown at it in a few weeks.

It's not a beefier server that's needed, it's a beefier IO subsystem. Make sure that whereever you're storing the data also gets an upgrade.

--
Gail Shaw
SQL Server MVP

psychotic_savage
Starting Member

25 Posts

Posted - 2010-11-11 : 06:51:14

Well the server replacing it is currently acting as the mirror for the entire SQL environment so the IO is definitely not going to be a problem. And the mirrors are going to run to a Modular server with a NAS. So I'm foreseeing only good things for my SQL environment.

What are the signs that an IO subsystem is beginning to get over worked?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-11 : 07:52:40

NAS for SQL data files? Unless it's an iSCSI setup, that's not permitted. Network file storage doesn't support the requirements SQL has for IOs

Overworked IO subsystem:
warnings in error log - IO taking 15 seconds to complete
High pageIOLatch waits
High avg disk sec/read and sec/write (perfmon)
High writelog and async io completion waits

--
Gail Shaw
SQL Server MVP

psychotic_savage
Starting Member

25 Posts

Posted - 2010-11-11 : 09:20:03

Well I brought that to their attention and its been scrapped anyway. They starting testing on the feasibility of using Microsoft Storage Server. Its not entirely for my mirroring as its attached to the modular server and other services are using it. I don't get much say in the hardware department. I get told here have this and make it work so it is very limiting.
Ah the sob stories of a lonely DB admin :P

I did get to use a Solid State Drive it has some very impressive IO. About that of 4 SATA RE2 drives in raid 5. But that was just at a glance.

Kristen
Test

22859 Posts

Posted - 2010-11-12 : 02:38:39

"I am busy writing a script to restore the DB's to my Data collection server, then run a full integrity check there."

Worth considering as a long term solution IMHO.

You make a backup. How do you know if it is valid and (come Disaster Day!) you will be able to restore it?

Answer: Restore it to another server and DBCC it there.

Bonus: You are not using CPU and Disk resources on your main server to do housekeeping DBCC

psychotic_savage
Starting Member

25 Posts

Posted - 2010-11-12 : 05:55:25

I already do restores once a week. Our off-site backups are held at our offices, these get restored to a test environment for the development team to use. Our live environment is at a remote site and this is where I will do the daily DBCC CheckDB above. I will also then perform the Full DBCC on a restored backup but from a non critical data collection server. I have a concern over what to do when the DB's are in the DR. I dont have a spare non critical server to perform the DBCC check's on. How should I handle that as we could be in that environment for weeks at a time.

Kristen
Test

22859 Posts

Posted - 2010-11-13 : 04:56:18

"I already do restores once a week"

Excellent! The DBCC you are doing on data-collection-server restored backups means you don't need to do DBCC on the Live server at all.

If data-collection-server is not available for this job you could do it on DEV / TEST environment instead?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources