Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Reclaim Transaction Log Space Expand / Collapse
Author
Message
Posted Friday, January 11, 2008 1:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:14 AM
Points: 167, Visits: 327
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
Posted Friday, January 11, 2008 9:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:29 PM
Points: 2,278, Visits: 3,047
You have to shrink the log file

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




My blog: http://jahaines.blogspot.com
Post #441843
Posted Friday, January 11, 2008 9:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:29 PM
Points: 2,278, Visits: 3,047
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
Posted Friday, January 11, 2008 9:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:29 PM
Points: 2,278, Visits: 3,047
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
Posted Monday, January 14, 2008 2:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:14 AM
Points: 167, Visits: 327
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
Posted Monday, January 14, 2008 7:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:29 PM
Points: 2,278, Visits: 3,047
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
Posted Tuesday, January 15, 2008 7:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 9:16 AM
Points: 417, Visits: 1,143
Also, don't forget to take a full backup after you shrink the log file.
Post #442993
Posted Tuesday, January 15, 2008 8:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 6:29 PM
Points: 32,834, Visits: 14,975
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
Posted Tuesday, January 15, 2008 9:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:14 AM
Points: 167, Visits: 327
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
Posted Wednesday, January 16, 2008 3:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:36 AM
Points: 1,199, Visits: 574
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 »

Add to briefcase 1234»»»

Permissions Expand / Collapse