Open SQL Management Studio.
Disconnect all server connections.
Leave GUI open as we need the Query Analizer
stop all SQL services
open services by running service.msc
open properties of the sql instance and copy the sql executing path
C:\Program Files\Microsoft SQL Server\MSSQL11.SQL1\MSSQL\Binn\sqlservr.exe -c -f -sSQL1
run this command line with additional option –c –f
now is a sql instance in single user mode running
Leave the process running, we come back later on
change to SQL Management Studio and run a new query as sa user
execute the following sql code after adjusting it to your needs
Use Master go ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', NEWNAME = 'tempdev1', FILENAME = N'T:\SQL1\tempDB\tempdev1.ndf') ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev1', SIZE = 5120000KB , FILEGROWTH = 51200KB ) go ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = 2048KB , FILEGROWTH = 1024KB); GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'T:\SQL1\tempDB\tempdev2.ndf' , SIZE = 5120000KB , FILEGROWTH = 51200KB ) GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'T:\SQL1\tempDB\tempdev3.ndf' , SIZE = 5120000KB , FILEGROWTH = 51200KB ) GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'T:\SQL1\tempDB\tempdev4.ndf' , SIZE = 5120000KB , FILEGROWTH = 51200KB ) GO Use Master go ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = N'T:\SQL1\tempDBlog\templog.ldf') GO |
close the query window
Then got to the command line and press ctr+c to terminate the single user session
now remove all temp db files . they will be created on next sql service start.
next start all sql services and check the new settings