Msg 1204 Lock Error

Question

Msg 1204 Lock Error

Jako de Wet

Hall of Fame

Points: 3123
More actions
January 14, 2012 at 2:32 pm

#254884

Hi everyone
I have SSIS package that I run every weekend to update one of our datasets, but for some reason I am getting the following error:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
The problem is that there should be no one accessing either the source or destination table at the time. I also rebooted the server and I still got the same error.
I am using a plain INSERT INTO SELECT FROM query.
The source has about 18.5 million rows, but I am only selecting about 3 million of these, only the records updated since the last run of the package.
Any ideas what the cause might be? Im a bit worried because when I did a reboot I noticed that the drive/raid light on the server was on, could the source table be corrupt?
Will get someone to come check the drives etc on Monday, but would like to finish the update before then and REALLY hoping that the error isn't caused by a faulty drive?
Thanks.

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

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

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

Are there any traceflags enabled on either of the servers?

You say you're using INSERT INTO ... SELECT FROM..., so are you not using a standard data flow task (data source, data destination)?

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

Jako de Wet Hall of Fame Points: 3123 More actions · Answer 2

Hi GilaMonster

Both db's are on the same server, I was using a normal SQL Script Task, will change it to a DataFlow Task.

I rebuilt the clustered index and ran the query again and it worked 100%, seems to have sorted out the problem.

Will still get someone to come check the drive though.

Thanks

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

I guess Lock Escalation is disabled for SQL Server Instance thus it’s resulting in multiple row / page locks and finally abrupt termination.

Please read following article, it explains the locking behaviour (for your case).

How to resolve blocking problems that are caused by lock escalation in SQL Server

http://support.microsoft.com/kb/323630

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

Jako de Wet (1/14/2012)
Hi GilaMonster
Both db's are on the same server, I was using a normal SQL Script Task, will change it to a DataFlow Task.
I rebuilt the clustered index and ran the query again and it worked 100%, seems to have sorted out the problem.

Interesting. If you change it to a data flow task, consider the tablock option on the destination (unless there needs to be concurrent access). You didn't answer my questions on traceflags... If you don't know, ask your DBA. There are two traceflags that can cause this.

The drive should be checked, but it's unlikely that it would cause locking problems. Other problems, sure, but just not this one

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

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

One other thing, check that your SQL instances are patched (the latest service pack is SP3 for SQL 2008), as there was an insert-related lock escalation bug in earlier releases of SQL 2008. See http://connect.microsoft.com/SQLServer/feedback/details/506453/lock-escalation-no-longer-kicks-in-for-inserts-in-sql-server-2008

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

Jako de Wet Hall of Fame Points: 3123 More actions · Answer 6

We don't have an official DBA, or actually I'm doing everything myself *hides

So I guess to answer your question, there are no traceflags as I haven't setup anything.

Server and all patches are always kept up to date.

Thanks for all the input.

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

Cool.

On that bug, while it says it's fixed I've heard someone say it's still a problem on even the latest builds, but I haven't confirmed that. If the table isn't needed by other processes during load, TABLOCK (option on the OLEDB destination in SSIS) should prevent this problem.

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