May 16, 2011 at 7:51 am
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?
May 16, 2011 at 7:56 am
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
May 16, 2011 at 7:58 am
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
May 16, 2011 at 8:18 am
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?
May 16, 2011 at 8:21 am
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
May 16, 2011 at 8:24 am
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'
May 16, 2011 at 8:32 am
Thanks Ninja, that seems very comprehensive (at least for those instances where you are able to break the log chain).
May 16, 2011 at 8:34 am
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.
May 16, 2011 at 8:35 am
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.
May 16, 2011 at 8:36 am
Ninja's_RGR'us (5/16/2011)
ALTER DATABASE [PROD] SET RECOVERY SIMPLE WITH NO_WAITGO
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
May 16, 2011 at 8:39 am
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
May 16, 2011 at 8:40 am
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 :-).
May 16, 2011 at 8:46 am
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
May 16, 2011 at 9:12 am
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?
May 16, 2011 at 9:13 am
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply