Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_Blitz: when databases have Numeric-Round Abort turned on, check 82 fails with an arithmetic overflow #2302

Closed
maddave2000 opened this issue Feb 17, 2020 · 6 comments
Assignees
Labels

Comments

@maddave2000
Copy link

@maddave2000 maddave2000 commented Feb 17, 2020

When executing Sp_Blitz on a server where databases have "Numeric-Round Abort" Set to True, then an 'arithmetic overflow error converting numeric to data type numeric' error is thrown. This is for CheckID 82, related to the select statement which gets the database file sizes. In particular the section of the query which converts the file size to a string to add to the results temp table:

Convert(NVARCHAR(10), CONVERT(NUMERIC(38,2), (f.size / 128.) /1024.))
From sys.database_files f

To replicate:
Set the database option "Numeric-Round Abort" to "true".
Then execute the query:
SELECT Convert(NVARCHAR(10), CONVERT(NUMERIC(38,2), (f.size / 128.) /1024.))
From sys.database_files f"

@BrentOzar

This comment has been minimized.

Copy link
Member

@BrentOzar BrentOzar commented Feb 17, 2020

OK, how would you like to handle this? (I don't have any databases like this, but sounds like it's important for you, so I'd rather let you choose how the fix is done.)

@BrentOzar BrentOzar changed the title SP_Blitz Arithmetic overflow error thrown - Check ID 82 sp_Blitz: when databases have Numeric-Round Abort turned on, check 82 fails with an arithmetic overflow Feb 17, 2020
@maddave2000

This comment has been minimized.

Copy link
Author

@maddave2000 maddave2000 commented Feb 17, 2020

It's not too important, but just found it cropping up on a customer site and took a while to understand what was causing it.

I've not contributed to any issues before, but happy to help.

I suppose one fix would be to check if is_numeric_roundabort_on = 1 for any databases on the instance, and if so, then run "set numeric_roundabort off" at the start of sp_blitz. This would then only affect the sp_blitz session and the error shouldn't get thrown. It would probably be worth setting numeric_roundabort back to on at the end of the procedure (if required) in case someone is running sp_blitz in a session with other stuff that may rely on this setting.

@BrentOzar

This comment has been minimized.

Copy link
Member

@BrentOzar BrentOzar commented Feb 17, 2020

OK, cool. Here's how to contribute:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/master/CONTRIBUTING.md

I know Github can get pretty intimidating and frustrating, though, so if you hit a wall, just copy/paste the script changes you want into here, or attach 'em as a file, and I can go from there. Thanks!

@maddave2000

This comment has been minimized.

Copy link
Author

@maddave2000 maddave2000 commented Feb 21, 2020

Apologies, I tried to work my way through Git, but got a bit lost and a few errors trying to clone the repository. I think I need to spend a few hours going through the tutorials to get my head round the process!

In the meantime I've amended a copy of the latest sp_blitz.sql and applied some changes which fix the problem. I've attached the amended file. Please let me know if you need any changes.
sp_Blitz.sql.txt

@BrentOzar BrentOzar added this to the 2020-03 Release milestone Feb 21, 2020
BrentOzar added a commit that referenced this issue Feb 21, 2020
If they had it turned on, and databases have roundabort on, temporarily set it off while processing, then turn it back on when we're done. Working on #2302.
@BrentOzar

This comment has been minimized.

Copy link
Member

@BrentOzar BrentOzar commented Feb 21, 2020

OK, thanks. It's ever-so-slightly more complex than that: we also need to track whether they had it turned on or not at the start of execution. I wouldn't want to leave their session in a different situation than when we ran the script.

So what I did was:

  • Check to see if they have it turned on
  • If so, then look to see if we need to turn it off (by checking to see if databases have it turned on)
  • If so, set a variable that we're going to need to turn it off temporarily, and turn it off

Then at the end, I check that variable to see whether we need to turn it back on. I know this sounds convoluted, but I'm worried about edge cases like someone having it turned on for their session only, and then adding their first database with numeric roundabort on while sp_Blitz is running, and we then discover that at the end, and turn it off for their session. (sigh)

You can see the changed lines here: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/pull/2309/files

This will be in the March release and I'll credit you. Thanks!

@BrentOzar BrentOzar closed this Feb 21, 2020
BrentOzar added a commit that referenced this issue Feb 21, 2020
…nd_abort

#2302 sp_Blitz numeric roundabort
@maddave2000

This comment has been minimized.

Copy link
Author

@maddave2000 maddave2000 commented Feb 21, 2020

Thanks Brent. I didn't think about the session state prior to running the process. Makes perfect sense.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.