none
After compact which gets rid of 30 gb log file, backup file hardly smaller?

    Question

  • I have a sql server 2005 db where the data file is about 1.6 gb and the log file is about 36 gb. I got rid of the enormous log file by setting recovery model to simple (from full) and doing a compact on ldb and mdb. I expected the backup files to be smaller. But they're only nominally smaller, 1 gb vs original 1.6 gb.

    Can someone explain the error in my assumptions? The 36 gb log file does not seem to be 'contained' in the 1.6 bak, just going by file sizes. But the log file does seem to be in play somehow...because a restore from the bak that is based on the 1.6 gb data file and 36 gb log file takes 18 mintues to restore, whereas a restore of a bak based on the compacted database takes just over a minute.


    • Edited by rusticloud Sunday, September 22, 2013 12:21 AM
    Sunday, September 22, 2013 12:21 AM

Answers

  • I see, I think I understand your question now.   In order to answer your question it requires a bit of an explanation.

    When you take a full backup of a database the following occur:

    Flush all transactions to disk (this gets transactions that may be sitting in memory).
    Backup of the physcial database (Your mdf file) and makes note of the latest log sequence number.
    Gets as many transactions as necessary from the log file dating back to the latest log sequence number in the physical database.

    So since much of your 36GB log is already commited to the physical database the span between the two log sequence numbers is much smaller.   Which causes the backup to use less space.

    In simple mode your log sequences stay closer together, and once the database reaches its check point it flushes the logs.  Preventing the log from growing as much (long transactions can still cause it to grow).

    The sizes can be explained because of free space in your files.   You may have a 36GB transaction log file but it may be empty (or close to empty).   When you restore the database, it knows there was supposed to be a 36GB transaction file, and the OS still has to allocate that space to the file.

    Hope this helps.

    Please click the Mark as answer button, and vote as helpful if this reply helps you.

    Thank you!


    • Edited by Jeremy D Allen Sunday, September 22, 2013 3:13 AM Corrected simple mode statement to make it more accurate.
    • Marked as answer by rusticloud Sunday, September 22, 2013 12:39 PM
    Sunday, September 22, 2013 2:42 AM

All replies

  • Hi rusticloud,

    Have you already checked the initial size of the log file.   Chances are that information is being restored with your backup file, and setting the log to 36GB.

    If this is what is happening find the physical name of your log file and execute this command  Replacing the name of the [database] and 'database_log'.

    USE [database]
    GO
    DBCC SHRINKFILE (N'database_log' , 100)
    GO
    

    You can do this in SSMS also by changing the inital size of the log in.

    right click the database go to properties,

    Click on "files" in the left pane,
    change the log file to 100 (this would be 100 MB)   If there is more than 100MB of actual data in the log it will not shrink to 100Mb but it should shrink to the smallest size possible.


    Please click the Mark as answer button, and vote as helpful if this reply helps you.

    Thank you!

    Sunday, September 22, 2013 2:07 AM
  • Thanks for the reply Jeremy. I'm not sure you understood what I meant to relay in the original message. Before backup, the log file is 36 gb and data file is 1.6 gb. It creates a backup file of 1.6 gb. I can't imagine how the large log file data can be included in the bak file? But if I do a compact that gets the log file down to almost zero (simple recovery model), a backup creates a 1 gb file. And, if I restore a bak created with the full sized log file, it takes many times longer to restore. It just doesn't make sense to me. I'm sure it does make sense, I don't know how.
    Sunday, September 22, 2013 2:14 AM
  • I see, I think I understand your question now.   In order to answer your question it requires a bit of an explanation.

    When you take a full backup of a database the following occur:

    Flush all transactions to disk (this gets transactions that may be sitting in memory).
    Backup of the physcial database (Your mdf file) and makes note of the latest log sequence number.
    Gets as many transactions as necessary from the log file dating back to the latest log sequence number in the physical database.

    So since much of your 36GB log is already commited to the physical database the span between the two log sequence numbers is much smaller.   Which causes the backup to use less space.

    In simple mode your log sequences stay closer together, and once the database reaches its check point it flushes the logs.  Preventing the log from growing as much (long transactions can still cause it to grow).

    The sizes can be explained because of free space in your files.   You may have a 36GB transaction log file but it may be empty (or close to empty).   When you restore the database, it knows there was supposed to be a 36GB transaction file, and the OS still has to allocate that space to the file.

    Hope this helps.

    Please click the Mark as answer button, and vote as helpful if this reply helps you.

    Thank you!


    • Edited by Jeremy D Allen Sunday, September 22, 2013 3:13 AM Corrected simple mode statement to make it more accurate.
    • Marked as answer by rusticloud Sunday, September 22, 2013 12:39 PM
    Sunday, September 22, 2013 2:42 AM
  • >>>I can't imagine how the large log file data can be included in the bak file?

    Backup reads  the data from the log file as last step (of the backup process) and includes all the data that have changed since the backup process has started.

    But for the restore sql server engine needs to allocate exact space for .ldf and .mdf as it has before , thus  your restore process takes longer..

    >>>But if I do a compact that gets the log file down to almost zero (simple recovery model), a backup >>>creates a 1 gb file

    Ok, your data pages (.mdf) are 1 gb file..


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Shanky_621 Sunday, September 22, 2013 5:30 AM
    Sunday, September 22, 2013 5:12 AM
  • Thanks for the reply Jeremy. I'm not sure you understood what I meant to relay in the original message. Before backup, the log file is 36 gb and data file is 1.6 gb. It creates a backup file of 1.6 gb. I can't imagine how the large log file data can be included in the bak file? But if I do a compact that gets the log file down to almost zero (simple recovery model), a backup creates a 1 gb file. And, if I restore a bak created with the full sized log file, it takes many times longer to restore. It just doesn't make sense to me. I'm sure it does make sense, I don't know how.

    Hello,

    What Uri told is correct.

    1. Full backup  includes committed data from your data file and some amount transaction log  for uncommitted  data (during time it was taking backup) it will no include free space.So your backup of DB (1.6 G MDF and 36 GB log) is approx 1.6 GB.
    2. Now when you restore this 1.6 G backup it will take original space and its log will be 36 G because it will gain that free space which it had originally taken  for rollback or roll forward of any transaction .The extra time which is takes is for committing or undoing of any transaction present in 36 G log.
    3. Now when you change recovery model to simple checkpoint happens and logs are truncated and after that you shrink  the log file(compacted it) now that free space is removed transaction it was holding has committed or rolledback. So less size and less time.

    Tried my best to answer your question,hope it helps


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Sunday, September 22, 2013 5:42 AM
    Sunday, September 22, 2013 5:41 AM
  • Thanks Jeremy (and the rest of you) that does make it clear.
    Sunday, September 22, 2013 12:40 PM