Best Practice on limit transaction log file over grow

http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/

 

UPDATE : Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008 .

Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.

USE DatabaseName
GO
DBCC SHRINKFILE (< TransactionLogName >, 1 )
BACKUP LOG < DatabaseName > WITH TRUNCATE_ONLY
DBCC SHRINKFILE (< TransactionLogName >, 1 )
GO

[Update: Please note, there are much more to this subject, read my more recent blogs. This breaks the chain of the logs and in future you will not be able to restore point in time. If you have followed this advise, you are recommended to take full back up right after above query.]

UPDATE : Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008 .

 

 

Note: Please read the complete post before taking any actions.

This blog post would discuss SHRINKFILE and TRUNCATE Log File. The script mentioned in the email received from reader contains the following questionable code:

“Hi Pinal,

If you could remember, I and my manager met you at TechEd in Bangalore.

We just upgraded to SQL Server 2008. One of our jobs failed as it was using the following code.

The error was:

Msg 155, Level 15, State 1, Line 1
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

The code was:

DBCC SHRINKFILE ( TestDBLog , 1 )
BACKUP LOG TestDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( TestDBLog , 1 )
GO

I have modified that code to subsequent code and it works fine. But, are there other suggestions you have at the moment?

USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE ( TestDbLog , 1 )
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO

Configuration of our server and system is as follows:

[Removed not relevant data]“

An email like this that suddenly pops out in early morning is alarming email. Because I am a dead, busy mind, so I had only one min to reply. I wrote down quickly the following note. (As I said, it was a single-minute email so it is not completely accurate). Here is that quick email shared with all of you.

“Hi Mr. DBA [removed the name]

Thanks for your email. I suggest you stop this practice. There are many issues included here, but I would list two major issues:

1) From the setting database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.

2) Shrinking database file or database adds fragmentation.

There are a lot of things you can do. First, start taking proper log backup using following command instead of truncating them and losing them frequently.

BACKUP LOG [TestDb] TO  DISK = N'C:/Backup/TestDb.bak'
GO

note: from envykok

!!!important : this statment will not shrink the size, but it will clean the used space, make more free space

1)if happen 'BACKUP LOG cannot be performed because there is no current database backup' issue

Reason: BACKUP LOG cannot be performed because there is no current database backup

This error occurs when the database recovery model has been changed to Simple and/or the recovery model has been changed back to Full but a full backup has not been performed since the change. A full backup must be performed before restarting the backup job.

 

2) How to find out usage of log file ?

http://surf11.com/entry/174/sql-server-transaction-log-space-usage

Need to find out the amount of free space in your transaction log file on Microsoft SQL Server? Run the following command:

DBCC SQLPERF ( LOGSPACE )





















It will tell you the percentage of transaction log space used, and



the size of the transaction log file, which you then use to calculate



the amount of free space in the transaction log or the amount of space



in use.

 

The Result of the DBCC SQLPERF (LOGSPACE) query is the following columns Database Name, Log Size (MB), Log Space Used, Status . If there is a lot of FREE space in the transaction log, and you the file is too big you can shrink the size of the transaction log by running:

DBCC SHRINKFILE ( DBFILENAME_Log






)

Remove the code of SHRINKING the file. If you are taking proper log backups, your log file usually (again usually, special cases are excluded) do not grow very big.

There are so many things to add here, but you can call me on my [phone number]. Before you call me, I suggest for accuracy you read Paul Randel ‘s two posts here and here and Brent Ozar ‘s Post here .

Kind Regards,
Pinal Dave”

I guess this post is very much clear to you. Please leave your comments here. As mentioned, this is a very huge subject; I have just touched a tip of the ice-berg and have tried to point to authentic knowledge.

Update: Small typo correction and small detail corrected based on feedback.