SOLVED: Need a Custom Metric to monitor TEMPDB - help!
PDinCA
Posts: 642 Silver 1
We had a runaway tempdb issue due to the version store not releasing its 240GB of reserved space and, likely, the GHOST cleanup process being unable to keep up with the deletion of somewhere approaching 50 million rows, index defragging and an emergency DB Shrink (DON'T bag on it - the choice was fail the system for lack of space or shrink it!), all at the same time...
Having failed the cluster over and reset the 16 individual tempdb files to their original 4GB, from 15GB per, I need to monitor tempdb VERY closely as we have a finite amount of space at our hosted site and expenditure of $,$$$ to permanently move tempdb to its own spindle isn't on right now.
Disclaimers:
1. I didn't architect the disk usage.
2. I'm stuck with mdf's and tempdb on the same drive.
3. I only have one other drive for ldf's and backups that doesn't have space for tempdb to grow to anything like the 240GB it just did.
4. Project IS underway to completely rearchitect the solution to spread to FOUR spindles and new server boxes and gobs of RAM.
I've seen the Custom Metric for Database Size at http://monitor.red-gate.com/Configuration/Custom-Metrics/Edit/11#/?v=1. Is this what I need as a basis and then qualify the query to feature just tempdb?
I need to get this set up ASAP so we don't get surprised, again, and run out of space...
Thanks for any assistance.
UPDATE
Thanks to what turned out to be very clear instructions on the page cited above, I was able to create and test the required metric.
However, the sample query purports to return the result in KB, but using ( 1024. * 1024. ) the result is in GB against a SQL 2005 instance. Strange but true...
Having failed the cluster over and reset the 16 individual tempdb files to their original 4GB, from 15GB per, I need to monitor tempdb VERY closely as we have a finite amount of space at our hosted site and expenditure of $,$$$ to permanently move tempdb to its own spindle isn't on right now.
Disclaimers:
1. I didn't architect the disk usage.
2. I'm stuck with mdf's and tempdb on the same drive.
3. I only have one other drive for ldf's and backups that doesn't have space for tempdb to grow to anything like the 240GB it just did.
4. Project IS underway to completely rearchitect the solution to spread to FOUR spindles and new server boxes and gobs of RAM.
I've seen the Custom Metric for Database Size at http://monitor.red-gate.com/Configuration/Custom-Metrics/Edit/11#/?v=1. Is this what I need as a basis and then qualify the query to feature just tempdb?
I need to get this set up ASAP so we don't get surprised, again, and run out of space...
Thanks for any assistance.
UPDATE
Thanks to what turned out to be very clear instructions on the page cited above, I was able to create and test the required metric.
However, the sample query purports to return the result in KB, but using ( 1024. * 1024. ) the result is in GB against a SQL 2005 instance. Strange but true...
Jesus Christ: Lunatic, liar or Lord?
Decide wisely...
Decide wisely...