Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

prevent SqlLogShip.exe from hanging while generating log backups

Posted on 2014-01-27
7
Medium Priority
?
748 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 40

Assisted Solution

by:lcohan
lcohan earned 2000 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

971 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