Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

TempDB Issue

My ASP.NET application requires me to create various temporary tables in TEMPDB. There is a problem that I am facing for which I need a solution badly.

There is a class which creates a temporary table in TEMPDB.
There is another class which needs to use this temporary table.

The issue is once the first class is exited or the connection to the database is closed, the temporary table vanishes and there is a runtime error thrown by the second class as it cannot find the temporary table.

Is there any way I can persist the tables in TEMPDB for subsequent operations?

Any help or clue in this regard would be a major help

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Tempdb Performance Issue
My Sql 2005 SP1 has performance trouble this afternoon. I
noticed a lot PageLatch_up and PageLatch_sh waiting with 2:1:1. I think the
problem may be caused by the tempdb. There are more than 1000 temp tables. The number
of user connections is abnormally high. “SELECT *
FROM tempdb.dbo.sysobjects� cannot be finished due to blocking.

I restarted the server to fix
the problem temporaryly.

Any clue to fix the problem? Does it help to replace temp
table with table variable in stored procedures? “Drop temp table� are called in
procedures, why the temp tables are still in the tempdb?


Thanks for your help.

View Replies !   View Related
Space Issue In TempDB
I have table with 40 MB and have 73 MB free space available in Log file and and 1.2 GB in TempDB. Both have set up 10% autogrow.
I'm trying to update a column but getting the below error. I saw many people has posted this request but coulnd't find valuable solution.
Hopefully our DBjournel Exprts give some valubale inputs.

Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.


View Replies !   View Related
Permission Issue With Tempdb Works Fine In SQL2000 But Not SQL2005
the following SQL works fine in SQL2000 but gets a permissions error when run on SQL2005:


IF not exists (SELECT * FROM tempdb.dbo.sysindexes WHERE NAME = 'PK_tblGuidContractMove')


 IF @DEBUG = 1 PRINT 'airsp_CopyContracts.PK_tblGuidContractMove'



EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidDestination GUID NOT NULL')









The user permissions are set the same in both 2000 and 2005 can you please explain what changed and what are the minimum permissions need for the user to be able to make these changes to the temporary table which the user created.

View Replies !   View Related
Tempdb Is Skipped. You Cannot Run A Query That Requires Tempdb
Has anyone seen the SQL Server error:

"tempdb is skipped. You cannot run a query that requires tempdb"?

We're running a .Net web application with a SQL Server 2000 backend, and we get the error intermittently. Restarting the SQL Server service seems to fix it, as it causes tempdb to be rebuilt, but this isn't a long term solution. Any direction or hints would be greatly appreciated. Thanks!
- Mike

View Replies !   View Related
SSIS Issue Data Type Issue While Loading From Oracle To SQL

I have SSIS package which is extracting data from oracle. I am using always use defualt code page =true
and Defualt code page value=1252.
While transferring values in to target table the package is success but when I check the decimal values
For example  I have 488.9602 or 56.908 values in oracle table but  when they come to SQL server they are rounded as 488.0000 or 56.000.
How can I populate the correct values from source to target
The target table has data type decimal(26,4).
Also when I check in the preview of source table I am able to see correct values. This is happening during the transfer only.

View Replies !   View Related
About TempDB
TempDB is one of the databases equipped with MSSQL Server by default.What is the purpose of it?Why do we use this temp database?

View Replies !   View Related
Tempdb In RAM?
Is it a good thing to do? what are the cons? Are there any risks? (this isan ISP database running 24 hrs) I have sql 6.5 on Win NT with 256 MB Ram (64MB reserved for SQL Server). tempdb size is 10 MB. Currently i'mexperiencing slow response for large queries and sometimes users haveproblems logging on the internet (authentication stops).thx

View Replies !   View Related
I have shut down the SQL agent, rebooted the box and still my TEMPDBis at 4 GB plus. Is there any way to shrink it another way?

View Replies !   View Related
About Tempdb

