tempDB in SQL Server

Posted by Bandi under Sql Server on 11/28/2013 | Points: 10 | Views : 920 | Status : [Member] [MVP] | Replies : 3
How to detemermine the size of tempDB?
How can we decide/set the size of tempDB in SQL Server

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif



Responses

Posted by: Kmandapalli on: 11/28/2013 [Member] Silver | Points: 25

Up
0
Down
Hi,

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev,
SIZE = 5MB) –Reduce to the size you want
GO

(You have to reboot SQL Server for the configuration to take effect).

If you experience an error “MODIFY FILE failed, it means that Specified size is less than current size”

Regards,
Shree M.

Kavya Shree Mandapalli

Bandi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/28/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Shree,
I'm asking for how to decide the size of tempDB?
i.e.
In real-time scenarios what shouls be the size of tempDB maily working with scheduled backup & restores in SQL server

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Bandi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 11/28/2013 [Member] Silver | Points: 25

Up
0
Down
Hi,

This query pulls the size of the DB…

SELECT name, size
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

This query to define to decrease the size… ante simple
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, SIZE=100Mb);

You cannot decide the size of tempDb, the system will by default give the size.
You can adjust the size (increase or decrease ) according to ur requirement.

Mark as answer if satisfied....

Regards,
Shree M.

Kavya Shree Mandapalli

Bandi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response