SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reclaim Transaction Log Space


Reclaim Transaction Log Space

Author
Message
Ankit Mathur-481681
Ankit Mathur-481681
SSC Eights!
SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)

Group: General Forum Members
Points: 809 Visits: 363
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
Adam Haines
Adam Haines
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7874 Visits: 3135
You have to shrink the log file

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



My blog: http://jahaines.blogspot.com
Adam Haines
Adam Haines
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7874 Visits: 3135
You can also do this via SSMS by right-clicking the database and going to tasks --> shrink --> files



My blog: http://jahaines.blogspot.com
Adam Haines
Adam Haines
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7874 Visits: 3135
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
Ankit Mathur-481681
Ankit Mathur-481681
SSC Eights!
SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)

Group: General Forum Members
Points: 809 Visits: 363
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
Adam Haines
Adam Haines
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7874 Visits: 3135
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
rlondon
rlondon
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1679 Visits: 1160
Also, don't forget to take a full backup after you shrink the log file.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)

Group: Administrators
Points: 183310 Visits: 19498
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
My Blog: www.voiceofthedba.com
Ankit Mathur-481681
Ankit Mathur-481681
SSC Eights!
SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)SSC Eights! (809 reputation)

Group: General Forum Members
Points: 809 Visits: 363
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
rajankjohn
rajankjohn
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2291 Visits: 575
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search