Stopping a check integrity DB job what will happen?

Question

Stopping a check integrity DB job what will happen?

rajsin7786

Right there with Babe

Points: 718
More actions
April 9, 2014 at 4:40 am

#287507

Hi friends,
i have a huge datawarehouse DB (750 GB) with 30 tables, i have never run maintenance plan before i.e. 'check DB integrity' plan in the last 2 years. I have no idea how long this will run for.
I have been asked to schedule a 'check DB integrity job' on every Sunday at 12 but i have also been asked to ensure the job finishes at 5 PM because the SSIS packages start running at 6PM.
My questions are,
1. How do i find out how long a check integrity DB job will run for? (given that we never ran it before)
2. If in case job runs more than 5 hours , how would i go about stopping the job?
3. What will be the impact of stopping a maintenance job(check integrity)? will it corrupt the DB?
4. I am new to SQL server so from where do i normally see the running jobs in management studio?
5. Is there some kind of alerts i can set up to ping the status of the maintenance job when it is running (hourly once)?
Thanks,

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If you stop it before it's complete, you're just wasting time, you won't get the results of what (if anything) is wrong and hence you may as well not have run it.

If you're planning to run database integrity checks (which everyone should) they need to be allowed to run to completion, or run them against a restored backups somewhere else.

As for how long it takes, about the only way is to run it and see. Too dependent on hardware and resources to estimate.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 2

Don't run DBCC checks hourly. That's a very serious load on the system.

Since you've never run the consistency checks, I'm with Gail, let them run no matter how long it takes. It won't prevent other users from accessing the system, although it's likely to cause some resource contention and blocking. Let it finish.

In addition to consistency checks, you might want to also look into defragmenting indexes and manually updating statistics. I'd suggest tracking down a copy of Michelle Ufford's maintenance scripts for this.

Also, do you have SQL Server backups in place? If not, I'd get on that right away too.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

rajsin7786 Right there with Babe Points: 718 More actions · Answer 3

Hey friends,

Thanks for your valuable suggestions. highly appreciated.

@grant:

I will be running the check Integrity job only once on Sunday at 12PM, i hope it doesn't run too long because the SSIS packages start running at 6PM and i dont want them to interfere with each other. the SSIS package deals with the tables in the database where i run the integrity check. Apart from performance issues could there be any other problems?

Also with respect to your suggestion on indexes, i have a fact table which is around 150GB in size, its got a clustered index column which has 78% (avg fragmentation) . reading online forums i understand that performing a rebuild of index is better than reorganize for such columns. So if i used the maintenance plan to rebuild index for this table will it delete and recreate the index or will it build a new index in the background and replace the old index with a new one? when an index is being rebuild what will happen if someone queries this table? will it disturb the rebuild index maintenance job?

I also see an option 'keep index online when re indexing' in the index maintenance job any idea what does this actually mean?

Thanks,

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 4

Rebuilding an index online means it will absolutely be accessible. An index rebuild without the online option will block access while the rebuild occurs. It's a bit of a dance to schedule that appropriately. Either way, online or off, make sure you have space in tempdb because sorting all the data will need the space. Also, the online index rebuild is an Enterprise only feature.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 5

One thing you should know about running DBCC CHECKDB is that it creates a snapshot of the database it is running checks on and runs those checks on the snapshot instead of the actual database, so you shouldn't have blocking issues while the SSIS packages run. There is a (potential) performance impact when it is running so that may have an effect on your SSIS packages.

With regards to online index rebuilds there are some limitations (since you are using 2008) to being able to use this feature like:

columns that include "blob" datatypes and you need to have Enterprise Edition or higher. Also your rebuilds may be slower than when you did them offline. I would also read up on how online rebuilds actually work here

It is a very useful feature just make sure that you understand what it does (and doesn't) accomplish.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Grant Fritchey (4/9/2014)
Rebuilding an index online means it will absolutely be accessible. An index rebuild without the online option will block access while the rebuild occurs. It's a bit of a dance to schedule that appropriately. Either way, online or off, make sure you have space in tempdb because sorting all the data will need the space. Also, the online index rebuild is an Enterprise only feature.

Just to add to that, you can't rebuild indexes online that contain blobs. That means you can't rebuild a clustered index in an online fashion if the table contains a blob nor a non-clustered index if the INCLUDE clause contains a blob.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rajsin7786 Right there with Babe Points: 718 More actions · Answer 7

hi friends,

I ran the check integrity maintenance job last Saturday and it took exactly 10 hours to run successfully and there were absolutely no errors logged in the log file. Unfortunately the DB performance has become poor the next day i.e.. Sunday after the check integrity job. Any idea why this could happen?

The SSIS packages has over run 5 hours its actual run time and this has caused some data missing in the reports which were emailed to recipients (from our front end reporting tool).

Would be of great help if you guide me where should i start my search on finding out the cause of this?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

It won't be due to CheckDB, it makes no changes to the DB.

Look for other stuff that changed. Make sure stats are up to date, the usual performance troubleshooting.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 9

You have other issues going on. Make sure that you have, at minimum, automatic creation and maintenance of statistics enabled. You'll probably need to manually update them as well, but if you have the automatic maintenance turned off you must maintain them. As Gail says, tune the query, tune the system, tune the indexes. It's something along those lines. It's not the consistency check causing any issues (except resource contention while it runs).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning