November 3, 2008 at 2:48 pm
After backing up the transaction log (with truncate log option ticked by default) in SQL 2005,
the log file size is still the same, only once you shrink the transaction log is its size reduced.
Why is the size not reduced after backup with truncate log ?
Regards
Kevin
November 3, 2008 at 2:52 pm
After taking the backup of transaction log, all transactions are backed up and log file get empty but size would not reduce.
That you have to do using this command.
use database
go
DBCC SHRINKFILE ('file_name', size)
go
SQL DBA.
November 3, 2008 at 2:59 pm
Yes...truncate frees up the logfile.But shrink command releases that free space to OS.
November 3, 2008 at 3:00 pm
What you did only truncates the virtual log file, a shrink would then reduce the physical size.
From BOL: "Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size."
_____________
Donn Policarpio
November 3, 2008 at 7:25 pm
All been said is true, if you want to automate this process of shrinking logfile soon after SQL Server takes SQL Server log file then do this,
Create a job, with
step1 : backup log databasename to disk = 'Path/Location'
Step2 : dbcc shrinkfile (logfilename, 1)
As soon as your log file is taken backup, step 2 shrinks log file.
Hope this helps.
IM
November 3, 2008 at 9:28 pm
And with all that having been said, you DON'T want to be shrinking the transaction log after every backup. You just force SQL Server to make it grow again when it needs additional space. You should set the size of the transaction log large enough to handle the transaction load on the database between transaction log backups, plus some additional space for slightly higher than expected loads.
The only time you should consider shrinking the transaction log is after a highly unusual level of activity that has caused you transaction log to grow much more than normal.
November 4, 2008 at 12:02 am
One of our database writes huge logs as as we have created a Transactional Replication on that. I was trying to automate the process of truncating the logs as it is around 25GB in one night. We are using SQL Server 2000 databases.How should I go about it?
Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly
November 4, 2008 at 12:11 am
try this
1) dbcc opentran(dbname) - this will show the oldest open transaction
2) Kill this process
3) backup log dbname with no_log
4) use dbname
5) dbcc shrinkfile(dbname_log,100) - shrinks the log back to 100mb
November 4, 2008 at 12:36 am
terryj30 (11/4/2008)
try this1) dbcc opentran(dbname) - this will show the oldest open transaction
2) Kill this process
3) backup log dbname with no_log
4) use dbname
5) dbcc shrinkfile(dbname_log,100) - shrinks the log back to 100mb
Then backup the entire database because you have broken the recovery chain and you will not be able to do point-in-time restores to any point after the log truncation.
See - 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
November 4, 2008 at 4:20 am
The physical size does not reduces when u backup the log. The inactive portion of the log gets truncated and the freed space is available for use to the same log file.
If you want to reduce the physical size of the log file, use DBCC SHRINKFILE command. this will free up the physical space and release it to the OS for use.
HTH
Sanjay.
November 4, 2008 at 4:27 am
sudeepta.ganguly (11/4/2008)
One of our database writes huge logs as as we have created a Transactional Replication on that. I was trying to automate the process of truncating the logs as it is around 25GB in one night. We are using SQL Server 2000 databases.How should I go about it?
Hi Sudeepta,
You can create a sql job which does the job of truncating the log (either by backup or simply truncate) and schedule it to run on a daily basis at the time suitable to you. You can also create a maintenance plan for the same.
HTH,
Sanjay.
November 4, 2008 at 7:06 am
sudeepta.ganguly (11/4/2008)
One of our database writes huge logs as as we have created a Transactional Replication on that. I was trying to automate the process of truncating the logs as it is around 25GB in one night. We are using SQL Server 2000 databases.How should I go about it?
If it is getting that big, then it needs to be that big. Shrinking the transaction log file will only cause SQL to grow it again when it needs space. I'd increase the frequency of your transaction log backups before I'd consider shrinking the physical size of the transaction log.
November 4, 2008 at 7:09 am
Sanjay Rohra (11/4/2008)
sudeepta.ganguly (11/4/2008)
One of our database writes huge logs as as we have created a Transactional Replication on that. I was trying to automate the process of truncating the logs as it is around 25GB in one night. We are using SQL Server 2000 databases.How should I go about it?Hi Sudeepta,
You can create a sql job which does the job of truncating the log (either by backup or simply truncate) and schedule it to run on a daily basis at the time suitable to you. You can also create a maintenance plan for the same.
HTH,
Sanjay.
Don't simply truncate the transaction log. You will break your log chain, and won't be able to do a restore to a point in time. Also, you won't be able to perform any more transaction log backups until you have completed another full backup (possibly just a differential, but not too sure on that one).
November 4, 2008 at 7:23 am
Lynn Pettis (11/4/2008)
Sanjay Rohra (11/4/2008)
sudeepta.ganguly (11/4/2008)
One of our database writes huge logs as as we have created a Transactional Replication on that. I was trying to automate the process of truncating the logs as it is around 25GB in one night. We are using SQL Server 2000 databases.How should I go about it?Hi Sudeepta,
You can create a sql job which does the job of truncating the log (either by backup or simply truncate) and schedule it to run on a daily basis at the time suitable to you. You can also create a maintenance plan for the same.
HTH,
Sanjay.
Don't simply truncate the transaction log. You will break your log chain, and won't be able to do a restore to a point in time. Also, you won't be able to perform any more transaction log backups until you have completed another full backup (possibly just a differential, but not too sure on that one).
You'll have to start over with a full backup.
_____________
Donn Policarpio
November 4, 2008 at 7:28 am
Lynn Pettis (11/4/2008)
Don't simply truncate the transaction log. You will break your log chain, and won't be able to do a restore to a point in time. Also, you won't be able to perform any more transaction log backups until you have completed another full backup (possibly just a differential, but not too sure on that one).
Diff is sufficient. It just has to be something to base the log chain off. Both full and diff do that. If diff didn't it wouldn't be possible to do the full backup restore, diff backup restore and then the tran logs from there.
alter database testing set recovery full
backup database testing to disk = 'D:\DEvelop\testing.bak' with init
backup log testing with truncate_only
backup log testing to disk = 'D:\Develop\testing.trn' with init
/*Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.*/
backup database testing to disk = 'D:\DEvelop\testing_diff.bak' with DIFFERENTIAL
backup log testing to disk = 'D:\Develop\testing.trn' with init
-- successful
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
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply