SQL Server 2005 - reduce database/logfile size on system that is running out of disk space
Posted on 2016-08-10
I need some basic advice on how to deal with SQL logfiles which seem excessive on a server with limited free filespace.
Rather worryingly, our old SQL Server database is on an equally old server whose free file system space is uncomfortably low. The system is on a 60GB disk and disk space has dropped to a few GB. Whilst there are various solutions involving upgrading the disk, OS, SQL version and so on, they're not ones that are going to happen quickly. I need to free up some space relatively quickly before the system grinds to a halt.
The log files are often the same or larger in size than the database files and over the last 24 hours I've dabbled dangerously with shrink operations, many of which seem to have the opposite affect to the one intended, with the log files doubling up in size. Time to stop dabbling and seek some wisdom!
At present the largest of the databases is 7GB with a logfile now limited to 4GB. (Over the course of the last 24 hours, my dabbling has caused the logfile to reach nearly 20GB but have now managed to get it down and set the 4GB threshold while I work out what to do. Without this, the log file just seems to keep growing and I have no idea why. I tried to see what was in the logfile using sys.fn_dblog - the first thing that happened was that the logfile size shot up and I had to abort! )
The disk now has 12GB free space.
I tried a backup of this database this morning but it failed with the exception message: "The transaction log for database 'mydatabase' is full." The backup file created reached 9.5GB leaving 3GB space on the disk. That's pretty much where I am now.
We've got a good leased line connection to this server, so can lift relatively large files off it if needs be.
The current recovery model is set to simple.
I could do with any help to identify why the logfiles just want to keep growing, pare them down to a reasonable minimum and do what I can to ensure the database isn't suffering as a result of any inadvertent disk/page/index fragmentation I may have caused!