none
Restore from Snapshot log file issue RRS feed

  • Question

  • When I restore my database from a snapshot, the logfile's autogrowth changes from the fixed size setting (increase by 100MB) to SQL server default (increase by 10%).

    I noticed this behaviour while doing some tests on SQL 2008 enterprise. Why does the logfile change its autogrowth options!? And even more confusing, why is it setting it to sql server default 10% when my model database has its sizes set on fixed 100MB increase.

    Is this somehow by design or a bug? Any option I could use when restoring the database to keep it, or do I need to change it afterwards..?

    Thanks in advance.

    Thursday, March 8, 2012 1:46 PM

Answers

  • Well, if it's by design than I have to change my entry to be a DCR. *g* The issue with multiple logfiles is clearly documented, and I also have a DCR open for this... But thanks for pointing it out Patrick.

    Saturday, March 10, 2012 6:37 PM

All replies

  • I cannot test it right now but it looks like a bug..

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

    Thursday, March 8, 2012 2:04 PM
  • I reproed it with SQL 2012 and will file a bug for it now.

    @Cloxy: If you need this to be fixed in SQL 2008 R2 you should get in contact with Microsoft Customer Support Services. My bug entry will make Microsoft aware of the problem, but they will not put too much priority on this as long as CSS is not involved.


    Edit: Here is the Connect-Link if anybody wants to vote on it: http://connect.microsoft.com/SQLServer/feedback/details/730113/revert-to-database-snapshot-looses-ldf-file-settings
    • Edited by PrinceLucifer Friday, March 9, 2012 8:35 PM Added Connect link
    Friday, March 9, 2012 8:30 PM
  • I would say that this is by design and has been a 'feature' since Snapshots were introduced.

    BOL entry for Reverting to a Database Snapshot states that a successful revert operation automatically rebuilds the log file.

    The log file created has the same size and growth as that created when you

    (a) Detach database and Attach using the ATTACH_REBUILD_LOG option

    (b) Delete the log file(s) and then repair the database using the Emergency mode repair (DBCC CHECKDB(DBname, REPAIR_ALLOW_DATA_LOSS)

    Paul Randal has a blog post on some of the problems that can occur reverting from a Snapshot

    http://www.sqlskills.com/BLOGS/PAUL/post/Database-snapshots-when-things-go-wrong.aspx

    Also note that along with the size and filegrowth options that are lost, the rebuilt log file will have only 1 file regardless of the initial configuration.

    A quick example of this behaviour was posted on http://killspid.blogspot.com.au/

    Saturday, March 10, 2012 11:51 AM
  • Well, if it's by design than I have to change my entry to be a DCR. *g* The issue with multiple logfiles is clearly documented, and I also have a DCR open for this... But thanks for pointing it out Patrick.

    Saturday, March 10, 2012 6:37 PM
  • Update:

    Paul Randal has posted a new Blog entry at

    http://www.sqlskills.com/BLOGS/PAUL/post/Bug-reverting-from-a-database-snapshot-shrinks-the-transaction-log-to-05MB.aspx

    which indicates that this is really a bug

    Wednesday, April 18, 2012 2:51 AM
  • Thank you, Patrick, for sharing your knowledge about this.

    In your first reply, it does seem to be somewhat by design. Perhaps Paul has not read the same thing?

    Of course, one could always reply to his blogpost and ask :) Maybe I'll do that.. Not sure where to go from here anyway. I've documented this behaviour in our environment and so that we will always look at the current logsize and autogrowth options before making a revert.

    Thanks again!

    Wednesday, April 18, 2012 5:14 AM