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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Cannot Shrink the tempdb Mdf

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server
 

Hi 

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.

 

Thanks 




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.  

Shrink database log file sql server 2005

  
Hi, I am trying to shrink log file for a testing database.This database will be restored with production database periodically.When i tried to create backup of the log file Processed 0 pages for database 'hcbeta', file 'HC_log' on file 6. The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed. BACKUP LOG successfully processed 0 pages in 0.423 seconds (0.000 MB/sec). I tried EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 Resulted with error:Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication. So tried to publish the database using EXEC sp_dboption 'hcbeta', 'Publish', 'true' resulted error mesage:Msg 15242, Level 16, State 1, Procedure sp_dboption, Line 138 Database option 'Publish' is not unique. and retrived duplicate_options as "merge Publish" ,"Publish" Plesae help me in shrinking my database log file. Thanks, Adi.

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.    

Why doesn't SQL Server 2005 Enterprise always shrink a log file after a backup?

  
I have several databases that, despite being backed up regularly, do not release space to the operating system.  In one extreme case, I have a log file that is less than 1% utilized (and has been for months.) I know how to manually force a shrink, but I should not need to do that on any sort of regular basis.  In fact, it is against best practices to manually shrink a log file (or any database file for that matter) because of the very high risk of an unrecoverable error. It is my understanding that SQL Server will eventually handle these things on its own.  However, it does not appear to be doing so.  Any thoughts as to why? Thanks, Dave

[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

SharePoint Content DB Shrink

  
I have a very large content (not log!) sharepoint database. About 80 Gb of its size is a dead, deleted from sharepoint data. I want to get rid of it, but common shrinking allows me to redeem only 1 Gb. I dont know why sharepoint didnt mark this deleted data as shrinkable. It just seems like this sharepoint procedure is far from state of art. So maybe you - sql pros - know some magical script that i can cast on my db to unravel this knot ? ) This should be a common trouble, but i cant find any solution in my realm (

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

DATABASE MEMORY AND TEMPDB

  
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?

How to shrink the sharepoint database log file

  

Hello,

I have a SharePoint content database with the size 70GB but he log file is 450GB. i wanted to shrink the log file. Can anyone tell me  how to do that ? It would be great if anyone can provide me the complete flow.

  1. Do I have to shink the log file when the database is offline?
  2. what if we just take the backup and create a new log file ?
  3. How do we restrict the size of the log file by not growing big but just auto shrink by it self.

This is all on the production server, i need to fix this asap. Any help would be appreciated.

Thanks in Advance.


How can I shrink the size of LDF file after BACKUP LOG?

  

Ive just done BACKUP LOG against particular database and found no changes in size for the appropriate LDF file. It is shrunk only after I issue:

ALTER

 

DATABASE dssip

SET

 

RECOVERY SIMPLE

But this approach breaks all logs chain. Is there any way to shrink the LDF file for FULL backup model without destroying the whole sequece of log backups?


How to shrink the sharepoint database log file

  

Hello,

I have a SharePoint content database with the size 70GB but he log file is 450GB. i wanted to shrink the log file. Can anyone tell me  how to do that ? It would be great if anyone can provide me the complete flow.

  1. Do I have to shink the log file when the database is offline?
  2. what if we just take the backup and create a new log file ?
  3. How do we restrict the size of the log file by not growing big but just auto shrink by it self.

This is all on the production server, i need to fix this asap. Any help would be appreciated.

Thanks in Advance.

 


Transaction logs will not shrink

  

We have two databases on our SQL Server 2000 installation that has huge transaction logs. One of them is at 32 GB and the other is around 48 GB. When I go into the Enterprise Manager and select shrink, and then select file and the log file, it shows the actual size is much less like around 2-3 GB. After selecting to shrink the file to this size (2-3GB) and waiting, when finished, I see no change in file size. I've gone back into shrink and the file size still remains the same, like it is not shrinking it properly. We also used Backup Exec and backed up the logs with trunicate. What can we do to shrink these log files?


Categories: 
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