Can't Truncate Log Because of DB Error?

Question

Can't Truncate Log Because of DB Error?

Dietmar Weickert

SSCrazy

Points: 2398
More actions
December 31, 2009 at 6:14 am

#228864

Hi all you gurus out there,
I have a problem I cannot find an answer for. We have a database for a mission critical system running in a 24*7 environment. Meanwhile the log file has grown so much that we are running into severe disk space problems.
The DB runs in the full recovery model, and we are taking log backups every 15 minutes. During the daily maintenance both the DB and the log file should be shrunk which does not happen. Meanwhile the log is app. 9 GB large and continues to grow at a slow but steady rate.
I have tried to analyze as much as possible and finally queried sys.databases with the following result:
namecompatibility_levelrecovery_model_descis_broker_enabledlog_reuse_wait_desc
RZB90FULL1REPLICATION
The DB, however, is not marked for replication, actually not a single DB on this server ever was.
Running sp_replcounters does not deliver a single record. (This does not surprise me, as no DB is marked for replication.) sp_replcmds tells me it cannot perform as the database has not been published - which is true.
Has anyone an idea what I could do to reduce log size?
Many thanks in advance and my best wishes for a happy, healthy, and successful New Year to all of you!
Best regards,
Dietmar Weickert.

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

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

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 1

Hi Dietmar

First thing that immediately I can think of is "Open transactions". If at all a big transaction is going on and not yet comitted or rolled back, then the log file will keep on increasing in size and would not be truncated by the Log Backup command. Could you please find out if there is any long running transaction going on.

Thanks

Satish More

P.S. Wish you all a very happy new year 🙂

Dietmar Weickert SSCrazy Points: 2398 More actions · Answer 2

Hi Satish,

Thank you for your prompt reply. Actually I can rule out open transactions as I have restarted all services. This should roll back and close all eventually open transactions.

Best regards,
Dietmar Weickert.

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 3

Secondly I would also suggest you to check in case any disaster recovery method like mirroring is configured. In case mirroring gets suspended, logs would not truncate.

Dietmar Weickert SSCrazy Points: 2398 More actions · Answer 4

Hi again,

No, neither replication nor mirroring is configured for anything on this server. If I look into the mirroring properties of the DB, it states "Diese Datenbank wurde nicht für die Spiegelung konfiguriert.", which means "This database has not been configured for mirroring." in English.

Best regards,
Dietmar Weickert.

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 5

I hope this link turns out to be useful:

http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/34ab68ad-706d-43c4-8def-38c09e3bfc3b

Please do let me know

Thanks

Satish More

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 6

Hey Dietmar

We had a similar issue on board earlier and Gail had a solution for it.

http://www.sqlservercentral.com/Forums/Topic695034-357-1.aspx

Do check this also out.

Thanks

Satish More

Dietmar Weickert SSCrazy Points: 2398 More actions · Answer 7

Thank you Satish,

Although it does not help me per se, I will try this forum as well. Maybe I can get some solution from there.

Best regards,
Dietmar Weickert.

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

I so wish I knew why this happened....

Solution's simple, though far from obvious.

Run DBCC OpenTran. There'll likely be a reference to replicated and unreplicated LSNs. The replicated will probably be 0

Create a transactional replication publication

Add one table as an article (irrelivent which table. Use a small one)

Once the publication has been created, run sp_repldone

Drop the publication you just created

Check the log_reuse_wait_desc column in sys.databases. It should no longer say 'replication'

Check DBCC OpenTran. There should no longer be a reference to replicated and unreplicated LSNs

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

Dietmar Weickert SSCrazy Points: 2398 More actions · Answer 9

Gail,

That did it. The DB log does not hang any longer.

Thanks to you and Satish for your quick and effective help.

Best regards,
Dietmar Weickert.