Thursday, November 17, 2011

0 Transaction log space

  • log file always use sequentially , therefore put the multiple log file in the different disk drive would not improve performance or WRITE_LOG wait issue.
  • log file must be zero out first and can not use instant initial file .Therefore shrink log file too often is not going to be good because the log file will grow again.
  • Backup log would ONLY release the free VLF

 

How big the log file should be

As big as it needs to be. Here are some guild line:

  • Single largest transaction
  • Largest index or index rebuild

Due to the number of VLF could affect the performance, we should set up the initial log size as reasonable size and set the extend size in the fix number so each VLF would be in the difference size.

For example, set the initial size as 4GB ( 256MB per VLF) and extend another 4GB after that .

 

  • chunks less than 64MB and up to 64MB = 4 VLFs
  • chunks larger than 64MB and up to 1GB = 8 VLFs
  • chunks larger than 1GB = 16 VLFs

 

Check log space usage

To see the log file space, we can use DBCC SQLPERF. example as below

  1. DBCC SQLPERF(LOGSPACE);

image

Or you can use below query to check the space usage for single database

  1. USE AdventureWorks2008R2;
  2. SELECT df.name ,df.size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB,df.size/128.0 as CurrenSizeinMB,mf.size/128 AS InitialSizeMB
  3. FROM sys.database_files df inner join
  4. sys.master_files mf on df.name = mf.name and database_id = DB_ID()
  5. go

image

Virtual Log files

Unlink the data file, Having multiple data files on the different disk may increase the performance. SQL Server treat multiple log file as a whole. Regardless number of log file you have for a single database, the SQL server internally divide into the multiple virtual log files. We can not control the number of Virtual log files. DBCC LOGINFO can show the VLF detail.

  1. DBCC LOGINFO(AdventureWorks2008R2)

Status 2: Contain the active portion of the log

Status 0: does not contain the active portion of the log.

image

When We use the BACKUP LOG to backup the log, the default is always truncate the log. ( Backup database will also backup the backs up enough of the transaction log to produce a consistent database when the backup is restored.) This will result of the log being truncate.  After the log being truncate, we can use the DBCC LOGINFO to exam the VLF.

image

As you can see, the stratus column changes from 2 to 0. From active become inactive.  The log files size does not change as we only truncate the log not change the file size. To change the files size, we need to shrink file. Shrink file ONLY works when there is inactive VLF.

  1. DBCC SHRINKFILE(AdventureWorks2008R2_Log, TRUNCATEONLY)

Truncate only option will only remove inactive portion of the VLF in the end last active VLF. Therefore, it is expected only 6,7,8 will be removed.

image

A log file can only be shrunk to a VLF boundary, shrinking a log file to a size smaller than the size of a VLF might not be possible, even if it is not being used. The size of the VLF is chosen dynamically by the SQL Server when the log file created or extend.

Open transactions

DBCC opentran show the open transactions that are using the log space.

USE [DB];
DBCC OPENTRAN()

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates