March 9, 2011 at 9:08 am
I have come across an interesting issue and was hoping somebody would be able to help explain why it is happening. What I am trying to do is find the backup devices that are not used by any agent jobs. All of our backup jobs have a step with the command
BACKUP DATABASE [i]databasename[/i] TO [Dmpdatabasename] WITH options
I have written the following code to identify which backup devices are not specified in any jobs
SELECT *
FROM sys.backup_devices
where name not in(
SELECT substring(command,charindex('TO [',command)+4,charindex('] WITH',command) -charindex('TO [',command)-4)
FROM msdb.dbo.sysjobsteps
where command like '%Dmp%WITH%')
The issue is on some servers I get an error.
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The odd thing is the subquery by it self does not return the error.
SELECT substring(command,charindex('TO [',command)+4,charindex('] WITH',command) -charindex('TO [',command)-4)
FROM msdb.dbo.sysjobsteps
where command like '%Dmp%WITH%'
The only thing I see that is similar between the servers that this statement fails on is they all have sharepoint databases and thus at least one database ( and corresponding backup device) named SharePoint_AdminContent_XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX. I figure it is the "-" that are causing the problem but the statement fails even when i exclude those names from both the outer query as well as the subquery.
Why would the substring function fail ONLY within the subquery?
March 9, 2011 at 1:52 pm
If I were to hazard a guess, it'd have to do with when the optimizer was choosing to apply the WHERE clause. It might be applying it before the select when you run the subquery on its own but after afterwards when it is a subquery.
I'm assuming you get the error every time when you don't specify the where clause at all. You'll probably have to use a CASE instead of (or in addition to) a WHERE.
This should provide the extra protection to stop the substring from attempting to run where the criteria doesn't apply.
SELECT *
FROM sys.backup_devices
where name not in(
SELECT CASE WHEN command LIKE '%Dmp%WITH%' THEN substring(command,charindex('TO [',command)+4,charindex('] WITH',command) -charindex('TO [',command)-4) ELSE 'asdf' END
FROM msdb.dbo.sysjobsteps
where command like '%Dmp%WITH%')
March 10, 2011 at 7:25 am
Thanks Seth. First for providing the code with the CASE statement, it does run successfully with it. But more importantly, based on you comment about the optimizer choosing when to apply the where it got me thinking.
My first thoughts were why would the optimizer be choosing to apply the where clause differently on different servers because on some servers it works and some it doesn't. So I looked at the estimated execution plan from two servers (one working and one not) with an identical patch level, same version, service pack and CU. Sure enough there was a difference. On a server where the statement works it filters through the the sysjobsteps rows properly. On the server where it doesn't work it filters through the sysjobsteps results twice, once the same a working server and the other filter is looking for "[command] is null and like '%DMP%WITH%'" which would always return no rows and thus is what is probably causing my problem.
Now to try and find out why the different plans.
I have attached both plans for anyone interested.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply