Sql-server – LSCopy job fails on SQL Server 2005 – Insufficient space

disaster recoveryhigh-availabilitylog-shippingsql serversql-server-2005

I'm having some trouble getting my LSCopy job of Log-Shipping on my SQL Server 2005 server, to work.

The LSBackup job is configured so the backup is made locally (D: drive)

The LSCopy job is configured to copy from the primary (\server_name\d$) and copies it locally to the secondary server.

The LSRestore is restores the local copy to the secondary database with NORECOVERY

The LSBackup job runs fine, copies the file to the location specified.

The LSCopy job fails. The error message states that it goes to the source for settings. Gets the settings (I assume) then begins the copying.

This errors saying:

Error: Not enough server storage is available to process this command.

What I'm not sure about is whether the destination folder needs to be a network share, or if just \server_name\d$ is accepted.

Any help will be really appreciated!

Best Answer

The destination path for the LSCopy job has to be an UNC Path for all practical purposes, to make things simpler to debug I would reccomend not to use the default \d$ shares as you will have to change the permissions on them but connecting to \localhost\c$ is a network connection. Now the error

***Error: Not enough server storage is available to process this command.

Is an operating system error. but not an error from the SQL Server. You are probably starving the computer on memory or having some network issues, If on Windows Server 2003 a quick reboot of the secondary server will fix this temporary and then I would look into memory settings on the server

Related Solutions

Sql-server – Unable to delete Log Shipping configuration

If something is still setup on the secondary server you need to run sp_delete_log_shipping_secondary_database there.

You can clear the log shipping configuration on the primary by executing sp_delete_log_shipping_primary_secondary

USE master;
GO
EXEC master.dbo.sp_delete_log_shipping_primary_secondary
    @primary_database = N'<PrimaryDatabase>'
   ,@secondary_server = N'<SecondaryServer>'
   ,@secondary_database = N'<SecondaryDatabase>'
GO

Sql-server – Log shipping restore: Only members of the sysadmin fixed server role can perform this operation

I found your question while googling the same errors and solved my issue by giving the monitoring server's agent account sysadmin (sysadmin was not needed for the agent accounts on the primary and secondary)

I think the problem might be caused by having the monitoring server on a separate machine but I could not find any documentation on this.

Running profiler during each logshipping job showed that the jobs explicitly ask if the user is sysadmin before continuing on to update the history tables:

select IS_SRVROLEMEMBER(N'sysadmin')

Initially I tried adding sysadmin to the primary and secondary server's agent accounts but the same errors continued. In the end I discovered that it is only the monitoring server's agent account that needs sysadmin. This allows the msdb.dbo.log_shipping_ tables to be updated with the last backup, copy and restore dates as well as the lsn numbers on the three servers. Then the LS_Alert_ job and the Transaction Log Shipping Status report are populated with the latest values.

Best Answer

Related Solutions

Sql-server – Unable to delete Log Shipping configuration

Sql-server – Log shipping restore: Only members of the sysadmin fixed server role can perform this operation

Related Question