How to truncate log file in SQL Server 2005

Question

How to truncate log file in SQL Server 2005

liewsb

SSC-Addicted

Points: 482
More actions
October 6, 2010 at 2:28 am

#230076

Hi,
I have a database with ldf file size more than 300GB and my datadase size is about 70 gb. Can someone show me how can I shink down the size of these two file.
Thanks.
Regards,

Viewing 15 posts - 1 through 15 (of 44 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Take a look through this article - http://www.sqlservercentral.com/articles/64582/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy4sql SSCoach Points: 19590 More actions · Answer 2

using SSMS-- right click on database--tasks--shrink--files--select the file type as log and shrink.

Make sure you take the fresh full backup after this activity, if you taking regular log backup.

----------
Ashish

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

crazy4sql (10/6/2010)
using SSMS-- right click on database--tasks--shrink--files--select the file type as log and shrink.

Won't help if the log is full.

Make sure you take the fresh full backup after this activity, if you taking regular log backup.

Why?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy4sql SSCoach Points: 19590 More actions · Answer 4

Won't help if the log is full.

But will help if its not.

Why?

If the log chain is broken after shrinking the log.

Have a look of

http://support.microsoft.com/kb/873235

saying

Important After you manually truncate the transaction log files, you must create a full database backup before you create a transaction log backup.

----------
Ashish

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

crazy4sql (10/6/2010)
If the log chain is broken after shrinking the log.

No it is not.

Have a look of
http://support.microsoft.com/kb/873235
saying
Important After you manually truncate the transaction log files, you must create a full database backup before you create a transaction log backup.

That kb article, and the quote you took from it refer to truncating the log. Not shrinking the log. completely different operations, totally different behaviour.

Truncate log:

Backup Log ... With Truncate_only (or switch to simple recovery and run Checkpoint). Marks the inactive portions of the log as reusable and discards log records in them. Does not change the size of the file.

Shrink log:

DBCC ShrinkFile. Releases unused (or reusable) portions of the file to the OS. Reduces the file size. Does not discard log records or break the log chain.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

liewsb SSC-Addicted Points: 482 More actions · Answer 6

Hi Ashish,

Sorry to ask that should I choose the file or Database? I am new to SQL 2005 and just takeover the this new job. And only recover that I have a so huge log file....

We have a schedule job to do a full backup on the database, but not these two file, i.e. MDF and LDF files. How to create a transaction log backup??

Is the following SQL command applicable to shrinking log file in SQL 2005 server:

USE DatabaseName

GO

DBCC SHRINKFILE(<TransactionLogName>, 1)

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<TransactionLogName>, 1)

GO

Please guide me on how to shink this two huge files. Is there a procedure of doing this? I really need help on this... Thanks.....

Regards

crazy4sql (10/6/2010)
Won't help if the log is full.
But will help if its not.
Why?
If the log chain is broken after shrinking the log.
Have a look of
http://support.microsoft.com/kb/873235
saying
Important After you manually truncate the transaction log files, you must create a full database backup before you create a transaction log backup.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

liewsb (10/6/2010)
Sorry to ask that should I choose the file or Database?

Neither initially.

We have a schedule job to do a full backup on the database, but not these two file, i.e. MDF and LDF files. How to create a transaction log backup??

That will explain why your log file is so large. You're in full recovery and running no log backups. In that scenario, the log will grow until it fills the drive.

Is the following SQL command applicable to shrinking log file in SQL 2005 server:
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Please do NOT run that at this point.

Please read and understand the article I linked to, and then make a decision as to what you want to do - start log backups or switch to simple recovery.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

liewsb SSC-Addicted Points: 482 More actions · Answer 8

Thanks. WIll read the article and...

GilaMonster (10/6/2010)
liewsb (10/6/2010)
Sorry to ask that should I choose the file or Database?
Neither initially.
We have a schedule job to do a full backup on the database, but not these two file, i.e. MDF and LDF files. How to create a transaction log backup??
That will explain why your log file is so large. You're in full recovery and running no log backups. In that scenario, the log will grow until it fills the drive.
Is the following SQL command applicable to shrinking log file in SQL 2005 server:
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Please do NOT run that at this point.
Please read and understand the article I linked to, and then make a decision as to what you want to do - start log backups or switch to simple recovery.

crazy4sql SSCoach Points: 19590 More actions · Answer 9

GilaMonster (10/6/2010)
liewsb (10/6/2010)
Sorry to ask that should I choose the file or Database?
Neither initially.

Gail, Why cant he shrink the file(log)?

----------
Ashish

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

crazy4sql (10/6/2010)
GilaMonster (10/6/2010)
liewsb (10/6/2010)
Sorry to ask that should I choose the file or Database?
Neither initially.
Gail, Why cant he shrink the file(log)?

Because if this is a case of full recovery, no log backups, that log will be full and shrinking it will not help. Even if it does help, it is not the right thing to do initially.

It's taking action without understanding what is wrong and what is the correct long-term solution to the problem. It is a knee-jerk reaction without due investigation. It is incorrectly giving the impression, to a new DBA, that the solution to a large log file is to shrink it. It is not.

The solution to a large log file is to investigate why it is large, to resolve whatever is causing it to be large (if it's not just a case of expected database activity) and then, if necessary, to shrink the log to a reasonable size.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 11

Definitely follow Gail's advice. She's rescued more databases than the Humane Society has rescued pets.

FYI: If you're not doing transaction log backups and you're in FULL or Bulk-Logged recovery model, start doing the transaction log backups.

There's no excuse not to and it'll help keep the log from growing even larger. Especially if someone adds disk space to the server or alters the .ldf files Max Size to something larger.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 12

liewsb (10/6/2010)
We have a schedule job to do a full backup on the database, but not these two file, i.e. MDF and LDF files. How to create a transaction log backup??

I do have to ask this. You have full database backups scheduled, but it's not backing up the MDF file?

How do you know it's not backing up the MDF file? What software is backing up the database?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

steveb. SSC-Forever Points: 46733 More actions · Answer 13

Yes the amount of advice that is given to Shrink a transaction log is bit worrying, do t-log backups and manage the size of the log correctly. Shrinking on a regular basis should never really be considered.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Brandie Tarvin (10/6/2010)
I do have to ask this. You have full database backups scheduled, but it's not backing up the MDF file?
How do you know it's not backing up the MDF file? What software is backing up the database?

Note that he is a new DBA. Probably worried about not having file-level backups of the files, or not understanding what a database backup is.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass