trying to edit a stored procedure

Question

trying to edit a stored procedure

myukas

SSC Eights!

Points: 860
More actions
November 21, 2017 at 8:42 am

#341619

i have a stored procedure exec usp_Sizing this is the bottom half it works very nice. my question is where i have the Free Space % can i change that so it only shows databases that are 80% or more or better yet is there a way i can trigger this so when it reaches 80% i can get notification immediately
BEGIN
               SELECT [Database Name],
               [File Name],
               [Physical Name],
               [File Type],
               [Total Size in Mb] AS [DB Size (Mb)],
               [Available Space in Mb] AS [DB Free (Mb)],
               CEILING(CAST([Available Space in Mb] AS decimal(10,1))/[Total Size in Mb]*100) AS [Free Space %],
               [Growth Units],
               [Max File Size in Mb] AS [Grow Max Size (Mb)]
               FROM ##Results
               WHERE [Database Name] = @Database_Name
           END
   END
DROP TABLE ##Results

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

As per your other post on that procedure, use sp_send_dbmail to trigger a mail alert based on a query from that table which satisfies your requirements from the output table.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

One option is to set up a custom counter in Performance Monitor and then create an alert to send you an e-mail whenever it goes over 80%.

John

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

To answer the immediate question...

I've also seen the rest of the code on your other posts. You're accumulating the information for each database in the global temporary table named ##Results. Once you've accumulated all the information for all the databases, do a select from that table into another Temp Table where the [Free Space %] is less than 20%. If the rowcount for that is > 0, then add the content of that new temp table to an email message and send it.

Shifting gears a bit and like I said, I've seen the rest of the code. All you're doing is checking the free space within the MDF and LDF files. It's going to be embarrassing for you and your client(s) when they run out of disk space because of database growth because you're not checking for free space on the disk.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

myukas SSC Eights! Points: 860 More actions · Answer 4

was told the server guys worry about the physical disk and i did not have to worry about that
we will see

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

myukas - Wednesday, November 22, 2017 7:19 AM
was told the server guys worry about the physical disk and i did not have to worry about that

Given that if the disk space runs out your database falls over, you really do have to worry about that. Indeed, you ought to worry about it more than they do, because it'll be you people come to when there's a problem with the database.

we will see

Yes, but better to see before it happens than after.

John

HappyGeek SSCoach Points: 18729 More actions · Answer 6

John Mitchell-245523 - Wednesday, November 22, 2017 7:28 AM
myukas - Wednesday, November 22, 2017 7:19 AM
was told the server guys worry about the physical disk and i did not have to worry about that
Given that if the disk space runs out your database falls over, you really do have to worry about that. Indeed, you ought to worry about it more than they do, because it'll be you people come to when there's a problem with the database.
we will see

Yes, but better to see before it happens than after.
John

+10

...

myukas SSC Eights! Points: 860 More actions · Answer 7

i agree and wanted to look at scripting from database
is there anyway i can do that
this place has a lot of issues

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

myukas - Wednesday, November 22, 2017 10:28 AM
i agree and wanted to look at scripting from database
is there anyway i can do that
this place has a lot of issues

Yes, there are plenty of ways. There are performance counters that give the free space on a disk drive. You could purchase (or use existing) third-party software. Or just type "sql monitor free disk space" into your favourite search engine. A lot of people have done this before and have blogged about it.

John