SQL Server – insufficient free space on disk volume while restoring database

Sometime you may come across situation where you try to restore database having large transaction log for small database. We also faced issue of insufficient free space on disk that was related to transaction log size while restoring production database on development server.   

There is insufficient free space on disk volume ‘O:\’ to create the database. The database requires 44640043008 additional free bytes, while only 19111600128 bytes are available.
Msg 3119, Level 16, State 4, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
You can try taking transaction log backup for the database and try to shrink again. There is a job that backs up all transaction log files for all databases and I think it runs hourly.

Workaround:

The other option will be to restore the database using other drive that has sufficient disk space, say place the log file on the Backup drive, shrink it, and then detach the DB, copy the log file and attach the db.

1. Restoring database by placing log file in drive (i.e D:\ drive)

use master

sp_helpdb ‘testdb’

— Check the logical database name using Restore filelistonly option in sql server

restore filelistonly from disk = ‘C:\sqlserver\adventure.bak’

— Restore database with MOVE option

restore database testdb
from disk = ‘C:\sqlserver\adventure.bak’
with replace,
move ‘AdventureWorksDW2008R2_Data’ to ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testdb.mdf’,
move ‘AdventureWorksDW2008R2_Log’ to ‘D:\LS_Back\testdb_log.ldf’

— Modify logical database name to original name
alter database testdb
modify file (name=’AdventureWorksDW2008R2_Data’, newname=’Testdb_Data’)

alter database testdb
modify file (name=’AdventureWorksDW2008R2_Log’, newname=’Testdb_Log’)

2. Shrink database transaction log file using either DBCC  SHRINKFILE or Sql server management studio GUI.

use testdb
DBCC shrinkfile(Testdb_Log)

3. Detach database

use master
exec sp_detach_db ‘testdb’

4. Copy the transaction log file to old file location

Copy the transaction log file to old file location

5. Attach database using below script

use master
exec sp_attach_db ‘testdb’,’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testdb.mdf’,
‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testdb_log.ldf’

Note: sp_attach_db is remove in future version of sql server, use CREATE DATABASE database_name FOR ATTACH instead

Do not forget to move the log file in the proper location if you choose that option!

 

Leave a Reply

  

  

  


− 5 = 3