.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

TempDB used

Posted By:      Posted Date: October 01, 2010    Points: 0   Category :Sql Server

A while ago I have moved the SSRS services to another database and with that I created a new ReportServer database and the TempDB that goes with it. For a while I had these four databases on the same SQL Server:

  • ReportServer_oldServer
  • ReportServer_oldServerTempDB
  • ReportServer_current
  • ReportServer_currentTempDB
Ok, so I removed the first two because they weren't used anymore. That resulted in my subscriptions not working anymore, and it turned out that the subscriptions were still stored in the oldServerTempDB! 

In the Reporting Services Configuration Manager I can only select the database of the ReportServer, but not the TempDB. So how do I control which TempDB is used? Or how can I move/modify my subscriptions so that it points to the correct database?



View Complete Post

More Related Resource Links

SQL Server RAID 1 , 1+0 and TempDB

Hi, I am tuning a disk sub system for our OLTP datebase server these days. This is DAS disks. I now came into a question. The server has 8 148GB SAS drives. To have fault tolerance, we want to have RAID mirroring, so RAID 1 or 10. That leaves us half of the whole capacity, which is 4 * 148GB. As this box is only used for database, we want to have partitions for OS, data file, log file and temp db. Now I there is a dilemma: I know for best practise, temp db should on its own physical disk. If I set up four partition(logical drives), one for OS, one for data files, one for logs and one for temp db, then I can only have RAID 1 on each of the disk array. But the advantage of this is temp db is only its own disk. Or the second choice I can have is to partition the disks into three array (logical drives), namely OS, data files and logs. In this case I can have RAID 1+0 (striping and mirroring) on data file partition and RAID 1 on OS  and log. Could you give me some idea, which one is better? Any disscussions are welcome. Thank you very much.

TempDB High Value for mixed_extent_page_count

Hi all, I'm trying to get to the bottom of a size issue with a TempDB.  TempDB consists of two data files - an 8MB primary (no autogrowth), and a 9.5 GB secondary (set to grow no larger than 10 GB). The problem is that the available space, as reported by Perfmon ("Free Space in tempdb (KB)") and "unallocated_extent_page_count" from sys.dm_db_file_space_usage is 3383 MB.  This indicates that 6300 MB is being used inside TempDB. Examining sys.dm_db_file_space_usage reports the following figures for the secondary TempDB database (the first is neligible, being 8 MB): unallocated_extent_page_count: 433000 (3383 MB) version_store_reserved_page_count: 64 (0.5 MB) user_object_reserved_page_count: 64 (0.5 MB) internal_object_reserved_page_count: 320 (2.5 MB) mixed_extent_page_count: 812120 (6344 MB) So, obviously the space is being taken by mixed extents.  But what?  sys.dm_db_task_space_usage and sys.dm_db_session_space_usage do not show any space being used.  There are no objects with large number of pages in their indexes.  sp_spaceused reports 2 MB reserved (which makes sense, as it's derived from sys.dm_db_partition_stats). The only other clue I have is that the Version Store was continually growing, and filled TempDB, resulting in a Version Store shrink operation.   The version store size was up to 3891 MB, and the followin

Disable tempdb creation

Hello, I need to know how I can disable the creation of tempdb and not work with it at all. It seems to cause problems in my DB. Thanks.  

tempdb file locks

We have an application which has heavy usage of temp tables. I'm trying to sort out what kind of lock issues this can present. So I did a test where I created a temp table and then inserted 100.000 rows in it: declare @counter int     set @counter = 0     while @counter < 1000000     begin       set @counter = @counter + 1       insert into #tmp values('test','test','test',NULL,NULL)     end At the same time I executed sp_lock to see what happens. What I see is that an X lock of type FIL appears. So, this means that every insert creates a database file lock on the tempdb? Doesn't sound that good.    

[298] SQLServer Error: 9001, The log for database 'tempdb' is not available.

I have SQL server 2000 standard edition running on Windows 2003 server. I am getting this error [298] SQLServer Error: 9001, The log for database 'tempdb' is not available. [SQLSTATE HY000] (ConnIsLoginSysAdmin) I have enough space and tempdb log file set  on unrestricted groeth. I don't know why I am getting this error

MERGE and growing tempdb

Hi all - I'm running into some issues with the MERGE command. Pretty much I take data from some table in 2 databases and then merge the data into one of those databases. What I'm running into though. is that the tempdb file grows 10 times as much as the target database does. Does MERGE operate on the tempdb? I have a handful of these queries that I need to run. I've ran them simultaneously and one at a time (up until the point where they fill up my temp drives and then I try something else). The structure seems strange...but there is a purpose and is how we need it. Car, State and Operator are in the Processed database (source). tblSource, tblOperatorVersion and tblCarVersion are in the Header database (target). Basic info is in the Processed database. The purpose of the target database is practically to hold the same data. There are multiple source databases that I'm merging into the target. Each have their own structure. The target provides a source where everything can be in one schema and hold the same information. Ok so that's that. Processed.dbo.Car has ~5million rows Processed.dbo.State has 50 rows Processed.dbo.Operator has ~900k rows Header.dbo.tblSource has 200 rows Header.dbo.tblOperatorVersion has ~900k rows Header.dbo.tblCarVersion has ~5million rows Header.dbo.tblCarOperator has 0 (this is what I'm trying to merge into. I nuked out all of the other rows i

tempdb not pointing to the correct .mdf file in SQL 2008

We had to restore our master.mdf file. Now when I right click on the properties of the tempdb it shows the path to the tempdb as pointing to the master.mdf file with a logical name of master. I have tried runnng the alter database command and it completes fine. The new tempdb.mdf file is created in the new location but when I go back in to look at the properties in SQL manager it stilll shows as pointing to the master.mdf file. How can I change? Any help would be greatly appreciated.   Steve

What happens to SQL when the TempDB drive fails?


I am looking to build a new 2 node clustered SQL 2008 R2 setup to support SharePoint 2010 and Project server 2010. I have two 48 core servers with 128gb ram and 2 drive shelves, one drive shelf has 24 x 1500rpm 146GB  drives and one backup drive shelf has  14 2TB drives.  I am trying to figure out what is the best way to setup the 1500rpm drives particularly for the temp DB data files.  My databases for SharePoint and Project server will be on two Raid 10 arrays and their logs will be on 2 separate RAID 1 arrays, all on 1500RPM drives.  That leaves me with five 1500RM disks left.  One of those disks will be hot spare drive waiting for a failure of any drive in the shelf.  That gives me 4 drives left for the Temb DB files.  To be safe I could build two raid 1 arrays and put some of the temp db files on the one array and some on the other array. 

But, what I am considering and looking for advice on is if I do not RAID the 4 drives and just use them as 4 individual drives knowing that if any of them fail the hot spare drive will kick in and become


please explain how db Memory and tempdb Relate on SQL SERVER 2008 ?

Tempdb and number of datafiles

I'm trying to determine how many tempdb datafiles for an instance of SQL Server 2005.  I have a server with 1 quad core cpu that is hyper-threaded (task mgr shows 8 cpu slices).  Now, when Microsoft recommends 1 tempdb datafile for each core are they recommending 4 or 8 datafiles?

Cannot Shrink the tempdb Mdf



I have a serious problem here. I have a production SQL 2005. Tempdb has grown  80 GB when I was doing the important operation in one of the tables in a database in this machine. Now everything finished. I saw tempdb has grown to 80GB I don't much space on this Server. I am left with 100 GB free space on the server. I tried shrinking the mdf file of tempdb. It executes successfully but no space is released to OS. Is restarting the SQL Server the only way to release the space. Can you please help me to release the space ? Is there any way i can do it without restarting the SQL service. 

Please need some help here.



How to increase Log Cache Hit Ratio for tempdb ?


How to increase Log Cache Hit Ratio for tempdb ?

Log Cache Hit Ratio=58%

UPDATE STATISTICS - Could not allocate space for object - Tempdb


Hi Everyone

I have scheduled a maintenance plan for index & update stats one after another.  Now-a-days this job is failing with below error.


Error Number: -1073548784

Executing the query "UPDATE STATISTICS [dbo].[***********]
" failed with the following error: "Could not allocate space for object 'dbo.SORT temporary run
storage:  142101814116352' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


Tables & Index Details

name rows reserved data index_size unused

*** 46613190 44534360 KB 37240624 KB 7293248 KB 488 KB

Multiple tempdb file size


Hello there,

I am little bit confused about multiple tempdb files.

Suppose i want to place tempdb on its own disk of 100 gb, and want to set the size of tempdb to 100 gb. So in near future it probably do not need to autogrow. To impliment multiple tempdb file i.e. 8 (as per MS recommendation), because we have 4 quadra core processor that is total 16 processors. So how to set size for 8 tempdb file.

Pl provide detail with step.




Clearing tempdb database. Event 17136

While a SQL 2005 or 2008 instance comes up this entry can be seen in the windows event logs.
What does this relates to? Is this about filling up TEMPdb space with 0's or something else...

On Using RAM Drive/USB Memory Stick for tempdb Data and Log Storage


128GB USB memory stick is around $400 (2010).

Since there are restrictions on the number of independent disks for a server, can RAM drives or memory sticks be used for tempdb?  Thanks.

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Freeing up space in tempdb


My database size is 300 Gb and tempdb has grown upto 110 Gb . When I restart SQL server - this space gets freed up

I want to free up my tempdb space without restarting SQL server . I tried the following -

backup log [tempdb] with truncate_only

but of no use !

Please help !


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend