SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Managing Database Data Usage With Custom Space Alerts


Managing Database Data Usage With Custom Space Alerts

Author
Message
Simon D Richards
Simon D Richards
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 639
Comments posted to this topic are about the item Managing Database Data Usage With Custom Space Alerts
jswong05
jswong05
SSC Eights!
SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)

Group: General Forum Members
Points: 897 Visits: 476
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
:-P
Simon D Richards
Simon D Richards
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 639
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.
Yuri Padorin
Yuri Padorin
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 119
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.
Josh Turley-300197
Josh Turley-300197
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 104
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*
Simon D Richards
Simon D Richards
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 639
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.
jswong05
jswong05
SSC Eights!
SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)SSC Eights! (897 reputation)

Group: General Forum Members
Points: 897 Visits: 476
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
:-P
quackhandle1975
quackhandle1975
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4673 Visits: 1244
Good article, thanks very much.

qh

Who looks outside, dreams; who looks inside, awakes. – Carl Jung.
alex.lam 53610
alex.lam 53610
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 95
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';
wolf138
wolf138
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 123
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search