October 4, 2007 at 6:50 pm
Hello!
I am trying to troubleshoot weird problem. I noticed that msdb log size was growing. I have discovered jobs executing particular stored procedure that had an open transaction in msdb and killed spid in question. This stored procedure was executing sp_send_dbmail at the time I killed spid and fn_get_sql returned xp_sysmail_format_query as currently executed statement. SPID is showing KILLED\ROLLBACK status but it hasn't changed for hours and msdb log continues to grow. I am running SQL Server 2005 SP1. I was wondering if anyone has expeienced similar problem. Any advice is greately appreciated.
Thanks,
Igor
October 5, 2007 at 7:24 am
Greetings,
This is normal behaviour when a rollback operation is going on. Even if you shut down and restart SQL Server service, it will pick up where it was left (and maybe at the whole beginning, depending of the transaction(s)).
Thank you
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
October 8, 2007 at 11:06 am
Hello kestak!
I had this resolved. It turned out that database mail extended stored procedure called from main stored procedure was in turn blocked but it. Main stored procedure and mail stored procedure had differenet spids. That what made me confused.
I got issue resolved by killing spid initiated by extended stored procedure.
Thanks,
Igor
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply