log_reuse_wait_desc in a 2000 database in 2005?

  • In SQL 2005 I ran SELECT log_reuse_wait_desc, * FROM sys.databases against a server. One database kept on giving LOG_BACKUP for log_reuse_wait_desc.

    So I backed up the database (truncated the log), did DBCC SHRINKFILE against the log file, checked my VLFs with DBCC LOGINFO, etc, but log_reuse_wait_desc kept on returning LOG_BACKUP.

    Finally I checked, and that database is in 80 (2000) mode.

    Does log_reuse_wait_desc have any meaning for a database in 80 mode?

  • A database attached to SQL 2005 is a SQL 2005 database in every sense of the word.

    All the compat level does is tell the query execution engine what T-SQL constructs are valid and what words are keywords, nothing more.

    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
  • jpSQLDude (5/16/2011)


    So I backed up the database (truncated the log), did DBCC SHRINKFILE against the log file, checked my VLFs with DBCC LOGINFO, etc, but log_reuse_wait_desc kept on returning LOG_BACKUP.

    Backing up a database does not truncate the transaction log. Only log backups do that.

    Shrinking a log is a bad idea, it should not be done unless something unusual has caused the log to grow. It's only able to release free space back to the OS, if the log is full then no space is free..

    Please read through this: http://www.sqlservercentral.com/articles/64582/

    and maybe this http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    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
  • Thank you Gail -- I should have been more clear -- I did Transaction Log backups, and immediately ran SELECT log_reuse_wait_desc, * FROM sys.databases afterwards.

    But the status of log_reuse_wait_desc does not change. I tried this Log backup/immediately check log_reuse_wait_desc a number of times, and it doesn't change. Also, there is no replication or log shipping set up.

    Any other reason why this might be?

  • Sure it was the same DB? (seen that and done that before)

    Log backup in code or GUI? If code, what command? If GUI, what options?

    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
  • I had the same problems here. Here's the script that solved it.

    Now I agree with Gail that this should be a 1 off execution because you had a strange issue and that you know have tlog backups scheduled regularly.

    In the best scenario, the first step would also be a full backup.

    USE [master]

    GO

    ALTER DATABASE [PROD] SET RECOVERY SIMPLE WITH NO_WAIT

    GO

    ALTER DATABASE [PROD] SET RECOVERY SIMPLE

    GO

    CHECKPOINT

    GO

    USE [PROD]

    GO

    DBCC SHRINKFILE (N'PROD_Log' , 3000) --mbs

    GO

    USE [master]

    GO

    ALTER DATABASE [PROD] SET RECOVERY FULL WITH NO_WAIT

    GO

    ALTER DATABASE [PROD] SET RECOVERY FULL

    GO

    EXEC msdb.dbo.sp_start_job @job_name = 'Backup PROD'

  • Thanks Ninja, that seems very comprehensive (at least for those instances where you are able to break the log chain).

  • Thanks Gail -- I went back to that server, hit F5, and log_reuse_wait_desc now returns NOTHING. That makes no sense! But "working" means I can move on to other stuff, such as my new 1000 page SSIS book!...........

    Thanks again.

  • jpSQLDude (5/16/2011)


    Thanks Ninja, that seems very comprehensive (at least for those instances where you are able to break the log chain).

    Well it doesn't <completely> break it. that's why I said I preffer to have that run with full backups as first and last steps.

    So the amount of time you can actually lose data is the time it takes to run the backups. On my system that's counted in minutes. But on a 50 tb db that might not be possible :-P.

    Maybe there's a way to run that with a diff at the begining instead of full. That should give tolerable recoverability.

    Anyhow I've run this a few times during business hour over the years and it never failed.

  • Ninja's_RGR'us (5/16/2011)


    ALTER DATABASE [PROD] SET RECOVERY SIMPLE WITH NO_WAIT

    GO

    ALTER DATABASE [PROD] SET RECOVERY SIMPLE

    GO

    No need for the No_Wait keyword. That's used with database setting changes that require exclusive access to the dB (set single user, set offline, set read_only, etc). See Alter Database in BoL for the options that can use the termination options. Recovery is not one of them.

    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
  • jpSQLDude (5/16/2011)


    Thanks Gail -- I went back to that server, hit F5, and log_reuse_wait_desc now returns NOTHING. That makes no sense! But "working" means I can move on to other stuff, such as my new 1000 page SSIS book!...........

    It probably had to wait for a checkpoint to come along as well.

    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
  • Ninja's_RGR'us (5/16/2011)


    jpSQLDude (5/16/2011)


    Thanks Ninja, that seems very comprehensive (at least for those instances where you are able to break the log chain).

    Well it doesn't <completely> break it. that's why I said I preffer to have that run with full backups as first and last steps.

    So the amount of time you can actually lose data is the time it takes to run the backups. On my system that's counted in minutes. But on a 50 tb db that might not be possible :-P.

    Maybe there's a way to run that with a diff at the begining instead of full. That should give tolerable recoverability.

    Anyhow I've run this a few times during business hour over the years and it never failed.

    Tx Gail. I just ran the GUI and copied the script into the job after a few alterations. I'll let MS know :-).

  • Ninja's_RGR'us (5/16/2011)


    Tx Gail. I just ran the GUI and copied the script into the job after a few alterations. I'll let MS know :-).

    SSMS writes crap code. I think MS knows. There have been enough people bitching about it for long enough...

    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
  • Ninja's_RGR'us (5/16/2011)

    Well it doesn't <completely> break it. that's why I said I preffer to have that run with full backups as first and last steps.

    I'm pretty sure the instant you set your database to SIMPLE Recovery model with ALTER DATABASE [PROD] SET RECOVERY SIMPLE you just broke the log chain. No?

  • jpSQLDude (5/16/2011)


    Ninja's_RGR'us (5/16/2011)

    Well it doesn't <completely> break it. that's why I said I preffer to have that run with full backups as first and last steps.

    I'm pretty sure the instant you set your database to SIMPLE Recovery model with ALTER DATABASE [PROD] SET RECOVERY SIMPLE you just broke the log chain. No?

    Yup. Log backups after cannot be applied to databases restored from backups from before.

    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 15 posts - 1 through 15 (of 15 total)

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