Microsoft – SQLServer – Database – Log File Utilization

Microsoft – SQLServer – Database – Log File Utilization

To review MS SQL Server Data file organization and size the MS Windows Explorer is one of the first and most accessible tools.

Internal to SQL Server itself, the traditional command has been the “DBCC Sqlperf(logspace)” command.

The command lists each database, the total size of each database’s log files, percentile used, and status.
Various design & technical issues govern the utilization, size, growth pattern, and re-usability of database log files.  Those choices includes:

  1. Database Recovery Settings (SIMPLE, BULK LOGGED, and FULL)
  2. Database Mirroring
  3. Database Replication

Log files are a bit different than data files.  Those differences are due to:

  1. As strict, very time  conscious and related journal entries, Log files are used and accessed serially

Because of there unique qualities, occasionally it is useful to be a bit familiar with database log files current and max sizes, recovery settings, whether they are being freed, etc.

All Databases Log file size & Utilization %

    dbcc sqlperf(logspace)

Current \ Contextual Databases Log file size & Utilization %

    --replace <db-name> with the name of DB
    use <db-name>
    go

    select
              dbname(db_id()) as databaseName
            , type
            , [name] as fileNameSymbolic
            , physical_name as fileNameActual
            , ([size] / 128) as sizeInMB
            , ([max_size] / 128) as maxSizeInMB
            , (fileproperty(name, 'spaceUsed')) / 128 
                as spaceUsedInMB
            , (size - (fileproperty(name, 'spaceUsed'))) / 128 
                as availInMB

     from   sys.database_files tblDatabaseFile

     where

            (

                 (tblDatabaseFile.type = 1)
            )


Determine if any Database Operations is in the way of Log Re-use

    

    select
            name
            , log_reuse_wait
            , log_reuse_wait_desc
    from sys.databases tblDatabase

References:

  1. The Rambling DBA: Jonathan Kehayias – The random ramblings and rantings of frazzled SQL Server DBA – Getting Log Space Usage without using DBCC SQLPERF
    http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/06/06/getting-log-space-usage-without-using-dbcc-sqlperf.aspx
  2. File Property
    http://msdn.microsoft.com/en-us/library/ms188401.aspx

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