473,721 Members | 1,769 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Locking on Tempdb...

Hi Group,
I am facing a problem regarding locking. I have created a Stored
Procedure in my Database. In this Stored Procedure Temprary Tables get
created and after that values are inserted in these tables.But this
Stored Procedure is called from Java Portal. When then Stored Procedure
execute from Java end new transaction begins. So all these temporary
tables are created in tempDB and locking the entire Database.

So at the same time if another user fires the same Stored Procedure
from portal end its give the error "Lock time out". But if the prev
Stored Procedure execution gets competed before this request then no
error comes.

So is there any way from database to restrict to lock the tempDB
database????
Can you provide some valuable advice?

Thanks in advance.
Regards
Arijit Chatterjee

Jul 23 '05 #1
8 12776
(ar************ *****@yahoo.co. in) writes:
I am facing a problem regarding locking. I have created a Stored
Procedure in my Database. In this Stored Procedure Temprary Tables get
created and after that values are inserted in these tables.But this
Stored Procedure is called from Java Portal. When then Stored Procedure
execute from Java end new transaction begins. So all these temporary
tables are created in tempDB and locking the entire Database.

So at the same time if another user fires the same Stored Procedure
from portal end its give the error "Lock time out". But if the prev
Stored Procedure execution gets competed before this request then no
error comes.

So is there any way from database to restrict to lock the tempDB
database????
Can you provide some valuable advice?


This sounds strange. Unless you are running SQL 6.5 that is. In 6.5
creating temp tables in transactions would block any other process
that tried to create a temp table.

But in SQL 2000, this should not happen, unless there is something
more to it. You should examine exactly what the blocked processes are
waiting for. I have a tool on my web site, that can help you do this,
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Hi Erland,
Thanks for your reply. Is there any way to prevent the tempdb for
making any locks on any objects.Thanks in advance. I am trying to do
R&D with the support of your product.
Regards
Arijit Chatterjee

Jul 23 '05 #3
Hi Group,
Continuation of the Problem...But I am trying to highlit is different
manner..Using Sqlserver 2000 with Service pack 3
After login When I am going to Current Activities under Management and
doing refresh it is fine.
But from my Database end under Query Analyzer I am typing these lines

begin tran t1
create table #t1 (a int)
insert into #t1 values(1)
---------But not commiting still now..
And again I am going to Current Activities under Management and doing
refresh but now it is telling
Error 1222: Lock request time out period exceeded.

Now plese guide me how to solve this..without commiting the
transaction..I can say this transaction process can take 1 hour also..
That is why it has not commited still now.. That means on that time no
one can interact with the tempDB?
Any advice is most welcome..Thanks in advance.
Regards
Arijit Chatterjee

Jul 23 '05 #4
(ar************ *****@yahoo.co. in) writes:
Continuation of the Problem...But I am trying to highlit is different
manner..Using Sqlserver 2000 with Service pack 3
After login When I am going to Current Activities under Management and
doing refresh it is fine.
But from my Database end under Query Analyzer I am typing these lines

begin tran t1
create table #t1 (a int)
insert into #t1 values(1)
---------But not commiting still now..
And again I am going to Current Activities under Management and doing
refresh but now it is telling
Error 1222: Lock request time out period exceeded.
Don't use Current Activity. It tries to look up the name of the temp
table, but uses READ COMMITTED isolation level to do this, so it gets
blocked. If you use my aba_lockinfo you will not get blocked, because
I read the system tables with READ UNCOMMITTED. (Dirty reads, which in
this special case is the right level.)
Now plese guide me how to solve this..without commiting the
transaction..I can say this transaction process can take 1 hour also..
That is why it has not commited still now.. That means on that time no
one can interact with the tempDB?


That's a foregone conclusion. Special operations like Current Activity
will block. Normal usage will not.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
Hi Erland ,
Thanks for your reply.I run your aba_lockinfo and getting the response
for there three columns as below.
Lock_Type,Owner _Type,Object

aba_lockinfo 0,0,0--Running this

