Post reply

Managing Database Data Usage With Custom Space Alerts

  • May 30, 2012 at 12:10 am

    #256785

    Comments posted to this topic are about the item Managing Database Data Usage With Custom Space Alerts

  • May 30, 2012 at 10:47 am

    #1494790

    The scheme and the idea are very good. However, I will suggest improvement as:

    1) make threshold parameter of the SP so you can change it on the agent job without altering SP

    ALTER PROCEDURE dbo.usp_DBA_checkfordatabasefreespace

    @threshold INT = 80

    AS

    2) it is comparing against the allocated space. In most databases, it is set as auto-grow. So the goal is really to alert on space remaining on the drive volume unless it is assuming the allocated database space is it.

    CREATE TABLE #TMPFIXEDDRIVES (

    DRIVE CHAR(1),

    MBFREE INT)

    INSERT INTO #TMPFIXEDDRIVES EXEC xp_FIXEDDRIVES

    3) besides system database, you might want to consider online database only, not read-only, ....

    FROM sys.databases

    WHERE

    name NOT IN ('master', 'model', 'msdb', 'tempdb', 'ADMINDB', 'Distribution') AND

    name NOT LIKE '%ReportServer%' AND

    DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND

    DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'

    Jason
    http://dbace.us
    😛

  • May 30, 2012 at 11:34 am

    #1494809

    Hi Jason, thanks for the comments, all advice is greatly appreciated.

    1. Good point, this will allow us to more easily deploy the process to servers with different threshold requirements.

    2. The purpose of these alerts is to notify us to the amount of free space in the data files, not the free drive space. We can then proactively manage growth in the data files more effectively, rather than relying on SQL to automatically grow the data files for us. You are correct though, an alert to notify us of full drives is highly recommended as well. You can't increase data and log files size if you don't have any capacity to do so. 🙂

    Thanks for the input.

  • May 31, 2012 at 7:33 am

    #1495207

    Another option for data file space consumption monitoring, and what I use, is policy based management.

    I have two conditions that test free space. One that tests for >= 10% free and another that tests for >= 20% OR <10% (so they don't overlap)

    I have one alert that responds to any failed policy event.

  • June 1, 2012 at 11:31 am

    #1495998

    You may need to edit your stored procedure. I set that up and it sent me an email for the one database that had more than 80% free. This should be showing me databases that are more than 80% used, or have less than 20% free.

    Quick fix: Set the threshold to 20, then change your delete from #space_used statement to delete anything where the free space is greater than the threshold (ie has more than 20 percent free space available). That should only leave files with less than 20% free.

    --remove any entries that do not fall under the threshold

    DELETE FROM #space_used WHERE free_space_percent > @threshold;

    Otherwise an awesome write-up and most insightful. This saved me a huge bundle of time.

    *bookmarked*

  • June 2, 2012 at 6:44 am

    #1496180

    Hey Josh, well spotted, that will teach me to play with my code while I write up my articles. 🙂

    I will see if I can edit the article now it is published to reflect the change.

    Thanks for the feedback.

  • June 5, 2012 at 12:04 pm

    #1497253

    I would change f.size to f.maxsize. case when 0, -1 use f.size.

    Also the 2005 table is sys.database_files. sys.sysfiles is only provided for backward compatibility.

    Jason
    http://dbace.us
    😛

  • July 13, 2012 at 7:40 am

    #1512538

    Good article, thanks very much.

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • June 25, 2014 at 8:59 pm

    #1724898

    Thanks for the article.

    I tried it and got an error. The error was caused by one of the database is OFFLINE.

    Hence, I added the line 'and state_desc != 'OFFLINE' as below and no error occurs.

    ...

    FROM

    sys.databases

    WHERE

    database_id > 4

    and state_desc <> 'OFFLINE';

  • July 14, 2015 at 3:15 pm

    #1812481

    late to game.. but

    why did you AVG() on all the database files? Some databases could have multiple files(*.ndf) and many of them could be small, but any one of them could run wild.

    Something I was looking for in this article was how to account for "free space" and autogrowth: What if a db file has an autogrow of 100MB, is currently allocated at 800MB, and is currently using 795MB. (let's say max size is 12000MB) If the database rarely grows, this alert would constantly nag. I would have to reallocate the database to 1000MB to avoid the alert (given a 20% threshold). That doesn't seem like the right way to mitigate the alert; translate this example to GB and it's a great waste of space. Do we compare against MaxSize? (what if allocation is unlimited?)

    How are others accounting for this?

  • July 16, 2015 at 6:00 am

    #1812907

    Hi, wolf318, thanks for the reply.

    The AVG() is exactly for the reason that some database have multiple data files. The alert described in this article is to 'monitor total space', not each data file that comprises your database. If you remove the AVG(), and then iterate over each data file and check it's free space, you can customize the message you are passing to the RAISERROR() event to include the data file that has reached your defined threshold in almost exactly the same way.

    Managing space in your data environment is a tricky topic to cover. No two systems are the same, and everyone has different requirements. At my old job, I was working with multi TB databases in an enterprise class data shop, and my preference (and that of the system owners) was to allocate more space to the data files, even if it meant 500GB of data that would slowly be consumed over 6 months being added to the database, as the fear and cost of systems going offline because of lack of space was greater than the cost of adding new files and new disk to a system. In my current job, at a small software house, the availability of resources outweighs the fear, so I am more likely to adjust my thresholds down to get around alerts, and then deal with it when the lack of space is more pressing.

  • July 16, 2015 at 8:33 am

    #1812997

    Thanks Simon. Do you know of any other articles that might explore various methods for identifying capacity concerns? For me, it may end up being a combination of a few methods. Leveraging Alerts and new error numbers is a method I didn't consider, but is definitely good to know about. thanks for posting.

  • October 28, 2015 at 6:44 pm

    #1836240

    Just wondering if anyone has had an issue with this script and '-' hyphens in the names of the db's. Do I have to ignore the db's with hyphens in them from the script or is there a way to include these db's?

  • October 29, 2015 at 5:13 pm

    #1836547

    Setting the line

    [font="Courier New"]USE ' + @db_name + ';[/font]

    to

    [font="Courier New"]USE ' + QUOTENAME(@db_name) + ';[/font]

    should do the trick

  • October 29, 2015 at 6:02 pm

    #1836555

    Thanks heaps worked perfectly 🙂

Viewing 15 posts - 1 through 15 (of 15 total)

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