Sql-server – How to find the VLF count for a SQL 2005 database without running dbcc loginfo

sql-server-2005

Is it possible to find the number of VLFs in a database's transaction log without running DBCC LOGINFO? DBCC LOGINFO seems a little heavy and verbose when count is the only information my t-sql script needs from it.

Best Answer

I don't believe there is another way; you could always do something like this:

   CREATE TABLE #dbcc_out 
                      (FileID varchar(3), 
                       FileSize numeric(20,0),
                       StartOffset bigint, 
                       FSeqNo bigint, 
                       Status char(1),
                       Parity varchar(4), 
                       CreateLSN numeric(25,0))

    INSERT INTO #dbcc_out                  EXEC ('DBCC LOGINFO')

    select count(*)  from #dbcc_out

Related Solutions

Sql-server – How does DBCC SHRINKFILE() affect backups

Per Microsoft Support http://support.microsoft.com/kb/272318, SQL Server shrinks the log file by removing as many VLFs as it can. It does so while trying to hit a target size without messing up your backup chain.

However, if you do use TRUNCATE_ONLY, the sequence gets messed up, so you'll need to either do that right before your scheduled backup or do full backup right after that.

Sql-server – How to find the detail cpu high process name which occupying most resource in the SQL Server database

You can look at sys.dm_exec_query_stats to find queries which are causing the highest total_worker_time.

SELECT      TOP 20
            ST.text,
            QS.*
FROM        sys.dm_exec_query_stats          QS
CROSS APPLY sys.dm_exec_sql_text(sql_handle) ST
ORDER BY    total_worker_time

Best Answer

Related Solutions

Sql-server – How does DBCC SHRINKFILE() affect backups

Sql-server – How to find the detail cpu high process name which occupying most resource in the SQL Server database

Related Question