April 15, 2016 at 9:04 am
Careful with that cursor definition: I had a horrible issue with backups never being executed using a solution much like this. Although a query from sys.databases returned the correct databases, the cursor would intermittently step through only some of the databases in the table. The other databases were never touched by the cursor, which meant backups weren't even attempted (no failure notice, therefore).
Again, this was an intermittent error that I could not reliably reproduce. Every second or third or fourth day, some of my databases weren't backing up.
I eventually found an answer from Tibor Karaszi at https://social.msdn.microsoft.com/forums/sqlserver/en-US/42f5c90d-0c96-44c9-a03f-9541e513bb72/ms-sql-backup-using-cursor-random-skip:
There is someting strange about sys.databases. When not defining the cursor as STATIC, you get some dnynamic behaviour of some kind (BOL has the details). And even though you probably don't add/remove database while this is happening, sys.databases seems to behave as if you did (apparently skipping databases). Defining the custor as STATIC makes SQL Server work with the result in a temptable, making for stable behavior. Thanks for teporting back, you're welcome to give feedback after a couple of days more, if this indeed seems to be the resolution.
It's possible that adding an intermediate step as you have (querying sys.databases to a table variable, then building the cursor from the table variable) would eliminate the problem.
Bottom line: I would declare the cursor as STATIC.
I don't know if this issue is still present in current versions of SQL Server.
Rich
April 15, 2016 at 11:30 am
Hmm... if it was truly Undocumented and Verboten, MS would've made it an obscure dbcc call, an extended proc, etc. Instead, it's definition is there, in the clear.
It's useful for what it does, and at least the source is there for other enterprising people look at and "make it better". It's too bad there's not an effective feedback loop for some of those mods to make it back to MS and get into new versions of the product. ("$70 billion cash in the bank, and they say, 'we don't have the resources available to fix it'?")
Looking around other things in the database schema and SMO, it doesn't take too long to realize how many hacks are built into SSMS et al to work around things that are inherently broken when us mere mortals look at them, and that things in SQL Server in general work well enough without any glaring show-stopping bugs.
April 16, 2016 at 3:21 pm
This is just an aside and certainly not a criticism of your well crafted code 🙂
I have always been "amused" by the "set-based mafia" who insist that my use of cursors in maintenance routines (indexes, statistics, check db, file sizes, etc.) is a sin against nature and that I would be much better off using a set-based approach with MS_foreachdb.
When I ask them how they think Microsoft executes the query in each Db they usually just stare at me an walk away. I imagine they are afraid my cursor pox might rub off on them:w00t:
I've had the same discussion about the for each table version also.
Use the right tool for the problem.
April 18, 2016 at 7:02 am
So we should never use it where we care about the results.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply