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 6, 2010 11:10 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, June 16, 2014 10:44 AM
Points: 153, Visits: 583
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 6, 2010 11:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:48 AM
Points: 1,935, Visits: 10,956
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 6, 2010 11:48 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, June 16, 2014 10:44 AM
Points: 153, Visits: 583
Then the tempdb.mdf file in original D:\ drive can be deleted?
Post #999706
Posted Wednesday, October 6, 2010 11:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:48 AM
Points: 1,935, Visits: 10,956
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
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:10 AM
Points: 104, Visits: 400
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


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 7:19 AM
Points: 728, Visits: 771
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