Shrink transaction log file

Sometimes the shrink transaction log file doesn't work.

Before we troubleshoot it, we need to know how the log file works:

Transaction Log Physical Architecture

http://msdn.microsoft.com/en-us/library/aa174526(v=sql.80).aspx

Here are some tools which can help us investigate the log file issues.

a. DBCC SQLPERF(logspace) : tell you how much transaction log is being used
b. DBCC LOGINFO: undocumented command which can tell you the VLFs (Virtual Log Files) status in the log file. For more infomation, please refer to http://sqlblog.com/blogs/kalen_delaney/archive/2009/12/21/exploring-the-transaction-log-structure.aspx
c. DBCC OPENTRAN: determine whether an open transaction exists within the transaction log
 

Let's create a testing db first

======================================================================

--the initial log file size is 64MB, the autogrew is enabled with 64MB:

CREATE DATABASE [testlog] ON PRIMARY
( NAME = N'testlog', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\testlog.mdf' , SIZE = 1048576KB , FILEGROWTH = 524288KB )
LOG ON

( NAME = N'testlog_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\testlog_log.ldf' , SIZE = 65536KB , FILEGROWTH = 65536KB )

GO
--create a test table
use testlog
GO

select a.* into testtable from sysobjects a, sysobjects b

GO

--Make a full backup first, it is important, or else sql will reuse the log file space

BACKUP DATABASE [testlog] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\testlog.bak' WITH NOFORMAT, NOINIT, NAME = N'testlog-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

DBCC LOGINFO

GO

=======================================================================

There are 4 VLF in the log file. Status "2" means that VLF is current active VL

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	25	2	64	0
2	16711680	16719872	0	0	0	0
2	16711680	33431552	0	0	0	0
2	16965632	50143232	0	0	0	0

let's insert some data:

=======================================================================

insert into testtable
select a.* from sysobjects a, sysobjects b,sysobjects c
GO

dbcc loginfo

GO
=======================================================================
the output of the "DBCC LOGINFO" like

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	25	2	64	0
2	16711680	16719872	26	2	64	0
2	16711680	33431552	27	2	64	0
2	16965632	50143232	0	0	0	0
2	16777216	67108864	0	0	0	27000000325600317
2	16777216	83886080	0	0	0	27000000325600317
2	16777216	100663296	0	0	0	27000000325600317
2	16777216	117440512	0	0	0	27000000325600317

 
The log file grew to 128MB now, the last active VLF is FSeqNo 27, so only 48MB(16MB x 3) log space was used. looks like the log file growth bases on the estimate of the transaction size, sometime the estimate is not accurate. ok, let's start our testing.

Create a active transaction, then insert some data:

====================================================================
--open a session and run
begin tran
insert into testtable
select a.* from sysobjects a

--open another session and run
insert into testtable
select a.* from sysobjects a, sysobjects b,sysobjects c
GO

DBCC LOGINFO

GO
====================================================================
the output of "DBCC LOGINFO" is

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	25	2	64	0
2	16711680	16719872	26	2	64	0
2	16711680	33431552	27	2	64	0
2	16965632	50143232	28	2	64	0
2	16777216	67108864	29	2	64	27000000325600317
2	16777216	83886080	0	0	0	27000000325600317
2	16777216	100663296	0	0	0	27000000325600317
2	16777216	117440512	0	0	0	27000000325600317

the last active VLF is 29 now. let's backup the log file:

BACKUP LOG [testlog] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\testlog.bak' WITH NOFORMAT, NOINIT, NAME = N'testlog-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

DBCC LOGINFO

GO

the output is

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	25	0	64	0
2	16711680	16719872	26	0	64	0
2	16711680	33431552	27	2	64	0
2	16965632	50143232	28	2	64	0
2	16777216	67108864	29	2	64	27000000325600317
2	16777216	83886080	0	0	0	27000000325600317
2	16777216	100663296	0	0	0	27000000325600317
2	16777216	117440512	0	0	0	27000000325600317

because we have a active tran open in VLF FSeqNo 27, so the bakcup log only truncate the VLF prior to FSeqNo 27. if you try to shrink the log file back to 64MB,

DBCC SHRINKFILE(2, 64)

the output is

DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
7	2	10240	8192	10240	8192

The currentsize is 80MB=10240(pages) * 8 / 1024, so the shrinkfile only remove the last 3 VLF, run

DBCC LOGINFO

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	30	2	128	0
2	16711680	16719872	26	0	64	0
2	16711680	33431552	27	2	64	0
2	16965632	50143232	28	2	64	0
2	16777216	67108864	29	2	64	27000000325600317

is correct! In order to find out which transaction is still active, we can run :

DBCC OPENTRAN

Transaction information for database 'testlog'.

Oldest active transaction:
SPID (server process ID): 53
UID (user ID) : -1
Name : user_transaction
LSN : (27:4861:1)
Start time : Jun 17 2012 12:37:02:740PM
SID : 0x0105000000000005150000005d28f57fd53ad8354354e02a75841400

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Based on the output we can get the spid is 53, the transaction type is user_transaction, we can also get what spid 53 is running by :

DBCC INPUTBUFFER (53)

the output is like

EventType	Parameters	EventInfo
Language Event	0	begin tran insert into testtable select a.* from sysobjects a

so active transaction will stop the log file shrik working. let's continoue our testing.

Commit the transaction first, and insert more data

--run in the session which you create the transaction
commit tran

--insert more data

insert into testtable
select a.* from sysobjects a, sysobjects b,sysobjects c
GO

DBCC LOGINFO

GO

ok, the log file size grew again

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	30	2	128	0
2	16711680	16719872	31	2	128	0
2	16711680	33431552	27	2	64	0
2	16965632	50143232	28	2	64	0
2	16777216	67108864	29	2	64	27000000325600317
2	16777216	83886080	32	2	64	31000000097600316
2	16777216	100663296	0	0	0	31000000097600316
2	16777216	117440512	0	0	0	31000000097600316
2	16777216	134217728	0	0	0	31000000097600316

compare with the previouse output, the original VLF FSeqNo 26 is reused(because its status was 0), the current number of it is 31, because FSeqNo 27,28,29 haven't been backed up(status is 2), so they can not be used. let's backup the log file again:

==================================================================
BACKUP LOG [testlog] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\testlog.bak' WITH NOFORMAT, NOINIT, NAME = N'testlog-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

DBCC LOGINFO

GO
==================================================================

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	30	0	128	0
2	16711680	16719872	31	0	128	0
2	16711680	33431552	27	0	64	0
2	16965632	50143232	28	0	64	0
2	16777216	67108864	29	0	64	27000000325600317
2	16777216	83886080	32	2	64	31000000097600316
2	16777216	100663296	0	0	0	31000000097600316
2	16777216	117440512	0	0	0	31000000097600316
2	16777216	134217728	0	0	0	31000000097600316

OK, this time only VLF FSeqNo 32 is active(status = 2), that's cool!, let's check the log file usage:

wow! 98% free space, only 3MB space is used. can we shrink it to initial size 64MB now? let's try

DBCC SHRINKFILE(2, 64)

GO

DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
7	2	12288	8192	12288	8192

the log file size is 96MB = 12288 * 8 / 1024.....

So DBCC SHRINKFILE can only truncate the VLF from last active VLF to the end, even if you have more free space in the log file, you can not shrink them.

if you really need to shrink the file, you need to move the active VLF to the beginning of the log file, create a dummy table and insert data into it, if the transaction is big enough to move the active VLF to the beginning of the log file, then backup the log. here is the VLF status you will get

--generate big transaction

--run the insert several times until the active VLF move to the beginning of the log file

insert into testtable

select a.* from sysobjects a, sysobjects b,sysobjects c

--backup the log file

BACKUP LOG [testlog] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\testlog.bak' WITH NOFORMAT, NOINIT, NAME = N'testlog-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

DBCC LOGINFO

GO

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	40	2	128	0
2	16711680	16719872	36	0	64	0
2	16711680	33431552	38	0	64	0
2	16965632	50143232	39	0	64	0
2	16777216	67108864	37	0	128	27000000325600317
2	16777216	83886080	32	0	64	31000000097600316

cool! the active VLF has been moved to the beginning of the log file.
let's try shrink file again

DBCC SHRINKFILE(2, 64)
GO

DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
7	2	8192	8192	8192	8192

DBCC LOGINFO

GO

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	40	2	128	0
2	16711680	16719872	36	0	64	0
2	16711680	33431552	38	0	64	0
2	16965632	50143232	39	0	64	0

well, the log file size is 64MB now :)

