I have a few hundred database catalogs that I administrate over and found that I was desperately running out of space. I had recently been attempting to shrink my databases using the Auto Shrink wizard that comes with SQL Server. This was doing the job of bringing the file size of my databases down to reasonable levels for a few of my databases. I still had the problem where there was a minimum limit on other databases, even though there was more then 90% of free space allocated.
It appears that I was in the rite place, but I just wasn’t doing everything that I needed to do. The Auto Shrink wizard has another option to shrink individual data and log files rather then the entire catalog as a while. It will allow you to specify the size to shrink the file down to, and indicate the minimum value.
I found that shrinking log files required an extra step. The wizard wasn’t able to detect how large the log file was. I had to go into the database settings and turn off the “Auto Close” property. Once this was done, the wizard was able to detect the logs file size.
The next problem that I ran into was that a minimum size of 0 MB was indicated for some of my log files. When I went along and asked the wizard to shrink the file down to 0MB, it told me that the file was truncated, but hadn’t actually done anything. I had more luck when specifying a minimum value of 1 MB for these log files.
I would find that the log file would only shrink down to a minimum size of 25 MB at times. I got around this by turning on “Auto Shrink” and “Auto Close” in my settings. I also set the recovery model to “Simple”. I closed my connection to the database itself and reconnected to find that the transaction log file was able to shrink itself on its own.
With all of these steps, the process can be time consuming. It gets worse with the larger databases as you are left there setting and waiting without an indication of how long the process will take. I may look into automating this process in my spare time.
Note: I realize that putting my databases into a simple recovery model hinders my recovery options. This is not a problem since most databases I am managing are not data-critical production databases. However, if you are in an environment working with live databases, then please backup your databases first before following these steps and do not change your recovery model to the simple recovery model as I have done.
This entry was posted
on Friday, October 7th, 2005 at 6:54 pm and is filed under Computers, Employment, Programming.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.