Reclaim Transaction Log Space

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Reclaim Transaction Log Space

34 posts, Page 1 of 4

1 2 3 4 »»»

Reclaim Transaction Log Space

Rate Topic

Display Mode

Topic Options

Author

Message

Ankit Mathur-481681

Posted Friday, January 11, 2008 1:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 163, Visits: 295

Hi,

My Transcaction log is increasing manifold to upto 20GB which is almost twice the size of whole DB. This is threatening to be a possible DISK SPACE CRUNCH scenario on my system in another few months.

I wish to reclaim the precious disk space also without taking my DB offline. I tried with transaction log backup but while it does take the backup doesn't let me reclaim the disk space.

I want to know what are my options w.r.t Disk Space Reclaim.

Please help.
Ankit

Post #441601

Adam Haines

Posted Friday, January 11, 2008 9:18 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 2,278, Visits: 2,998

You have to shrink the log file

DBCC SHRINKFILE (N'LogFileLogicalName', 0, TRUNCATEONLY)
GO

My blog: http://jahaines.blogspot.com

Post #441843

Adam Haines

Posted Friday, January 11, 2008 9:20 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 2,278, Visits: 2,998

You can also do this via SSMS by right-clicking the database and going to tasks --> shrink --> files

My blog: http://jahaines.blogspot.com

Post #441846

Adam Haines

Posted Friday, January 11, 2008 9:28 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 2,278, Visits: 2,998

Note the above is only needed if you want to truncate the log and reclaim space. If not use
DBCC SHRINKFILE (N'LogFileLogicalName', 0) instead, to just shrink the log file.

My blog: http://jahaines.blogspot.com

Post #441858

Ankit Mathur-481681

Posted Monday, January 14, 2008 2:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 163, Visits: 295

Well I wish to reclaim disk space. So I believe I'll go for Enterprise Manager Option.

Can you pinpoint me to any important thing I ned to watchout for in its settings or, apprise me about any side-effects it may cause ?

Thanks for your time.
Ankit

Post #442341

Adam Haines

Posted Monday, January 14, 2008 7:20 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 2,278, Visits: 2,998

Well one thing you should watch out for is the use of the truncate command, as this will break your backup chain.

There is really no penalty for shrinking the log becuause you are just returning the log to the original file size.

My blog: http://jahaines.blogspot.com

Post #442436

rlondon

Posted Tuesday, January 15, 2008 7:30 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 03, 2013 12:41 PM
Points: 417, Visits: 1,122

Also, don't forget to take a full backup after you shrink the log file.

Post #442993

Steve Jones - SSC Editor

Posted Tuesday, January 15, 2008 8:42 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 4:42 PM
Points: 31,421, Visits: 13,733

Keep in mind that the log can be bigger than the data size. It depends on transaction volume. I could have a 1MB database with 1 table, 1 row, 1 column, and if I update this value every second, my log will be larger than the data.

I'm guessing that you don't have log backups running. You should be running transaction log backups periodically, more often than database backups. I see many people running hourly if not more often.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #443068

Ankit Mathur-481681

Posted Tuesday, January 15, 2008 9:18 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 163, Visits: 295

This is a reporting server of mine whereby in a day we feed in a lot of records & rest of the time it is used to fetch data ranging from days, weeks & months.

But considering the solutions provided to me I have recreated the log file for once & scheduled the Transaction Log Backups to 4 times a day.

I believe these measures should keep the log space in control.

Thanks everybody for your inputs. Would revert back if I continue to face problems in this respect.

Ankit

Post #443398

rajankjohn

Posted Wednesday, January 16, 2008 3:30 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, February 15, 2013 2:53 PM
Points: 1,199, Visits: 568

why do you need the transactional log backup of it is just a reporting server? You can just take a full backup just after you complete the data feed. Change the recovery model to simple.

Post #443498

« Prev Topic | Next Topic »

34 posts, Page 1 of 4

1 2 3 4 »»»

Permissions