I have a feature suggestion for SQL Prompt that is an extension to settings currently available under the
Show execution warnings section of the
Code Analysis settings. We have people that are using SSMSBoost because it offers the ability to warn you of certain "dangerous" actions. Without getting into the SSMSBoost functionality, this got me thinking about whether it would be useful to have more warning functionality in SQL Prompt, and I think it would. Specifically:
- the ability to have warnings on specific servers only (so that you don't get warned in DEV or TEST, for example); or
- the ability to shut off warnings on specific servers only (so that you only get warned in PROD);
- more types of warnings to choose, including things like:
- you are going to update data
- you are going to update data without using a WHERE clause
- you may be about to begin an explicit transaction
- your script may not commit or rollback an explicit transaction
- @trancount > 0
- you are going to DROP something
- you are going to ALTER something
- you are going to TRUNCATE something
- etc.
I haven't really thought through what could be on the list. It would be really cool if it was extensible somehow so that we could define what we want on the list. But my immediate concern is the following kind of situation: a colleague recently left a row locked in tempdb. This, of course, meant that the log couldn't truncate even though it was being backed up every 10 minutes. This was on the OLTP server that backs up the GoDaddy registrar system (think: average of 15k transactions/second, up to 35K transactions/second). Eight hours later, the tempdb log filled up. We have two separate SQL Agent jobs defined by our operations group that should have caught this, but neither was functioning correctly, so we actually incurred a 10-minute outage in our domain control center, which for us is a Very Big Bad Deal. If SQL Prompt had warned him that he had a transaction open after his script completed, that would have been a Very Big Good Thing.
Thoughts?
Thanks Chris this is a really interesting idea and fits with some discussions we've been having here recently to expand this functionality.
I haven't really thought through what could be on the list. It would be really cool if it was extensible somehow so that we could define what we want on the list. But my immediate concern is the following kind of situation: a colleague recently left a row locked in tempdb. This, of course, meant that the log couldn't truncate even though it was being backed up every 10 minutes. This was on the OLTP server that backs up the GoDaddy registrar system (think: average of 15k transactions/second, up to 35K transactions/second). Eight hours later, the tempdb log filled up. We have two separate SQL Agent jobs defined by our operations group that should have caught this, but neither was functioning correctly, so we actually incurred a 10-minute outage in our domain control center, which for us is a Very Big Bad Deal. If SQL Prompt had warned him that he had a transaction open after his script completed, that would have been a Very Big Good Thing.
Thoughts?