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

avatar image

narendba
648 34 44 54

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

avatar image

Kev Riley ♦♦
63.8k 48 61 81

(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

avatar image

Usman Butt
13.9k 6 12 21

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

avatar image

TimothyAWiseman
15.6k 22 49 38

+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.

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:

x2016

asked: Jul 19, 2012 at 08:39 AM

Seen: 1092 times

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

Copyright 2016 Redgate Software. Privacy Policy