July 8, 2019 at 3:28 pm
Actually auto shrink would cause greater transaction log growth - not less. Shrinking the files is a logged activity.
I'd just set it to simple recovery and leave autoshrink off. the db will be faster, generate fewer logs and use less resources.
July 8, 2019 at 3:35 pm
Yes - by all means do a one-off shrink after you've truncated all those tables, but don't set auto-shrink on, otherwise you'll be wasting resources shrinking and growing, shrinking and growing in perpetuity.
John
July 8, 2019 at 3:54 pm
+1 for simple mode
July 8, 2019 at 4:15 pm
Thank you for the reply. I always set my test DB's to simple recovery mode, yet I have mistakenly thought setting Auto Shrink to true would be better option for the DB which will be mainly used for query tests. In which cases one would set Auto Shrink to true, please?
July 8, 2019 at 4:30 pm
Autoshrink - never. I mean, I've struggled to think of a scenario where this makes sense. Paul Randal, who managed and wrote code for the storage engine thinks this should be removed.
Maybe for client machines that run an Express edition and are really space sensitive, but really, no. Just don't.
July 8, 2019 at 7:12 pm
The only case I have ever seen that autoshrink MIGHT be reasonable is on busy data warehouse, where extreme amounts of logging happen, then its done writing until the next day.
One of my data warehouse servers are like this, which has an incredibly complex job that runs each day with about 400 ETLs which use C#, stored procedures and native SSIS transforms to construct olap data sources from several remote, and generally very dirty data sources. It goes through, cleans the data up, transforms it then stages the data out before loading it into the data warehouse. It really blows the log up and then loads just a little bit (still tens of thousands of rows) of data that has actually changed, then rebuilds all the indexes and finally shrinks. The log files will typically be at least 10-12 times larger than the data file by the time it is done.
I think that more development time could probably reduce some of the logging however. Even then, once I get some of my SQL environment consolidated and can reclaim some SSD, i'll probably just give it more SSD and have the developer turn that step off.
Viewing 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply