{"id":3337,"date":"2012-12-05T02:39:04","date_gmt":"2012-12-05T02:39:04","guid":{"rendered":"http:\/\/craftydba.com\/?p=3337"},"modified":"2024-02-17T17:15:54","modified_gmt":"2024-02-17T17:15:54","slug":"check-database-integrity-task","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=3337","title":{"rendered":"Check Database Integrity Task"},"content":{"rendered":"

\"\"<\/a><\/p>\n

Today, I will be continuing the series of articles on the control flow tasks available in SQL Server Integration Services (SSIS<\/a>) to perform Database Administration (DBA<\/a>) tasks. We will be designing a SSIS package that checks the database integrity of all the user databases. <\/p>\n

I am going to start up SQL Server Data Tools (SSDT<\/a>) which leverages the Visual Studio 2010 shell. This development environment replace the older Business Intelligence Development Studio (BIDS<\/a>) which was released with SQL Server 2005.<\/p>\n

I will create a project named “check-database-integrity”<\/span> under the “c:\\ssis-depot\\” directory. Upon having a blank package, we will be adding and configuring several objects. You can either craft this package from scratch or use the “full-user-backup” package as a starting point.<\/p>\n

I am going to list each of the objects we will need to solve this business problem.<\/p>\n

cmMySqlServer<\/span> \u2013 The local database server connection information.<\/p>\n

cmMySmtpServer<\/span> \u2013 The local mail server connection information.<\/p>\n

tskCheckDbIntegrity<\/span> \u2013 This is a control flow task based on DBCC CHECKDB.<\/p>\n

tskEmailSuccess<\/span> \u2013 Notify email list that the package completed successfully.<\/p>\n

tskEmailFailure<\/span> \u2013 Notify email list that the package failed miserably.<\/p>\n

The Check Database Integrity task is the only new object on the control flow canvas. Let’s take a closer look at the properties that can be configured. <\/p>\n

You will need to select the database connection in which the task will be performed on. Do not forget to include indexes. Otherwise, all index pages in the database will be skipped during the check since they can be rebuilt.<\/p>\n

\"\"<\/a><\/p>\n

By clicking the database drop down list, we have the ability to specify user, system or individual databases to be checked. Also, we can tell the package to skip databases that are not on line.<\/p>\n

\"\"<\/a><\/p>\n

After you have your package looking like mine, you can execute (debug) it in the environment. Hopefully it runs to completion without any errors.<\/p>\n

\"\"<\/a><\/p>\n

I just noticed a success email that was sent to my email account and it appear in MS Outlook application.<\/p>\n

\"\"<\/a><\/p>\n

I wonder what the SSIS package is actually doing to perform the backup? To discover what T-SQL is being sent to the server, we can open a client\/server side trace using SQL Server Profiler<\/a>. I am going to skip over the mechanics of starting a profiler trace and running the SSIS package in debug mode.<\/p>\n

The whole trace file (\u201ccheck-database-integrity.trc\u201d<\/span>) along with the SSIS package (\u201ccheck-database-integrity.dtsx\u201d<\/span>) are included as a download bundle at the end of the article.<\/p>\n

Basically, there are two distinct TSQL commands: one to include index checking and one to skip index checking.<\/p>\n

\r\n-- With indexes\r\nUSE [MATH]\r\nGO\r\n\r\nDBCC CHECKDB(N'MATH') WITH NO_INFOMSGS\r\nGO\r\n\r\n-- Without indexes\r\nUSE [MATH]\r\nGO\r\n\r\nDBCC CHECKDB(N'MATH', NOINDEX) \r\nGO\r\n<\/pre>\n
<\/P><\/p>\n
In conclusion, the Check Database Integrity<\/a> task is calling our old friend DBCC CHECKDB<\/a> under the covers.  I have no issues with scheduling the enclosed package to check the health of my databases in the future.  Next time, I will be talking about reorganizing and rebuild indexes uses control flow tasks.<\/p>\n
check database integrity bundle<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Today, I will be continuing the series of articles on the control flow tasks available in SQL Server Integration Services (SSIS) to perform Database Administration (DBA) tasks. We will be designing a SSIS package that checks the database integrity of all the user databases. I am going to start up SQL Server Data Tools (SSDT) which leverages the Visual Studio 2010 shell. This development environment replace the older Business Intelligence Development Studio (BIDS) which was released with SQL Server 2005. I will create a project named “check-database-integrity” under the “c:\\ssis-depot\\”…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[661,662,663,12,15,638,28,642,636,643,644],"class_list":["post-3337","post","type-post","status-publish","format-standard","hentry","category-other","tag-check-database-integrity-task","tag-control-flow","tag-dbcc-checkdb","tag-free-code","tag-john-f-miner-iii","tag-send-mail-task","tag-sql-server","tag-sql-server-profiler","tag-ssis-developer","tag-trace-file","tag-tsql-trace-template"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3337","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3337"}],"version-history":[{"count":1,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3337\/revisions"}],"predecessor-version":[{"id":8950,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3337\/revisions\/8950"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3337"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3337"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3337"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}