can I shrink the log file to the size which is smaller than initial size? Yes, you can.
let's run

DBCC SHRINKFILE(2, 32)

GO

DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
7	2	6121	6121	6120	6120

DBCC LOGINFO

GO

FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	16711680	8192	40	2	128	0
2	16711680	16719872	36	0	64	0
2	16711680	33431552	38	0	64	0

We have 3 VLF, 48MB now. However, you can not shrink the log file smaller than 2 VLF, in our case, the smallest size of log file is 16MB * 2 = 32MB.

As a summary

1. Active transaction.

we can use "DBCC OPENTRAN" find out it, and use "DBCC INPUTBUFFER" to check the query. Sometimes replication will cause active tran issue.

As I remember, once I restored a database backup which was taken when the database was under transaction replication, Log Reader agent maintains pointer in the Transaction Log for tracking the changes in the database. so the log file can not be shrink, but we can use the command below to remove it

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

this command can only be used when the database is not in replication.

 2. Active VLF is not at the beginning of the log file

backup log file first, then create a dummy table, generate a big transaction to move the active VLF to the beginning of the log file.

3. if log file keeps growing, check the sys.databases table

select log_reuse_wait_desc from sys.databases where name='dbname'

4. for the optimize VLF size, create the initial log file as 8GB, then extend to 16GB, then 24 GB and so forth,

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