Being new to SQL server I would like some information regarding the tempdb database.

I user SQL Server 2000.

At present I appear to be backing up my tempdb but it has been failing since way back. It gives me this error when trying to bakup the database:

Executed as user: NT AUTHORITYSYSTEM. Backup and restore operations are not allowed on database tempdb. [SQLSTATE 42000] (Error 3147) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Can someone give me a basic grounding as to what the tempdb is used for and should I back it up or not?

Thanks in advance


View Replies !   View Related
My db tempdb is very big (55 Go), how can i reduce it or erase it and why it's so big ?

View Replies !   View Related
Tempdb In RAM
We are using Peoplesoft HRMS version 7.0 on NT Server 4.0 and SQL Server 6.5, service packs on both up-to-date.

The server is a HP LX Pro with 4 200 MHz 1MB cache processors with 2,560 MB of RAM and 18 9GB drives configured with different combinations of RAID 1, 1+0, and 5.

The database is 2.3GB in actual size with the specific size of 6GB for data and 2GB for log on separate RAIDs.

We would like to use tempdb in RAM to boost the speed of complex queries that use the tempdb for intermediate working tables.

We would like to know of any experience of running tempdb in RAM, issues to overcome, of how to setup.

There are articles and books not recommending using tempdb in RAM. Comments anyone?

Note: Have looked in SQL Server DBA Survival Guide, and SQL Server Unleashed, along with the normal MS Documentation, technet, online books, and knowledge base searches.

View Replies !   View Related
TempDb In RAM

When should I put tempdb in RAM and when should I not ?

Thanks in advance


View Replies !   View Related
Tempdb In RAM
System: Pentium II - Dual 450. 13 GIG RAID. 128 MB RAM.

Everything I've ever heard or seen says not to put tempDB in RAM, citing reasons of negligible benefit or even worse performance as it steals memory from SQL's cache memory.

My issue is I need to take anywhere from 500 to 3000 records (games played) in a batch every five minutes from a table, ordered by game id. There's no guarantee of sequential order to how the records are inserted, so I need to do an ORDER BY in my query. I also do a stored procedure for ranking that requires an ORDER BY.

The performance difference between the two is staggering. 1000 records with a 200 mb tempdb database (not in RAM) takes about 300 seconds. With a 16 MB tempdb database IN ram, it takes just under 127 seconds. Only problem? I'm constantly getting, after a few batches have run, "out of space in syslogs for tempdb ram messages. "

Is there a way to net such performance from tempdb without putting it in RAM, and if not, is there a way to explicity clear out tempdb. Thanks for any help.

View Replies !   View Related

I moved my tempdb (2MB) to RAM and restarted the server. It did restart but when I tried to connect thru ISQL, it gave me an ERROR -

A connection could not be established to <server-name> - DB-Library. Unable to Connect.

What could be the possible reason.

To move tempdb to RAM, do i just have to change the values of the MEMORY and TEMPDB IN RAM parms and restart or do I have to do anything else.

Please help.


View Replies !   View Related
Tempdb In Ram Not &#34;going Away&#34;
I am trying to configure a 6.5 server to set the tempdb to run off disk. I reset the tempdb in ram = 0 in the configuration, and restarted the service, but it left it as running in ram, with 0 configured. I then rebooted the server, and it still left the tempdb in ram. Any ideas?

View Replies !   View Related
Hi! How can I change the default physical location of the tempdb.
In the setup , you will only be prompted the directory for the software
and the directory for the data.

In sq6.5, you can move the tempdb out of the master device by putting it to
ram and changing the default device. But in sql7.0 it is separated from
the master but the physical device is located the same as the other data files.

Appreciate any inputs..

View Replies !   View Related
I read an article on this site by Michael Hotek re "Basic SQL Server 6.5 Configuration Options". In the paragraph about TempDB he says that you should always avoid using Temp tables in stored procs. I use this feature a lot when trying to do "not in" type queries (I filter out a portion of a larger table and then use the "not in" on the temp table rather than the entire table.)
Is there a better way to run a Not in query. I have the table well indexed (i think) but it seems to do a full table scan if I use the entire table.

