Transaction Log Size

Question

Post reply

Transaction Log Size

msalmaan

Mr or Mrs. 500

Points: 571
More actions
December 22, 2008 at 5:23 am

#216745

Hi to all,
Basically I am Oracle DBA and new to SQL Server.
The database(SQL Server 2005) which i handle has a very huge transaction log in size. Is there any option to reduce the size of transaction log. Currently its size is about 1GB and unable to open it.

Viewing 15 posts - 1 through 15 (of 18 total)

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

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

What recovery model and how often are you doing transaction log backups?

What do you mean by "unable to open it"?

Take a read through this - 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ps. One Orange Chip Points: 29422 More actions · Answer 2

What is the frequency with which u're taking transaction log backup vis-a-vis transaction volume?

try reducing the frequency of tran log backups. this will flush out inactive transaction more frequently from the log file.

Pradeep Singh

Atul DBA Hall of Fame Points: 3610 More actions · Answer 3

Hi,

You need to check your database recovery mode in Properties window. It should be Full Recovery for best result.

You also need to check the frequescy of database Full Backup and transaction log backup schedule.

Once you have the database full backup done, you may shrink the Log file.

Don't try to open the Transaction Log file. It may get corrupt and chances of recovery will fail.

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

ps (12/22/2008)
try reducing the frequency of tran log backups. this will flush out inactive transaction more frequently from the log file.

Other way around. Reducing the frequency of the log backups (ie running them less often) will flush inactive transactions less often. Increasing the frequency of the log backups will flush inactive transactions more frequently.

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

ps. One Orange Chip Points: 29422 More actions · Answer 5

Thanks for correcting Gail :). Actually i meant what you've written. was a slip off :w00t:

Pradeep Singh

Perry Whittle SSC Guru Points: 233918 More actions · Answer 6

msalmaan (12/22/2008)
very huge transaction log in size.

msalmaan (12/22/2008)
size is about 1GB

wow, as big as that 😀

msalmaan (12/22/2008)
and unable to open it.

not advisable

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

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

Thank You to all.

I got an overview on how to manage the transaction log

msalmaan Mr or Mrs. 500 Points: 571 More actions · Answer 8

Thank You to all.

I got an overview on how to manage the transaction log

msalmaan Mr or Mrs. 500 Points: 571 More actions · Answer 9

Thank You to all.

I got an overview on how to manage the transaction log

srikanth.katta Newbie Points: 7 More actions · Answer 10

Hi,

Better you should use DBCC SHRINKDB(file_name, target size) or DBCC SHRINKFILE(file_name.log,t size )...

steveb. SSC-Forever Points: 46733 More actions · Answer 11

srikanth.katta (12/23/2008)
Hi,
Better you should use DBCC SHRINKDB(file_name, target size) or DBCC SHRINKFILE(file_name.log,t size )...

Don't do this,

Shrinking the transaction log is not a good idea especially on a production system. you should give the t-log enough room to grow, and back it up regularly.

Ells SSCrazy Eights Points: 9901 More actions · Answer 12

why not use shrink (shrink file rather than shrink db) to shrink the log files? what are the downsides?

Sorry to hijack the thread but I have started to use shrink as some of my log file (ldf) are now getting very big.

Is there an article somewhere on how to control these properly as the ones I am working with are running riot.

Thanks.

Ells

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 13

Backup log file with truncate_only option and shrink database log file.

_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

Ells SSCrazy Eights Points: 9901 More actions · Answer 14

Many thanks Paresh,

that is exactly what I have been doing, however I was very concerned that ther may be an issue with doing this. Just wondered if there was a problem with this strategy.

Thanks.

Ells