Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1173
  • Last Modified:

Tempdb shrink- sql server 2005\2008

Hi,

Please let me know, how to shrink tempdb in business hours using tsql (sql server 2005 and 2008).

In business hours, we are receiving tickets related to tempdb volume that is there is no enough space in tempdb volume.

i have tried with tsql shrink command, but sometimes it won't releases free space in tempdb volume\disk.

0
sg05121983
Asked:
sg05121983
1 Solution
 
mcs0506Commented:
Hi,
Try this script

DECLARE @DB_Name nvarchar(255);
DECLARE @DB_LogFileName nvarchar(255);
SET @DB_Name = '<Database Name>';               --Input Variable
SET @DB_LogFileName = '<LogFileEntryName>';         --Input Variable
EXEC
(
'USE ['+@DB_Name+']; '+
'BACKUP LOG ['+@DB_Name+'] WITH TRUNCATE_ONLY ' +
'DBCC SHRINKFILE( '''+@DB_LogFileName+''', 2) ' +
'BACKUP LOG ['+@DB_Name+'] WITH TRUNCATE_ONLY ' +
'DBCC SHRINKFILE( '''+@DB_LogFileName+''', 2)'
)
GO
0
 
hspoulsenCommented:
you should not shrink the tempdb files.
Here is why:
http://sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx

But you need to ensure that you have enough space for tempdb, and that the T-SQL is proper and that you have the right indexes and table format.
0
 
Anthony PerkinsCommented:
mcs0506,

The TRUNCATE_ONLY is a deprecated switch that is finally (thank goodness) no longer supported in SQL Server 2008 and SQL Server 2008 R2
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
TempDBACommented:
If its not shrinking, it means there is something running that is consuming space in tempdb. Try checking if there are any open transaction during the time frame.

Use tempdb
go

dbcc opentran
go

If there is some open trasaction and the starttime for that transaction is long back, check what is running in that spid. You can do it by running sp_who2 <the spid returned by sp_who2>.
Try checking why the statement is consuming so much of tempdb or why it is running for such a long time. If everything is fine, check for the size you allocated for tempdb. You may want to increase the size of the file assigned.
For temporary fix, you can kill the spid opened and then shrink the tempdb. But its always better to kill the problem.
0
 
Anthony PerkinsCommented:
And here is the MSDN documentation on how to shrink tempdb:
How to shrink the tempdb database in SQL Server
http://support.microsoft.com/kb/307487
0
 
LowfatspreadCommented:
why do you want to shrink tempdb ... surely it requires more space is the jist of the message you've received?

what other problems are you experiencing?
0
 
Scott PletcherSenior DBACommented:
It's extremely difficult to shrink tempdb while SQL is running.  It's very easy to crash some running code that way.  IF  you can prevent all other activity to tempdb -- lol, yeah right -- then you can safely shrink it.

However, you are almost certainly hurting your overall system performance by shrinking tempdb.  You should preallocate enough space to tempdb that it doesn't need to grow.

Temporarily, if you're short of disk space for a period of time, you might consider moving a relatively lightly used db -- NOT tempdb -- to a slower, and thus cheaper, type of drive to free up fast disk space for tempdb.  Tempdb is too usually too critical for performance to relegate it to lesser drives.
0
 
sg05121983Author Commented:
--
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now