Solved

prevent SqlLogShip.exe from hanging while generating log backups

Posted on 2014-01-27
7
654 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Output of Select Statement 2 38
Do not display comma when no last name 8 48
TDE for SQL Web Edition 1 42
Set a variable value in SQL Procedure 3 26
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

751 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