Michael Earl (12/22/2008)

---

Don't bite my head off Gail,

just wanted to make the point that it is logged "differently" without typing out a lot of detail.

Perry Whittle (12/22/2008)

---

Ahmad Osama (12/22/2008)sqlcmd -S BUILDSERVER -E

is BUILDSERVER the correct instance name?

Michael Earl (12/22/2008)

---

TRUNCATE is a non-logged operation, so if you are using log shipping or have regular log backups running, it can cause you some issues.

HKwai (12/17/2008)

---

Does anyone know of a solution to this?

Peso (12/22/2008)

---

Doesn't

IF UPDATE(colStatus)

do that for you?

subha.v (12/22/2008)

---

Advanced thaks for all.I didn't expect this much reply from my post. It's really helpful for me to know more about the problem.

Perry Whittle (12/22/2008)

---

are you sure

NET STOP MSSQLSERVER

is stopping the correct instance?

Paul (12/22/2008)

---

From what I've read, there's an overhead associated with checking for locks that you incur even if there aren't any.

Adding NOLOCKS is supposed to skip that.

subha.v (12/22/2008)

---

Sorry. I am waiting for my PL approval.

Could you please confirm is searching based on the non-clustered index cause the performance problem.

Paul (12/22/2008)

---

I meant that the data is updated so infrequently that I dont think the costs of record locking outweigh the performance gain from ignoring it.

ps (12/22/2008)

---

try reducing the frequency of tran log backups. this will flush out inactive transaction more frequently from the log file.