too many VLF will degrade the log performance.

5. Use "DBCC SQLPERF (LOGSPACE)" and "DBCC LOGINFO" to check the log file status.

Max Server Memory

Sakthivel Chidambaram recently created a calculator which can find out the max server memory value based on the input.
http://blogs.msdn.com/b/sqlsakthi/archive/2012/05/19/cool-now-we-have-a-calculator-for-finding-out-a-max-server-memory-value.aspx

you can try the tool from http://blogs.msdn.com/b/sqlsakthi/p/max-server-memory-calculator.aspx

Then Jonathan Kehayias made a post to clarify why the calculator doesn't work
http://www.sqlskills.com/blogs/jonathan/2012/05/default.aspx
Jonathan has another great post relative the max server memory
http://www.sqlskills.com/blogs/jonathan/post/How-much-memory-does-my-SQL-Server-actually-need.aspx

This reminded me another max server memory formula which I follow up for many years posted by other MVP Glenn Berry

http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/

a little script which I used to run from central management server and configured the max server memory for all sql servers

====================================================================
DECLARE @curMem int
DECLARE @maxMem int
DECLARE @sql varchar(max)
select @curMem=physical_memory_in_bytes/1024/1024 from sys.dm_os_sys_info
SET @maxMem = CASE
    WHEN @curMem < = 1024*2 THEN 1500
    WHEN @curMem < = 1024*4 THEN 3200
    WHEN @curMem < = 1024*6 THEN 4800
    WHEN @curMem < = 1024*8 THEN 6400
    WHEN @curMem < = 1024*12 THEN 10000
    WHEN @curMem < = 1024*16 THEN 13500
    WHEN @curMem < = 1024*24 THEN 21500
    WHEN @curMem < = 1024*32 THEN 29000
    WHEN @curMem < = 1024*64 THEN 60000
    WHEN @curMem < = 1024*72 THEN 68000
    WHEN @curMem < = 1024*96 THEN 92000
    WHEN @curMem < = 1024*128 THEN 124000
     END
SET @sql='
EXEC sp_configure ''Show Advanced Options'',1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ''max server memory'','+CONVERT(VARCHAR(6), @maxMem)+';
RECONFIGURE WITH OVERRIDE;'

EXEC(@sql)
====================================================================

However, with Jonathan's post, that formula might not correct, and especially have on the big memory systems. There are 2 options which are mentioned in Jonathan's post
1. "reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. This has typically worked out well for servers that are dedicated to SQL Server. "

2. "((Total system memory) – (memory for thread stack) – (OS memory requirements ~ 2-4GB) – (memory for other applications) - (memory for multipage allocations; SQLCLR, linked servers, etc)), where the memory for thread stack = ((max worker threads) *(stack size)) and the stack size is 512KB for x86 systems, 2MB for x64 systems and 4MB for IA64 systems. The value for 'max worker threads' can be found in the max_worker_count column of sys.dm_os_sys_info "

