Transaction logs have stopped on all SS2005 DB's

Question

Post reply

Transaction logs have stopped on all SS2005 DB's

john.doss

SSC Rookie

Points: 40
More actions
November 3, 2010 at 10:50 am

#388163

Greetings,
A few days ago, I encountered a situation where the server where my SS2005 Standard Edition resides began using-up over 13GB of Physical Memory when I only had 8GB to begin-with. In Task Manager, I sorted the processes by memory size and sqlserver.exe was the most with almost 11GB. I stopped all SQL Server services and restarted the server. Everything went back to normal, sqlserver.exe only taking-up 3GB. The problem now is that none of my Transaction Log files are updating. Meaning that they're not increasing in size and/or date. It is still the same size and date as the day when all of this happened. What do I need to do to fix this.
Thank you in advance.
John

Viewing 13 posts - 1 through 12 (of 12 total)

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

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

This isn't a problem. Your log will only grow if it needs to - not growing is a good thing.

See this article I wrote for TechNet Magazine: Understanding Logging and Recovery in SQL Server

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

GSquared SSC Guru Points: 260824 More actions · Answer 2

Are you expecting a certain amount of growth in them per day? That's kind of unusual, if so.

Have you taken a look at setting the maximum RAM available for SQL Server? You can do so through the properties of the instance.

But the main thing is, what process caused SQL Server to take over all the RAM in the first place, and grew your transaction logs to the size they are? What was the server doing at that time?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

john.doss SSC Rookie Points: 40 More actions · Answer 3

Whatever it was didn't grow my Transaction Log files. My concern was that they havent changed since it happened although there have been many database entries made since then. Like Sales Orders, Purchase Orders, Inventory, etc going in on a constant basis.

john.doss SSC Rookie Points: 40 More actions · Answer 4

Thanks Paul. 🙂

So, even though there are entries being made in the database from an application, my transaction log shouldn't grow proportionately??

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

The log is usually circular in nature so overwrites itself and doesn't need to grow. Checkout the article for full details.

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

john.doss SSC Rookie Points: 40 More actions · Answer 6

Paul,

Ok, so then shouldn't, at the least, the Transaction Log file (..._Log.ldf) date change?

GSquared SSC Guru Points: 260824 More actions · Answer 7

Not necessarily. What you're seeing in the file date is what's recorded in the Windows file system meta data. That doesn't necessarily get updated on these. Can you imaging what Windows would have to do to keep that up-to-date on a data file and log file for a database with a few thousand transactions per second? There are reasons it doesn't go through all that unnecessary overhead.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

john.doss SSC Rookie Points: 40 More actions · Answer 8

ok, whew (wiping sweat from my brow). No disrespect to you please, but Paul do you concur with what he's saying??

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

Indeed. The log file metadata will only be updated when it grows or shrinks - possibly when the file header page at the front is updated after a backup, log backup, or checkpoint (only in SIMPLE).

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

john.doss SSC Rookie Points: 40 More actions · Answer 10

Great,

Thanks guys. Paul & GSquared.

You guys rock.

John

GSquared SSC Guru Points: 260824 More actions · Answer 11

You're welcome.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

NP - here to help.

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