DB full alert??

[paul172]

I am looking for an alert to let me know when my DB gets to a certain % remaining. I know the alert 9002 alerts you to DB logs getting to a certain %, is there a similar alert for a database.

The reason I am asking this is due to a problem, I'm yet to trace, with the database not expanding when it should.

Thanks...

 

[paul172]

Anyone....?

 

[SQLBill]

I don't know how to get the info you are asking about.

But, have you double checked to make sure the Data files and the log files are set to AutoGrow? And that they are set to Autogrow by a large enough amount?

You didn't post the errors you are getting or any other information about the actual problem, so it's hard to tell what might be the issue.

Some commands cause the log and data files to grow very quickly, if autogrow isn't set high enough this can cause an error about the log or database running out of room. DBCC REINDEX is one command that needs a lot of space. DBCC INDEXDEFRAG is another.

-SQLBill

Posting advice: FAQ481-4875

 

[paul172]

There are no errors that I can see. It does grow occasionally, for instance last week when a re-index was running at night it automatically grew the ammount I've stipulated in the database options(1000MB). However a few times through the day I've noticed the front end app has stopped processing interface files, on checking the database, it was full. I can only assume there is some sort of timeout issue happening during the day?

Would you say that growing the database by 1000MB is too much, we're currently processing 200mb a day?

 

[SQLBill]

My database grows by about 1/2 GB a day and I autogrow by 2000 MB. That's worked for me. So 1000MB should work for you. I would try adding a DBCC SHRINKFILE after the REINDEX. That will reclaim some of the empty space.

One issue with growing the data though...lets say you have 10 GB hard drive. Your database is 6 GB and set to autogrow by 2 GB for datafile and 1 GB for the log. Currently, your database will be using 9 GB of space (6 used and 3 empty - but still part of the database). Now you begin filling up that empty space and the db needs to grow...oops, you only have 1 GB left. Even if you still have unused space as part of the database, it needs to grow at a certain point (usually 80% filled). If it can't do it, it reports 'not enough room'.

Refer to the BOL for the syntax for DBCC SHRINKFILE.

-SQLBIll

Posting advice: FAQ481-4875