Error 9002 transaction log full (SIMPLE mode) - but it's not.

Question

Error 9002 transaction log full (SIMPLE mode) - but it's not.

satter

Old Hand

Points: 383
More actions
December 4, 2014 at 8:30 am

#313526

Good morning,
I'm in charge of a SQL Server, and unfortunately I don't have a ton of experience with SQL, and have had to learn as I go. I know that's not ideal, but it's my reality. I'm hoping someone here can help, as I haven't been able to find my exact situation via Google so far.
I've got a database on our SQL 2008 server that is throwing a 9002 "Transaction Log full" error for basically any task I try to perform against it. It uses SIMPLE recovery mode.
The log file is only 504kb in size, and it's properties are set to auto-grow, with no hard limits set. The drive it's located on has 80 GB+ of free space.
Any thoughts on what I need to do to get past this error would be greatly appreciated. This error is causing issues with the application that uses the DB.
Thanks,
Ryan

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

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

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

Can you post the exact error you're getting?

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

satter Old Hand Points: 383 More actions · Answer 2

Sure thing. Sorry, should have included that initially.

An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.ConnectionInfo)

The transaction log for database 'dbname_db' is full. To find out why the space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9002)

Also, when I checked that column in sys.databases, it shows "ACTIVE_TRANSACTION" for the description.

Thanks!

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

So you have an open transaction that's preventing log reuse. Could be that the autogrow increments are too small and the log isn't growing fast enough. Could be that the autogrow increments are too large taking too long or timing out.

Manually grow the log file a little and track down that active transaction and figure out why it's still an active transaction (someone forgot a commit perhaps)

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

satter Old Hand Points: 383 More actions · Answer 4

When I try to manually grow that log file, I'm getting the following error:

MODIFY FILE encountered operating system error 1392 (The file or directory is corrupted and unreadable.) while attempting to expand the physical file 'D:\LOGS\dbname_db_1.LDF' (Microsoft SQL error, Error: 5149)

Any thoughts on that? I'm not liking the sound of it.

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

Well that would explain why the log isn't growing.

Looks like problems with drive or file system. Got a system admin there?

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

satter Old Hand Points: 383 More actions · Answer 6

Talking with them now. Going to try restoring a t-log from a backup first and see if that helps any.

Thanks for the help.

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

NO!

If you mean a file backup of the log, that will trash your database. DO NOT ever restore database files from a file backup unless you know exactly what you're doing (and even then don't.)

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

Perry Whittle SSC Guru Points: 233921 More actions · Answer 8

satter (12/4/2014)
Talking with them now. Going to try restoring a t-log from a backup first and see if that helps any.
Thanks for the help.

temporarily add a new t-log file on another drive and track the active trans. The log can be removed afterwards

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

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

satter Old Hand Points: 383 More actions · Answer 9

GilaMonster (12/4/2014)
NO!
If you mean a file backup of the log, that will trash your database. DO NOT ever restore database files from a file backup unless you know exactly what you're doing (and even then don't.)

Sorry, I wasn't very clear in my statement. We actually tried restoring the entire database and transaction log from a backup. In talking more with the department that utilizes this database, I found that as far as end users are concerned, it's basically a read-only database for a front end web application. The data gets loaded nightly, fresh, from flat-files generated from an application that runs on an Alpha server. The users write no data to the database via the app, so it wasn't a problem to just try going back a ways and restoring the whole unit, to see if it resolved the issue.

That said, still getting that same error from my prior reply, so still working with it some more.