?
Solved

log shipping SqlLogShip.exe exception handling

Posted on 2014-01-25
9
Medium Priority
?
1,060 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
[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
9 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 43

Expert Comment

by:Eugene Z
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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