transaction logs full in simple recovery mode?

Question

transaction logs full in simple recovery mode?

DBA-640728

SSChampion

Points: 12898
More actions
October 14, 2010 at 8:09 am

#145643

I have a couple of databases in SQL server 2005 (64 bit) server. The recovery mode are set to simple recovery, but still from time to time, the databases become unusable for having the transaction logs full, these databases are small around 1 GB , but the transaction logs get as big as 5GBs,l i can't shrink/truncate the logs since is in simple mode, so i have to restart the server and then they work again. , any ideas why is this happening?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

in simple mode, the log would only have uncommitted transactions in it, right?

could there be a developer or some process that is starting a transaction, but never issuing the COMMIT? is anyone noticing any data loss ?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DBA-640728 SSChampion Points: 12898 More actions · Answer 2

yes, this is a biztalk database and has a lot of uncommitted transactions. So that is the reason?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Are the databases published for replication?

What is the result of this:

DBCC OPENTRAN

DBA-640728 SSChampion Points: 12898 More actions · Answer 4

Oldest active transaction:

SPID (server process ID): 210

UID (user ID) : -1

Name : DTCXact

LSN : (119410:21151:2)

Start time : Oct 12 2010 3:07:00:280PM

SID : 0x010500000000000515000000d115462ba53ab44c8c45bc2f852d4300

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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

No replication, but that's an old active transaction.

In any recovery model, the log can only be truncated to the beginning of the oldest active transaction. So if you have transactions running for multiple days (which really looks wrong) your log is going to grow.

I suggest your next course of action be to investigate why there are long running transactions and see if there's maybe a bug somewhere in whatever is using this DB.

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

DBA-640728 SSChampion Points: 12898 More actions · Answer 6

DBA-640728

SSChampion

Points: 12898

October 14, 2010 at 12:16 pm

#1237128

thanks i will check that.

Feeg SSCrazy Points: 2766 More actions · Answer 7

how often do you backup those db's?

MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics

vky3 SSC Eights! Points: 994 More actions · Answer 8

You can also check why the log is not being reused using the following query.

Select log_reuse_wait_desc from sys.databases where name = 'DBNAME'