I have been involved in countless database migrations. Most of the time I have access to the existing source servers and future destination servers. In cases where you have access to both environments, it is very easy to evaluate and clean up any issues with the source databases. In situations where you are only provided the database backups from the source, you may run into issues of bloated databases.
Recently a lot of projects I’ve been part of, we simply get the databases to restore to a new environment. Things are very standard on the new environment, however the source data was handled by various customers. Often enough when restoring a database, I’d run into an error that there isn’t adequate disk space to perform the restore.
For example, I created an 80GB empty database, backed up the database, then tried to restore it to drive C: which did not have enough space.
When dealing with this in the real world, you are presented with a couple of options. One is to add more storage to accommodate the size of the database files or Two, determine if the source database has a lot of wasted space and needs to be resized by shrinking one or more of the database files.
To determine if the source database is bloated, an easy option is to email whoever sent you the database backup file and ask if they can run a query, something like sp_spaceused or something more elaborate to give you more data. Another option that I’ve found helpful to quickly review is to look at some data from the backup file itself.
You can restore headeronly or filelistonly to obtain vital details about the backup file. For my example, I get the the following results.
If you look at size 85899345920 = 81,920MB or 80GB the BackupSizeInBytes 89194496 = 85.06 MB which is a clear indication that the raw backup file is significantly smaller than the 80GB database. With this data, I would have a very high confidence level that there is significant wasted space in the database LargeDB.
When I connect to the database itself and run sp_spaceused, I get the following.
As you can see, there is approx 80GB of unallocated space in this database. I would then reach out to the customer DBA and ask them to shrink this database to a reasonable size, then take a fresh full backup and resend the backup file for us to restore.
In the real word, I’m dealing with this on a level of 100’s of GB of unallocated space, or just a massive transaction log file that is a bit quicker and easier to clean up.
To help make this easier, I created a quick video walking through this process.
The post There Is Insufficient Free Space on Disk Volume To Create the Database appeared first on Tim Radney.