Thanks a lot. That worked like a charm. One more question? How do i change the default location of creating a data file and log file? I thought changing the...

I see. Thanks for your response again.

Gail, one thing though could I have used profiler instead of DMv to troubleshoot this? Which template would be best to troubleshoot this?

Application was issuing a DELETE on 4 mil rows on a table that caused the log to full....Easy solution was just to truncate those records so when the application start...

One thing though I didn't understand was if transactions gets logged in Simple Recovery model as well then what is the difference between Simple Vs Full recovery model?

Gail thank you so much for your help on this thread...You rock!!!!! You are a life savior.

Should we be concerned about number of VLFS? There are about 735 in there? Would adding another Log file help?

Actually i removed all the records and that helped. My VLSF are high and thats hurting the performance. I did shrinfile as well.....What should I do to improve the performance?...

Thanks Gail. Through the DMV, I think found the offending query. Its deleting 4 mil rows from a table. But why it would fill up the log while db in...

reserved data index_size unused

20416424 KB10244200 KB9928824 KB...

Does OS server level configuration (for e.g Lock pages in memory or any other configuration settings on the sql server will have issue on the transaction log becoming full...

Thanks Gail from your help. Given the transaciton id, what other DMV can I use what query/process is it executing?

Attached is the result of DMV..See what do u suggest?

Gali, when user gets the error, log_reuse_wiat desc is changed to ACTIVE_TRANSACTION.

Yes. autogrow is enabled. I have increased the size o 5000 MB as DB size is 20 GB.