Any ideas???

View Replies !   View Related
Need Help Tempdb

I get a message:

Error : 933, Severity: 22, State: 1
Logical page 258 of the log encountered while retrieving highest timestamp in database 'tempdb' is not the last page of the log and we are not currently recovering that database.

I use sqlserver -T4022 to start my SQL Server since it will not start with out it. When I start sqlserver without the option, it tells me that

Error : 615, Severity: 21, State: 1
Unable to find database table id = 2, name = 'tempdb'.

I just want my SQL Server back. Please help!

Betty Lee

View Replies !   View Related
Hello All~

I have a problem with Tempdb database on SQL 6.5 server.....when i create a temporary table,
i block other users in other databases. Actually i run a stored procedure which creates a temporary table and then the procedure insets some values of a table from my test database. When the spored procedure is executed, users in other databases also get blocked and no new user will be allowd to log on to the server. When i check as to what the other users are executing, i find that they are also creting or droping a temp table.
So i will have to either kill my SP or let it finish and continue blocking other users.

As of now, i am not able to find a reason for that and i try to run the Stored procedure in offpeak hrs.

I have not played around much with Temp tables in SQL 6.5. Pl do suggest me as to what to do....

Some details you would be interested on :

Server - SQL Server 6.5
Front End - Great Plains
Size of Tempdb - Data Space Available( It says shared with data)
database_name database_size unallocated space
------------------------------ ------------------ ------------------
tempdb 1502.00 MB -6301.82 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
7991108 KB 7996222 KB 72 KB -5186 KB

Hope this information helps.

Thanks in Advance...


View Replies !   View Related
Another Tempdb ?
I have a dumb ?
When I go in to expand the tempdb to at least 25mb req. for the migration
It is actually expanding the Log size & the Db available size still says 1.76mb.

What am I doing wrong?

Thanx for your time
Rick C

View Replies !   View Related
Hi, our tempdb log keeps filling up and suggests we back it up. Can we double the size of the log file if we are using simple recovery?

View Replies !   View Related
Tempdb Way To Big
Our Tempdb.mdf file is 11 gigs. I have tried several things to shrink this but with no luck. Does anybody have a suggestion on how I can free up that space. I have tried to re-start Sql but that didn't do anything. I thought that there was a bug, if the files got above 4 gig that sql wouldn't clear them, but I could be wrong

I thought I could detach it, and attach a new file, but makes me nervous without knowing if that’s correct.

Thanks for the help

View Replies !   View Related
Hi All,

I am trying to get some information about tempdb database. I've tried BOL but I couldn't find a whole lot of info. I am trying to find out what size should tempdb be to not to cause problems. Also, I am trying to shrink tempdb by using shrink database option in EM, but it only shrinks the tempdb transaction log not the datafile, and I don't know why it is happening.


View Replies !   View Related
I need to move tempdb to another drive,also increase the size.Largest database is 15GB.Can anyone suggest the size and also the exact commands to move.Do I need to backup the databases before I do this task?If SP1 is not installed,will it be o.k for me for this tempdb problem.If we have a larger tempdb like 4GB,will it effect anything?...Urgent!!

View Replies !   View Related
TEMPDB in one of our production servers does not clear up so every three to four weeks I have to restart NT. Nothing like this happens on any of the other three servers. Does anybody know where I should look at to correct the problem. I sure would appreciate it.

View Replies !   View Related
My company uses a CRM application on SQL Server. We recently performed some upgrades of the CRM application that required us to move from SQL 6.5 to SQL 7.0. In our stored procedures we make extensive use of temporary tables. Our performance has slowed a lot since the upgrade. When we used SQL 6.5 we placed the TEMPDB in RAM to increase performance. According to Microsoft, this option is no longer available because of SQL's self-tuning ability. It would seem obvious that having the TEMPDB on disk is going to be much slower than having it in memory. I was wondering if anyone else is in the same boat, and what you did to speed up SQL Server?