S Sess NV_DAILY
Sch-M Xact tempdb.dbo.#4AC 307E8
Sch-M Xact tempdb.dbo.#inp utbuffer_______ _____0001000000 3D
Sch-M Xact tempdb.dbo.#obj ects___________ _____0001000000 3D
Sch-M Xact tempdb.dbo.#res ult____________ _00010000003D
Sch-M Xact tempdb.dbo.#t1_ _______________ _____0000000000 3D
Sch-M Xact tempdb.dbo.DF__ #result__last__ 4F87BD05
Sch-M Xact tempdb.dbo.PK__ #result__4E9398 CC
IX Xact tempdb.dbo.sysc olumns
X Xact tempdb.dbo.sysc olumns.ncsyscol umns
X Xact tempdb.dbo.sysc olumns.syscolum ns
IX Xact tempdb.dbo.sysc omments
X Xact tempdb.dbo.sysc omments.syscomm ents
IX Xact tempdb.dbo.sysd epends
IX Xact tempdb.dbo.sysi ndexes
X Xact tempdb.dbo.sysi ndexes.sysindex es
IX Xact tempdb.dbo.syso bjects
X Xact tempdb.dbo.syso bjects.ncsysobj ects
X Xact tempdb.dbo.syso bjects.ncsysobj ects2
X Xact tempdb.dbo.syso bjects.sysobjec ts
IX Xact tempdb.dbo.sysp ermissions
IX Xact tempdb.dbo.sysp roperties
Sch-M Xact tempdb.dbo.UQ__ #objects__52642 9B0
X Xact tempdb.MISSING?
X Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
IX Xact tempdb.MISSING?
X Xact tempdb.MISSING?
X Xact tempdb.MISSING?
X Xact tempdb.MISSING?

NV_DAILY is my Database.And after that the tempdb can't create any
#table on the same name until that procedure commits the
transaction.Any advice is acceptable.Than ks in advance.
Regards
Arijit Chatterjee

Jul 23 '05 #6
Hi Erland ,
Thanks for your reply.I run your aba_lockinfo and getting the response
for there three columns as below.
Lock_Type,Owner _Type,Object

aba_lockinfo 0,0,0--Running this

S Sess NV_DAILY
Sch-M Xact tempdb.dbo.#4AC 307E8
Sch-M Xact tempdb.dbo.#inp utbuffer_______ _____0001000000 3D
Sch-M Xact tempdb.dbo.#obj ects___________ _____0001000000 3D
Sch-M Xact tempdb.dbo.#res ult____________ _00010000003D
Sch-M Xact tempdb.dbo.#t1_ _______________ _____0000000000 3D
Sch-M Xact tempdb.dbo.DF__ #result__last__ 4F87BD05
Sch-M Xact tempdb.dbo.PK__ #result__4E9398 CC
IX Xact tempdb.dbo.sysc olumns
X Xact tempdb.dbo.sysc olumns.ncsyscol umns
X Xact tempdb.dbo.sysc olumns.syscolum ns
IX Xact tempdb.dbo.sysc omments
X Xact tempdb.dbo.sysc omments.syscomm ents
IX Xact tempdb.dbo.sysd epends
IX Xact tempdb.dbo.sysi ndexes
X Xact tempdb.dbo.sysi ndexes.sysindex es
IX Xact tempdb.dbo.syso bjects
X Xact tempdb.dbo.syso bjects.ncsysobj ects
X Xact tempdb.dbo.syso bjects.ncsysobj ects2
X Xact tempdb.dbo.syso bjects.sysobjec ts
IX Xact tempdb.dbo.sysp ermissions
IX Xact tempdb.dbo.sysp roperties
Sch-M Xact tempdb.dbo.UQ__ #objects__52642 9B0
X Xact tempdb.MISSING?
X Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
IX Xact tempdb.MISSING?
X Xact tempdb.MISSING?
X Xact tempdb.MISSING?
X Xact tempdb.MISSING?

NV_DAILY is my Database.And after that the tempdb can't create any
#table on the same name until that procedure commits the
transaction.Any advice is acceptable.Than ks in advance.
Regards
Arijit Chatterjee

Jul 23 '05 #7
(ar************ *****@yahoo.co. in) writes:
NV_DAILY is my Database.And after that the tempdb can't create any
#table on the same name until that procedure commits the
transaction.Any advice is acceptable.Than ks in advance.


Try this:

BEGIN TRANSACTION
CREATE TABLE #temp(a int NOT NULL PRIMARY KEY)

Run from two windows. This does not block on the machine I tested.

I'm sorry, but you will have to examine more in detail, why you get
blocking. Maybe there is something special in your code that gives you
blocking. But I can't sit and guess from a distance what may be.

You could post the entire output from aba_lockinfo. Please put in an
attachment, so that the lines don't get wrapped.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8
Thanks Erland ,
Thanks for your response.
Reg
Arijit Chatterjee

Jul 23 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
6515
by: New DB Admin | last post by:
Is it a good thing to do? what are the cons? Are there any risks? (this is an ISP database running 24 hrs) I have sql 6.5 on Win NT with 256 MB Ram (64 MB reserved for SQL Server). tempdb size is 10 MB. Currently i'm experiencing slow response for large queries and sometimes users have problems logging on the internet (authentication stops). thx
10
13271
by: Jay Chan | last post by:
I keep getting the following error message when I run a serie of SQL commands: Server: Msg 9002, Level 17, State 6, Line 15 The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space. I have tried "dump transaction tempdb with no_log" right before I run the SQL command. But that doesn't help.
3
3660
by: Deaconess | last post by:
I have shut down the SQL agent, rebooted the box and still my TEMPDB is at 4 GB plus. Is there any way to shrink it another way?
3
8291
by: tom horner | last post by:
Something strange is happening to our SQL Server DB (2000). The tempdb transaction log file continues to grow (quite slowly) for no apparent reason. We have it in simple mode, and I have tried a manual checkpoint command and manual shrink (of the log file only). There are no unusual SQL's (large or small) going on. A "heavy hitter" would make it grow fast, not 10 MB every 30 minutes or so. This server has been in production for over a...
2
3728
by: Warren Wright | last post by:
Hi All, First, where can I get some questions of this sort answered? Preferably, are there good books or online guides that I can consult for these types of answers when necessary? 1. How do I know, when executing a query from Query Analyzer or otherwise, how many temporary tables will be needed, and how big they will be? 2. Where will those temporary tables be created? Always in tempdb?
2
3895
by: Tom | last post by:
I received an error that the log in tempdb was full, but the log and data segments are set to automatically grow with no limit AND there is plenty of available space on the disk. So I don't understand why this error occurred. Does anyone have any ideas? Thanks, Tom
3
6677
by: New MSSQL DBA | last post by:
has anyone met with this before? the setting is SQL2K with SP3 on a 2 node active-active W2K3 cluster. on one of the machine, it occasionally prompts for the following error: "The log file for database "tempdb" is full. Back up the transaction log for the database to free up some log space." the problem is, at the time of error, the tempdb tx log is only 200MB and there are over 50G disk space available.
1
7227
by: SQL Server | last post by:
Hi, The tempdb file on one of our servers grew very large and used all available disk space. This is SQL Server 2000 SP4. I have installed hotfix version 8.00.2187. I opened a profiler trace but can't still get to the root of the problem. Any help will be appreciated. Egbon *** Sent via Developersdex http://www.developersdex.com ***
2
12717
by: Thomas R. Hummel | last post by:
I was able to find a few posts on this topic, but none of them quite seemed to fit the situation, so I'm hoping that someone else might be able to help me here. I have a client who is using SQL 2005 (sorry, don't have the exact build with me). They run a weekly process which causes TempDB to grow to over 100GB before it fails due to a full disk. Once it's grown to that size we can't seem to shrink it again short of restarting the server.
0
8837
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8728
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9214
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9059
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6668
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5977
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4484
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4751
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2129
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.