Solved

log shipping SqlLogShip.exe exception handling

Posted on 2014-01-25
9
980 Views
Last Modified: 2014-02-01
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
Comment
Question by:pae2
  • 4
  • 2
9 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 500 total points
ID: 39810706
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
 

Accepted Solution

by:
pae2 earned 0 total points
ID: 39813040
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
 

Author Comment

by:pae2
ID: 39813503
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39813504
The comment here shows to award 500 points to my comments but it never did...
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 39813683
please clarify what are your sql server version edition \sp
0
 

Author Comment

by:pae2
ID: 39813835
EugeneZ this has been resolved already. So no need to answer. But we're using SQL Server 2008R2.
0
 

Author Comment

by:pae2
ID: 39813838
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

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

19 Experts available now in Live!

Get 1:1 Help Now