Monitor space in sqlserver datafile

Question

Usually I don’t let the databases grow automatically up to the disk boundary. Therefore, I would like to know if colleagues have any Procedure so that I can monitor and be alerted of when the space in a Datafile is running out. Be it log or data.

Example:

Assuming I have a bank account and limited its space for it to grow up to 10 MB and log up to 3 MB.

When it reaches 97% of the space used, I would like to be notified of either the log or the datafile.

Answer 1

From version SQL Server 2008 you can make a query the catalog view sys.database_files:

SELECT
    DB_NAME() as NomeBD,
    name as NomeArquivo,
    size/128 as TamanhoMB,
    max_size/128 as MaxTamanhoMB,
    cast(100 * cast(size as float) / max_size as varchar(max)) + '%' as OcupacaoArquivo
FROM sys.database_files;

Upshot:

Nome BD         NomeArquivo         TamanhoMB   MaxTamanhoMB    OcupacaoArquivo
--------------------------------------------------------------------------------
MeuBancoDados   MeuBancoDados             126            400                31%
MeuBancoDados   MeuBancoDados_log         139            600                23%

You can then use this query in a Procedure that sends email and schedule a job in SQL Server. Of course, you can also use it in View and use a small app to monitor. Or rotate by hand even when you want.
_{(I am not going to detail that part because it is not within the scope of the question.)}

Observation 1:

We need to convert the "size" field to float to get the decimals. If no conversion occurs, the entire split occurs (int/int), overriding decimals. A 99.999% occupancy would be reported as "only" 99%.

For example, for a 10GB limit, the file might be at ~100KB of the limit, while it would be informed that it would be at 100MB.

Observation 2:

Note that the columns size and max_size do not inform the size in any multiple Bytes unit. They inform in units of page. The page has 8KB by definition of the SQL Server architecture, since version 7.0.

Multiplying the number of pages by the space occupied, we have the space occupied in KB. To calculate these values in MB:

TamanhoMB = size * 8 / 1024
          = size / 128

---

NOTE: In a first version of that reply, he said that it was better to use decimal about float in the calculation. I went back because the difference is negligible for this account, in the 4th decimal place in more extreme cases. I left as float to make the query cleaner (and take up less space while running, measly 1 Bytes using default values).