January 31, 2016 at 8:44 pm
Hi, I have an issue with SQL server log backup. I found our log is keep growing so I try to back it up and shrink the file. But it did not allow me to do that and say the log is corrupted. The full back is running ok without problem. I have restored the backup to be another database and it allow me to backup log and shrink it. Also I have run the dbcc checkdb and no error returned.
I have checked the log and there is an error keep appearing but I am not sure if it is related:
The Database ID 5, Page (1:7041), slot 5 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.
Can someone please give some suggestion to fix this? Do I have to restore DB to another database and use that one?
Thanks heaps!
Regards
Daniel
February 1, 2016 at 1:27 am
dxu (1/31/2016)
Run DBCC CHECKTABLE.
And have you? What are the results?
What's the exact message you're getting from the log backups?
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
February 1, 2016 at 8:34 pm
Hi GilaMonster,
Thanks for your reply. I have run the dbcc checkdb and there is no error returned. Do I have to run dbcc checktable?
Thanks
Daniel
February 1, 2016 at 8:36 pm
The message when I back up the log is:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Backup failed for Server 'DBMEL02'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: BACKUP detected corruption in the database log. Check the errorlog for more information. (Microsoft.SqlServer.Smo)
------------------------------
February 1, 2016 at 11:34 pm
Yes I think you will have to run DBCC CHECKTABLE incase DBCC CHECKDB does not return any error.To run DBCC CHECKTABLE first identify all the possible tables that have LOB data types and run CHECKTABLE against each of them.
You can refer to this MS connect https://connect.microsoft.com/SQLServer/feedback/details/361624/error-7105-followed-by-the-database-id-x-page-1-xxxxxx-slot-x-for-lob-data-type-node-does-not-exist-error for more details
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
February 2, 2016 at 1:39 am
Run checkDB to be safe.
If it's just the log, the usual solution to that is to, during a maintenance window:
Switch the DB to simple recovery
Shrink the log file to 0
Resize the log back to its original size (in chunks as necessary to get a good number of VLFs)
Switch the DB back to full recovery
Take a full backup.
This does break the log chain, so be sure to do it at a quiet time and make sure that full backup you take at the end is valid and restorable.
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
February 2, 2016 at 4:04 pm
thanks guys I will run checktable and let you know the result.
The interesting thing is I have restored the full back up into another database and I can shrink the log without any problem.
Thanks
Daniel
February 3, 2016 at 2:08 am
dxu (2/2/2016)
The interesting thing is I have restored the full back up into another database and I can shrink the log without any problem.
Not at all a surprise, since when you restore a DB the contents of the log aren't replaced on disk.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply