Log Writer Suspended?

Question

Log Writer Suspended?

Brandie Tarvin

SSC Guru

Points: 173002
More actions
July 19, 2007 at 12:32 pm

#153693

I haven't seen this one before and can't find anything on Google.
I ran an sp_who2 active on my system and found 2 spids suspended. #5 (Log Writer) and #12 (Checkpoint). This slightly worries me, though everything still seems to be running fine.
Can anyone tell me if this is a normal state for these two processes in between Transaction Log backups? Or is this something I should be worried about?
Thanks,
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 8 posts - 1 through 7 (of 7 total)

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

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

Google is simply amazing. All i entered as a search string was "LOG WRITER SUSPENDED" and what i found, as the top entry, was

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=660381&SiteID=1

Bottom line, and what i believe is your answer, from this webpage:

"They are almost perpetually going to be in a suspended status. What this means is that the process is running, it just isn't currently executing anything on the SPID when you look at it. This is normal operations for the log writer and checkpoint processes. (In previous version, it showed up as sleeping although this wasn't exactly accurate.)"

have a great day, and a wonderful weekend!

steve

Brandie Tarvin SSC Guru Points: 173002 More actions · Answer 2

Wow. I didn't come up with that entry at all on my search.

Thanks, Steve. I appreciate your help with that.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

Moreover SPIDs less than 50 are meant for System processes so you no need to worry about those processes until they affect your server performance.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Brandie Tarvin SSC Guru Points: 173002 More actions · Answer 4

Sugesh,

Thanks, but I already knew about the System Process IDs.

My concern was why system processes were suspended. And my philosophy is "Better to know now when it's NOT a problem than to wait until it IS a problem". Hence the reason I asked the question.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

Ok Brandie no issues. I too learnt few things in this post.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

sqldba-294117 Hall of Fame Points: 3486 More actions · Answer 6

Hi:

how often do you see this this suspended state for log writer & checkpoint. As you knwo that they both are different.

what your SQL Server is doing when you see this state suspended. do you see long waits on WRITELOG & ASYNC_IO_COMPLETION.

collect some perfmon counters and look for Avg Disk Sec/ Transfe for log disk.

i hope this helps.

Thanks

Jay

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

Please note: 4 year old thread.

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