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 |
|
leoc50
Yak Posting Veteran
54 Posts |
 Posted - 2014-09-26 : 12:46:32
|
| In the old days I remember I used to use backup .... with truncateonly, but I believe that has been removed in some later versions. I'm interested in the "ocassionally" actual file size shrinking.Any ideas or suggestions?Thanks,Leo- lec |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-09-26 : 12:56:35
|
| Backup with truncateonly didn't shrink the database anyway, so you aren't losing that functionality. What you are losing is the ability to clear the transaction log (completed transactions) without having to backup the log or switch to simple recovery model.Occasionally running a shrink would be fine if it's because a large delete occurred and you know you don't need that space anymore. Be aware of the massive fragmentation it's going to cause though. What I mentioned is regarding the data files. Regarding the log files, it needs to be sized big enough to handle index rebuilds (if that's in place) and the largest transaction (can't answer that for you as that's system specific). In addition, it depends on your recovery model and then if not using simple, it depends on how often you backup the log.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
leoc50
Yak Posting Veteran
54 Posts |
Posted - 2014-09-26 : 13:28:12
|
| Thanks for the clarifications and/or memory lapses but I do appreciate your input and specially the fragmentation...I had overlook that part.Cheers,LC- lec |
|
|
|
|
|
|
2012 Shrinking data or log files