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 |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2007-02-22 : 11:57:25
|
| Guys,I've been asked to properly schedule some jobs on our system for a database following a full recovery model. I realized that the order for some of the jobs might matter and came up with the solution, which is described below. Please let me know if this ordering makes sense and/or if you have any general comments/suggestions:1. Backup all dbs [in case defragmentation messes something up]2. Defragmentation [this will grow the transaction log]3. Backup transaction log [backs up trans log and truncates inactive portion of it]4. Shrink log fileAlso, I've been previously suggested not to shrink the log file, unless absolutely necessary, as it is resource intensive. In my experience, however, this operation does not seem to be resource intensive at all. It took me 1 second to shrink the transaction log file from 25GB to 2GB. Is it regrowing of the log file that will affect performance?Also, if I do shrink the transaction log file, is there a suggested value to shrink it to - possibly a function of the database file size?Thanks a lot |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-22 : 13:05:23
|
| "Is it regrowing of the log file that will affect performance?"See your previous question:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79030#287258How are you planning to do 2. Defragmentation? Using REINDEX presumably?REINDEX will neatly move all the indexes to fresh space at the end of the file ... and then the SHRINK will chop them up and move them to any free slots earlier in the file ... which rather undoes all the good that was done!Do this:1. Make sure your backup policy is in place - e.g. Full backup of the database every night, and the TLogs every, say, 10 minutes.2. Put in place your Defragmentation policy - e.g. once a week2b. (And update Statistics and so on)3. Shrink the database just once, if you haven't done so before / recently.4. Let it grow back to whatever size it needs to be to get through the week5. When you next have scheduled downtime physically defrag the files (whilst SQL Server services are stopped)Kristen |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-02-22 : 17:05:57
|
| Kristen,We use "DBCC INDEXDEFRAG" to perform defragmentation. Who will SHRINK chop up? If we shrink the Transaction Log file, why should it touch the indexes at all? Am I missing something here ... please advise?In step 3, are you referring to the database or the transaction log or both?Thanks a lot for all the suggestions! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-22 : 17:54:50
|
| Shrinking doesn't affect the indexes, it affects the file size. Why do you need to do the shrink at all? How much free disk space do you have when the tlog is at its biggest?If no defragmentation jobs exist, then the tlog is usually around 25% of the data file. When DBCC DBREINDEX is in place, I've seen the tlog be greater than the data file.Tara Kizer |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-02-22 : 18:27:06
|
| "Shrinking doesn't affect the indexes, it affects the file size"That's what I thought ... that is why I got a bit confused by what Kristen said.Tara, I know that you do not support shrinking in general. However my transaction log, before I shrank it yesterday, was 25GB in size, while the database is 9GB. And on top of it, we were using simple recovery model - which is another reason I was surprised by this.I changed the recovery model to full (since we decided that we need point in time recovery) and also to be able to back up the transaction log, so that we can shrink it upon necessity ... and shrank it to 500MB. Throught the day, trans log went up to 507mb only.Thanks for the suggestions! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-22 : 18:41:25
|
| 25GB is a bit excessive for a 9GB database. I suspect that the database was set to FULL recovery model and no transaction logs were being performed. Then someone switched it to SIMPLE. Either that or some very large transaction ran which caused it to grow that size.If the tlog grows to ~10GB during a reindex, then I'd suggest leaving it as it obviously needs it.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-23 : 08:27:58
|
"Shrinking doesn't affect the indexes, it affects the file size."Well maybe I misunderstand what happens then, I'm keen to understand it better though.Say I have a database and it has a big-ish table with clustered index and some other indexes.Lets assume that the database has close to zero slack space at that moment.Then I run REINDEX.My understanding is that will move the whole Clustered-Index (and its associated data) to the end of the MDF file - indeed, it will have to extend the MDF file because there is insufficient space in this example.The other indexes will get tackled similarly - except they will probably fit into the space just vacated by the reindexing of the big Clustered index.If you then SHRINK the database the free space, now scattered around the middle of the MDF file, has to be reorganised.My presumption was that stuff from the end of the MDF file is "slotted back in" to the free spaces that can eb found earlier in the MDF file. The index will have the same density, as before, but it will now be less contiguousIts the less-contiguous bit that I was thinking of, but I don't know if that's right or not?(Either way, sql_er was using INDEXDEFRAG, so I doubt the MDF is full of slack space ... and thus shrinking the MDF will be less of a problem)sql_er: I reckon you are now at my Point 4 above: "Let it grow back to whatever size it needs to be to get through the week"  Kristen |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-02-23 : 09:57:52
|
| Tara: Your theory could be right. I inherited the system. So, unless there is a place where the history of switching between the database models for each database is recorded, I'll never really know. I guess the best thing to do now is to monitor the transaction log, especially what happens to it after the defragmentation job (which we run twice a week)Kristen: I am still confused. Why are you still referreing to the mdf file? I did not mention shrinking the mdf file - I was referring to the transaction log (i.e. ldf) file. Shrinking one does not affect the other, no?A related question: Should I ever considering shrinking the database (i.e. mdf) file itself?Thank you |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-24 : 04:51:45
|
"Why are you still referreing to the mdf file?"You did say "shrink the log file", but it didn;t register in my pea-sized brain, so my answer was referring to putting the whole lot on a Hot Wash! Sorry about that."Shrinking one does not affect the other, no?"Not if you script it, no. But if you use the Enterprise Manager "Shrink Database" option it will do both.I'm not sure it matters to much shrinking the lot, to be honest. You need to be aware that it may cripple performance on a heavily used database whilst the files grow back again. I don;t know if your database is "heavily used"?But after a week it will have regained equilibrium.So as you "inherited the system" this might help get it back under control.Having said that if you have very little / modest "slack" space in the MDF file then don't bother with shrinking that ..."Should I ever considering shrinking the database (i.e. mdf) file itself?"Damn! My Read-ahead mode was turned off!I've probably already answered that, but deleting a big table might be a reason. Or some predecessor's daft MDF non-maintenance policy perhaps?  Kristen |
|
|
|
|
|
|
|
|
Wise Job Scheduling