How To Reconfigure SQL 7 With More LOCKS
Oct 21, 1998
Hello to all!
I`m two weeks old with SQL 7 Beta 3 with no formal training whatsoever. I just kinda tinker with it at the moment since it`s installed in a stand alone server with me having sole access.
Using DTS (import into SQL), I tried to migrate an Inventory History dbf (Dbase 3) having 13+ million records. I got this error message after a few hours:
"Error at Destination for Row number 6353502. Errors encountered so far in this task: 1. SQL Server has run out of LOCKS. Rerun your statement where there are fewer active users, or ask the system administrator to reconfigure SQL Server with more locks."
It was the only application running on the server (aside from the SQL services) and i was the sole user. This is my first time on SQL Server and as DBA i should know everything `bout it.
Question is how and where do i reconfigure for more LOCKS? And how many LOCKS do i have to set?
Please help. Thank you in advance
View 3 Replies
ADVERTISEMENT
Jul 16, 2015
I've got an INSERT that's selecting data from a linked server and attempting to push 10 million rows into the blank table. More or less, it looks like this:
insert into ReceivingTable (
Field1, Field2, Field3, Field4
, Field5, Field6, Field7, Field8
, Field9, Field10, Field11, Field12
, Field13, Field14, Field15
[code]...
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. There are no other active users. I ran it again and monitored the following DMO to watch the growth of locks for that spid:
SELECT request_session_id, COUNT (*) num_locks
-- select *
FROM sys.dm_tran_locks
--where request_session_id = 77
GROUP BY request_session_id
ORDER BY count (*) DESC
The number of locks started small and held for a while around 4-7 locks, but at about 5 minutes in the number of locks held by that spid grew dramatically to more than 8 million before finally erroring again with the same message. Researching, I can't figure out why it's not escalating from row locks to table locks at the appropriate threshold. The threshold in was set to 0 at first (Server Properties > Advanced > Parallelism > Locks). I set it to 5000, and it still didn't seem to work. Rewriting the INSERT to include a WITH (TABLOCK) allows it to finish successfully in testing. My problem is that it's coming out of an ETL with source code that I can't edit. I need to figure out how to force it to escalate to locking the entire table via table or server level settings.
A colleague suggested that installing service packs may take care of it (the client is running SQL Server 2008 R2 (RTM)), but I haven't found anything online to support that theory.
View 9 Replies
View Related
Aug 5, 2015
We are migrating our database(s) from ORACLE to SQL. In Oracle we were able to issue a SELECT statement and see all of the locks (Blocking and Non-Blocking) currently in the system. The query also included the Process ID of the process we needed to kill in order to get rid of the lock.
We now need to create the same type of query for Microsoft SQL Server 2012. I have seen postings on different sites saying that this info can be obtained using SP_WHO2 or using the SQL Server Management Studio Activity Monitor's PROCESSES tab, but we are looking for a SELECT statement that will give us similar information.
View 7 Replies
View Related
Aug 3, 2004
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
THis is the error that I am receiving, suggesting that I have made a change to the configuration, which I haven't, anway I have responded with this code:
USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
EXEC sp_configure
However I don't know if anything have change with the configuration. I am new to the DBA world. HELLLP!!!
View 2 Replies
View Related
Nov 14, 2000
hi, I have NT server which has drive c: 500 MB and drive d has 44 GB.
I know that the person who set up this server did not give enough space to the c drive, here is the problem. I am running sql server 7.0 which has 30 GB of data in the d drive. I need to reconfigure the NT hard drive so I can allocate 2 GB for C drive and 42 GB for D drive.
What is the best, safe method to accomplish this task.
Ahmed
View 3 Replies
View Related
Apr 11, 2006
If you add a "Union All" task to your Data Flow the output columns get configured by the first input you connect to it.
However, if you later want to modify the input columns on the inputs (perhaps after adding an upstream Data Conversion) there seems to be no way to get it to forget the original set of output columns.
Ideally, there would be a button you can press to reset the output columns based on the earliest added input. At the very least it ought to forget its output columns when the last input is removed.
Other tasks offer a dialogue to resolve input / output mismatches but not Union All.
or am I missing something?
jks
View 7 Replies
View Related
May 11, 2008
Hi all,
Is it possible to reconfigure the sysadmin server role?
How can I see it's permissions?
Thanks,
Assaf
View 1 Replies
View Related
Mar 20, 2008
Hello
I have a working package. It imports a file. The file import uses a file connection that points to one file. This file connection contains all of the file layout and field information like the name, length etc. This information is for 85 columns.
I would like to change the file connection to a source of a SQL task (or a for loop task). This SQL task will get the same file type but there will be multiple instances of the file (different names.)
What is the best way to migrate this current Flat File Source so it can now take different names and yet not loose the current field mapping that the current flat file source contains? Is there a way to accomplish this?
Thanks
View 5 Replies
View Related
Nov 10, 2003
I have read that even during read procedures (sql select statements), sql server uses row locking. I know that you can use the NOLOCK keyword, but if you don't everytime that a user makes a selects statement on a table, does sql server really lock those rows, and if so are they then unavailable to another user who wants to make a select statement at the same time on that same table? That does not seem like it would be the case otherwise it would not scale well. Thanks for any clarification on this.
View 5 Replies
View Related
Feb 13, 2002
I am using SQL Server7.0. I opened a table through the Enterprise Manager and left it open. In the Query Analyzer when I try to update a field on that table(more than 2000 rows), it goes on running. When I watched the Current Activity, it shows that the update process is being blocked by the select query. But if I try to update the same column for less than 1500 rows, there is no blocking issue and the update occurs immediately. Can anybody let me know why this is happening and what should I do to prevent it?
View 1 Replies
View Related
Mar 28, 2000
I am using Sql Server 7.0
To I got the following error message. Can some one tell how to solve this issue.
Server: Msg 1204, Level 19, State 1, Procedure OPEN_OBJECTS, Line 2
SQL Server has run out of LOCKS. Rerun your statement when there are fewer active users, or ask the system administrator to reconfigure SQL Server with more LOCKS.
ranga.
View 1 Replies
View Related
Sep 26, 2000
what the best way to control locks, if inserting couple thousands records from one table to another.
View 5 Replies
View Related
Feb 20, 2001
Hi
I have a big query which updates around 14000 rows at a time if i place a lock on the table and others try to update the same table is it possible to let them know that table is locked by someone else.
View 1 Replies
View Related
Sep 19, 2002
2 quick questions :
1) How do I keep multiple users from editing the same record without locking the entire table? What would be a 'standard' way of handling this?
2) How do I keep 2 people from posting the same record?
Please help me understand locking, THANKS!!!
View 2 Replies
View Related
Apr 11, 2005
I have a stored proc which will be entering/updating a record into a table. The table's key is an integer field which I may have to increment by one. I know I can use
declare @nextid int
set @netxid = max(id) from table
insert @nextid into table
Is some kind of lock the best way to approach this?
View 4 Replies
View Related
Jun 15, 2004
Hello,
I have a problem in SQLSERVER 2000, when I execute a Query, the table get locked for insert or any other transaction, even for other queries.
Does SQL Server have a kind of lock mode different of Oracle ?
How do I solve this problem ??
View 5 Replies
View Related
Jun 16, 2004
Hello There !!
I have a very big problem, with SQL SERVER 2000. I want to know about the locks with select.
When I execute a Select (so big), and I try to update or Insert into one of the tables that I invoke in the select, I get locked.
Is there in SQL Server, something like a Select for update, that could be causing the problem ???
Is there any way to select rows from a table without locking it ?
I really have a big problem with this, and I don't know so much about sql server !
Thank you so much !!!
View 14 Replies
View Related
Jul 1, 2004
Some of my tables Were locked IN IS Mode.What does it mean?
Thanks.
View 2 Replies
View Related
Jul 1, 2004
I Have 359 locks on MY Database ,They are always there on my DB.The DB is a development database and lots of summary Stored procedures will be running on this DB.
Does it effect the performance.How can I remove those locks.
Thanks.
View 2 Replies
View Related
Jan 13, 2004
Hi All,
Sql server 7
pls provide me sql statement that shows all the locks. the goal is to produce the output on an HTML page.
pls help me in solving this
TIA
Adil
View 8 Replies
View Related
Apr 18, 2007
Hi
I want to write all my select ststements using locks
how i should write a select ststement using locks
i searched for examples but iam not getting the syntax
Plz give me one example for select statements using locks
thanks in advance
Malathi Rao
View 4 Replies
View Related
Jan 7, 2008
if have a transaction with statments like
begin
insert into table A ...where exists (select .. from table A)
transaction
What locks will be placed while inserting and selecting rows,if multiple users are accessing this concurrently
View 7 Replies
View Related
Jun 1, 2007
How to lock a Row in SQL2000 so that nobody can select that row.
I applied ROWLOCK, but i am not finding the way.
My query is "SELECT * FROM tablename WITH (ROWLOCK)"
Is this the correct way to write locks.
I would be thankful if u help me
View 2 Replies
View Related
Jul 31, 2002
Sir,
This is top urgent Sir !
There are 2 tables for transaction. The header and tail tables. How do I insert records. if one is updated & another is not ? the sequence for tracking the records will fail. How do I deadlock the table for insert especially when I use stored procedures for 'Inserts'.
Sundar Raman S K
View 1 Replies
View Related
Apr 3, 2000
Hi,
I have just upgraded my sql 65 to sql 7. The problem I am running into is running out of locks all the time with 50000 locks. The sql box has 4 pentium pro 200 processors and 1 Gig of ram. Here is the error message:
DESCRIPTION:Error: 1204, Severity: 19, State: 1
SQL Server has run out of LOCKS. Rerun your statement when there are fewer active users, or ask the system administrator to reconfigure SQL Server with more LOCKS.
COMMENT:Fatal Error Occured in Resource
Help!!!
Jim
View 3 Replies
View Related
Sep 13, 2000
Everytime I try to open the design-mode of a table from enterprise mgr, I get this error 1204.---
SQL Server has run out of LOCKS. Rerun your statement when there are fewer active users, or ask the system administrator to reconfigure SQL Server with more LOCKS.
There no other users but me. Locks have been configured to their max (over 200000000). Don't understand whats wrong. Please help..
View 4 Replies
View Related
Dec 26, 1999
I am using SQL Server 6.5, when two or more independent applications put transactions through SQL, it locks up. Example of locks up.
When the OrderLines table is locked, then I put the following (Select * from OrderLines) then the query does not return any values, the world goes round and round, the only way out is to shut down and cross my fingers whilst SQL goes into recovery mode.
I have read through some of the documentation, such as deadlocks, livelocks and lock starvation but it say none of these will lock the whole machine. But somehow simultaneous transactions can, and the current activity dialog goes red, bright red.
Any ideas?
View 1 Replies
View Related
Oct 10, 2003
I am interested in getting a better handle on how SQL 2000 determines the locking level to apply to different transactions. I am familiar with the fact that SQL does this on the fly but I was wondering if this could be specified in a Stored Procedure to use one over the other instead and what impact if any that might have on indexes. The databases I work with run anywhere from under 100 GB to 150 GB. Thanks for anyone's input on this subject.
Thomas
View 2 Replies
View Related
Sep 15, 1999
I have a problem where Backup Exec crashed during a backup of a SQL database and now SQL show 3 extent lock that are now redundant. These cannot be removed from Enterprise manager Is it therefore possible to remove them directly from the master database ??
Thanks
View 1 Replies
View Related
Jun 8, 2004
We've experienced persistent hang up on our application. I tried opening the enterprise manager->management->Current Activity->Locks/Object and I've seen ridiculous entries like master.dbo.spt_values, SIS.dbo.feesclass,tempdb.dbo$##lockinfo205. I don't what this mean? does it tell us that something wrong with the application that does not release locks? please help me.
View 4 Replies
View Related
Jul 13, 1999
I am attempting to delete some records in a table using msquery. Msquery locks up and I see that I have an intent lock on the table in enterprise manager that never seems to get an exclusive lock. What would inhibit an exclusive lock. USer connecting to the database at this time all seem to experience an ODBC error until I kill msquery. I can not kill the intent locks unless killing the msquery app.
Any Ideas??
View 1 Replies
View Related
Nov 3, 2005
Hi,
I am having trouble with locks and performance issues in one application. The SQL server is specific to this application. What settings should i choose to set an alert to let me know when i get too many locks? the locks i am interested in ar ethe ones that show up in SQL EM. ie where a process is blocking another process(es).
I have tried New Alert | lock requests /sec but that is a huge figure when the server is running OK, so i cant guess what value to set for the counter. Is there a better object/counter i should use?
thanks
fatherjack
View 5 Replies
View Related
Sep 4, 2006
Hi Guys,
I have written quite a big stored procedure which creates a temporary table (multi-session) and updates it. All the statements are encapsulated in a single transaction which is explicitly declared in the code. What happens is that a lock is being put by the server on that table (of type Sch-M) in order thus preventing any type of operations on it (including simple select)
Now, I want to be able read that table from within another transaction. Why is that I cannot use a table hint NOLOCK in the select statement?
Here is some code which reproduces my problem.
Query A:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN TR_DEMO;
CREATE TABLE ##TBL1(
Oidx int not null primary key identity(1,1),
Name nvarchar(30) not null,
Type char(1) not null
);
INSERT ##TBL1 (Name,Type) VALUES ('Car','M');
WAITFOR DELAY '00:00:10';
INSERT ##TBL1 (Name,Type) VALUES ('Plane','M');
WAITFOR DELAY '00:00:10';
INSERT ##TBL1 (Name,Type) VALUES('Submarine','M');
WAITFOR DELAY '00:00:10';
DELETE FROM ##TBL1;
DROP TABLE ##TBL1;
COMMIT TRAN TR_DEMO;
Query B:
SELECT TOP 1 * FROM ##TBL1 (NOLOCK) ORDER BY oidx DESC;
Launch query A and then execute query B.
Thanks a lot for your help.
View 2 Replies
View Related
