September 24, 2009 at 12:03 pm
Only changes are logged in the transaction log. A SELECT will not cause problems in the transaction log. It could cause tempdb issues, and possible tempdb log problems, but not in the db log.
a SELECT INTO, is an INSERT statement.
September 24, 2009 at 12:11 pm
Steve Jones - Editor (9/24/2009)
Only changes are logged in the transaction log. A SELECT will not cause problems in the transaction log. It could cause tempdb issues, and possible tempdb log problems, but not in the db log.a SELECT INTO, is an INSERT statement.
Yes, absolutely. I am 100% agree.
I was saying a SELECT statement may initiate a short chain leading to a log space issue, but I was not clear enough.
September 24, 2009 at 12:12 pm
Sergey Vavinskiy (9/24/2009)
Lynn,Curious, how does a SELECT statement fill a transaction log? I can understand UPDATE, DELETE, and INSERT; but SELECT?
If it is only one process running against this DB, it will not fill tr.log, because for SELECT statement truncount =0. You are right. But this is unreal situation to have only one process.
In real life we have the multiple processes running against a database simultaneously.
So, for example we have a badly designed SELECT statement, (something like a big report), which runs for hours. This report can easily create locks on several tables of course for hours as well.
In this case any UPDATE/DELETE/INSERT process will try to modify one single row in these tables for hours, filling a transaction log.
Actually, in this case, I think you'd start seeing lock timeouts from the UPDATE/DELETE/INSERT processes since they wouldn't be able to get the needed locks required in a timely manner.
I also think you missed the point, as Steve pointed out, only changes are logged. A simple, or complex, select statement for a report is only reading the database. Not a logged activity.
September 24, 2009 at 12:21 pm
I also think you missed the point, as Steve pointed out, only changes are logged. A simple, or complex, select statement for a report is only reading the database. Not a logged activity.
I got this point. This changes come not from SELECT statement, but from uncommitted UPDATE/DELETE/INSERT transaction, which cannot be committed because of running SELECT statement.
This uncommitted transaction fills the log.
I was not clear meaning that.
September 24, 2009 at 12:25 pm
Sergey Vavinskiy (9/24/2009)
I also think you missed the point, as Steve pointed out, only changes are logged. A simple, or complex, select statement for a report is only reading the database. Not a logged activity.
I got this point. This changes come not from SELECT statement, but from uncommitted UPDATE/DELETE/INSERT transaction, which cannot be committed because of running SELECT statement.
This uncommitted transaction fills the log.
I was not clear meaning that.
The UPDATE/DELETE/INSERT may not even be able to complete the respective action because it can't get the required locks due to the locks put on the rows/pages/extents/table as a result of a poorly written SELECT. In that case, no changes will have been logged in the transaction log to begin with either. If the SELECT is bad enough, you may get a lot of lock timeouts and users complaining.
September 24, 2009 at 12:26 pm
I agree with Lynn. A SELECT might blog an insert/update/delete from committing, but that doesn't mean more log records. The SELECT is not logged.
I can start a tran, do an update of one row, have it blocked for 100 hours by a SELECT that needs 100million rows. Only one row is logged in the tlog.
September 24, 2009 at 12:31 pm
The UPDATE/DELETE/INSERT may not even be able to complete the respective action because it can't get the required locks due to the locks put on the rows/pages/extents/table as a result of a poorly written SELECT. In that case, no changes will have been logged in the transaction log to begin with either. If the SELECT is bad enough, you may get a lot of lock timeouts and users complaining.
Lynn,
I agree with you.
You was asking
Curious, how does a SELECT statement fill a transaction log? I can understand UPDATE, DELETE, and INSERT; but SELECT?
I was modeling the situation making it is possible.:-)
September 24, 2009 at 12:37 pm
Sergey Vavinskiy (9/24/2009)
The UPDATE/DELETE/INSERT may not even be able to complete the respective action because it can't get the required locks due to the locks put on the rows/pages/extents/table as a result of a poorly written SELECT. In that case, no changes will have been logged in the transaction log to begin with either. If the SELECT is bad enough, you may get a lot of lock timeouts and users complaining.
Lynn,
I agree with you.
You was asking
Curious, how does a SELECT statement fill a transaction log? I can understand UPDATE, DELETE, and INSERT; but SELECT?
I was modeling the situation making it is possible.:-)
Not really. A SELECT statement isn't logged. And in Steve's example, I'd think there would be a timeout before 100 hours had elapsed. If not there would definately be an angry/upset user calling someone. I may be wrong, but I'm pretty sure that the UPDATE wouldn't even be logged until the UPDATE was no longer blocked.
September 24, 2009 at 1:32 pm
Steve and Lynn,
Thank you for a good discussion.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply