Solved

prevent SqlLogShip.exe from hanging while generating log backups

Posted on 2014-01-27
7
669 Views
Last Modified: 2014-03-05
I am using SqlLogShip.exe (Log Shipping) to generate log backups. I have one critical issue that I need to address with my script and that's identify what is causing and what could cause SqlLogShip.exe to hang. In essence, I need to be sure that SqlLogShip.exe does not hang!

In testing, it did hang once and I don't know why. I ran it about a 1000-times with a single instance of hanging. The problem is when I tried to kill the hung SqlLogShip.exe in Windows Task Manager it would not die!  And I really can't have that in production.

Once a single SqlLogShip.exe was hung, the copy module in my script would no longer complete further executions. It would just hang when trying to copy. I would kill the script but then another SqlLogShip.exe was hung in Windows Task Manager. I did this a few times and then there were 4-SqlLogShip.exes in Task Manager. I tried to kill them using  TaskKill and End Process via Task Manager. Neither worked. I had to reboot the server to make them go away. The reboot worked.

I don't know what caused it to hang. Previously, I had a similar problem with robocopy.exe. Then decided to use this instead. How can I detect what is causing the hanging? Would some sort of trace work? If so, what counters? Can I prevent it from hanging somehow? Is there a better way to kill the processes, if needed?

-- ****************   GET DB LISTS   ****************

SELECT primary_id  
INTO #LSBackupTmp
FROM msdb.dbo.log_shipping_primary_databases;

SELECT secondary_id
INTO #LSCopyTemp 
FROM msdb.dbo.log_shipping_secondary_databases;

-- **************** BACKUP OPERATION ****************

-- enable the feature
EXEC sp_configure 'xp_cmdshell', 1
GO
-- update the currently configured value for this feature
RECONFIGURE
GO

-- loop through table if rows exist
DECLARE @BACKUPID uniqueidentifier
DECLARE @BACKUPCMD varchar(300)
DECLARE @BACKUPRETURNCODE INT

WHILE EXISTS(SELECT 1 primary_id FROM #LSBackupTmp)

BEGIN -- begin while for backup

SELECT TOP 1 @BACKUPID=primary_id FROM #LSBackupTmp;
SET @BACKUPCMD = '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SqlLogShip.exe" -backup ' + CAST(@BACKUPID AS varchar(50)) + ' -server PRIMARYSERVER -querytimeout 120'
EXEC @BACKUPRETURNCODE=xp_cmdshell @BACKUPCMD, no_output

DELETE FROM #LSBackupTmp WHERE @BACKUPID=primary_id;

-- will return 1 if there is a failure and 0 if it executed successfully
IF @BACKUPRETURNCODE=0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'dbmail',
	@recipients = 'Whoever@Whatever.com',
	@body = 'The log backups backup operation succeeded for the following databases: DB1, DB2',
	@subject = 'SQL Server Logs PRODUCTION',
	@query = 'SELECT GETDATE()';
END

END -- end while for backup

RETURN

-- ****************  COPY OPERATION  ****************

-- enable the feature
EXEC sp_configure 'xp_cmdshell', 1
GO
---- update the currently configured value for this feature
RECONFIGURE
GO

-- loop through table if rows exist
DECLARE @COPYID uniqueidentifier
DECLARE @COPYCMD varchar(300)
DECLARE @COPYRETURNCODE INT

WHILE EXISTS(SELECT 1 secondary_id FROM #LSCopyTemp)

BEGIN -- begin while for copy

SELECT TOP 1 @COPYID=secondary_id FROM #LSCopyTemp;
SET @COPYCMD = '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SqlLogShip.exe" -copy ' + CAST(@COPYID AS varchar(50)) + ' -server SECONDARYSERVER -querytimeout 120'
EXEC @COPYRETURNCODE=xp_cmdshell @COPYCMD, no_output

DELETE FROM #LSCopyTemp WHERE @COPYID=secondary_id;

-- will return 1 if there is a failure and 0 if it executed successfully
IF @COPYRETURNCODE<>0

BEGIN

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'dbmail',
	@recipients = 'Whoever@Whatever.com',
	@body = 'The log backups backup operation failed for the following databases: DB1, DB2',
	@subject = 'SQL Server Logs PRODUCTION',
	@query = 'SELECT GETDATE()';
END

END -- end while for copy

RETURN

Open in new window

0
Comment
Question by:pae2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 500 total points
ID: 39824327
"I am using SqlLogShip.exe (Log Shipping) to generate log backups"

Why would you use that utility "...to generate log backups" and not BACKUP LOG command?
0
 

Author Comment

by:pae2
ID: 39826911
I am backing-up using SqlLogShip.exe and copying to another server using SqlLogShip.exe. This is the exact executable that Log Shipping uses for both backup and copy. Does anyone have a solution to my problem?
0
 

Author Comment

by:pae2
ID: 39833894
Thanks _alias99. Hopefully, it gets answered.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 40

Expert Comment

by:lcohan
ID: 39833919
Why reinvent the wheel (and run into all kind of unknown issues due to permissions, lack of documentation, etc.) when there is SQL Transactional Replication or other SQL own methods available to suit most of the people using SQL.
0
 

Accepted Solution

by:
pae2 earned 0 total points
ID: 39895288
Nobody really provided a useful answer to my questions above. I stopped looking myself. However, I did find an alternative solution for SQL Server Enterprise 2005 and up (Enterprise only) that achieves what I was looking to do. There isn't much about this on the web but it's really quite useful. The following is an excerpt from BOL that explains:

MIRROR TO <backup_device> [ ,...n ]

Specifies a set of up to three secondary backup devices, each of which will mirror the backups devices specified in the TO clause. The MIRROR TO clause must be specify the same type and number of the backup devices as the TO clause. The maximum number of MIRROR TO clauses is three.

This option is available only in SQL Server 2005 Enterprise Edition and later versions.

pae2
0
 

Author Closing Comment

by:pae2
ID: 39905883
None of the answers addressed my questions. But alternatives were found, so good enough, I guess.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question