April 1, 2009 at 11:34 am
Hi Everyone,
I initially did a search on the forum but nothing really answers my problem.
I have a Tempdb which has grown to around 16gb and my attempts at shrinking it do not work at all! I realise that the problem is resolved with a restart of sql server but this is unacceptable in my production environment (and a restart should never be a norm to fix a problem!)
Use [Tempdb]
GO
SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', * FROM sys.database_files;
tempdev
O:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf
Total Size in MB: 15891
Available Size in MB: 15783.187500
templog
O:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf
Total Size in MB: 259
Available Size in MB: 247.187500
select * from sys.dm_db_file_space_usage
Unallocated_extent_page_count: 2020560
The unallocated extent page count when divided by 128 (number of pages in a mb) = 15785.625 mb
Ive tried the following:
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 1024)
GO
USE [tempdb]
GO
DBCC SHRINKDATABASE (N'tempdb')
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY)
GO
the shrink file gui window of tempdev shows allocated space of 8.00MB and available free space of -96.19mb ????
Can anyone help??
April 1, 2009 at 12:01 pm
Why do you need to shrink it? If it's grown that large it means that something within your production workload needs a 16 GB tempDB. If you do somehow force a shrink, it could well grow back to that size. Unless you are running out of disk space, there is no good reason to shrink the database. Empty space within won't cause issues.
If your workload is such that tempDB needs to be that size, then make it that size from startup.
Oh, and you may want to note that running Shrink on TempDB while it's in use can result in a corrupt tempDB. There is a kb article on that - http://support.microsoft.com/kb/307487
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2009 at 12:09 pm
is not good idea shrink tempDB depend activity on your database but 16gb is not unusual my production server have 40Gb for tmpdb into 6 physical files. maybe considere add additional disk to have suffisent space.
April 2, 2009 at 3:30 am
I know that tempdb can get currupted and i have read the various Ms kb articles. I wouldnt be doing this during the day, but the transaction that caused the tempdb to grow so much was a one off and not our standard tempdb growth.
i would be shrinking tempdev to around 10gb which is enough to handle our everyday transactions.
I dont have the capacity to maintain a 16gb tempdb which is why i want to shrink it.
The point is not why i am trying to do this but why it is not working when i try the options given by Microsoft
April 2, 2009 at 3:51 am
Did you run the 'update usage' that the kb article refered to?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2009 at 3:54 am
We've had a similar issue on one of our environments. The only explanation I can give at the moment is similar to problems when attempting to shrink the transaction log file: if there is an active transaction at the end of the file then DBCC SHRINKFILE will not be able to shrink it. Like I allude to, this is only a guess.
The script below should be able to tell you what pending I/O requests are on what particular database and file. That'll give you some idea regarding any (long/continuous) oustanding requests. Then maybe look at killing the spid which is causing the request which might enable you to shrink tempdb
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
fromsys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
wheret1.file_handle = t2.io_handle
The above advice might be a load of rubbish but definitely comes without warranty and I take no responsibility for bringing your company down to a grinding halt.:ermm:
April 2, 2009 at 3:59 am
Hi,
I ran updateusage against tempdb and it didnt resolve the minus free space issue.
Im a bit baffled about it all - a friend mentioned that it may be because the tempdev is holding data at the back of the file preventing a shrink (similar to the way a log would) but a reorganise would have resolved this.
Is there any way to check this out?
April 2, 2009 at 4:24 am
GUI showing percent free in minus is common for large tempdb's, I dont know exactly why is this, but I have seem in several servers.
April 2, 2009 at 4:26 am
Can you try this? It worked for me...
USE [tempdb]
GO
Checkpoint
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY)
GO
April 2, 2009 at 4:27 am
Or
use tempdb
backup log tempdb WITH NO_LOG
dbcc shrinkfile (templog,0)
dbcc shrinkfile (tempdev,0)
April 2, 2009 at 4:35 am
April 2, 2009 at 4:37 am
April 2, 2009 at 6:12 am
temdb data files move only after SQL service restart
July 27, 2009 at 7:56 am
SQL 2005 caches a lot of things in tempdb, most tied to a query plan. Try a DBCC FREEPROCCACHE then you should be able to shrink. I should mention though that you really do not want to shrink TempDB unless you are doing some specific maintenance like splitting it into multiple files.
October 16, 2010 at 5:17 am
David Levy
SQL 2005 caches a lot of things in tempdb, most tied to a query plan. Try a DBCC FREEPROCCACHE then you should be able to shrink. I should mention though that you really do not want to shrink TempDB unless you are doing some specific maintenance like splitting it into multiple files.
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
wow, you are so great, I tried many ways to shrink tempdb online, finally after I run dbcc freeproccache as you suggested, and then I shrink it finally.
Thanks!!!
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply