Tuesday, November 23, 2010

Moving tempdb to new clustered disk with SQL Server 2008

If you create new clustered disk for tempdb database and you want to move the current tempdb database to this new clustered disk you have to do the following:
so after you add new storage to SQL Server services group


and this disk for example label as I: so if you try to run the following command:

USE master
GO

ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'I:\MSSQL10_50.SQL2008\MSSQL\DATA\tempdb.mdf')
GO

ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'I:\MSSQL10_50.SQL2008\MSSQL\Data\templog.ldf')
GO

you will get this error

Msg 5184, Level 16, State 1, Line 1
Cannot use file 'I:\MSSQL10_50.SQL2008\MSSQL\DATA\tempdb.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

Msg 5184, Level 16, State 1, Line 1
Cannot use file 'I:\MSSQL10_50.SQL2008\MSSQL\Data\templog.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
 
so to resovle the above error you have to add the new Clustered disk to dependencies of SQL Server resource

so go to Other Resources and Right Click on Sql Server instance resource and then go to dependencies
and then add a Dependency and choose Clustered disk (I:)


Now again re-run the T-Sql statement and then restart the SQL Server instance serivce.
NOTE: SQL Server will create new tempdb database in the new place so you have to go to the old space where the original files of tempdb database and remove it.
Done.

3 comments:

Christian said...

Great post, man!
Thanks.

Anonymous said...

worked like a charm... thanks!!

Anonymous said...

Thanks a lot. This worked great.

Jay