Solved

prevent SqlLogShip.exe from hanging while generating log backups

Posted on 2014-01-27
7
617 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
  • 4
  • 2
7 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 500 total points
Comment Utility
"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
Comment Utility
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
Comment Utility
Thanks _alias99. Hopefully, it gets answered.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
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
Comment Utility
None of the answers addressed my questions. But alternatives were found, so good enough, I guess.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now