TempDB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

TempDB

Author

Topic

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-16 : 15:26:05

I can't access my TempDB
When I run DBCC OPENTRAN('TempDB') to see any Open Transactions. I see the below erros and when I try to kill the SPID , it gets killed
and the same Stored Proc which is running with the Process ID 216 will run with a diff Process ID.

What do we do with this??

Transaction information for database 'tempdb'.

Oldest active transaction:
SPID (server process ID) : 216
UID (user ID) : 2
Name : implicit_transaction
LSN : (2333:395114:102)
Start time : Jul 16 2008 6:30:06:027AM
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-16 : 15:26:50

What do you mean by "I can't access my TempDB"?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-16 : 15:29:38

My tempdb size is very large and I was trying to run the below Queries and these gets blocked by some Process ID ( If I kill that
process ID , immediately there is one more process that blocks it )

SELECT name
FROM tempdb..sysobjects

SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-16 : 15:31:16

I don't see why you'd want to run those queries to solve this issue.

What does the disk usage report show for the tempdb's data file?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-16 : 15:38:49

I am just showing these Qrys to show that I am not able to access the TempDB.

Here is my Problem, My tempDB size is 60 GB so I just wanted to see which objects were taking that kinda space so I ran the below Qry

SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC

It was running for a few minutes so I checked with SP_WHO2 and this process was bloced by some other other
process which was running a small SP which returns 25 rows in no time. So I went and killed that Stored Proc and then I see one more SPID blocs the above Qry and when I run the DBCC INPUTBUFFER() , I see the same stored Proc I just killed a few minutes back comming again with a diff SPID.

Hope I am clear in explaning it??

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-16 : 15:40:20

So I am in kind of cyclic process where the same SP comes up with diff SPID bloking anything I do on tempDB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-16 : 15:44:30

But is the data file full? You can view this information in the disk usage report which is why I asked that question in my last post.

Can't you run SQL Profiler to determine what is launching the stored procedure repeatedly?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-16 : 21:58:43

Thanks Tara,

I Guess this was happening because we have temp object open by one user and user never disconnect from the system.

That SP had some temp tables created and that were not dropped in the end of the Stored Proc ( I know it will be dropped once the SP
finished executiong ) but some times I guess its good to drop implicitly.

Thanks Again for your time.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-17 : 00:44:33

But even if a user remains connected to the system, the stored procedure should not just keep running.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-17 : 14:58:37

again I have the same Issue this morning on the Server.

This morning I wanted to check the size of the TempDB, so I just ran

Use TempDB
GO
sp_spaceused

This was running for ever. So I just ran SP_who2 and saw that
SPID 536 (running sp_spaceused) is blocked by 1360 which is running a stored Proc running usp_GetFxSymbols. So I killed 1360 and some other SPID 4178 came up running the same SP. I killed 4178 ..but then the same SP is comming with the Diferent SPID and I guess it got into some kind of loop.

How do I debug this.

Thx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-17 : 15:54:08

You should probably crack open the code for the stored procedures that you are seeing and see if it can be optimized.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-18 : 10:09:09

There is nothing in the stored Proc

This is a simple stored Proc returns a few rows in no time .
Here is the code of the stored Proc

CREATE PROC dbo.usp_CAGetCnumbesByBranchCode
@psPartitiondID VARCHAR(32)
AS
BEGIN

SET NOCOUNT ON

CREATE TABLE #BCE
(BranchCodeExternal VARCHAR(10)
)
INSERT INTO #BCE (BranchCodeExternal )
EXEC usp_CAResolveBranchCodeExternal @psPartitiondID

SELECT DISTINCT C.cNumberExternal
FROM cNumbers C , CTSBranches B, #BCE T
WHERE c.BranchCodeInternal = B.BranchCodeInternal
AND B.BranchCodeExternal = T.BranchCodeExternal

/*INNER JOIN cNumberAccountEntitlements E
ON E.cNumberInternal = C.cNumberInternal
INNER JOIN CTAccountMaster M
ON M.AccountNumberInternal = E.AccountNumberInternal
WHERE M.BranchCodeExternal = @psBranchCodeExternal*/

DROP TABLE #BCE

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-18 : 12:34:23

But what's in usp_CAResolveBranchCodeExternal?

Are the join columns indexed in the parent stored procedure?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-19 : 07:27:00

usp_CAResolveBranchCodeExternal is one more select statement and it returns in no time.

Also the parent SP jons has all the indexes and it returns 0 secs.
I guess it has some thing to do with the way the application connects and calls the stored Procs.

Thx
Venu

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-19 : 07:36:23

Tara,

Is there any way to find all the SPID's running a particular Stored Procedure from sysprocesses table

Thx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-19 : 08:51:39

I'd just use SQL Profiler to capture this as you can filter what you want.

Add SP:Completed to the trace and then filter on your stored procedure using %spName%.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-20 : 12:36:01

How many times/sec is usp_CAGetCnumbesByBranchCode called?
Can you post the code of usp_CAResolveBranchCodeExternal please? From what you've said, the temp table can probably be removed completely with a little bit of rewriting

Looks to me like you have a lot of connections and a lot of temp table usage. That would explain why TempDB is big.

--
Gail Shaw
SQL Server MVP

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-20 : 20:05:12

here is the code for the SP, which is a very simple SELECT statement
and returns the result set in no time. I guess it has nothing to do with the SP's its the way
the application is calling these SP's. I see around 13 to 15 spids running the same SP.

CREATE PROC dbo.usp_CAResolveBranchCodeExternal
@psPartitionID VARCHAR(32)
AS

SELECT BranchCodeExternal
FROM PARTITIONS
Where Partitionid = @psPartitionID

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-21 : 02:49:00

From what I can see, you should be able to eliminate the temp table completely. This *should* be equivalent to the version you posted with the temp table. It should run quicker because the unnecessary insert has been removed. It should also have less impact on TempDB

I say should, because I may have missed something and I don't have your db structure or data to test. Please test carefully




CREATE PROC dbo.usp_CAGetCnumbesByBranchCode 
@psPartitiondID VARCHAR(32)
AS
BEGIN

SET NOCOUNT ON

SELECT DISTINCT C.cNumberExternal
FROM cNumbers C INNER JOIN CTSBranches B ON c.BranchCodeInternal = B.BranchCodeInternal
 INNER JOIN PARTITIONS T ON B.BranchCodeExternal = T.BranchCodeExternal
WHERE Partitionid = @psPartitiondID

END

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-21 : 02:49:53

In addition, you may want to look through your stored procs to see if you have more constructs like this.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources