Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TEMPDB space issue Expand / Collapse
Author
Message
Posted Wednesday, October 06, 2010 11:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 9:26 AM
Points: 138, Visits: 511
what is the alter tempdb command to move the tempd.mdf file from D:\MSSQL2005\MSSQL$Instance\MSSQL.1\MSSQL\Data to the H:\SQL directory
Post #999654
Posted Wednesday, October 06, 2010 11:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 1,654, Visits: 10,864
Assuming tempdb logical names are the defaults (which they likely are):

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'H:\SQL\tempdb.mdf'); -- as in 'c:\tempdb.mdf'
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'H:\SQL\templog.ldf'); -- as in 'c:\templog.ldf'

After this you'll have to stop and restart your SQL instance.


Rob Schripsema
Accelitec, Inc
Post #999691
Posted Wednesday, October 06, 2010 11:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 9:26 AM
Points: 138, Visits: 511
Then the tempdb.mdf file in original D:\ drive can be deleted?
Post #999706
Posted Wednesday, October 06, 2010 11:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 1,654, Visits: 10,864
Yes, after you're restarted the server instance. You'll know it is no longer in use when the OS allows you to delete it; as long as it's attached by SQL Server, you won't be able to.

Rob Schripsema
Accelitec, Inc
Post #999731
Posted Thursday, May 17, 2012 12:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 3:04 AM
Points: 68, Visits: 251
Thanks for you reply.. but after executing this alter statement...
can we do to move .mdf,.ldf to other location? or not ?
Post #1301513
Posted Thursday, May 17, 2012 12:55 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 9:31 AM
Points: 643, Visits: 693
You can move the files, after the service has been stopped but there is no need as new ones are created
Post #1301515
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse