1

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.

3
  • Can you get the customer to temproarily hook up an external USB drive?
    – jftuga
    Oct 28, 2011 at 18:04
  • This is probably my last resort. There is nobody on site at the client site right now and there won't be for a few days due to flooding (customer is in Thailand). We are really hoping to have this all solved before they are back in the office though, I have remote access.
    – Jon
    Oct 28, 2011 at 18:26
  • Might be onto something here.... I just found this command and it seemed to work ALTER DATABASE dbName MODIFY FILE( NAME = dbName , FILENAME = 'c:\dbName.mdf' );
    – Jon
    Oct 28, 2011 at 18:32

2 Answers 2

2

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.

0
0

If the disk volume is using NTFS 4K allocation units you may be able to stop the instance, compress the database files (do the smallest first) and then restart the instance and bring the database online.

Another option might be to add space to the server using iSCSI and then mount the volume below the existing volume. This is a long shot but is worth looking into.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .