you're reading...
Microsoft SQL

Database Consistency Check (DBCC) with daily report generation in MS SQL 2008 R2 & 2012

The following shows how you can automate the process of Database Consistency Checks (DBCC) for your MS SQL servers. This produces an email daily, weekly or monthly depending on how frequent you would like the checks to run and contains a link to a SQL Server Reporting Services report which displays a summary of the DBCC results. I successfully deployed this in a SQL 2012 environment but it should also work fine in a SQL 2008 R2 environment too.

You must have DBMail configured and ready before starting, see my post Configure Database Mail in SQL Server 2012 on how to do this. Also you will need SQL Server Reporting Services installed and Business Intelligence Development Studio 2008 available to create the SSRS report, this comes bundled with MS SQL Server (and the Express Edition with Advanced Services)

The following scenario will utilize DBCC to scan and report on the databases Accounts_DB and HR_DB.
The first step is to create a new Database on the SQL server to store the DBCC results.

1. Connect to your SQL instance using SQL Server Management Studio (SSMS)
2. Right click on Databases in Object Explorer, click New Database…
3. Under General name the database DBA
4. Click Options and ensure Recovery Model is set to Simple. Click OK

Now that we have the DBA database we can create the SQL job that will run the DBCC checks, the steps it carries out are:

1. Drop any existing DBCC tables
2. Recreate new DBCC tables
3. Run DBCC checks against Accounts_DB
4. Run DBCC checks against HR_DB
5. Send E-mail to DBA with link to the report

Back in SSMS do the following:

1. In Object Explorer, expand SQL Server Agent and right click on Jobs. Click New Job…
2. Give the job a meaningful name, e.g. Daily DB Consistency Checks
3. Set the owner to a user account that has privileges to run DBCC against the required Databases e.g. sa account
4. Enter a meaningful description
5. Click on Steps under Select a page (left menu)
6. First we drop any existing DBCC tables. Click New…
7. For Step name type Drop existing DBCC tables
8. In the Database dropdown select DBA
9. In command type the following:


10. Click OK to close
11. Now to re-create the DBCC tables. Click New…
12. For Step name type Create new DBCC tables
13. In the Database dropdown select DBA
14. In command type the following:

CREATE TABLE [Accounts_DB_DBCC] (error int,
level int,
state int,
MessageText nvarchar(2000),
RepairLevel nvarchar(30),
status int,
dbid smallint,
objectid int,
indexid smallint,
partitionid bigint,
allocunitid bigint,
[file] int,
page int,
slot int,
reffile int,
refpage int,
refslot int,
allocation int)

level int,
state int,
MessageText nvarchar(2000),
RepairLevel nvarchar(30),
status int,
dbid smallint,
objectid int,
indexid smallint,
partitionid bigint,
allocunitid bigint,
[file] int,
page int,
slot int,
reffile int,
refpage int,
refslot int,
allocation int)

15. Click OK to close
16. Next is the run DBCC check on Accounts_DB. Click New…
17. For Step name type Run DBCC checks against Accounts_DB
18. In the Database dropdown select DBA
19. In command type the following:

INSERT [Accounts_DB_DBCC] EXEC(dbcc checkdb([Accounts_DB]) with tableresults, ALL_ERRORMSGS)

20. Click OK to close
21. Repeat steps 16 to 20 for HR_DB, renaming the appropriate items (Accounts_DB_DBCC to HR_DB_DBCC and Accounts_DB to HR_DB etc)
22. Finally we send an email if all jobs ran ok (you need DBMail enabled). Click New…
23. For Step name type Send E-mail
24. In the database dropdown select msdb
25. In command type the following:

EXEC sp_send_dbmail @profile_name=DBA,
@subject=Database consistency check results,
@body=Report is available at http://myssrsreportserver/Reports/dbcc_report

26. In the above script, you need to change @profile_name=’DBA’ to the profile name of your DBMail profile i.e. @profile_name=’MyProfile’
27. For the @recipients line, enter the email address of the distribution group or person you want to receive the report
28. For @body, this is what appears in the email body. Here I’ve put the URL to the SSRS report I made which displays the DBCC summary when clicked (the report creation will be covered below)
29. Click Advanced under Select a page
30. Change the On success action to Quit the job reporting success
31. Click OK
32. Click Schedules under Select a page
33. Click New and enter the schedule information that suits your needs (daily, weekly, monthly, time the checks should start etc)
34. Once the schedule is configured, click OK
35. Click OK to close the Job Properties dialog

The DBCC SQL job

This concludes the work needed to be done on the SQL server, now to the SSRS report – I’ve also attached a sample report to the end of this post that you should be able to reconfigure for your own use.

1. Open Business Intelligence Development Studio
2. Click File > New > Project
3. Select Report Server Project and give it a meaningful name
4. In Solution Explorer, right click Reports and click Add > New Item…
5. Select Report, give it a meaningful name and click Add
6. From the Toolbox, add a label to the top of the report and enter the reports title (i.e. Database Consistency Checks)
7. Under Report Data at the left, right click on Data Sources and click Add Data Source
8. Type a name for the data source
9. Ensure Embedded connection is selected and from Type list select Microsoft SQL Server
10. In the Connection string box, enter the connection string for your SQL server – in my example it is Data Source=mysqlserver;Initial Catalog=DBA
11. Click Credentials in the left menu, and select which is most appropriate for your environment – I use Prompt for credentials
12. Click OK and you should see the data source under Data Sources in the Report Data menu
13. Now we need to setup the datasets to get the data from our Accounts_DB_DBCC and HR_DB_DBCC tables
14. Right click Datasets under Report Data and click Add Dataset…
15. Name the dataset, and select Use a dataset embedded in my report
16. Select the data source you just set up from the Data source drop down
17. Ensure Query type is set to Text and enter the following query

SELECT MessageText from Accounts_DB_DBCC WHERE error=’8989′

18. Click Fields
19. Click Add > Query Field
20. Type MessageText into both Field Name and Field Source columns
21. Click OK to close the Data set dialog
22. Repeat steps 14 to 21 for the HR_DB, naming the data set appropriately and modifying the query in step 17 to HR_DB_DBCC
23. From the Toolbox, drag a Table onto your report
24. Highlight the entire first row (Header) by clicking the grey block to the left of the row, right click and click Delete

Selecting the top row of the table to delete

25. Delete the 2nd and 3rd columns in the same manner
26. You should now be left with a single table cell
27. Re-size the cell width so its as wide as the report will allow
28. Right click the cell, select Insert Row > Inside Group – Below

Your table should now look like this

29. Hover the mouse over the first cell and you should see a blue and white icon appear at the right of the cell, click this to select the Data Source, select the first data set and click on MessageText

Hover over the first table cell to show the table data icon (top right)…

…clicking the icon allows you to select the DataSet to use

30. Do the same for the second cell, selecting the second data set and clicking on MessageText
31. Save the report and upload this to your SSRS server

You should now have an automated way to ensure the integrity of your databases and to notify you of any errors that may occur.

Example of the report from my example, its by no means attractive but it is functional

Download the sample SSRS .rdl report by clicking here – it should work in your environment with just a few tweaks to the DataSource/DataSets.

Note: Take a look at the contents of the databases Accounts_DB_DBCC or HR_DB_DBCC – they contain a more comprehensive set of results from the DBCC check, items you may find useful to incorporate into your own reports – the report above only includes a single row from this table, the summary with error 8989


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: