January 28, 2014 at 3:23 am
Hi,
I will explain my issue as below:
Database 1:
Table : myTable1
View : myView1 (referring myTable2 in database 2)
Database 2:
Table : mytable2
Process is trying to populate myTable2 from myTable1 using view myView1. myTable1 has around 8 million of records but process fails while insertion with error "The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions."
Sql Server Instance memory is set to : 27307 MB
Ram is : 64 GB
Locks value is set to 0.
Using sp_lock I found that Type is : RID, mode is : X and Status is : GRANT
Just to experiment I created a table (same as myTable2) in database 1 and ran the same insert query on this table and this time it passed and populated 8 million records in around 10 minutes. This time the Type in sp_lock was TAB.
Can you guys please help me to resolve this issue? Let me know if you need any other info.
Thanks,
Sandeep
January 28, 2014 at 3:32 am
What's the query you're running?
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
January 28, 2014 at 3:37 am
The query i am running is:
INSERT INTO myView1 (fileid,timekey,geogkey,prodkey, m001, m002, m003,m004, m005, m006, m007, m008, m009, m010, m011, m012, m013, m014, m015, m016)
SELECT 2369, timekey,geogkey,prodkey, m001, m002, m003, m004, m005, m006, m007, m008, m009, m010, m011, m012, m013, m014, m015, m016 FROM myTable1
WHERE file = fileName
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply