I am planning a fresh installation of SQL Server 2005 on a new machine, which I have to order. I know that tempdb tuning is very important to the overall performance of the SQL Server instance. I've read that it's best practice to create as many tempdb files as you have CPU's (or cores?). Is that correct? Are there any other recommendat...
We are having some issues with our production SQL Server. Server: Dual Quad Core Xeon 8 GB RAM Single RAID 10 Array Windows 2003 Server 64-bit SQL Server 2005 Standard 64-Bit There is about 250MB of free RAM on the machine right now. SQL Server has around 6GB of RAM, and our monitoring software says that only half of the SQL Server al...
Is it possible to have a 'persistent' temp table in MS-SQL? What I mean is that I currently have a background task which generates a global temp table, which is used by a variety of other tasks (which is why I made it global). Unfortunately if the table becomes unused, it gets deleted by SQL automatically - this is gracefully handled b...
We have a SQL Server 2000 production environment where suddenly (ie. the last 3 days) something has caused the tempdb data file to grow very large (45 gigs with a database which is only 10 gigs). Yesterday, after it happened again we shrank the database and ran the major batch processes individually without any problems. However, this mo...
On a SQL Server 2000 system, I have a templog.ldf file that seems to grow without bound! But when I check, there are never any open transaction in the tempdb (using DBCC OPENTRAN), nor do I ever use explicit transactions within tempdb. I do, however, use temp tables within my stored procedures. And who knows what ASP.NET data access obj...
I am trying to understand the tempDB and following are the doubts popping in my mind. What is the lifetime of data in tempDB? Say a query is doing some Order By and uses tempDB for performing that. After this query finishes, someone else also executes a query which utilizes the tempDB. Will the second query find records written by firs...
I have a report which on execution connects to the database with my_report_user username. There can be many end-users of the report. And in each execution a new connection to the database will be made with my_report_user (there is no connection pooling) I have a result set which I think can just be created once (may be on the first run ...
Hi GUys, I am facing a serious issue in my production server where the temp DB grow exponantialy. Is there any way we can recover the tempDB space without restarting the SQL service? Cheers Kannan. ...
I have huge query on my SQL 2005 Server. This have to be run once everyday but when this query runs temp db grows from 2GB to 48GB. What is the best way top optimize or find the reason why tempdb is growing when this query adds/updates on 80K records with (~120 columns) on a single table? What should I do on this query that tempdb would...
Hello! Our application runs alongside another application on a customers machine. We have put some efforts regarding avoiding long-running locks in tempdb since this obviously affects concurrency badly. The other application, however does things like: begin transaction create #Table(...); insert into #Table(....) values(...
Does the recovery model of a database effect the size of the tempdb.mdf? We have a database which involves a lot of processing and bulk inserts. We are having problems with the tempdb file growing to extremely large sizes (over 70 gb). The database in question is set to Full Recovery. Will changing it to Simple Recovery(on the database ...
Is there an expected size of tempDB for a Data Warehouse application. Is 10 gigs excessive? It's hit by frequent large queries. The Data Warehouse itself is 50 gigs. I'm using SQL Server 2000 ...
Hi, Im working with Sybase central - Adaptive Server Anywhere 9 and I want some examples in the use of de tempDB. How , for example can I insert some records in a temporary table ? I tried for example: select * into TempDB.dba.#testing from testTable but I got the followiing error: Syntax error near '.' on line 1 ...
We have an analytics database server running SQL 2008 R2 with 192 GB of physical RAM. However, we're noticing that during analytics process, the server only uses around 70 GB, but tempdb swells to 300+ GB. The current Maximum Memory is set to 2147483647 MB. The server is running Windows 2008 R2. Is there a way to make SQL Server use more...