Thanks for any help or suggestions,


View Replies !   View Related
The tempdb has grown to over 2 GB on our DB. There are no user tables or SP in it. Is there a way to clean up and are there any consequences to this?

View Replies !   View Related
Hi why I cannot backup the tempdb. Is this a normal or there is something that I am doing wrong.


View Replies !   View Related
We want to shrink the size of our TEMPDB , Does anyone know how to without blowing it away and re creating it.

Also, our tempdb expands to occupy all available space on the server, the space is not released until the server is restarted.

Any ideas would be appreciated.

View Replies !   View Related
I have never done this before and thought I would ask. Is it possible to detach the tempdb database, move it to another drive or partition, and then re-attach it? What would be the downside or side-affects to doing such a thing?

Thanks. JT

View Replies !   View Related
Tempdb Help

THis is sql server 6.5 question.
I have tempdb data device size default 2 MB, which has completely filled up. I am trying to expand data device to it.
I created new device tempdb_data_ext (250 MB) and tried to expand tempdb data device. But everytime I do it, it ends up adding space to tempdb log device. How can I expand tempdb data device?

It's extremely urgent.


View Replies !   View Related
How can I control the growth of tempdb in SQl server.It's growing like anything.
CAn I create some alerts or jobs and what those alerts/job are supposed to do?
All help appreciated.

View Replies !   View Related
This is error message I discovered in NT even viewer:
c:MSSQL7DATATEMPDB.MDF: Operating system error 112(There is not enough space on the disk.) encountered.

In SQL Server error log the errors are:
Error: 1101, Severity: 17, State: 10

Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth..

Currently tempdb rezides on C drive and it's almost out of space.
What should I do?
Detach tempdb and then move to different drive?
What's the procedure?



View Replies !   View Related
is it ok to move the this from the c drive to another drive?

View Replies !   View Related
Hi All
if temdb grows and use all the disk space and then after log backup or other clean up process its size got reduced but it is still holding the space it was grown upto. In this case what would be ideal to do restart server/services or is it possible to reduce the space alocated to tempdb through properties.

any help will be appreciated.


View Replies !   View Related
Help With Tempdb
So i've been delegated to be a SQL Server DBA now (which I am perfectly content with), but im running into some issues and I am not sure how to proceed.

To make a long story short:

-installed a new server with SQL Server 2005 SP1. Has two databases on it.
-(2) arrays: 1st array is RAID 1 with some of the database and t-logs on it (I know..working to fix): 2nd array I just moved the tempdb.mdf file and tempdb.ldf files over this morning.

I was getting horrible I/O performance (to be expected) which prompted me to put in some more drives and move the tempdb files. This helped a bit, but now I am noticing some very funky things.

Over the last hour, i have seen my tempdb.mdf file grow from 10mb to almost 90gigs. I've been watching the performance on this machine, and the array with the tempdb is really seeing a lot of writes to it.

I am very very new to SQL server. Does anyone have some suggestions on profiiling and tracing I can do to see what is going on? Something in the Query analyzer?

I really appreciate it very much. This is new territory for me.


View Replies !   View Related
For a variety of reasons, the C drive on our Production Server is low on disk space. When we ordered the server it came installed with SQL Server 2005 and they defaulted the System databases to the C drive. All our client dbs are on the F drive. The tempdb database gets fairly large every day and consumes a large portion of what is left of the C drive. Every evening, after work hours, I have to stop SQL Server, delete the tempdb.mdf and ldf files and restart. Is there a way to move the default location of the tempdb files to the F drive? I do not want to repartition the hard drive or uninstall and reinstall SQL Server.

Thanks in advance,


View Replies !   View Related
Hi Experts,

