I have a database in SQL Server 2008 on a 1Tb hard drive and it filled the drive, there is only 4Kb free. The MDF file is 323Gb and the LDF is 653Gb. The hard disk this DB is on has no other files on it other than the MDF and LDF so it's impossible to free up any space on the drive. The main hard disk is smaller but there is enough room to transfer the MDF to that drive, in case that helps. This server is overseas at a customer site and it's not possible at the moment to add more disk space to the server. It's also not possible to delete any records because the DB is in a failed mode (due to no disk space) and it doesn't respond to most commands. The Db is currently in full recovery mode which is why the LDF file is so large. This DB really doesn't need to be in full recovery so going forward we plan on switching it to simple mode which will save us a lot of space. I also don't care about losing the LDF file, but I need all of the data. I've spent a lot of time looking for a way out of this problem but everything I've found first involves either freeing up disk space or adding more disk space, neither of which is an option at this time. I'm stuck and any help would be greatly appreciated.
I get the following log when trying to switch the DB to online mode.
Msg 945, Level 14, State 2, Line 3 Database 'DBNAME' cannot be opened due to inaccessible > files or insufficient memory or disk space. See the SQL Server errorlog for details. Msg > 5069, Level 16, State 1, Line 3 ALTER DATABASE statement failed. Msg 1101, Level 17,
State 12, Line 3 Could not allocate a new page for database 'DBNAME' because of
insufficient disk > space in filegroup 'DEFAULT'. Create the necessary space by dropping > objects in the filegroup, adding additional files to the filegroup, or setting autogrowth > on for existing files in the filegroup.
I've found the following solutions but none work due to having no disk space on that drive, and since the DB is in a failed state I can't run most commmands.
– DBCC SHRINKFILE – can't be run because doing a 'use DBNAME' fails
– Detaching the DB and then changing the location of the MDF/LDF files, this fails because the DB is in an offline mode so you can't run detach.
I'm at a loss about what else to try.
Thanks.
Best Answer
This is because you're not doing backups. Your log files (.LDF) get cleared when you do a backup of the database. You can either do a real backup and shrink the logs, or you can use simple recovery mode and shrink the database files to trick SQL Server into thinking that you did a proper backup, but all it will do is free up space. It will prevent your ability to replay the transaction logs and roll back. Be sure you understand the implications before doing this.
Of course, this may fail given your extreme disk space issue, so you may need to extend the drive first (which you said may not be easy) before any of these commands can work.