I think I will use the first option as an initial setup, then follow up Jonathan's post to monitor the system memory status, and adjust it as needed. here is the script for the option 1

--reserve 1 GB of RAM for the OS,
--1 GB for each 4 GB of RAM installed from 4–16 GB,
--and then 1 GB for every 8 GB RAM installed above 16 GB RAM
DECLARE @curMem int
DECLARE @maxMem int
DECLARE @sql varchar(max)
select @curMem=physical_memory_in_bytes/1024/1024 from sys.dm_os_sys_info
SET @maxMem = CASE
    WHEN @curMem < = 1024*2 THEN @curMem - 512
    WHEN @curMem < = 1024*4 THEN @curMem - 1024
    WHEN @curMem < = 1024*16 THEN @curMem - 1024 - Ceiling((@curMem-4096) / (4.0*1024))*1024
    WHEN @curMem > 1024*16 THEN @curMem - 4096 - Ceiling((@curMem-1024*16) / (8.0*1024))*1024
     END
SET @sql='
EXEC sp_configure ''Show Advanced Options'',1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ''max server memory'','+CONVERT(VARCHAR(6), @maxMem)+';
RECONFIGURE WITH OVERRIDE;'

EXEC(@sql)


except the max server memory, here are some other post regarding the sql memory setting:
1. Fun with Locked Pages, AWE, Task Manager, and the Working Set…
http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

2. Be Aware: Using AWE, locked pages in memory, on 64 bit
http://blogs.msdn.com/b/slavao/archive/2005/04/29/413425.aspx

3. Q & A: Does SQL Server always respond to memory pressure?
http://blogs.msdn.com/b/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx

4. Importance of setting Max Server Memory in SQL Server and How to Set it
http://blogs.msdn.com/b/sqlsakthi/archive/2011/03/12/importance-of-setting-max-server-memory-in-sql-server-and-how-to-set-it.aspx

Setup windows firewall for SQL Server

On Windows 2008 R2, the windows firewall will turn on by default which causes connection issure for the remote client, here is a script which can turn off the firewall for all SQL Server default port. if there is a named instance, the script should be modified based on the correct setting.

==============================================================
@echo off
@echo This scripts sets the default firewall configurations for sql server components
echo.
echo Setting the core components for a database instance
echo Default Instance
netsh advfirewall firewall add rule name="SQLServer" dir=in action=allow protocol=TCP localport=1433 profile=DOMAIN
echo Dedicated Admin Connection
netsh advfirewall firewall add rule name="SQL DAC" dir=in action=allow protocol=TCP localport=1434 profile=DOMAIN
echo SQL Browser Service
netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=UDP localport=1434 profile=DOMAIN
echo Setting the core firewall rules for database mirroring, Service Broker, TSQL Debugger, Analysis services, Reporting Services
echo Mirroring EndPoint - Change Port Number as needed depending on which role
netsh advfirewall firewall add rule name="Mirroring EndPoint" dir=in action=allow protocol=TCP localport=5022 profile=DOMAIN
echo Service Broker
netsh advfirewall firewall add rule name="SQL Service Broker" dir=in action=allow protocol=TCP localport=4022 profile=DOMAIN
echo Enable TSQL Debugger (uses RPC)
netsh advfirewall firewall add rule name="T-SQL Debugger" dir=in action=allow protocol=TCP localport=135 profile=DOMAIN
echo Browser service for Analysis Service
netsh advfirewall firewall add rule name="SQL Browser for Analysis Service" dir=in action=allow protocol=TCP localport=2382 profile=DOMAIN
echo Browser service for Analysis Service
netsh advfirewall firewall add rule name="SQL Browser for Analysis Service" dir=in action=allow protocol=TCP localport=2382 profile=DOMAIN
echo Analysis Service Default
netsh advfirewall firewall add rule name="Analysis Service" dir=in action=allow protocol=TCP localport=2383 profile=DOMAIN
echo HTTP/HTTPS for the reporting services
netsh advfirewall firewall add rule name="HTTP Reporting Services" dir=in action=allow protocol=TCP localport=80 profile=DOMAIN
netsh advfirewall firewall add rule name="HTTPS Reporting Services" dir=in action=allow protocol=TCP localport=443 profile=DOMAIN

====================================================================