August 27, 2009 at 12:16 pm
I started work at a new place and alot of the databases have auto shrink and auto close marked as true on them and they are having performance issues. Wondering if I set them to false, what is the possiblity that it may mess up the database? I just am very cautious aabout changing things on my first week.
August 27, 2009 at 12:29 pm
Shutting off 'Auto Shrink'. It could possibly be part of your problem with performance. I prefer to do this task with a SQL Agent job. Gives you more control over when it happens and allows for backup prior to the attempt to shrink the database.
Found SSC article on [/url]the auto shrink.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 27, 2009 at 12:37 pm
Wouldn't it be better to shut off Auto Shrink and then have the backup job copy the transaction log and then remove it? Is Auto Close a bad think also. It is turned on as well.
August 27, 2009 at 1:50 pm
Backups don't cause the log file to shrink but rather help control the growth of the file.
I would suggest speaking with someone that is familar with the use of those SQL Servers you find this setting on. Ask them if they know why the settings are turned on. If no one there knows why it is, do some research on both properties to be come more familar with what they do and present that to the right channels to suggest they be turned off.
They can both greatly affect performance of the server. Auto Shrink can cause serious file fragmenation and should rarely be run at all. The Auto Close (from what I read about it) can cause overhead when it is continously stopping and starting databases.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 27, 2009 at 2:02 pm
No one really knows why. I have done some research and I will probably turn them off. Would you suggest having a job shrink it on the weekends?
August 27, 2009 at 2:05 pm
Don't shrink your databases or the logs, unless you have done some one off processing that may have resulted in excessive growth of the log or significantly decreased the amount of data in your data file(s).
If it has grown, it has grown for a reason. Your data files should have enough free space to allow for data growth over s period of 3 to 6 months without having to increase the size of the database.
August 27, 2009 at 2:07 pm
I have a database that is 205GB and the log file is 30GB. Does that sound about normal?
August 27, 2009 at 2:12 pm
It Depends. Not being familiar with your systems I can't really answer that question.
August 27, 2009 at 2:23 pm
Thanks for the help everyone 🙂
August 27, 2009 at 2:34 pm
They may or may not be set liek that intentionally. Since its ur first week, observer the log growth for few days and then may be take a call. But in my opinion it should be off. About Autoclose it depnds on the activity the database has. But in most cases it should be off. This may be a reason for poor perfomance
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply