Integrity checking

Question

Integrity checking

SQL Galaxy

One Orange Chip

Points: 29472
More actions
April 29, 2009 at 4:31 am

#97686

Integrity checking
I created database maintenance plan for Integrity checking ever 1 week on Sunday night, after completion of jobs database size have been increased automatically.
Please tell me, anything goes wrong on this database after end of Integrity checking jobs .
350 GB database size, after end of Integrity checking jobs size have been increased more than 5 GB
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

Integrity check shouldn't grow a DB, it just reads data. Index rebuilds will grow the DB

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

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

Check your maintenance plan or other jobs to see if they are rebuilding indexes.

tempdb may get used in a checkdb, but it shouldn't grow the db that I'm aware of.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 3

Thanks for your 's reply Steve & Gail..

I should be disabled Re-organaize index page from maintenance plan.

instead of that can i enable to remove unused space from datafiles.

1. shirnk database when it goes beyond : 50 MB

2. Amount of free space to remian after shirnk : 10 % of the database.

Could you tell me, above 2 points which is goes right or wrong. on the production database.

Thanks

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

ananda.murugesan (5/2/2009)
Thanks for your 's reply Steve & Gail..
I should be disabled Re-organaize index page from maintenance plan.

Absolutely not. You need to be rebuilding your indexes on a regular basis

instead of that can i enable to remove unused space from datafiles.
1. shirnk database when it goes beyond : 50 MB
2. Amount of free space to remian after shirnk : 10 % of the database.

Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.

Shrinking causes massive index fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 5

Thanks so much Gail for your valuable reply....

I should correct my mistake....

Please check the following steps and times for maintenance plan.

1.Rebuild index every week sunday on 8:00 PM.

2. Update_statistices daily on 2:00 AM (precentage of database sampling 10 %)

3.Check Integrity every week sunday, Tuesday, friday on 12:45 AM.

4. Full Backup daily on 9:00 PM .

5. TLog backup daily on every 1 hours.

Could you suggestion me above steps are have been configured at production database.

Thanks.