Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
T2000
Starting Member
3 Posts |
 Posted - 2006-02-26 : 08:39:11
|
| Hello 2 all,Could someone advise and/or correct me with my thoughts on how I would do my db maintenance plans?(db's on SQL2000 as 'full' model)Backups:1) Daily Transaction log backups scheduled frequently enough.2) Full Backup scheduled daily. Good way to start I presume ;)Maintenance: Would be scheduled daily if possible, on non-production hours and if not colliding with daily full BU schedule.3) Full DB reorg data&indexes.4) Update Query Optimizer Stats (although 'Auto Update Stats' is on)5) Shrink the logfile (ldf) as I presume this will have grown due to previous maintenance jobs.6) If 5 ok, alter ldf filesize back to new allocated size.Rgds,T. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-26 : 21:24:07
|
3) Make sure that the 10% free space thingie is NOT checked (its checked by default) - otherwise it will change all your indexes to 90% fill  5) No NO Please NOT !!!It your database / log file needs X MB/GB then shrinking it just means it has to re-acquire the disk space again - and whilst its doing that connections are likely to time out, and each time it re-extends the files they are likely to get more fragmentedhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Shrink,MaintenanceKristen |
 |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-02-27 : 08:59:57
|
| CHECKDB, although I'm sure Kristen's link probably covers that. |
|
|
|
Kristen
Test
22859 Posts |
|
|
T2000
Starting Member
3 Posts |
Posted - 2006-02-27 : 11:31:02
|
quote:
Originally posted by Kristen
5) No NO Please NOT !!!Kristen
I understand the performance drop when a db (mdf/ldf) has to auto-grow. Currently I have my mdf's and ldf's with enought allocated space so auto-grow _should_ not occur. Ok there. I have also created alerts to notify when the ldf size rise above my allocated ldf space. If so, I think I've set the allocation too low and will increase it and monitor it again.But on the "No No" part, can you argument on that? Have you got an idea on how much the ldf CAN grow if optimizing jobs are started? Say the ldf is allocation to 20% of the mdf size. All is fine in production time (week) and the ldf does not auto-grow. But in the weekend when optimizing starts, it does auto-grow. There's not other means than to shrink (LDF ONLY!!!) back to its allocated size of 20% mdf.Rgds,T. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-27 : 12:14:57
|
| "Have you got an idea on how much the ldf CAN grow "Personally I do not worry about LDF size up to 120% of MDF filesize."can you argument on that?"Please read the links I posted above." But in the weekend when optimizing starts, it does auto-grow. ... back to its allocated size of 20%"Please read the links I posted above.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-27 : 13:11:51
|
| We've got several LDFs that are 2 times greater than the MDF. The cause is DBCC DBREINDEX. You can use DBCC INDEXDEFRAG instead though.In a production environment, shrinks should never occur unless you know for sure that the space will never be needed again. You receive a huge performance penalty if the file has to grow. So let SQL Server have it.Tara Kizeraka tduggan |
|
|
|
|
|
|
|
|
Maintenance plan OK?