check database integrity - 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

SQL Server 2005 Forums

SQL Server Administration (2005)

check database integrity

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-22 : 17:23:41

im trying to set up maintenance plan for the check database integrity...

In sql 2000 you get a nice little log in SQL Logs
DBCC CHECKDB (WSS_Search_db3) executed by xxx found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.

But in SQL 2005
Im not getting a nice log of this but getting it against some system database and not the database i selected

Date 1/22/2008 5:19:43 PM
Log SQL Server (Current - 1/22/2008 5:19:00 PM)
Source spid84
Message
DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs executed by XXX found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.

Anyone got this to work?
Trying to get the same message i got for SQL 2000 or at least so i can confirm it ran.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-22 : 22:25:14

Check plan's log file.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-23 : 08:44:01

I looked in Job history

Message
Executed as user: xxx. ...cute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:44:14 PM Progress: 2008-01-22 17:44:15.81 Source: {3FF4B569-670C-4E27-AB1F-AC0819FEF51E} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2008-01-22 17:44:16.53 Source: Check Database Integrity Task Executing query "USE [AdventureWorks] ".: 100% complete End Progress Progress: 2008-01-22 17:44:21.12 Source: Check Database Integrity Task Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2008-01-22 17:44:21.12 Source: Check Database Integrity Task Executing query "USE [AdventureWorksDW] ".: 100% complete End Progress Progress: 2008-01-22 17:44:25.08 Source: Check Database Integrity Task Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress . The step succeeded.

Its there just a little messy......will be hard to decipher with 100 databases.
Is it possible to get this sent to email to me the log above?

Haywood
Posting Yak Master

221 Posts

Posted - 2008-01-23 : 10:45:12

You can read the errorlog and search for DBCC...

[CODE]

DECLARE @Foo TABLE
(PK_Foo_Col int IDENTITY(1,1) PRIMARY KEY
, LogDate datetime
, ProcessInfo varchar(15)
, [Text] varchar(max) );

INSERT INTO @Foo
EXEC master..xp_readerrorlog;

SELECT CONVERT(char(26),LogDate,109) AS [LogDate]
, ProcessInfo
, [Text]

FROM @Foo

WHERE [Text] LIKE 'Error%'
OR [Text] LIKE 'Warning%'
OR [Text] LIKE 'Login Failed%'
OR [Text] LIKE 'DBCC%'
ORDER BY CONVERT(datetime,LogDate) DESC;
[/CODE]

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-23 : 11:54:39

In sql 2005 what would this be EXEC master..xp_readerrorlog;
Not getting anything

Haywood
Posting Yak Master

221 Posts

Posted - 2008-01-23 : 14:01:03

xp_readerrorlog reads the SQL Server errorlog...

The code puts the contents of the errorlog into a declared table and then is searched based on the LIKE clauses. Remove the WHERE clauses and see if you are even getting the errorlog output into the table - it works for me on all my 2k5 servers...

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-23 : 14:39:35

Got it but it keeps coming back with
DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs executed by XXX found 0 errors and repaired 0 errors.

But this was not the database i selected to do check database integrity with in the maintenance plan.
When i view sql code for the maintenance plan all looks good.

Just using the good old example for this case.

USE [AdventureWorks]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO

Confused was trying to find this message in the logs that it worked correctly and no errors

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-23 : 14:42:07

If i run manually
USE [AdventureWorks]
GO
DBCC CHECKDB WITH NO_INFOMSGS

It writes to the log and i can view this
DBCC CHECKDB (AdventureWorks) WITH no_infomsgs executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 23 seconds.

But running as Maintenance plan just does DBCC CHECKDB (mssqlsystemresource)

Odd

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-23 : 15:00:03

This seems to be incorrect?

In the maintenance Plan.
For check database integrity.
If you do not include the index check.
You recieve this message
DBCC CHECKDB (AdventureWorks, noindex)
If you have the tick enabled to include indexes you get
DBCC CHECKDB (mssqlsystemresource)

So the tick enable is not actually doing the checking of the correct database?

Haywood
Posting Yak Master

221 Posts

Posted - 2008-01-23 : 15:24:30

Sorry, I don't use the wizard built maintenance plans for this type of operation (or at all)...couldn't tell ya.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-23 : 15:29:41

No problem, I think i write my own and run it as a script.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-23 : 22:39:21

Maintenance plan logs all message in its own log file, check details in it.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-01-24 : 07:07:26

Got it working created my own via script.

First time ever i had a corruption SQL 2000 never got any errors...SQL 2005 first database with errors.

Msg 8914, Level 16, State 1, Server , Line 1
Incorrect PFS free space information for page (1:26839) in object ID
181575685, index ID 1, partition ID 72057594039435264, alloc unit ID
71788018805309440 (type LOB data). Expected value 0_PCT_FULL, actual
value 100_PCT_FULL.

Managed to fix with REPAIR command.

After running check
DBCC CHECKDB ([databasename]) WITH DATA_PURITY

Is it best to then do the UPDATE STATISTICS before running the index rebuilds.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-24 : 22:53:23

Why? Rebuild index also updates statistics.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources