Monitor space in sqlserver datafile

Question

Monitor space in sqlserver datafile

Navigation

#1 by (3 votes)

6

Generally, I do not let databases automatically grow to the edge of the disk. So I would like to know if colleagues have any procedure so 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 client bank, I've limited its space so that it can grow up to 10 MB and log up to 3 MB.

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

sql-server

asked by anonymous 28.10.2014 / 12:55

1 answer

How to Direct Commands to Linux Terminal with Python set the position of the "search cancel" button in an input type = search in Safari 7

score 3 · Answer 1

From the SQL Server 2008 release you can 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;

Result:

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 e-mail and schedule a job in SQL Server. Of course, you can also use it in View and use a small application to monitor. Or run in the same hand when you want. (I will not detail this part because it is not part of the scope of the question.)

Note 1:

We need to convert the "size" field to float to get the decimals. If the conversion does not occur, the entire split occurs (% with%), truncating the decimals. A 99.999% occupancy would be reported as "only" 99%.

For example, for a limit of 10GB, the file may be ~ 100KB limit, while it would be reported to be 100MB.

Note 2:

Note that the int/int and size columns do not report the size on any multiple unit of Bytes. They report in units of page . The page has 8KB per 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 this answer, you said that it was preferable to use max_size over decimal 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 leave the query cleaner (and take up less space during execution, meager 1 Bytes using default values).