• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1158
  • Last Modified:

log shipping SqlLogShip.exe exception handling

Log shipping creates three jobs per-db. We have lots of dbs, so that's not going to work. I wrote a script that copies logs from one server to the next successfully. I'm having some problems with the exception handling. My script grabs all of the log shipping db ids on the primary puts them in a temp table then grabs one id at a time to perform a log backup. All of that works well. I'm using the querytimeout parameter so that sqllogship.exe skips logs backups that take longer than 2-minutes. That is represented by the 120. That also works. What I need help with is a way to send an error message to the CATCH letting me know that a log backup was skipped for a particular db. Does anyone have any ideas? I am open to restructuring the error-handling, if needed. I want to avoid using the alert jobs because I'm trying to make this happen with a single job.

Thanks,

pae2

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

-- 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 @ID uniqueidentifier
DECLARE @CMD varchar(300)

WHILE EXISTS(SELECT 1 primary_id FROM #LSTemp)

BEGIN -- begin while

BEGIN TRY
SELECT TOP 1 @ID=primary_id FROM #LSTemp;
 SET @CMD = '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SqlLogShip.exe" -backup ' + CAST(@ID AS varchar(50)) + ' -server SQLCONDEV02 -querytimeout 120 -verboselevel 2'
 EXEC xp_cmdshell @CMD, no_output
DELETE FROM #LSTemp WHERE @ID=primary_id;
END TRY

BEGIN CATCH
SELECT
CAST(@ID AS varchar(50)) AS ':log backup failed', ERROR_MESSAGE() AS ErrorMessage, GETDATE() AS ErrorDateTime;
-- RETURN(1)
END CATCH

END -- end while

RETURN

Open in new window

0
pae2
Asked:
pae2
  • 4
  • 2
2 Solutions
 
Surendra NathTechnology LeadCommented:
this might work for you,

The below code is not tested, this only work assuming SQLLogship.exe returns an error to XP_CMDSHELL upon time out or else it will not.


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

-- 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 @ID uniqueidentifier
DECLARE @CMD varchar(300)
DECLARE @ret_code INT

WHILE EXISTS(SELECT 1 primary_id FROM #LSTemp)

BEGIN -- begin while

BEGIN TRY
SELECT TOP 1 @ID=primary_id FROM #LSTemp;
 SET @CMD = '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SqlLogShip.exe" -backup ' + CAST(@ID AS varchar(50)) + ' -server SQLCONDEV02 -querytimeout 120 -verboselevel 2'
 EXEC @ret_code = xp_cmdshell @CMD, no_output
if (@ret_code = 1)
   RAISERROR('Errored with logshipping ' + CAST(@ID AS varchar(50)),16,1)

DELETE FROM #LSTemp WHERE @ID=primary_id;
END TRY

BEGIN CATCH
SELECT
CAST(@ID AS varchar(50)) AS ':log backup failed', ERROR_MESSAGE() AS ErrorMessage, GETDATE() AS ErrorDateTime;
-- RETURN(1)
END CATCH

END -- end while

RETURN

Open in new window

0
 
pae2Author Commented:
Thanks for the help. I'm not sure if what you suggested works. But it's similar to what I did. I dropped the try/catch code. Then did the following which does work!

DECLARE @RETURNCODE INT
EXEC @RETURNCODE=xp_cmdshell @CMD, no_output

-- will return 1 if there is a failure and 0 if it executed successfully
IF @RETURNCODE<>0
BEGIN
-- SOME CODE
END

Open in new window

0
 
pae2Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for pae2's comment #a39813040
Assisted answer: 500 points for Neo_jarvis's comment #a39810706

for the following reason:

My code definitely works. I'm not sure if Surendra Ganti's will work, but it looks good to me.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Surendra NathTechnology LeadCommented:
The comment here shows to award 500 points to my comments but it never did...
0
 
Eugene ZCommented:
please clarify what are your sql server version edition \sp
0
 
pae2Author Commented:
EugeneZ this has been resolved already. So no need to answer. But we're using SQL Server 2008R2.
0
 
pae2Author Commented:
Surendra Ganti, I tried to award the points. I'm not sure what's wrong. The question isn't closed yet. Maybe EE can fix it. I'll respond to whatever questions they have.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now