Today there was an activity to perform restore on the PROD server using the TEST database backup file,the
test Backup file is around 23GB, so moved to Production server and started executing the restore command but the below error has thrown-
Msg 3257, Level 16, State 1, Line 2There is insufficient free space on disk volume 'R:\' to create the database. The database requires 293066571776 additional free bytes, while only 75202101248 bytes are available.Msg 3119, Level 16, State 4, Line 2Problems were identified while planning for the RESTORE statement. Previous messages provide details.Msg 3013, Level 16, State 1, Line 2RESTORE DATABASE is terminating abnormally.
So used the below query to see what are the files that exists in the Backup file i,e size,Maxsize, Filegroup,Logical file name and Physical filename.
Restore filelistonly from disk='<Drive>:\XXXXXX.BAK'
so we observed that the Initial size of the MDF file is near to 176GB and remaining other files(1 ndf and 2 ldf files) occupies is 96.93GB ->Over all 272.3G.
But when we have checked - what is the usuage of the database ?
--the database has 78% free space in it & used space is 22%.
how ever our restore strategy pointing to the drive where it was less space i,e R:Drive has 70.73GB free space out of 120GB, hence the error throwns that-
"There is insufficient free space on disk volume 'R:\' to create the database. The database requires 293066571776 additional free bytes, while only 75202101248 bytes are available.Msg 3119, Level 16, State 4, Line 2"
I,e A)
the database requires 293066571776 additional free bytes --->Here the whole Database size 272.93GB i,e the Physical files to get initialize on the Physcial disk eventhough the usuage percentage of the database was less.
B)
while only 75202101248 bytes are available ---------->Here clearly expalins that in the drive which you are restoring is have only 70.73GB hence database not able to Initialize .
so the next error will be as mentioned below -
Msg 3119, Level 16, State 4, Line 2Problems were identified while planning for the RESTORE statement. Previous messages provide details.Msg 3013, Level 16, State 1, Line 2RESTORE DATABASE is terminating abnormally.
To resolve
==========
1.Before taking the Backup of the database --Verify what is the Initial file size and check what is the overall usuage of the indivual file size i,e how much used space & free space.
2.Once identifed then go for altering the initial file sizes using the Alter command or you can go for shrinking then take the backup and restore it on where ever you want.
3.or If any other drive has enough space to hold then change the restore command i,e where you wan to be Physical files to be
or if you do not have the space then check Muliple drive free space then you can add accordingly.
Bottom Lines
=============
Want to know more about what is the Database file initialization then refer the below links -
http://msdn.microsoft.com/en-us/library/ms175935.aspx
http://sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-instant-file-initialization.aspx
http://sqlserverperformance.wordpress.com/2011/02/10/windows-instant-file-initialization-and-sql-server/
http://sqlblog.com/blogs/tibor_karaszi/archive/2009/03/09/do-you-have-instant-file-initialization.aspx
http://www.databasejournal.com/features/mssql/article.php/3899156/Instant-File-Initialization-for-SQL-Server-20052008.htm
Thanks,
Rama Udaya.K
