Database – Why is the empty SQL 2008 database so big

This is fairly straight forward... Here is your script below. Let me know if you need anything else.

THanks!

-VM

USE [MasterScratchPad]

GO

ALTER DATABASE [MasterScratchPad] REMOVE FILE [MasterScratchPad_X1_Log]

GO

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)