• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

Backup Failed

This is SQL Server 2016 Ent. Differential backup failed with one of the database with below error. All other DB's went fine.

***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\MSSQL13.MSSQLSERVER\MSSQL\LOG\SQLDump0002.txt
2018-02-04 00:33:18.82 spid87      * *******************************************************************************
2018-02-04 00:33:18.82 spid87      *
2018-02-04 00:33:18.82 spid87      * BEGIN STACK DUMP:
2018-02-04 00:33:18.82 spid87      *   02/04/18 00:33:18 spid 87
2018-02-04 00:33:18.82 spid87      *
2018-02-04 00:33:18.82 spid87      * Latch timeout
2018-02-04 00:33:18.82 spid87      *
2018-02-04 00:33:18.82 spid87      * Input Buffer 510 bytes -
2018-02-04 00:33:18.82 spid87      *             BACKUP DATABASE [database33] TO  DISK = N'B:\Differentia
2018-02-04 00:33:18.82 spid87      *  l\database33_backup_2018_02_04_000006_9916706.bak' WITH  DIFFERENTI
2018-02-04 00:33:18.82 spid87      *  AL , NOFORMAT, NOINIT,  NAME = N'BlobMigration33_backup_2018_02_04_00000
2018-02-04 00:33:18.82 spid87      *  6_9916706', SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10  
2018-02-04 00:33:18.82 spid87      *  
2018-02-04 00:33:18.82 spid87      * *******************************************************************************
2018-02-04 00:33:18.82 spid87      * -------------------------------------------------------------------------------
2018-02-04 00:33:18.82 spid87      * Short Stack Dump
2018-02-04 00:33:18.90 spid87      Stack Signature for the dump is 0x0000000021DB4CB8
2018-02-04 00:33:31.62 spid87      External dump process return code 0x20000001.
External dump process returned no errors.

2018-02-04 00:33:31.62 Backup      Error: 3041, Severity: 16, State: 1.
2018-02-04 00:33:31.62 Backup      BACKUP failed to complete the command BACKUP DATABASE database33 WITH DIFFERENTIAL. Check the backup application log for detailed messages.
0
Vijay
Asked:
Vijay
  • 6
  • 4
  • 3
  • +1
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
This is from the C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\MSSQL13.MSSQLSERVER\MSSQL\LOG\SQLDump0002.txt file or from the SQL Server log?
0
 
VijayAuthor Commented:
This is from SQL Server Error Log
0
 
Mark WillsTopic AdvisorCommented:
Unfortunately, 3401 is a fairly generic error.

There should be more information in the log to help reveal the real problem. Start checking the logs more closely

There wasnt anything else running in the background ?

Have you run a DBCC checkdb on database33 - was a DBCC running ?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Perhaps sharing the full error log with us will help. You can find some log entry before the error happened that can justify the error itself.
0
 
VijayAuthor Commented:
Before and after that backup failure message: i found below error:

Timeout occurred while waiting for latch: class 'FCB', id 000000AFF440AC70, type 2, Task 0x000000B00C1E1C28 : 0, waittime 1800 seconds, flags 0x2000000019, owning task 0x000000B00E018CA8. Continuing to wait.
2018-02-04 00:33:02.21 spid598     Timeout occurred while waiting for latch: class 'FCB', id 000000AFF440AC70, type 3, Task 0x000000B00E841468 : 0, waittime 6000 seconds, flags 0x2000000019, owning task 0x000000B00E018CA8. Continuing to wait.
2018-02-04 00:33:02.64 spid1107    Timeout occurred while waiting for latch: class 'FGCB_ADD_REMOVE', id 000000AFECF64F78, type 2, Task 0x000000B00E018CA8 : 0, waittime 6000 seconds, flags 0x1a, owning task 0x000000B00E841468. Continuing to wait.
2018-02-04 00:33:05.87 spid38s     Timeout occurred while waiting for latch: class 'FCB', id 000000AFF440AC70, type 2, Task 0x000000AF193784E8 : 0, waittime 6000 seconds, flags 0x2000000019, owning task 0x000000B00E018CA8. Continuing to wait.
2018-02-04 00:33:18.81 spid87      A time-out occurred while waiting for buffer latch -- type 3, bp 000000B0C4E1BA00, page 1:18524279, stat 0x10b, database id: 38, allocation unit Id: 72057594085244928, task 0x000000B00C1F1848 : 0, waittime 300 seconds, flags 0x200000001a, owning task 0x000000B00C1E1C28. Not continuing to wait.
0
 
VijayAuthor Commented:
Even performed DBCC CHECK. no errors.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check if there's any process running that's trying to write to the disk at the same time, that can originate the timeout error.
If you can, move the differential backup to another scheduled time to see if it runs with no issue.
0
 
VijayAuthor Commented:
I observed that, i am getting this errors frequently not only while taking backups.
0
 
Mark WillsTopic AdvisorCommented:
Then there is a possibility of sizing problems with your DB - including log file sizing and fragmentation.

Do you get the error with a full backup ?

How often are you running a full backup, and how many differential backups ?
What about disk allocation (both DB size and physical disk)

I have written articles (still applies)
https://www.experts-exchange.com/articles/657/Managing-the-Transaction-Log-for-the-Accidental-DBA.html
https://www.experts-exchange.com/articles/691/Managing-Fragmentation-for-the-Accidental-DBA.html
0
 
VijayAuthor Commented:
Hi Mark,

I mentioned in my previous comment that i am getting these errors very frequently.
All these databases are in SIMPLE Recovery mode.
0
 
Mark WillsTopic AdvisorCommented:
Even with SIMPLE recovery mode, you still have the challenges with Fragmentation and Log files.

And I did see your previous comment, that's why I posted the comments above :)

From Paul Randal : https://www.sqlskills.com/blogs/paul/most-common-latch-classes-and-what-they-mean/
FGCB stands for File Group Control Block. This latch is required whenever a file is added or dropped from the filegroup, whenever a file is grown (manually or automatically), when recalculating proportional-fill weightings, and when cycling through the files in the filegroup as part of round-robin allocation. If you’re seeing this, the most common cause is that there’s a lot of file auto-growth happening. It could also be from a filegroup with lots of file (e.g. the primary filegroup in tempdb) where there are thousands of concurrent connections doing allocations. The proportional-fill weightings are recalculated every 8192 allocations, so there’s the possibility of a slowdown with frequent recalculations over many files.
Your should read those two articles :
https://www.experts-exchange.com/articles/657/Managing-the-Transaction-Log-for-the-Accidental-DBA.html
https://www.experts-exchange.com/articles/691/Managing-Fragmentation-for-the-Accidental-DBA.html
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
The fact is that when you get a Stack Dump when performing things, you should call SQL Server Support as many times there is a bug that is feeding this.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
What service pack are you on?  Are you past SP1?  Just post the whole version number like 13.0.5454.
0
 
VijayAuthor Commented:
Thank you Vitor.
This issue is due to some DBCC command. After disabling this job, Everything went fine.
0
 
Mark WillsTopic AdvisorCommented:
Hmmm.... I was under the assumption that you were getting the error "i am getting this errors frequently not only while taking backups." and "I  mentioned in my previous comment ... very frequently"

DBCC probably isnt being run a lot, maybe with backups, but doesnt explain "very frequently".  And Vitor wasnt the only one who mentioned background jobs. ;) At least you might now be aware of FGCB - File Group Control Block - as your error.

Still a bit confused...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now