x

need script db size reaches to 90%?

I need a script for sending mails to all DBA's if any db reaches to 90%...

Could you please suggest any script for it...
more ▼

asked Jul 19, 2012 at 08:39 AM in Default

narendba gravatar image

narendba
570 28 39 46

Great kev need two columns table with databse name and status how much % it would be filled?

Any script do we have will schedule it by job...
Jul 19, 2012 at 09:39 AM narendba
Thanks Usman
Jul 19, 2012 at 10:18 AM narendba
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You need to investigate Alerts and Operators.

For example you could alert on a given data size, if your databases are fixed size and not set to auto-grow.

If there is no alert suitable for your scenario, then maybe have a scheduled job that runs periodically and fires an email to your defined Operators, using sp_send_dbmail when the condition is reached.

more ▼

answered Jul 19, 2012 at 09:10 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

(comments are locked)
10|1200 characters needed characters left

Not sure you relate the DB Size to Data Files Size With/Without including Log files? Anyways, how about the following script to know the Data/Log files sizes and the space remining. You can use a CURSOR / LOOP to get all the databases information

SELECT  *
FROM    ( SELECT    DB_NAME() DBName
          ,         df.name AS [FileName]
          ,         physical_name AS PhysicalName
          , [type_desc] FileTypeDescription
          ,         size / 128 AS TotalSizeinMB
          ,         size / 128.0
                    - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS FLOAT)
                    / 128.0 AS AvailableSpaceInMB
          ,         CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS FLOAT) / 128.0 AS ActualSpaceUsedInMB
          ,         ( ( CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS FLOAT)
                        / 128.0 ) / ( size / 128.0 ) ) * 100.0 AS PercentageSpaceUsed
          FROM      sys.database_files df
          WHERE     [type] IN ( 0, 1 )
        ) A
WHERE   PercentageSpaceUsed >= '90' 
Moreover, you can filter out some output using type column as used in the WHERE clause to return information about the DATA and Log files. Hope it helps.
more ▼

answered Jul 19, 2012 at 09:48 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

If you are willing to use an undocumented procedure, sp_msforeachdb tends to be faster and use shorter code than a cursor for something like this.
Jul 19, 2012 at 03:27 PM TimothyAWiseman
I'd just add that sp_msforeachdb has been shown to be unreliable http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx, and as it's undocumented, well its tough!
Jul 19, 2012 at 03:32 PM Kev Riley ♦♦
Nice. As an aside, this is what I was hoping the SQL Server Utility Control Point would show on the database storage bar graphs. Instead they show the percentage of total available space on the drive. So my 1TB database shows 90% and all the rest show 0%--worthless
Jul 21, 2012 at 12:47 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

Kev probably has the best answer with the alerts and operators, but I would add that specialized Monitoring tools like SQL Monitor will handle this very simply s well as many other monitoring tasks.

more ▼

answered Jul 19, 2012 at 03:28 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

+1: definitely - Monitoring tools make this stuff so easy
Jul 19, 2012 at 03:32 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948

asked: Jul 19, 2012 at 08:39 AM

Seen: 922 times

Last Updated: Jul 21, 2012 at 12:47 AM