Transaction Log

ta5

Hello
Maybe a stupid question :? , but anyway:
How can I make sure the transaction log does not grow too much?

Background: We have a navision db, SQL Server 2005, log type=full, the db and the log are backed up daily by a maintenance plan. In Mgt-Studio 2005, "DB Properties", "General", "Backup ,"Last Log File Backup" it shows the date of the last backup which is correctly. The Backups of the logs are in the backup directory which is correct. I know that the size of the log file can be limited by properties of the db.

If I understand it right, the backup of the logfile does not shrink the file, but clears the inactive part of the log. However, the log is quite big now, about 4GB, the db itself is about 1.25GB. Could there be something else wrong? Otherwise I'll check in the next time wheter the log is still growing or not.

Thanks in advance
Thomas

bbrown

To start with 4 GB is not a large log file.

To keep a log file from growing, schedule the transaction log backups more often.

DenSter

Everyone that works with SQL Server should know about Books Online. This is the help system for SQL Server, and shows a LOT of information from the local help system, online resources, and even community contributions, so it always contains recent information. You will find a bunch of articles that explain exactly how the transaction log works and how to manage the size.

Go to the Start menu, and browse to the SQL Server group. For SQL Server 2000 it's right there, for 2005 it is in the documentation submenu, for 2008 I'm not sure (that VPC is not running at the moment)

garak

How looks you backup maintenence plan(s)?
When do you make a full backup and when (how often) you make a Translog Backup?
Also as Daniel said, read also the books online (F1). Also a book (paper form) is a good christmast present.

Regards

stryk

ta5 wrote:

Hello
Maybe a stupid question :? , but anyway:
How can I make sure the transaction log does not grow too much?
...
Could there be something else wrong?

Firstly: there are NO stupid questions, only stupid answers.

The growth/usuage of the TLog depends on the number of transactions you processed, the Recovery Model and the frequency of (TLog-)backups.
You can control the physical file-size of the TLog by adjusting the "Auto Growth" of that file, maybe to 100MB or something (instead of default 10%).

Several maintenance tasks - like REBUILDING or REORGANIZING INDEXES - will cause heavy load on the TLog; it is not unusual that after re-indexing the TLog size is larger than the database itself.

If you want to shrink the physical file size you should look into the DBCC SHRINKFILE command.

Merry Christmas ,
Jörg

kriki

You can also take a look at my blog with some tips&tricks: http://mibuso.com/blogs/kriki/2008/12/23/some-tips-tricks-for-the-transaction-log/.

ta5

Thanks to all your answers.

Is there a possiblitiy to see how much of the physical log file is active? If I get it rigth the active part must be almost zero % right after the backup of the log assuming there are no other transactions.

Thomas

kriki

In the Windows performance monitor, you can add counter SQLSERVER:Databases => Percent Log used.

stryk

... or you could execute this TSQL in "Management Studio"

DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS

Or maybe a little bit more "sophisticated":

CREATE TABLE #LogSpace_tmp
( 
	[Database Name] sysname, 
	[Log Size (MB)] decimal(18,2), 
	[Log Used (%)] decimal(18,2), 
	[Status] int
) 
INSERT INTO #LogSpace_tmp EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS')
DELETE FROM #LogSpace_tmp WHERE [Database Name] <> db_name()  -- this filters on the current database
SELECT *, CONVERT(decimal(18,2), [Log Size (MB)] * [Log Used (%)] / 100) AS [Log Used (MB)] FROM #LogSpace_tmp
DROP TABLE #LogSpace_tmp

I use this piece of code in several jobs to determine the TLog "impact", e.g. when re-indexing, so I could determine more precisely which would be the ideal size. Generally "Auto Growth" of the TLog should be avoided, as this would mean more "administartion effort" of the "Virtual Logs" - the more VLogs you have, the more performance could be degraded (see "Books Omline" about details).

DenSter

In SQL Server 2000: use the taskpad view, that will give you a nice graph to show you how much each file is filled up

In SQL Server 2005 this is the 'Disk Usage' reports under standard reports for each database, and gives you an even nicer graph.