2

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

3
  • 1
    Where are the log files if the MDF is taking up the whole drive?
    – Chris S
    Jun 25, 2010 at 15:57
  • possibly using a mount point, maybe?
    – tony roth
    Jun 25, 2010 at 16:14
  • 1
    all of our sql installs have everything broken out onto mount points under a dir on the c: drive. The c: drive appears to be full but there can be many TB's on the mounted volumes
    – tony roth
    Jun 25, 2010 at 16:57

3 Answers 3

2

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)
0

You can try to shrink the database files. Right click the database -> Tasks -> Shrink (inside of Mgmt Studio). For a 250 GB database, this will take a long time - maybe up to an hour.

Or you can attach an external USB hard drive and try backing up to that.

2
  • I've seen it take many many hours on a good sized database; it seems to work best if you do it in small chunks.
    – SqlACID
    Jun 25, 2010 at 16:33
  • Why -1? The OP asked how to shrink databases, the answerer told how to do that. This is a perfectly fine answer.
    – Massimo
    Sep 14, 2010 at 10:45
0

Obviously, take the backup on another drive. If the database must have 250GB, then leave it at 250Gb. You need additional space for maintenance operations, you have a perfect business justification for additional storage. Here's a good reason not to run SHRINKDATABASE...

You must log in to answer this question.

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