Whats is the best Disk raid for TempDB? Will the performance of the database will go slow if I move it to a slower disk like RAID 5 with 5000 RPM?

Thanks in Advance

Don't sit back because of failure. It will come back to check if you still available. -- Binu

View Replies !   View Related
Is TempDB Used?
Hello, hopefully this is not a redundant question...

Our software uses a SQL database of it's own, and a scanner will run to collect file information on folders specified.  This will get posted to the SQL DB using ODBC calls to insert into the database. 

My question is if at any time (probablly more in general), does any inforation get posted to the TempDB before it makes it to the actual database.  If not, what is the TempDB used for?

View Replies !   View Related
Hi all,
Is 'TempDB in RAM' still available in 2005?

View Replies !   View Related

If your tempdb is on a drive that crashes, is it possible to recover by moving/re-creating the tempdb on another drive?


This would be SQL2000

View Replies !   View Related
Locking On Tempdb...
Hi Group,I am facing a problem regarding locking. I have created a StoredProcedure in my Database. In this Stored Procedure Temprary Tables getcreated and after that values are inserted in these tables.But thisStored Procedure is called from Java Portal. When then Stored Procedureexecute from Java end new transaction begins. So all these temporarytables are created in tempDB and locking the entire Database.So at the same time if another user fires the same Stored Procedurefrom portal end its give the error "Lock time out". But if the prevStored Procedure execution gets competed before this request then noerror comes.So is there any way from database to restrict to lock the tempDBdatabase????Can you provide some valuable advice?Thanks in advance.RegardsArijit Chatterjee

View Replies !   View Related
Many Tempdb Locks?
hi all,we have a SQL2000SP3 runing in W2K3. The application is JDEdwards.recently I've observed that once in a while (about a few hours), therewould be a process from the ERP application that holds quite a numberof extent locks in tempdb, can be as high as 10000 locks. when I runsp_lock on that spid, it gives something like this:697200EXT1:156760 XGRANT697200EXT1:94896 XGRANT697200EXT1:132224 XGRANT697200EXT1:140488 XGRANT697200EXT1:181552 XGRANT697800DB SGRANT697200EXT1:165280 XGRANT697200EXT1:127888 XGRANT697200EXT1:173544 XGRANT697200EXT1:152624 XGRANT697200EXT1:160888 XGRANT697200EXT1:144616 XGRANT697200EXT1:198336 XGRANT697200EXT1:107296 XGRANT697200EXT1:99176 XGRANT697200EXT1:169344 XGRANT697200EXT1:115704 XGRANTI am wondering what action is it doing, creating temp tables?? manythanks.

View Replies !   View Related
Transaction Log And Tempdb
Hello,I have questions about SQL Server and I hope you could help me a lot.- Is it possible to reduce the size of my transaction log file duringthe execution of a sql script ? Indeed, my sql script work with verylarge table and my transaction log file use all the space on my harddrive. so the script have to stop with an error.- Is it possible to limit the size of the database tempdb ? I have ananother script sql who do an insert from a select with join (joinbetween 2 table of more that 24 Go) and group by. During the executionof that script, SQL Server freeze, it doesn't progress anymore, and Ihave to stop my process. So that is it possible that script works witha tempdb database not larger than 24 GO per exemple ?Thanks you in advance for your adviceK.

View Replies !   View Related
Transaction Log And Tempdb
Hello,I have questions about SQL Server and I hope you could help me a lot.- Is it possible to reduce the size of my transaction log file duringthe execution of a sql script ? Indeed, my sql script work with verylarge table and my transaction log file use all the space on my harddrive. so the script have to stop with an error.- Is it possible to limit the size of the database tempdb ? I have ananother script sql who do an insert from a select with join (joinbetween 2 table of more that 24 Go) and group by. During the executionof that script, SQL Server freeze, it doesn't progress anymore, and Ihave to stop my process. So that is it possible that script works witha tempdb database not larger than 24 GO per exemple ?Thanks you in advance for your adviceK.

