1

I was working on an old project the other day over vpn for a client and found an issue where I was purging data off of the wrong PK and as a result their database was huge and was slow to return info which was causing our software to freak out.

I got to thinking that I would just like to know when I am approaching the max size. I know how to set up the sql server for email notification but I've only sent test messages. I looked at my databases properties hoping I would see some options related to email but I saw nothing.

I've seen where you can send out the email after a job so i'm hoping you can do this too. Anyone know how I can achieve this?

TWood
  • 2,563
  • 8
  • 36
  • 58
  • What is "the max size"? Is it 2008 or 2008R2? Have you looked at all into database mail http://msdn.microsoft.com/en-us/library/ms175887.aspx? Note that if this is an Express database you don't have access to jobs natively because Express doesn't have SQL Server Agent. – Aaron Bertrand Aug 24 '11 at 21:45
  • I'm using DBMail currently to send test messages from my server. I actually read the article you linked in the process of doing that setup. This is sql 2008 R2 standard edition so it supports the agent. The size I guess would be a couple of gigs before I started to care. – TWood Aug 25 '11 at 12:35
  • And is this about a specific database, any database, or the sum of all databases on the server? – Aaron Bertrand Aug 25 '11 at 12:37
  • I would want to do this on an individual database. Most servers I do will only have one but some have three. – TWood Aug 25 '11 at 12:38

1 Answers1

0

sys.database_files has a size column which stores the number of pages. A page is 8kb, so you need to multiply this by 8 * 1.024, which is 8.192. This will show us the size of the file on disk. Just replace [database name] with the actual name of your database, and adjust the size check if you want something other than 2 GB as the warning threshold.

DECLARE @size DECIMAL(20,2);
SELECT @size = SUM(size * 8.192)/1000 FROM [database name].sys.database_files;
IF @size >= 2000 -- this is in MB
BEGIN
    -- send e-mail
END

If you want to do it for all databases, you can do this without going into each individual database's sys.database_files view, by using master.sys.sysaltfiles - I have observed that the size column here is not always in sync with the size column in sys.database_files - I would trust the latter first.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490