Huge SQL Server 2005 Express transaction log

Question

question

biloxibay asked Dec 5, '13

Huge SQL Server 2005 Express transaction log

I have SQL Server 2005 Express 9.0.2047, and database Recovery Model is Simple. The database is 3.5GB and the Transaction Log is 14.2GB. I ran BACKUP LOG 'mylogfile' WITH_TRUNCATE_ONLY. This ran successfully but the transaction log was still 14.2GB. I ran SQLPERF(LOGSPACE) and found the transaction log is 90% full. I ran log_reuse_wait_desc and get 5 entries: 1 NOTHING 2 ACTIVE_TRANSACTION 3 NOTHING 4 NOTHING 5 REPLICATION I ran DBCC OpenTran and get: Replicated Transaction Information: Oldest distributed LSN : (0:0:0) Oldest non-distributed LSN : (16826:25:1) I ran: EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,@time = 0, @reset = 1 and get: Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication. There never was replication - this is 2005 Express. Can you suggest what steps I can take?

replication transaction-log log-backup

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2013-12-05T21:39:34Z

KenJ answered Dec 5, '13

try sp_removedbreplication - http://technet.microsoft.com/en-us/library/ms188734.aspx "*Note This procedure should be used only if other methods of removing replication objects have failed.*" sometimes, as a last resort, this will get you out of mystery replication trouble

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

biloxibay commented · Dec 06, 2013 at 05:53 AM

Thanks you for taking the time to respond. After reading your suggestion this is what I did... I ran 'sp_HELPDB" The result was: DB_SIZE = 10,192 MB I checked Database Properties Files Page and found: Data File = 4095 MB Log File = 6098 MB I ran "DBCC SQLPERF(LOGSPACE)" The result was: Log Size: 6079 MB Log Space Used (%) 92.6 I ran "SELECT log_reuse_wait_desc FROM SYS.DATABASES" The result was: 1 NOTHING 2 NOTHING 3 NOTHING 4 NOTHING 5 REPLICATION Since the issue still exists, I tried your suggestion and I ran: "exec sp_removedbreplication @dbname = 'mydb'" The result was: Command(s) completed successfully. To check, I again ran "SELECT log_reuse_wait_desc FROM SYS.DATABASES" The result was: 1 NOTHING 2 NOTHING 3 NOTHING 4 NOTHING 5 REPLICATION So as you can see there was no change.

0 ·

Kev Riley ♦♦ biloxibay commented · Dec 06, 2013 at 09:38 AM

Which database is showing 'REPLICATION' in the log_reuse_wait_desc column?

0 ·

biloxibay commented · Dec 06, 2013 at 03:42 PM

I looked again to be sure and it is the "production" database that is showing "REPLICATION". The transaction log was still huge, and SQLPERF(LOGSPACE) showed that it was 91% full. So I switched to Full Recovery Mode, and backed up the database 3 times (I recalled reading somewhere that it might require multiple efforts). I re-ran SQLPERF(LOGSPACE) and the size was unchanged but it was now 3% full. So something worked - I wish I understood better. I switched back to Simple Recovery mode, backed up the database, and ran SHRINKFILE on the log which is now 113 MB. This is answered and I appreciate the help. Thanks.

0 ·

question