Microsoft – SQLServer – Log File Utilization

Microsoft – SQLServer – Log File Utilization

It appears that Microsoft SQL Server does not not have a lot of instrumentation around Database Log File Utilization.

There are a couple of basic premises:

  •  They are written to serially.  One can not line them up and assume that the DB Engine will write into each allocated file in parallel

Having said that, there are a couple of steps that are helpful in determine which log files are active.

Take a log backup:

Syntax

Backup Log  to disk = 'Nul';

Issuing this step will reveal which log files are being processed

Processed 0 pages for database '<db>', file 'filename' on file 1.

Processed 0 pages for database '<db>', file 'filename' on file 1.

Processed 0 pages for database '<db>', file 'filename' on file 1. 

Processed 0 pages for database '<db>', file 'filename' on file 1. 

Processed 35001 pages for database '<db>', file 'filename' on file 1. 

Processed 0 pages for database '<db>', file 'filename' on file 1. 

BACKUP LOG successfully processed ### pages in seconds 
(IIII.III MB /sec)

In the example above, the database is in full recovery mode and we simply backing up to the NUL device.

In essence, basic house-keeping. Though, we are taking a transaction log backup, we are not keeping the accompanying backup files.

Based on the accompanying results of the backup, only the fifth log file is actually active and contains log activity data that is eligible for pruning.

A more passive approach for determining usage data of Log files is to use the fileproperty  function.

The FileProperty function accepts two parameters; the symbolic file name and the property type one is inquiring about.

In our case we will be using the SpaceUsed property.  The doc says that this property will return “the amount of space used by the file”.

use <database>;

select
      db_name(DB_ID()) as databaseName
    , name
    , physical_name
    , (size / 128) as 'storageAlloc (MB)'
    , ((fileproperty(name, 'spaceUsed')) / 128)
           as 'storageInuse (MB)'
     , ( size - (FILEPROPERTY(name, 'spaceUsed'))) / 128 
            as 'storageAvail (MB)'

    from  sys.database_files tblDatabaseFile

    where  

           (

                (tblDatabaseFile.type = 1)

           )

 

References:

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s