Reduce Physical size of Transaction Log

Question

Post reply

Reduce Physical size of Transaction Log

msalmaan

Mr or Mrs. 500

Points: 541
More actions
December 4, 2010 at 1:02 am

#229838

Hi all,
In Sql server 2008, I have a database with 2 logfiles whose initial size is 52198 MB and 253,278 MB.
How can i reduce the physical file size of these llog files?
Help Please...Thank in advance

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

A few questions first:

What recovery model?

What's the interval on log backups (if answer to recovery model != Simple)

Why two log files?

Why do you want to reduce the size of the files?

What's the max usage of the log file?

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

msalmaan Mr or Mrs. 500 Points: 541 More actions · Answer 2

Recovery Model is Full.

The two log files total size is 300 GB and i want to reduce this size.

The usage shown is 99% according to DBCC SQLPERF.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Great, now can you answer the one outstanding question - frequency of your log backups.

While you're at it, please read through this - Managing Transaction Logs[/url]

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

Brigadur SSCrazy Points: 2097 More actions · Answer 4

Hi,

Do you use backup?

Otherwise, if you use full(or bulk logged) recovery model you should setup a backup/recovery strategi.

You can begin with reading this: Backup Overview

To manage the log files you have to do regular (like once an hour) log backups. Prerequisite is at least one full backup. When there are log backups set up, you can shrink the log files. Frequency of log backups and proper log file size depends on the load on the database.

Regards

msalmaan Mr or Mrs. 500 Points: 541 More actions · Answer 5

Hi Gail,

I had taken backup of transaction log and reduced the size of transaction log through alter command.

Thanx.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Great, now make sure you have log backups scheduled regularly, or you'll be right back in the same scenario in a few weeks.

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

msalmaan Mr or Mrs. 500 Points: 541 More actions · Answer 7

msalmaan

Mr or Mrs. 500

Points: 541

December 6, 2010 at 12:30 am

#1258908

Yes Sure...........Have a Nice Day Ahead