Today an alter procedure statement was called. The alter statement was made, the correct DB using statement was in place, and immediately SSMS returned 'completed successfully' to the DBA. The proc being run in production continued to show old results however. After verifying with users the DBA right clicked on the object and generated schema, and the resulting script from SSMS showed the original code not the code as scripted in the alter statement.

The window with the alter statement was still up, so F5 was hit again. Same results. 'Completed Successfully' displayed by SSMS, while scripting the stored proc out reveals old version still in place.

After hitting F5 repeatedly, say maybe 20-30 times, the users finally started reporting that the code was working, and scripting out the object revealed the alter statement had finally taken effect.

When modifying schema definition, I know SQL Server takes schema locks to ensure that active queries and processes are consistent, but that didn't seem to be the case here as SSMS was reporting 'completed successfully' almost immediately.

Does SSMS report success when modifying some schema objects before it actually is successful? What is the isolation level of querying changes to programmable object definitions, i.e stored procs. I would think altering any object would delay the success result from SSMS until the schema lock had cleared and SQL Server had actually committed the change to the system object storage on which the catalog views depend. For example an alter table definitely does. This can take a significant amount of time depending on what changes are meta data only versus what changes require row by row conversions. The command is not reported as successful on an alter table statement until actually done.

Is that not the case for programing objects such as stored procs and functions?

  • 1) Can you easily reproduce this? 2) catalog data and system tables are not versioned, so it would block if anything and you'd get the correct value back. – Sean Gallardy Oct 19 '18 at 1:14
  • Is it maybe a compilation lock causing a block? support.microsoft.com/en-us/help/263889/… – Jeffrey Van Laethem Oct 19 '18 at 14:56
  • Interesting link Jeff, I'm going to have to internalize that info. As for reproducing this, no, not easily due to production environment. I agree that my expectation is it would have been blocked, but the immediate return of 'Completed Successfully' is what confuses me here. – RThomas Oct 19 '18 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.