The pains of SQL Server 2005!
I quite like Sql Server and have used it at various jobs but I recently had a problem restoring a backup that failed with "There is insufficient free space on disk volume" and "restore failed for server" or such like and I had to start a long foogle (find out on google) to try and track down the cause. It is worth mentioning that the free space reported was much lower than was actually available.
It was confusing because I had successfully restored it previously but since the last restore I had installed Windows over the top of the old installation and re-installed SQL Server so I assumed it was a SQL Server setting that had been reset but couldn't find anything.
It turns out the message is quite common and like a lot of half-arsed programs provides a very unhelpful message that maybe sounds more reasonable once the real fault it found out but doe not really lead you there. I thought it was because SQL server might require contiguous space on the disk so I copied off the 35Gb backup which took hours and this didn't help! I also checked that the size of the restored database would fit onto the server since some people think the size of the backup is the same size it will take up when restored but unfortunately not: SQL Server pre-allocates the sizes of tables required even if they are empty. You can fix this on the database but you would then need to take another backup afterwards since you can't modify the backup in this way. Since I had already restored the backup previously, the number reported (35Gb) was about right and I had this space.
My problem turned out to be "quota management" which is turned on by default in windows server 2003 and was preventing me from using over 30Gb of disk space during the restore even though I was not putting the database into My Documents! Not sure why because the quote management looked more like I had 5Gb max but some random number was obtained and displayed to me in the error dialog.
I turned off the quota management (Right-click the disk in explorer and choose Properties->Quota) and everything was fine except now I had copied the backup away from the server I had to restore from the network which was painfully slow.
Not sure why it is such a difficult thing but you get a help link when you get the error and it goes to a microsoft site in which I have yet to see with any help at all, it usually says, "there is no additional help for this error". What a load of crap. These types of problems are massively common but rather than nailing down the help system and making it sweet and usable, we have 400,000 pages of google to try and filter down to our specific problem, oh and old links get broken which is also crap.
It was confusing because I had successfully restored it previously but since the last restore I had installed Windows over the top of the old installation and re-installed SQL Server so I assumed it was a SQL Server setting that had been reset but couldn't find anything.
It turns out the message is quite common and like a lot of half-arsed programs provides a very unhelpful message that maybe sounds more reasonable once the real fault it found out but doe not really lead you there. I thought it was because SQL server might require contiguous space on the disk so I copied off the 35Gb backup which took hours and this didn't help! I also checked that the size of the restored database would fit onto the server since some people think the size of the backup is the same size it will take up when restored but unfortunately not: SQL Server pre-allocates the sizes of tables required even if they are empty. You can fix this on the database but you would then need to take another backup afterwards since you can't modify the backup in this way. Since I had already restored the backup previously, the number reported (35Gb) was about right and I had this space.
My problem turned out to be "quota management" which is turned on by default in windows server 2003 and was preventing me from using over 30Gb of disk space during the restore even though I was not putting the database into My Documents! Not sure why because the quote management looked more like I had 5Gb max but some random number was obtained and displayed to me in the error dialog.
I turned off the quota management (Right-click the disk in explorer and choose Properties->Quota) and everything was fine except now I had copied the backup away from the server I had to restore from the network which was painfully slow.
Not sure why it is such a difficult thing but you get a help link when you get the error and it goes to a microsoft site in which I have yet to see with any help at all, it usually says, "there is no additional help for this error". What a load of crap. These types of problems are massively common but rather than nailing down the help system and making it sweet and usable, we have 400,000 pages of google to try and filter down to our specific problem, oh and old links get broken which is also crap.