Ran it again: now log_reuse_wait_desc = NOTHING! Good news!

I have re-run this query:

SELECT name, recovery_model_desc, log_reuse_wait_desc

FROM sys.databases

WHERE name = @DatabaseName

Same results: recovery_model_desc = FULL, log_reuse_wait_desc = LOG_BACKUP

Puzzling!

One of the hourly backups failed in the middle of the night, but all have run OK since then.

I have changed to every 15 mins, and the first one of...

I should have also mentioned that recovery_model_desc = FULL.

log_reuse_wait_desc = LOG_BACKUP

I have increased the frequency of log backups to every 15 mins, instead of every hour.

Is that a step in the right direction?

Best wishes

Mark

Important piece of information: last night's Full Backup failed, at 00:30 in the morning.

I presume this means that more data needs to be stored in the Transaction Log.....?

hmmmm.... this query returns zero rows.....curiouser and curiouser!

Dear Gail,

Sorry to be so long with an update, I have been away for a week on a training course.

Anyway, I largely have good news for you: I TRUNCATEd...

Dear Gail,

I have scheduled this to run at 3am. If it doesn't work, then I will TRUNCATE the table during tomorrow's scheduled maintenance session.

Many thanks for taking such...

GilaMonster (1/13/2012)

---

Yeah, the ghost records are definitely the problem.

Two things you can try (easier one first). Run a SELECT * FROM <table name> (must include LOB columns), see if that...

Dear Gail,

I have run your code and attach an Excel file, as you requested.

Best wishes

Mark

Hi Perry,

Thanks for helping. I ran Paul's code and got this result:

session_idstart_time status ...

Bad news.... last night I ran the following two jobs:

DBCC CLEANTABLE (0, SessionsTableName, 0) WITH NO_INFOMSGS;

ALTER INDEX ClusteredIndexName ON TableName REORGANIZE WITH (LOB_COMPACTION = ON)

The jobs ran without errors, but...

I hope to - but please don't take that as a promise. I am away all next week so tomorrow will be a very busy day!

I am in the process of scheduling this as a one-off job tonight. Assuming it works, I will then schedule it as a weekly job.

Many thanks for your help,...