Automatically check results of DBCC CHECK DB

Question

Automatically check results of DBCC CHECK DB

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

Howard: upper right hand corner of the code block is a set of icons. Second one in (the two pieces of paper) is "copy to clipboard". I just copied this and am looking at it now.

I'm already doing regular DBCC CHECKDB's but am not logging the results so I may add this into my maint scripts.

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

hmbtx (12/27/2011)
Gianluca
The only reason that I decided to run bwoulfe's code instead of yours is that his already had the code to email the report.
You stated "To get a notification, you can set up a job that runs the sp as query for a dbmail" but I had never done that before and was not certain how to write the proper code."

That's ok. This is what you have to do, but do it after creating the TOOLS database and the dba_runCHECKDB stored procedure.

Navigate your object explorer to the SQL Server Agent and expand it. Right click the Jobs node and select "New Job...".

In the new job dialog, type a name for the job (consistency check, for instance) and select "Steps" from the left panel.

Click the New button on the bottom left corner.

Type a step name (checkdb, for instance). Select Transact SQL Script as step type, leave the rest untouched and type this in the command box:

EXEC [TOOLS].[maint].[dba_runCHECKDB]

@dbName = NULL,

@PHYSICAL_ONLY = 0,

@allmessages = 0,

@dbmail_profile = 'DBA_profile'

@dbmail_recipient = 'dba@mycompany.com'

"DBA_profile" must be a valid database mail profile. See here for details: http://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-2005/

"dba@mycompany.com" should be your email address.

There's no code to run, you can configure Database Mail from the GUI if you prefer.

The "Schedules" section of the new job dialog controls when the job should run. Pick an existing schedule or create a new one.

You also said "I posted a corrected version of this code on my blog: http://spaghettidba.com/2011/11/28/email-alert-dbcc-checkdb/" however, when I went to that site I did not find a way to easily download the code. When I tried to cut and paste it, it also captured the line numbers which I would have had to remove by hand for each line.
Can you tell me an easy way to get your corrected version and also do you happen to have the proper code that I can use in order to email the report?
Any help will be appreciated.
Howard

When you hover the code with the mouse pointer, three buttons should appear on the top right corner of the code block. The second button from the left is the "copy" button, that copies the code to your clipboard.

Before running the code, you'll have to create a TOOLS database and a maint schema:

CREATE DATABASE TOOLS;

USE TOOLS;

CREATE SCHEMA maint;

Paste the code from the blog in your SSMS code editor window and run it after creating the TOOLS database and the maint schema.

Hope this helps.

Gianluca

-- Gianluca Sartori

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 3

Gianluca:

Thank you very much for your reply.

After creating the Tools database and maint schema I ran your new corrected code.

I received the following error:

Msg 208, Level 16, State 6, Procedure dba_runCHECKDB, Line 389

Invalid object name 'maint.dba_runCHECKDB'.

Line 389 starts with:

IF @localTran = 1 AND XACT_STATE() <> 0

ROLLBACK TRAN

RAISERROR ( @Message, @Severity, @State)

END CATCH

END

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

My bad.

The code starts with ALTER PROCEDURE maint.dba_rucnCHECKDB ....

Change it to CREATE PROCEDURE

-- Gianluca Sartori

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 5

I changed the Alter to Create and got the following message.

The module 'dba_runCHECKDB' depends on the missing object 'TOOLS.dbo.sp_send_cdosysmail'. The module will still be created; however, it cannot run successfully until the object exists.

Howard

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

You don't need that on SQL Server 2008.

The code checks for the instance version and never runs that branch of code.

You should be okay now.

-- Gianluca Sartori

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 7

I executed the following code as a query in SSMS prior to setting up the job in order to verify that it works.

EXEC [TOOLS].[maint].[dba_runCHECKDB]

@dbName = NULL,

@PHYSICAL_ONLY = 0,

@allmessages = 0,

@dbmail_profile = 'DBA_profile',

@dbmail_recipient = 'dba@mycompany.com'

I substituted 'DBA_profile' for my dbmail profile name and 'dba@mycompany.com' for my email address and verified they were correct.

The result of the query was:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

However, I never received anything via email.

Am I overlooking something?

Thanks,

Howard

free_mascot One Orange Chip Points: 27169 More actions · Answer 8

If you have configured database mail; check your mail profile by sending test mail.

http://msdn.microsoft.com/en-us/library/ms190307.aspx

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 9

hmbtx

SSCarpal Tunnel

Points: 4664

December 28, 2011 at 10:01 am

#1426172

My test email works.

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 10

Gianluca:

I got your code to work. In order to test it I changed "IF @body IS NOT NULL" to "IF @body IS NULL" in order for the dbmail to send an email.

However, there was no attachement in the email.

Also, I cannot see where the file 'DBCC_CHECKDB_Errors.log' comes from as referenced in the

@query_attachment_filename ='DBCC_CHECKDB_Errors.log'

I am very close to success here.

Any help with these last two items would be appreciated.

Thanks,

Howard

spaghettidba SSC Guru Points: 105732 More actions · Answer 11

The email gets sent only when DBCC failed for at least one database, otherwise it won't send anything.

If you don't like this behaviour, you can change the code to send the e-mail anyway.

'DBCC_CHECKDB_Errors.log' is the file name for the e-mail attachment, it doesn't need to be an existing file.

-- Gianluca Sartori

seatedElephant Say Hey Kid Points: 674 More actions · Answer 12

Many thanks for the useful script Gianluca. I had to run checks on an old server running SQL Server 2000 (I know they are upgrading soon) so I made a few changes and it worked great. In the cursor SELECT statement I've added single quotes to the NAME column as the instance has some databases that have spaces included in the names. This made the script run fine for all the database checks. Also the SELECT references master.dbo.sysdatabases as it is 2000.

CREATE PROCEDURE [dba_runCHECKDB]

@dbName sysname = NULL,

@PHYSICAL_ONLY bit = 0,

@allMessages bit = 0

AS

BEGIN

IF OBJECT_ID('tempdb..#DBCC_OUTPUT') IS NOT NULL

DROP TABLE #DBCC_OUTPUT

CREATE TABLE #DBCC_OUTPUT(

Error int NOT NULL,

[Level] int NOT NULL,

State int NOT NULL,

MessageText nvarchar(256) NOT NULL,

RepairLevel int NULL,

Status int NOT NULL,

DbId int NOT NULL,

[Id] int NOT NULL,

IndId int NOT NULL,

[File] int NOT NULL,

Page int NOT NULL,

Slot int NOT NULL,

RefFile int NOT NULL,

RefPage int NOT NULL,

RefSlot int NOT NULL,

Allocation int NOT NULL

)

DECLARE c_databases CURSOR LOCAL FAST_FORWARD

FOR

SELECT '''' + [Name] + ''''

FROM master.dbo.sysdatabases

WHERE [Name] = ISNULL(@dbName, [Name])

OPEN c_databases

FETCH NEXT FROM c_databases INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @sql nvarchar(4000)

SET @sql = 'DBCC CHECKDB(' + @dbName + ') WITH TABLERESULTS, ALL_ERRORMSGS'

IF @PHYSICAL_ONLY = 1

SET @sql = @sql + ', PHYSICAL_ONLY '

INSERT INTO #DBCC_OUTPUT

EXEC(@sql)

FETCH NEXT FROM c_databases INTO @dbName

END

CLOSE c_databases

DEALLOCATE c_databases

IF NOT EXISTS (

SELECT 1 FROM #DBCC_OUTPUT

)

BEGIN

RAISERROR('No database matches the name specified.',10,1)

END

SET @sql =

'SELECT DB_NAME(DbId) AS DatabaseName, ' +

CASE @allMessages

WHEN 1 THEN '*'

ELSE 'Outcome = CASE WHEN MessageText LIKE ''%0 allocation errors and 0 consistency errors%'' THEN 0 ELSE 1 END, MessageText '

END + '

FROM #DBCC_OUTPUT ' +

CASE @allMessages WHEN 1 THEN '' ELSE 'WHERE Error = 8989' END

EXEC(@sql)

END

Dave Morris :alien:

"Measure twice, saw once"

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

I know this is an old thread, but since it was reactivated today, I suggest that the code being used for checkdb be updated to also include running checkdb against tempdb. Tempdb can get corruption and the fix isn't just to restart SQL Server (you have to delete the tempdb files). So it would be good to know if/when tempdb becomes corrupt.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

shahgols SSCrazy Eights Points: 9874 More actions · Answer 14

Many thanks for sharing the code Gianluca, it's great!

spaghettidba SSC Guru Points: 105732 More actions · Answer 15

Dave Morris (1/23/2013)
In the cursor SELECT statement I've added single quotes to the NAME column as the instance has some databases that have spaces included in the names. This made the script run fine for all the database checks. Also the SELECT references master.dbo.sysdatabases as it is 2000.

Glad you liked my script.

However, you'd better use the updated version you can find on my blog here[/url].

It handles garbage database names and SQLServer versions through 2000 to 2012.

-- Gianluca Sartori