SQL Jobs - Cannot Obtain A LOCK Resource At This Time
May 24, 2007
Hi All,
We recently bought a new (sql) database server with 8 gb ram. I started moving the databases from our older 2005-sql to the new one. Everything goes well, except...
Some steps in our jobs do a truncate table of a delete from... When the job is running and it reaches the step where the truncation/deleting starts, it stops its execution and gives the following error :
Code Snippet
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. [SQLSTATE HY000] (Error 1204). The step failed.
When I try to execute the procedure that does the truncation manually, it works perfect. Do you guys have an idea on what the cause of this error might be ?
Thanks a lot in advance !
(FYI: we use the enterprise edition of sql server 2005)
I have a deadlock situation, part of the problem looks like this;spid ecid dbid ObjId IndId Type Resource ModeStatus------ ------ ------ ----------- ------ ---- ---------------- -------------404 0 9 792389892 1 KEY (010086470766) SIUWAIT72 0 9 792389892 1 KEY (010086470766) SIUWAIT115 0 9 792389892 1 KEY (010086470766) SIUWAIT61 0 9 792389892 1 KEY (010086470766) ISWAIT318 0 9 792389892 1 KEY (010086470766) SIUWAIT430 0 9 1112391032 1 KEY (010086470766) XGRANTThey are key locks I am used to the resource value generally beingunique by table. I am wondering if the other 'WAIT's are indeedwaiting based on the 'unique' resource value. Note that the exclusivegrant is on a different table but, the resource rsc_text value is thesame. Looking it up I get for a key lock that it's a hashed value, myreferance tells me what is is for a non-clustered index but, does notexplain what it would be for a clustered index.Does it protect this resource based exclusively on resource rsc_text oris it unique by ObjID/Rsc_text, has anyone seen this before?Suggestions?SQL Server 2000/SP3-Chris
Frank writes "Any suggestion on the best design to store data for resource management so that data can be displayed afterwards in something like a gantt chart.
For example, you have to asign resources, say personID 1 to 100 to fill positions A, B, C, D, E and F
A position must always be filled. A person can only fill one position at a time. You want to be able to detect overlaps when you do your planning.
So if person 2 is in position B from 1 Jan to 15 March, he is currently unavailable, but he should be availabe in my planning for any position from 16 March onwards etc.
Possible queries - 1) list all positions not filled during period XXXX to YYYY 2) List all persons available to fill a certain postion during period XXXX to YYYY 3) List any overlaps where a person is assigned to different postions during overlapping time frames..."
while my db is executing a store procedure i try to view the current activity in the managent but it returns to me 'Lock request time out period exceded'. It happens until the store procedure is finished. After that everything is ok. However i can see the activity executing sp_who and the db seems to work ok, maybe a little slow.
Hi , i am getting error Lock request time-out period exceeded frequently Could any one explain how can i track the Locking transactions SP_who,SP_lock helps me partially but i need clear solution
I have a query which is part of bigger transaction:
;WITH CTE( SELECT id, q, totalQ=SUM(q) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) FROM dbo.myTable WITH (UPDLOCK, ROWLOCK) ), CTE1 AS( SELECT id, q_take=CASE WHEN c.Total<@qRequired THEN c.q ELSE @qRequired-c.Total+c.q END FROM CTE WHERE (CASE WHEN c.Total<@qRequired THEN c.q ELSE @qRequired-c.Total+c.q END)>0 ) UPDATE t set q-=c1.q_take FROM dbo.myTable t INNER JOIN CTE1 c1 ON t.id=c1.id
Because CTE query is executed separately of update query I must put UPDLOCK hint, otherwise myTable could be changed while my query executes by some other user.(I have snapshot row committed transaction).
The problem is that my hint will hold lock on rows of myTable until the end of outer transaction(the bigger one).
But I need lock only for the time of this small query execution. What would be the best way to achieve that?
Hello:I run one process that calls the following the store procedure andworks fine.create PROCEDURE sp_GetHostSequenceNumASBEGINSELECT int_parameter_dbf + 1FROM system_parameter_dbtWHERE parameter_name_dbf = 'seqNum'UPDATE system_parameter_dbtSET int_parameter_dbf = int_parameter_dbf + 1WHERE parameter_name_dbf = 'seqNum'ENDGOIf I run two processes that call the above store procedure, I mightoccasionally get the dirty data of int_parameter_dbt. I guess that iscaused by two processes accessing to the same resource simultaneously.Is there any way to lock the store procedure call from MSSQL Serverand allow only one process to access it at a time?Thanks for help.Best Jin
We're running a SQL-Server 2012 and for a while now my accessing records from bigger tables became tricky.There is a Tomcat-8 running which sometimes can't access these tables at all or only after a long delay. As this happened first I went to the Server-Room and opened the Database with the Management Studio to see if there were any issues. open the Database but expanding the directories for "Tables" or "Views" failed after 10 Seconds with the Error 1222.
I turned the Tomcat-8 off to find out whether some unclosed connections are open. Same result, no changes even after one hour.Another 3rd-Party program which we are using seems to connect via other mechanisms to the SQL-Server (Is there a way to list current connections and their types in the Management-Studio, I'm under the impression this program does a lot of caching, it's much faster than the Management-Studio itself.The question is now how can I find out why these time-outs happen? I'm not an expert in SQL-Servers so.
What is this? How do I fix it? I get a ton of these and it slows down the server for 5 minutes.
An error occurred in Service Broker internal activation while trying to scan the user queue 'msdb.dbo.ExternalMailQueue' for its status. Error: 1222, State: 51. Lock request time out period exceeded. This is an informational message only. No user action is required.
I have a very peculiar problem. I have few CommandExec jobs on SQL Server 7.0 which has been scheduled to execute at a specific time. The jobs are executing properly but, the problem is they stop after the completion. i.e after finishing the jobs they still flash a message "Executing the job" they don't flash the message "Not Running" this is happening only to commandExec jobs, I have also few TSQL jobs which are working properly. I tried even giving the duration (start and stop time) but no use. Has anyone encountered this problem? If you have a solution PLS pass it on to me. I am in a very desparate situation.
This must've been convered before, but I can seem to find a solution to it. Daylight Savings Time is about to end in my area, at which time the clocks will go backwards from 1:59am to 1:00am. I have scheduled jobs that run every minute, and I'm expecting that when the clock rolls back, they will stop running for the gained hour until the clock catches back up. So, I need an automated way to correct this.
I was looking at the msdb..sysjobschedules table, at the next_run_time column. But it doesn't make much sense. That value will periodically update, but it is always a good bit behind the next run time reported in EM. And manually updating it seems to have no effect. Is there a way to get a job to run or to recalculate the next run time via tsql script?
We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.
1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).
2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..
on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.
can any one can also suggest me server level settings for aviding locking time out.
Error: The Script returned a failure result. Task SCR REIL Data failed
OnError - Task SQL Insert Error Msg Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out. Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
And how did I get 4 errors? - I only set my script task result to failure
Hi We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.
Consider a scenario in which User 1 is clicking on a button which calls a SQL stored procedure. This procedure selects Group A of records of Database Page1.
At the same time if User 2 also clicks the same button which calls same SQL stored procedure. This procedure selects Group B of records of Database Page1.
So, its the same Page1 but different sets of records. At this moment, both the calls have shared locked on the Page1 inside the procedure.
Now, in call 1, inside the procedure after selecting Group A of records, the next statement is and update to those records. As soon as update statement executes, SQL Server throws a deadlock exception as follows :
Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
We are able to understand why its happening. Its because, Group A and Group B of records are on the same Page1. But both the users have shared lock on the Page1. So, no one gets the exclusive lock in records for update, even though, the records are different.
How can I resolve this issue? How can I get lock on wanted rows instead of entire page?
Hi, I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task-> dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
I want to lock a table so others cannot lock it but able to read it inside transactions.
The coding I need is something like this: set implicit_transactions on begin transaction select * from table1 with (tablock, holdlock) update table2 set field1 = 'test' commit transaction commit transaction
I have tried the coding above, it won't prevent others from locking table1.
So, I changed the tablock to tablockx to prevent others from locking table1. But this will also prevent others from reading table1. So, how can I lock table1 so others cannot lock it but still able to read it?
I'm using sql server 2005, I created a customer table and set the customer_id as an uniqueidentifier that is a NEWSEQUENTIALID()...when a user is created I want to obtain the last customer_id so that I can insert it in another table that has a one to many relationship with the customer table. Please help...how do i do this?.Thanx
I'm working on a search engine right now, and I am querying out a set amount of information at one time. How can I grab the last row count of a query, and use that as a starting point, or index when a "Next 100" is pressed. For instance, a person searches through a user table, but only wants 25 users at a time. So the beginning index is 0 to 25. In my query, I have "SELECT TOP 25 from users", but now when the person searching want's to move to the next 25 users, so the index would be 26 to 50, how can I hold the last row displayed? It gets a little more complex also, if a user is searching by last name, the rows will be staggered. Is there a SQL function which will grab the number of the last row displayed? I've looked through the SQL Book, and was unable to find a function that does this, or anything close to it. I hope I made sense of this question, if you need more information, email me and I will try to clarify. Thank you for any information or hints.
If I have say 20 records of sales employees, for example, how can I get the top 3 locations for $$$sales for EACH employee? Each employee can have multiple locations where they have sold(let's say up to 50). I only want the names of the top 3 locations. The closest I can get is filtering the dataset by a HAVING clause > a dollar amount but this still gives me between 3 - 12 records for each plus I have to literally enter each salesperson's number as it stands now. Is this a loop or a cursor? Thanks.
i need to run a restore of a database overnight onto a different server using the live data .bak file. however the job failed on the first run (last night) with the error:
"Exclusive access could not be obtained because the database is in use. ...."
how do i gain this Exclusive use via an SQL statement please?
I have a new computer, clean, I installed WinXP, and I want to connect a SQL Server (other computer in the same network) For this I installed the client tools from SQL SERVER 2000 kit. Ok. When I try to connect to the server, I can't connect through "trusted connection", only whit user name and password. I mention that I set the sql server in mixed mode autentification.
When I try to connect from other computer (my laptop, or other computer from this network) I can connect both types of connection: trusted and with
sa (for example)
The question is:
How I can set up a "trusted connection" at SQL Server?
The computer is completely new, clean and has win xp with sp2.
I have a new computer, clean, I installed WinXP, and I want to connect a SQL Server (other computer in the same network) For this I installed the client tools from SQL SERVER 2000 kit. Ok. When I try to connect to the server, I can't connect through "trusted connection", only whit user name and password. I mention that I set the sql server in mixed mode autentification.
When I try to connect from other computer (my laptop, or other computer from this network) I can connect both types of connection: trusted and with
sa (for example)
The question is:
How I can set up a "trusted connection" at SQL Server?
The computer is completely new, clean and has win xp with sp2.
i need to programmatically obtain and the process constraintinformation. the problem with the output from sp_helpconstraint isthat it outputs two resultsets. (the 2nd resultset is exactly what ineed though)as far as i know i can't stick this output into a temp table to thenprocess with cursors or anything like that. i'm trying to directlyaccess all kinds of sytem tables but not getting the complete info thatthe 2nd resultset that sp_helpconstraints offers.(E.g. sp_helpindex is more useful as it outputs only one resultset ofinformation so i could programmatically use that output to do what ineed to do)any suggestions? thanks.
i need to programmatically obtain and process constraint keyinformation. the problem with the output from sp_helpconstraint isthat it outputs 2, and sometimes 3 resultsets. (the 2ndresultset/constraint_keys is exactly what i need however)because the resultsets vary in output format, i cannot programmaticallyuse the output to process in my DB proc. its essentially useless tome. i can only suppress the 1st resultset (with tablename info), andcannot suppress the occasional 3rd resultset (that has referencingforeign key info).i've tried to directly access all kinds of sytem tables but i'm notgetting the complete info that the 2nd resultset thatsp_helpconstraints offers.any suggestions? thanks.
Sorry for my englishI have a table that contains data of career about the person (staff)like this ...EMATREANIDEMEIDEGIIDecc. ..ecc. ..ecc. ..ecc. ..1199912312002123111200311000321999123120021231122003110003419991231200073115199912312001131161999123120021231162003110003719991231200212311whereEMATR is a not unique key (person ID)EANID, EMEID, EGIID (assembled) are the date of the last advance ofcareerI want extract from the table below only the actual position,therefore a view that return this rowsEMATREANIDEMEIDEGIIDecc. ..ecc. ..ecc. ..ecc. ..1200311000322003110003419991231200073115199912312001131162003110003719991231200212311I hope of to have been explainThank you from Maximiliano (italy)RE-Sorry for my english--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
