March 5, 2019 at 4:17 am
Hi - I noticed this entry within the SQL log file this morning. I have checked the DB properties for this DB & notice that the Auto Shrink option is set to True which i know is bad practice, as this can cause performance issues.
The DB is relatively small in size & I haven't received any reports from users regarding access/performance. However I would like to resolve this. Can I simply right click on the DB & set the Auto Shrink option to False whilst users are in the database?
I also notice that the Data file is set to 1975 Mb initial size with autogrowth set to 10 percent unrestricted growth & the log file has been set to 2 Mb initial size with autogrowth set to 10 percent unrestricted growth. The data file and log files are on separate drives & the Database is set to Full Recovery mode with transaction log backups being performed hourly.
I would suggest the data & log file sizes have been set up using the default option when setting up a new DB?
I would really appreciate some advice on this, as I've noticed another couple of DB's with the Auto Shrink option set to true & would like to change this for those DB's also.
Many thanks in advance
Dax
March 5, 2019 at 7:49 am
dax.latchford - Tuesday, March 5, 2019 4:17 AMHi - I noticed this entry within the SQL log file this morning. I have checked the DB properties for this DB & notice that the Auto Shrink option is set to True which i know is bad practice, as this can cause performance issues.The DB is relatively small in size & I haven't received any reports from users regarding access/performance. However I would like to resolve this. Can I simply right click on the DB & set the Auto Shrink option to False whilst users are in the database?
I also notice that the Data file is set to 1975 Mb initial size with autogrowth set to 10 percent unrestricted growth & the log file has been set to 2 Mb initial size with autogrowth set to 10 percent unrestricted growth. The data file and log files are on separate drives & the Database is set to Full Recovery mode with transaction log backups being performed hourly.
I would suggest the data & log file sizes have been set up using the default option when setting up a new DB?
I would really appreciate some advice on this, as I've noticed another couple of DB's with the Auto Shrink option set to true & would like to change this for those DB's also.
Many thanks in advance
Dax
Yes you can change it while user are in the database. If you want to use tsql to make the changes, you can use:
ALTER DATABASE YourDatabaseName SET AUTO_SHRINK OFF WITH NO_WAIT
Sue
March 5, 2019 at 8:20 am
Just to add, you could make sure the Model database has this setting off so newly created databases do not inherit this setting.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply