Post reply

Standby/Read-only DB state

  • July 30, 2007 at 10:09 pm

    #179921

    Hello,

    We are using SQL 2k5. One of the DB is restored to a reporting server every night and is always in 'Standby / Read-only' mode.

    What I want to achieve is to use DBCC SHRINKFILE to shrink the log file after the DB is restored.

    When I try to use the following statement:

    Use

    go

    BACKUP LOG WITH TRUNCATE_ONLY

    DBCC SHRINKFILE (, TRUNCATEONLY)

    I get this error msg:

    Msg 3036, Level 16, State 3, Line 1

    The database "db_name" is in warm-standby state (set by executing RESTORE WITH STANDBY)

    and cannot be backed up until the entire restore sequence is completed.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP LOG is terminating abnormally.

    Msg 7992, Level 16, State 2, Line 2

    Cannot shrink 'read only' database 'db_name'.

    Any suggestions?

    Thanks.

  • July 30, 2007 at 11:08 pm

    #723461

    When you restore with standby, according to Books Online...

    ---------------------------------

    Specifies the undo file name so the recovery effects can be undone. The size required for the undo file depends on the volume of undo actions resulting from uncommitted transactions. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.

    STANDBY allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores.

    If the specified undo file name does not exist, SQL Server creates it. If the file does exist, SQL Server overwrites it.

    The same undo file can be used for consecutive restores of the same database. For more information, see Using Standby Servers.

    ----------------------------

    THUS:

    You cannot get rid of the transaction log if you wish to be able to restore subsequent transaction logs. Therefore you cannot shrink it or alter it in any way. The UNDO file is an important part of the standby restore because it includes the bit of the transaction log that was discarded so you could start reading from the DB. If you were allowed to alter the DB then the standby file would be useless as the logs would be inconsistent. Bottom line..... You cannot shrink the logs on the standby server.

    Why do you need to shrink the logs? Are they growing uncontrollably on the source server?

  • July 31, 2007 at 9:56 pm

    #723767

    The logs on the prod (source) server are huge! So when the DB is restored on the reporting server the logs are restored along with it.

    Any suggestion on how the logs could be truncated after the restore?

  • August 1, 2007 at 6:29 am

    #723826

    You are restoring the DB to the reporting server every night?  The way you have it setup, you could just restore the logs from the production server.

    If you are just restoring the logs, then you can't change the way you are doing it.  The previous post explained that.  However, if you are restoring the complete DB, then yes you have an option.  (sort of)

    You could restore the DB to a read/write state instead of a read only state.   Shrink the logs, then switch to readonly.  However, you will still need the space to do the restore.  So what is better, showing that you have xxxGig of logs, or xxxGig of free space (that you can't use). 

    I would just leave it alone, and start just restoring logs if that isn't what you are doing.

     

  • August 1, 2007 at 9:10 am

    #723910

    Couple of things in question:

    1. DB on the report server will be in No Recovery mode when using log shipping

    2. The t-logs of the prod server are taken every 15 mins to a network share. Is there a way to apply them automatically (without using log shipping)?

  • August 1, 2007 at 7:20 pm

    #724036

    i dunno if am right   this is wat comes to my mind, since log shipping is configured i believe the db is in read-only mode @ destination.......just disable the restore job in log shipping and bring the destination to read-write state as,

    restore database dbname with recovery -----> brigs your secondary db online ....

    take a database snapshot and use it for reporting purposes as in your case....

    now shrink the secondary db and once it completes just delete the snapshot and restore the next subsequent T-log using with standby option and then enable the restore job so that all other T-logs will get restored subsequently

    [font="Verdana"]- Deepak[/font]

  • August 3, 2007 at 5:01 am

    #724457

    you could truncate the log and shrink it on the live side then when it restored on your reporting server the log will be sgrunk over there as well.

    IMPORTANT - make sure you the truncate of the log immediately before the full backup to maintain recoverability!

    hopefully you would only have to do this occasionally to maintain the log at a decent size.

    ---------------------------------------------------------------------

  • January 11, 2011 at 11:50 am

    #1272016

    If you truncate the log on the live side, you will break the log shipping chain. All you need to do is shrink the log on the live side and automatically on the next log shipping's restore, the standby side log file will be shrunk as well.

  • January 11, 2011 at 1:42 pm

    #1272076

    Please note: 3 year old post.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply