SQL Server Compressed backups fail with SetEndOfFile error


When performing a backup dump using compression you may get an error like this one:

Executing the query “BACKUP DATABASE [MyDatabase] TO  DISK = N’\\\\myserver\\myshare\\…” failed with the following error: “The operating system returned the error ’87(failed to retrieve text for this error. Reason: 15105)’ while attempting ‘SetEndOfFile’ on ‘\\\\myserver\\myshare\\\\MyDatabase_backup_2012_06_12_210001_7306331.bak’.
BACKUP DATABASE is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 12767904 pages for database ‘MyDatabase’, file ‘MyDatabase_dat’ on file 1.
100 percent processed.
Processed 9009 pages for database ‘MyDatabase’, file ‘MyDatabase_log’ on file 1.”.
Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

It doesn’t always happen every backup. The problem appears to be related to the way that SQL server creates the backup file. When using compression the SQL server pre-allocates space for the backup file and the data is then written into it. Since the data is compressed the backup takes less space than was allocated and SQL server instructs the filesystem to truncate the file (set the end of the file). On local disk this can happen quickly but over a network to a share it can take longer.

The function used to truncate the file is SetEndOfFile (http://msdn.microsoft.com/en-us/library/windows/desktop/aa365531%28v=vs.85%29.aspx).

When the backup file is written to a SMB share the truncation can take longer than expected, especially if the amount of data to be truncated is quite large (i.e. it was compressed quite alot) and the request can timeout.

The timeout relating to this on SMB shares is in the Workstation properties (LanManWorkStation). You can tweak the timeout by adding the following registry entry:

1. Click Start, click Run, type regedit.exe, and then click OK.
2. In Registry Editor, locate and then right-click the following registry subkey:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters
3. On the Edit menu, point to New, and then click DWORD Value.
4. Type SessTimeout, and then press ENTER.
5. On the Edit menu, click Modify.
6. In the Edit DWORD Value dialog box, click Decimal under Base, type 300 under Value data, and then click OK.

The default SessTimeout setting is 50 seconds. This changes it to 300 seconds (5 minutes).

For more information check out:

http://support.microsoft.com/kb/961657
http://support.microsoft.com/kb/102067
http://support.microsoft.com/KB/97876

About these ads

One thought on “SQL Server Compressed backups fail with SetEndOfFile error

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s