David, I've been working on databases for more than 25 years. They're complex beasts, and there are really so many things to watch that you really need to have someone who can dedicate some time to PM to avoid some nasty problems sometimes. Or find some other way to benefit from the experiences of others in maintaining these systems.
You don't say why the autogrow wasn't completing (I'm guessing that it was timing out, but if you don't know the answer to that you definitely need to find out). The SQL logs should provide a message that would allow you to diagnose the reason for the failure. And I'd wonder about the reason it took 4 hours to find out - personally, I head for the logs as one of the first steps in any troubleshooting, even more so for apps I'm not familiar with. If a timeout was the reason, then I'm speculating that the database has grown to the point that 10% is a large chunk of disk space to allocate. Even if the file extend is successful, your database is paused until it completes. Probably not desirable. I think most SQL Server DBAs would attempt to avoid % growth params in general. I do (and most of my databases are small - under 100 GB, most much smaller). You don't want it to grow any longer than you want to be waiting for it to complete. Even if you'd known that the database needed more space, what would you have done proactively? It's designed to extend when it needs to. The problem was that it didn't. The only thing with regard to freespace that you should EXPECT to monitor is exactly what you're already doing - freespace on disk. Because if it (or the logfile) can't grow when it needs to because of lack of disk space, you have a REAL problem. The other problem you're facing is that there are LOTS of things that it's worth monitoring to avoid problems, and freespace is only one of them. (Or one class of them - there are a number of kinds of fragmentation issues for SQL Server, too.) I'd recommend doing a web search for free SQL Server monitoring tools. You'll find some reviews and 'top 10s', in addition to lots of ads and tool-specific links. A number of the free ones are slimmed down versions of for-pay solutions. You'll get a much better coverage of the problem space, and you'll be able to leverage the expertise of those tools to know what to look for. HTH Frank From: [email protected] [mailto:[email protected]] On Behalf Of David McSpadden Sent: Friday, January 30, 2015 12:36 PM To: [email protected] Subject: [NTSysADM] RE: monitoring sql db's This morning my IVR unit ran out of SQL space even with a 10% growth selected and having 500GB of space. The Autogrow function was not completeing. Took support 4 hours to find that it just needed to grow. I would like to monitor the db's so my members are not affected by an alert I could have gotten days ago. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Frank Ress Sent: Friday, January 30, 2015 1:32 PM To: [email protected]<mailto:[email protected]> Subject: [NTSysADM] RE: monitoring sql db's SQL Server databases will automatically add space to their data and log files as needed (in either fixed-size increments, or as a % of size of the existing file, which is a configurable parameter for each file). Also, free space will be fragmented within the data file based on a number of factors. Overall free space may not be particularly useful - it's possible to have even more than 10% free, and the database may need to expand the file anyway, depending on the DML operation required. What is the goal of alerting on some kind of free space threshold? From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of David McSpadden Sent: Friday, January 30, 2015 12:15 PM To: '[email protected]' Subject: [NTSysADM] monitoring sql db's Anyone have a good method,app, or script to alert your admins when SQL database is below 10% free? This e-mail and any files transmitted with it are property of Indiana Members Credit Union, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this email is strictly prohibited. Please consider the environment before printing this email. ________________________________ This communication is for the use of the intended recipient only. It may contain information that is privileged and confidential. If you are not the intended recipient of this communication, the disclosure, copying, distribution or use hereof is prohibited. If you have received this communication in error, please advise me by return e-mail or by telephone and then delete it immediately. This e-mail and any files transmitted with it are property of Indiana Members Credit Union, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this email is strictly prohibited. Please consider the environment before printing this email. ________________________________ This communication is for the use of the intended recipient only. It may contain information that is privileged and confidential. If you are not the intended recipient of this communication, the disclosure, copying, distribution or use hereof is prohibited. If you have received this communication in error, please advise me by return e-mail or by telephone and then delete it immediately.
