SQLTeam.com | Weblogs | Forums

Auto add new SQL data/log file when the db reaches its max


#1

Hi,
We have a very busy sql environment and currently have an issue that our sql db run out of disk space because the db is either reaches its max set size or if the db file is set to grow until the disk is full. I'm looking for a sql script to automate this as conditions below:

  1. Script to run every maybe 5 minutes
  2. Script to loop through each db and all dbs and check all databases on the server for both data file and log files
  3. If the data/log file reaches 90% of it set size, then check if the file is capped, if it's capped then auto expand the cap size if the disk has available space. Maybe save like 10% on disk
  4. If the file is not capped, then the disk the data/log file is on must reaches its full, then auto create new data/log file on the set drive (say F drive. this drive will be saved for this purpose)

Thank you very much!


#2

In SQL Server you can create a script for this, but it will not work as you expect. Have a look here:


#3

Thank you Jason. This is probably not what I'm looking for.


#4

I expect you've considered this, but in case not:

If you database(s) are in FULL recovery model, and the log files are "big", and the log files do use that space (at times) then I would consider increasing the Log backup frequency - once a minute would be OK, if not already set to that sort of frequency.

Won't help with the data files of course (although if the culprit for those might be Reindex it might be worth changing that to Reorganise as IME that is more likely to reuse space within the file than extend it.

My concern, reading your question, is that it seems (because you want to automate this) that the file growth is likely to be unexpected / unanticipated, which is a concern as it suggests that some processes are greedy but unexpected ... I would want to discover what they are and try to mitigate / improve their actions as a first step. but I guess you've either been there, or perhaps need an Elastoplast to buy some time.

I guess for #3 you could just change the DBs to allow them to NOT be capped, but I suppose that just means that they all become part of #4