Always there exists a single log file for each SQL Server database. Log file keeps growing when in full or bulk logged recovery model. DBAs implement strategies to manage the log file size. There are two aspects of log file size.
- One is log file size that it captures on disk and is shown at OS level.
- Second aspect relates to filled part of over all log file.
Suppose there is a log file with size 20 GB on disk then it is quite possible that it is filled with only 1 GB log and rest of 19 GB is empty. SQL Server will keep populating the remaining part unless it is full and then will claim more size according to file growth settings.
It is a basic requirement to have an idea of log file and its used space. Following is a very simple way to get this information. You may also insert the information into a table on daily or weekly basis and may process alerts for these.
DBCC SQLPERF (LOGSPACE) GO
As a result we have Database name, total log file size as well as the filled log space percentage. Last column status is always zero.