Category Archives: SQLServer

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

Tagged , , ,

Querying other SQL databases using active script in WhatsUp

This is a script that allows you to query an SQL server other than the WhatsUp DB. It doesn’t require a DSN ODBC entry and is self contained.

Let’s assume we want to run this query:

SELECT cur_latency from MSreplication_monitordata where agent_type='3' and publication_id='2'

against a server named mySQLserver. To do that we can use the ADODB object in VBScript:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

'We're not using a DSN so specify the connection string
strConnectionString="DRIVER={SQL Server};SERVER=" & strDatabaseServer & ";UID=" & strUsername &_
";PWD=" & strPassword & ";DATABASE=" & strDatabaseName
context.logmessage strConnectionString

objConnection.Open strConnectionString
objRecordset.CursorLocation = adUseClient

This bit of the script creates a Connection object and using the connection string opens a connection to the SQL server. We then use that connection to perform our query:

'This is the query to run
strQuery="SELECT cur_latency from MSreplication_monitordata where agent_type='3' and publication_id='2'"
'Connect to the DB server and run the query
objRecordset.Open strQuery, objConnection, adOpenStatic,adLockOptimistic

The rows returned populate the record set object. We’re only expecting one row for this one so we can get the result by:

If objRecordset.EOF Then
    'No rows returned
    context.logmessage "Record cannot be found."
Else
    'rows returned
    context.logmessage objrecordset("cur_latency") 
    context.setvalue int(cur_latency)
End If

and then close the objects sets.

objRecordset.Close
objConnection.Close

Here’s the full script:

'The database server is kept in an attribute named "DatabaseServerName"
strDatabaseServer="mySQLserver"

'The database that stores the replication configuration is "distribution"
strDatabaseName="distribution"

'This is the query to run
strQuery="SELECT cur_latency from MSreplication_monitordata where agent_type='3' and publication_id='2'"

'Get the logon credentials
strUsername = Context.GetProperty("CredADO:Username")
strPassword = Context.GetProperty("CredADO:Password")

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

'We're not using a DSN so specify the connection string
strConnectionString="DRIVER={SQL Server};SERVER=" & strDatabaseServer & ";UID=" & strUsername &_
";PWD=" & strPassword & ";DATABASE=" & strDatabaseName
context.logmessage strConnectionString

objConnection.Open strConnectionString
objRecordset.CursorLocation = adUseClient

'Connect to the DB server and run the query
objRecordset.Open strQuery, objConnection, adOpenStatic,adLockOptimistic

If objRecordset.EOF Then
    'No rows returned
    context.logmessage "Record cannot be found."
Else
    'rows returned
    context.logmessage objrecordset("cur_latency") 
    context.setvalue int(cur_latency)
End If

objRecordset.Close
objConnection.Close

Download the source from the link:

Download

Tagged , , , , , ,