May 8, 2008 at 12:15 pm
I'm not sure the best way to articulate this so I'll give two examples I'm aware of:
- While shrinking a database or files, don't take backups.
- If you add/delete files to/from a database, you have to take a full backup before you can take a diff or trn log backup.
Are there any other tasks that you should not do together? Are there any tasks that before or after you do them, you have to do some other kind of task?
I imagine that you can't shrink and reorg or rebuild indexes but I'm finding specific information hard to come by.
Thanks,
Rob
May 8, 2008 at 12:59 pm
1. DO NOT SHRINK - causes fragmentation. Set the sizes you need and do not use the shrink except for one offs if something grows crazily.
2. Not sure, but it would make sense to capture the new metadata about the db.
Not sure what other tasks off the top of my head that might cause issues.
May 9, 2008 at 7:18 am
Steve Jones - Editor (5/8/2008)
1. DO NOT SHRINK - causes fragmentation. Set the sizes you need and do not use the shrink except for one offs if something grows crazily.
Agree completely. I have one db that I'm in the process of purging about 500mil records from 2 tables. I have a task in a maintenance plan that runs every 30 minutes and if the load is low enough, it will delete tables. It has to stay online so I purge in small batches throughout the day. Typically removing between 4-7mil rows per day right now. I will use shrink on that database when I'm done. Another case I've used shrink in is where the tran logs grew abnormally large due to other DBA's not running proper tran log backups on a Full Recovery database. Would you agree that those are appropriate uses for shrink or am I off the mark?
Steve Jones - Editor (5/8/2008)
2. Not sure, but it would make sense to capture the new metadata about the db.
I learned this from BOL: http://msdn.microsoft.com/en-us/library/aa933089(SQL.80).aspx.
Here is another example you or somebody else might be able to help with:
I have some maintenance plans that are running tran log backups every 20 minutes. Nightly I typically take a diff or a full depending on the database. On the weekends for some databases I will also reorg or rebuild indexes. During that time, the transaction log jobs are still running. Will any of these tasks conflict?
80% of my servers are SQL 2005SP2 but I do have a few SQL 2005SP1 and SQL 2000SP3.
Thanks,
Rob
May 9, 2008 at 9:08 am
Ironically, I found this doing QotD and it answers some of my questions:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply