August 27, 2015 at 1:06 pm
Hi,
Under sql server errors, I am seeing frequently
The activated proc 'sp_name' running on queue 'TestDB.dbo.SP_name' output the following: 'The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.'
What does it mean?
Is this something serious issue? Do we need to worry about that sp?
August 27, 2015 at 1:21 pm
ramana3327 (8/27/2015)
Hi,Under sql server errors, I am seeing frequently
The activated proc 'sp_name' running on queue 'TestDB.dbo.SP_name' output the following: 'The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.'
What does it mean?
Is this something serious issue? Do we need to worry about that sp?
How about some details??? The code for this procedure would be a nice addition to your post. I am guessing you probably have some level of "nested" transaction going on. Also, you really should avoid the SP_ prefix for procedure names, or even better drop the prefix entirely. That prefix is reserved by MS for system procedures and can cause performance and other challenges if there is a name collision.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2015 at 1:34 pm
If you have error log entries saying that a transaction can't be committed, then I'd say you should worry about it if that transaction is important to you, the owner of the database or the business. My guess is that if it was regarding a payroll run, it would be the most important thing in the entire company - just ask any employee. 😀 You have to figure out what sp_name is and what it's doing to be able to determine what to do about it.
I'm glad I'm not the only one who notices the sp_ prefix to the procedure name and knew what it meant. 😛
It will slow you down because SQL Server has to first check the master database for a procedure of that name. Finding none, it'll come back to the current database and look for one.
August 27, 2015 at 2:28 pm
Ed Wagner (8/27/2015)
I'm glad I'm not the only one who notices the sp_ prefix to the procedure name and knew what it meant. 😛It will slow you down because SQL Server has to first check the master database for a procedure of that name. Finding none, it'll come back to the current database and look for one.
Yeah it kind of drives me nuts to see prefixes on objects. Don't know why, I suppose it reminds me of my VB6 days which makes me shudder. The worst of that kind of thing is when you do something like create a view to replace a table and you have to name your view something like tblTableName to avoid having to rewrite tons of code. What ends up happening is you have the incorrect prefixes on stuff. I can remember some crazy things like iEmployeeID being redefined as a string and contain string data because we need to change the datatype from int to string or vice versa. The same kind of nonsense ends up in the database in large systems.
Here is a great article discussing the sp_ prefix issue. http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2015 at 3:35 pm
To easily recognize, I think dev prefix as Sp_name but most of them are spname only
August 28, 2015 at 5:23 am
Sean Lange (8/27/2015)
Ed Wagner (8/27/2015)
I'm glad I'm not the only one who notices the sp_ prefix to the procedure name and knew what it meant. 😛It will slow you down because SQL Server has to first check the master database for a procedure of that name. Finding none, it'll come back to the current database and look for one.
Yeah it kind of drives me nuts to see prefixes on objects. Don't know why, I suppose it reminds me of my VB6 days which makes me shudder. The worst of that kind of thing is when you do something like create a view to replace a table and you have to name your view something like tblTableName to avoid having to rewrite tons of code. What ends up happening is you have the incorrect prefixes on stuff. I can remember some crazy things like iEmployeeID being redefined as a string and contain string data because we need to change the datatype from int to string or vice versa. The same kind of nonsense ends up in the database in large systems.
Here is a great article discussing the sp_ prefix issue. http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix
I've seen the same thing. A table named vwJobs is just wrong and bothers me. I do, however, tend to use Hungarian notation in my code. Not database objects, just the code. I find it helps to keep me aware of data types, which can help when typing near the bottom of a 2000-line procedure.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply