Solved

prevent SqlLogShip.exe from hanging while generating log backups

Posted on 2014-01-27
7
625 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
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 39

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

911 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

22 Experts available now in Live!

Get 1:1 Help Now