View Replies !   View Related
Transaction Log And Tempdb
HelloI have questions about how works transaction log et the databasetempdb in SQL Server and I hop you could help me- Is it possible to reduce the size of the transaction log fil duringan execution ? Indeed, I have a script inserting a very large quantityof data (many Go) and during that process my transaction log file useall the space avaible on my hard drive. Is there any way to solve thatproblem ?- Is it possible to limit the size of the database tempdb ? I have ananother script inserting data using a select joinning 2 tables ofabout 20 Go with group by. If I execute that script sql server seemsto freeze and I must kill the process. What can I do ? Is the onlysolution is that I must make more avaible space on my hard drive ?Thanks in advance for your answers.K.

View Replies !   View Related
TempDB Won't Shrink
I was able to find a few posts on this topic, but none of them quiteseemed to fit the situation, so I'm hoping that someone else might beable to help me here.I have a client who is using SQL 2005 (sorry, don't have the exactbuild with me). They run a weekly process which causes TempDB to growto over 100GB before it fails due to a full disk. Once it's grown tothat size we can't seem to shrink it again short of restarting theserver.The database is set to Simple recovery mode and I believe that it isset to auto shrink.Here are some things that found out/tried:DBCC SHRINKFILE (tempdev, 50000) does nothing.DBCC OPENTRAN returns no transactions.If I look in TempDB for any temporary tables, I get a couple dozen.They all have zero rows in them though. I didn't think to look at thecolumns that they contain, but maybe that will give me an indicationof their use. I used SELECT OBJECT_NAME(id), rowcnt FROMtempdb..sysindexes WHERE OBJECT_NAME(id) LIKE '#%'sp_spaceused shows that almost none of the space is actually beingused.I've looked for reindexing operations in their code and didn't seeany, but there's quite a bit of code there. While there are someoperations against some very large tables, I didn't see any obviouscartesian products or sorts either. Again though, there's a lot ofcode and I haven't profiled much of it.My plan right now is to reboot, set up a trace to track both filegrowths and SQL statements and then see if I can find whichstatement(s) are causing the TempDB to grow to be so large. Anysuggestions on additional things to track? Even given this though, Idon't know if that will help me with the shrinking issue, except topossibly prevent it from being necessary in the future.Any advice or suggestions welcome. Please let me know if I've left outany important information. I always seem to forget at least oneobviously important bit of information. :-)Thanks!-Tom.

View Replies !   View Related
Is Tempdb Big Enough For What I'm Doing, Plus Other Questions.
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 forthese types of answers when necessary?1. How do I know, when executing a query from Query Analyzer orotherwise, how many temporary tables will be needed, and how big theywill be?2. Where will those temporary tables be created? Always in tempdb?3. If part of the estimated execution plan in Query Analyzer involves a"Hash Match/Inner Join" with an estimated row count of 5 million, howmany tables will be created along the way?4. What happens if tempdb doesn't have enough space to create atemporary table in its entirety?5. etc.Those are just examples. I guess what I need is a good book onquery/script optimization, including selects, deletes, updates, etc,that I can reference that will describe to me what is going oninternally when certain execution plans are executed.Not only how to write optimized queries (use SARGs, etc), but why towrite a query one way over another.Second, my current question regards an UPDATE I'm trying to get to runin a reasonable amount of time.It has to update 11 million rows of a 175 million row table. I did themath, and with each record containing 220 bytes of data, a temporarytable of all the rows would take up 33+ gigs. If, by chance, such anupdate creates a temporary table with the full 175 million rows, whathappens if the partition tempdb.mdf resides on is only 28 gig in size,etc?I hope that made some sense, and thanks.Warren WrightDallas*** Sent via Developersdex ***Don't just participate in USENET...get rewarded for it!

View Replies !   View Related

Copyright © 2005-08, All rights reserved