locked
Restore Log File to Big RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    SQL Server 2008 R2

    I have an uncompressed backup of a database created about 2
    years (.bak). When I attempt to restore it I get a message saying that I don’t
    have enough storage space. The file is about 36gb and the restored database
    should be about 40gb. I have over 100gb available.

    Running

    restore filelistonly from
    disk = 'E:\M2M\M2MDATA01.bak' with
    file = 1

    I get

    LogicalName      PhysicalName    Type      FileGroupName                Size        MaxSize               FileId     CreateLSN           DropLSN                UniqueId             ReadOnlyLSN     ReadWriteLSN   BackupSizeInBytes          SourceBlockSize               FileGroupId                LogGroupGUID DifferentialBaseLSN        DifferentialBaseGUID    IsReadOnly         IsPresent             TDEThumbprint

    m2mdata99        C:\Program
    Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\M2MDATA01.mdf  D                PRIMARY             39393624064      35184372080640               1              0              0              00000000-0000-0000-0000-000000000000    0              0              39305478144      512         1              NULL     4266235000023271200064             D6BD8395-E065-45FF-AA1F-7B1639EE2023            0              1              NULL

    ftrow_MailArchiverBodies           C:\Program
    Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\M2MDATA01.ndf    D             ftfg_MailArchiverBodies               1048576                35184372080640               4              4264929000000012400002             0              90EFACB1-BB04-4979-BADA-3CD47B84F05F                0              0              131072  512         2              NULL     4266235000023271200064             D6BD8395-E065-45FF-AA1F-7B1639EE2023    0              1              NULL

    m2mdata99_log               C:\Program
    Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\M2MDATA01.LDF    L              NULL     42467328             35184372080640                2              0              0              00000000-0000-0000-0000-000000000000               0              0              0              512         0                NULL     0              00000000-0000-0000-0000-000000000000               0              1              NULL

    m2mdata01_1_Log         C:\Program
    Files\Microsoft SQL
    Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\M2MDATA01.m2mdata01_log           L              NULL     350437244928                35184372080640               3              4137648000000011700001             0              00000000-0000-0000-0000-000000000000                0              0              0              512         0              NULL     0              00000000-0000-0000-0000-000000000000               0              1                NULL

    Based upon this it seems that the file sizes are correct
    except for the last log file. It says it needs about 350gb. There is no way it
    should need this much and I don’t have that much space available.

    Any thoughts on what is going on?

    Thanks,

    Scott

    Monday, March 19, 2012 9:46 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    I found enough hard drive space by using a USB drive. Unfortunatly it is getting stuck at "Executing (100%)". I tried it on another server using SAN storage and it is getting stuck at the same spot there.

    Thanks,
    Scott

    hi long did it take to reach those 100% status message and how long did you wait with 100% in the status before aborting the restore task?

    If it really needs to write 350GB transactional it may takes several hour to initialize the log file even the full size appears immediately but SQL Server had to (over-)write the complete 350GB files.

    Assuming your running SQL Server on Windows 7 or Windows Server 2008R2, you can start task manager - > Performance tab -> Resoure Monitor -> Disk IO and you'll than see the progress of writing the log file.

    If you don't see any disk IO activity after (several) hours it may be possible that the backup file is corrupt (which would explain the 350GB size as well). but from the delay or your answer I expect that you didn't wait long enough for the log file beeing written completely.

    • Marked as answer by amber zhang Wednesday, March 28, 2012 5:49 AM
    Tuesday, March 20, 2012 5:01 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    It seems that the log had either  never been truncated or it had grown to 350GB. the only save way to restore this database is to attach another drive and restore the larger to the additional disk.

    You need to supply the target directory otherwise the database files (data and log) will be installed in the original directory or in the default directory - in your case this is on the c: drive.

    After restoring you can truncate the 2nd log file or even better remove it, if you only need 1 log file - the recommended choice.

    • Proposed as answer by Sean Massey Monday, March 19, 2012 10:39 PM
    Monday, March 19, 2012 10:33 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Agree with Daniel - you get no performance benefit from having more than one log file as SQL server will write to one log file at a time. It also looks like the backup strategy for log backups needs to be looked at for that DB however if this backup is 2 years old this may have been resolved however I would definately check the status of the log file internal VLF's and the log backups.

    http://sqlskills.com/blogs/kimberly/post/8-steps-to-better-transaction-log-throughput.aspx

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Monday, March 19, 2012 10:42 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Scott,

    Try this: Create a blank database with two MDF file with 50 GB each and 1 log file with few MB.

    User below restore command:

    restore database <dbname>
    File = 'm2mdata99',
    file = 'ftrow_MailArchiverBodies',           
    from disk = 'E:\M2M\M2MDATA01.bak'
    with file = 1, norecovery

    Once restore is completed, recovery the database using: Restore database <dbname> with recovery

    This should restore only the Primary and secondary data file and not the log file.

    Kindly mark the reply as answer if they help


    • Edited by Sunil Gure Tuesday, March 20, 2012 10:57 AM
    Tuesday, March 20, 2012 10:57 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Suni,

    I tried your statement. After removing the comma after "Bodies'" it still gives me an error saying that I don't have enough space for the log file.

    Thank you,
    Scott

    Tuesday, March 20, 2012 11:26 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    can you paste the command you tried? and complete error also

    Kindly mark the reply as answer if they help


    • Edited by Sunil Gure Tuesday, March 20, 2012 11:29 AM
    Tuesday, March 20, 2012 11:28 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    restore database M2MDATA06
    File = 'm2mdata99',
    file = 'ftrow_MailArchiverBodies'         
    from disk = 'E:\M2M\M2MDATA01.bak'
    with file = 1, norecovery

    It returned:

    Msg 3257, Level 16, State 1, Line 1
    There is insufficient free space on disk volume 'L:\' to create the database. The database requires 343443243008 additional free bytes, while only 75602194432 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.

    Tuesday, March 20, 2012 11:31 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Drive L is where my log files are stored. I store my mdf file on another drive.
    Tuesday, March 20, 2012 11:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I just found this thread. It seems to suggest that I have to restore the log file. If anyone can come up with another method though I would appreciate it.

    http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/fe0911f1-dc9d-4a39-bf2a-9015472e399a

    Tuesday, March 20, 2012 11:52 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    OK, It is not possible to restore the only the Primary or secondary files, Log file also get restored automatically (:( MSDN does not talk about it)..

    Another method you can try is to restore the database on a machine where you have space and detach the database from there and copy across the MDF and NDF file and make use of sp_attach_single_file_db (or CREATE DATABASE ... FOR ATTACH )

    Kindly mark the reply as answer if they help

    Tuesday, March 20, 2012 12:00 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I just found this thread. It seems to suggest that I have to restore the log file. If anyone can come up with another method though I would appreciate it.

    http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/fe0911f1-dc9d-4a39-bf2a-9015472e399a

    is it not possible to temporarily attach another large disk and specify that disk as target for the 2nd log file (MOVE TO attribute)?

    after successfull restore you can empty and drop the 2nd log file and remove the temp disk.

    Tuesday, March 20, 2012 12:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Another method you can try is to restore the database on a machine where you have space and detach the database from there and copy across the MDF and NDF file and make use of sp_attach_single_file_db (or CREATE DATABASE ... FOR ATTACH )

    this would work only if he shrinks the 2nd log file to a reasonable size or get rid off it completely after successfull restore and use only 1 log file.

    Tuesday, March 20, 2012 12:21 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I found enough hard drive space by using a USB drive. Unfortunatly it is getting stuck at "Executing (100%)". I tried it on another server using SAN storage and it is getting stuck at the same spot there.

    Thanks,
    Scott

    Tuesday, March 20, 2012 2:51 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I found enough hard drive space by using a USB drive. Unfortunatly it is getting stuck at "Executing (100%)". I tried it on another server using SAN storage and it is getting stuck at the same spot there.

    Thanks,
    Scott

    hi long did it take to reach those 100% status message and how long did you wait with 100% in the status before aborting the restore task?

    If it really needs to write 350GB transactional it may takes several hour to initialize the log file even the full size appears immediately but SQL Server had to (over-)write the complete 350GB files.

    Assuming your running SQL Server on Windows 7 or Windows Server 2008R2, you can start task manager - > Performance tab -> Resoure Monitor -> Disk IO and you'll than see the progress of writing the log file.

    If you don't see any disk IO activity after (several) hours it may be possible that the backup file is corrupt (which would explain the 350GB size as well). but from the delay or your answer I expect that you didn't wait long enough for the log file beeing written completely.

    • Marked as answer by amber zhang Wednesday, March 28, 2012 5:49 AM
    Tuesday, March 20, 2012 5:01 PM