Sql-server – SQL Server 2008 Database 250GB on a 250GB drive

backupsql serversql-server-2008

Started a new job and there is a 2008 sql database that is 250gb in size. The developers insist that the size is about right. THe problem is that the 250gb MDF file is sitting on a 250GB drive.

I have tried to do a backup of the database but i get this error:

System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file "C:\Backup\backup.bkf:" 112(failed to retrieve text for this error. Reason: 1815). (Microsoft.SqlServer.Smo)

I basically have two options:

  • Try and reduce the size of the MDF, if this is possible!
  • Upgrade the Raid Array

Any help appreciated.

Thanks

Best Answer

Yes, you'll definitely have to perform the backup to a different location that has sufficient free space. If you have SQL2008 R2 Standard (or any release of 2008 Enterprise), you can do a compressed backup (also possible with 3rd-party software like Quest Litespeed). Otherwise, your backup will be as big as your data file size + log file size.

Regarding shrinking, you'll have to first determine if it is even possible to shrink the file, by knowing the used space/free space within the file itself. A 250GB file could have 240GB used and 10GB free, or vice-versa. (Easiest way to determine this: right click the database and go to Tasks > Shrink > Files. DON'T hit OK, just look at the Currently allocated space VS "Available free space" figures, dropping down the "File type" box to see the data vs log).

Depending on what you find, you'll have a few choices:

  1. If you have sufficient free space within the file, you might be just fine. The data file isn't going to try to grow until it completely fills up (with some exceptions, like an index rebuild on a particularly massive table)
  2. If you do have free space within the file, you could try to recover some of it. (SQL needs some "elbow room", so to speak, so don't try to recover all free space.) Use the "Release unused space" option first (this will simply free the space already at the end of the file). Then, if you still need more, pick a specific target size, instead of trying to recover all free space. Always follow up with a full index rebuild, since data shrinks cause file fragmentation.
  3. If the data file is mostly full, then you might still have some options: You can add a new data file (in the same "filegroup") on a different drive. Make sure the 250GB file is set not to auto-grow, and that the new new file (on a different drive) is set to auto-grow. So any "overflow" from the first file will grow into the second file.
  4. If you want to be a little more deliberate, you could create a file (on a different drive) with a new Filegroup. Then you'll need to move specific tables or indexes to this new file (using ALTER TABLE)
Related Topic