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.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 2012 Shrinking data or log files

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -