Transaction logs larger than actual data imported

Question

Transaction logs larger than actual data imported

montril2121

SSC Enthusiast

Points: 186
More actions
August 5, 2010 at 8:29 am

#238623

Hi,
We have production server with Windows 2008 and SQL 2008. Our transaction logs are larger than the actual data being imported into the databases. This is not happening on SQL 2005 servers that use the same database.
Please reply ASAP as this is critical server.

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

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

Dave Coats SSC Eights! Points: 885 More actions · Answer 1

Sounds like you just need to do a transaction log backup which will decrease the size of the log files. Do you have a regularly scheduled transaction log backup task?

Dave Coats

montril2121 SSC Enthusiast Points: 186 More actions · Answer 2

Hi Dave,

Thanks for you prompt reply. Yes we do backup regularly and database recovery model is set to Simple.

Dave Coats SSC Eights! Points: 885 More actions · Answer 3

Oh ok....I just assumed it was set to Full since it was a Production system. Thanks for clarifying.

Dave Coats

montril2121 SSC Enthusiast Points: 186 More actions · Answer 4

Our application database's transaction logs grow more than what the actual data is imported.

Is it possible to see what occupies our transaction logs (all committed, uncommiteed, broken) through queries or some mechanism. If we can schedule for this we may probably get an idea on this.

Please let me know your thoughts on this and any other method apart from the above method.

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

satish.adlurmohan (8/5/2010)
Our application database's transaction logs grow more than what the actual data is imported.

That's quite normal. Log records have headers and other information in them about the transaction as well as the actual changed data.

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

montril2121 SSC Enthusiast Points: 186 More actions · Answer 6

montril2121

SSC Enthusiast

Points: 186

August 5, 2010 at 11:05 am

#1202968

But this is not the case in SQL 2005 server.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 7

Could be that you have more memory on the 2008 server and checkpoint is occuring less frequently and the log is growing until checkpoint occurs and clears the log.

What does the following return: SELECT log_reuse_wait_desc from sys.databases WHERE name='your2008db'

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

montril2121 SSC Enthusiast Points: 186 More actions · Answer 8

montril2121

SSC Enthusiast

Points: 186

August 5, 2010 at 8:16 pm

#1203225

Hi Paul,

Can i have your e-mail ID?

Paul Randal One Orange Chip Points: 29544 More actions · Answer 9

Please continue the thread here where everyone can benefit from the discussion.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Paul Randal One Orange Chip Points: 29544 More actions · Answer 10

Dave Coats (8/5/2010)
Sounds like you just need to do a transaction log backup which will decrease the size of the log files. Do you have a regularly scheduled transaction log backup task?

Btw - this is incorrect. A log backup will *never* decrease the size of the log file. The *only* way to do that is using a DBCC SHRINKFILE.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

eng_mgomaa Right there with Babe Points: 796 More actions · Answer 11

I had the same situation in three months ago. My DB log file began to increase and increase, I went around and around in the end i found the reason of such behaviour. I executed DBCC OpenTran and i found that there is one transaction with was running for long time, i checked this process, which began this transaction and i found that this process is hung up i killed it ,after killing process my transaction file size went down and everything go right.

montril2121 SSC Enthusiast Points: 186 More actions · Answer 12

SELECT log_reuse_wait_desc from sys.databases WHERE name='your2008db'

The above command shows NOTHING for all databases

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 13

VastSQL

SSC-Dedicated

Points: 36121

August 5, 2010 at 11:22 pm

#1203254

For both 2005 & 2008 the recovey model is Simple??

montril2121 SSC Enthusiast Points: 186 More actions · Answer 14

Yes. Both are simple.

And there are no active transactions when i executed DBCC OPENTRAN