transactional log remedy

Question

transactional log remedy

fawwad

SSC Eights!

Points: 965
More actions
September 6, 2010 at 10:02 am

#387735

HI all,
I have some doubts and misconceptions, please correct me.
Senario :
i have database running in Full Recovery Model
i take full backup on daily basis and transactional log backup every two hours
now please tell me when we take Transactional log backup , DOES IT TRUNCATE LOG automatically ???
or do i have to Truncate transactional log by myself ???
using dbcc shrinkfile ('db_logs',100) with no_infomsgs
please clear my concept...
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

No, it does not reduce the size of the file, just makes the space available to be written to again. But unless it was an unusual operation that caused it to grow a lot more than the normal amount in a 2 hour period, why would you want to shrink it?

You still need to have the space available for the next time it grows, but now it has to physically grow the file again, costing more I/O and potentially fragmenting across the disk.

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

Truncate and shrink are two different concepts.

Truncating the log makes space within available for reuse. Log backups do this.

Shrink releases unused space to the OS. Backup log does not do this and it is not recommended that this be done on a regular basis.

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

fawwad SSC Eights! Points: 965 More actions · Answer 3

so, it means if i do Transactional log backup ONLY , it will truncate logs, am i right ???

and there is no need to run dbcc shrinkfile

i read in many article shrink causes Index fragmentation too. so, i want to avoid Shrink

so, please tell me is it a good practice to only take log backup and dont shrink log files ??

unless it is necessary

HowardW One Orange Chip Points: 29109 More actions · Answer 4

Correct.

Have a look at the output of:

DBCC SQLPERF(logspace)

Before and after a log backup