Articles in this section

See more

1280 - Errors when backing up many SQL databases on a single machine

Avatar
Jolette Roodt
  • Updated
Follow
    Tags:
  • SQL Server
  • SQL

Symptom

If you have a very large number of SQL databases (50+) on a single machine, you may encounter a variety of VSS and System errors. Some of these include:

Could not prepare for snapshot creation (VSS writer in an unexpected state): writer error (not retryable), please check the event log for related events from the application hosting the VSS writer (0x800423f4); 'failed at prepare snapshot' [Writer Name: 'SqlServerWriter', Writer ID: 'a65faa63-5ea8-4ebc-9dbd-a0c4db26912a', Instance ID: '696307ae-11de-41a4-8972-7eb4f15a15ee', Internal ID: '4ebc5ea8-a65f-aa63-2a91-26dbc4a0bd9d']

EVENT source: Microsoft-Windows-CAPI2, level: Error, message: Details:
AddLegacyDriverFiles: Unable to back up image of binary Microsoft Link-Layer Discovery Protocol.

System Error:
Access is denied.

INFO  : 08:24:32 EVENT source: MSSQLSERVER, level: Informational, message: PSDVATTSALESv2
INFO  : 08:24:32 EVENT source: SQLVDI, level: Error, message: TriggerAbortinvoked041926744ServerMSSQLSERVERGlobal\{4B5F2CC3-4BC8-4F63-8601-56F5F336F039}312_SQLVDIMemoryName_0
INFO  : 08:24:32 EVENT source: MSSQLSERVER, level: Error, message: BACKUP DATABASE PSDM0836
INFO  : 08:24:32 EVENT source: MSSQLSERVER, level: Error, message: BACKUP DATABASE PSDINTERTRUST
INFO  : 08:24:32 EVENT source: SQLVDI, level: Error, message: TriggerAbortinvoked041924624ServerMSSQLSERVERGlobal\{4B5F2CC3-4BC8-4F63-8601-56F5F336F039}126_SQLVDIMemoryName_0
INFO  : 08:24:32 EVENT source: MSSQLSERVER, level: Error, message: BACKUP DATABASE PSDB339BL
INFO  : 08:24:32 EVENT source: SQLVDI, level: Error, message: TriggerAbortinvoked0419216840ServerMSSQLSERVERGlobal\{4B5F2CC3-4BC8-4F63-8601-56F5F336F039}86_SQLVDIMemoryName_0
INFO  : 08:24:32 EVENT source: SQLVDI, level: Error, message: TriggerAbortinvoked041928424ServerMSSQLSERVERGlobal\{4B5F2CC3-4BC8-4F63-8601-56F5F336F039}336_SQLVDIMemoryName_0
INFO  : 08:24:32 EVENT source: MSSQLSERVER, level: Error, message: BACKUP DATABASE PSDSANTACOLE
INFO  : 08:24:32 EVENT source: SQLWRITER, level: Error, message: ICommandText::Execute0x80040e14SQLSTATE: 42000, Native Error: 3013
Error state: 1, Severity: 16
Source: Microsoft SQL Server Native Client 11.0
Error message: BACKUP DATABASE is terminating abnormally.
SQLSTATE: 42000, Native Error: 3224
Error state: 1, Severity: 16
Source: Microsoft SQL Server Native Client 11.0
Error message: Cannot create worker thread.
INFO  : 08:24:32 EVENT source: SQLWRITER, level: Error, message: ICommandText::Execute0x80040e14SQLSTATE: 42000, Native Error: 3013
Error state: 1, Severity: 16

 

Solution

If the machine is running a 64-bit Windows OS, you can resolve this by increasing the max worker threads to twice the number of databases (e.g. if you have 200 databases, set the max to 400 threads). Then schedule a reboot of the server and try the backup again.

To do this using SQL Server Management Studio:

  1. In Object Explorer, right-click a server and select Properties.
  2. Click the Processors node.
  3. In the Max worker threads field, type or select the appropriate number as explained above.

Note: This may significantly increase CPU and RAM usage at the time of the snapshot.

Related articles