By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,717 Members | 1,268 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,717 IT Pros & Developers. It's quick & easy.

How to drop one of the tempdb files

P: n/a
Hi all, I have a tempdb that consists of 8 datafiles, tempdb_data_1 to
tempdb_data_8, each is 8GB. Now how can I drop 7 of them and leave
only tempdb_data_1? Can this be done? Thanks a lot.

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
New MSSQL DBA (bo*******@gmail.com) writes:
Hi all, I have a tempdb that consists of 8 datafiles, tempdb_data_1 to
tempdb_data_8, each is 8GB. Now how can I drop 7 of them and leave
only tempdb_data_1? Can this be done? Thanks a lot.


I have not tried it, but my naive guess is that you would do as for
any other database. That is first DBCC SHRINKFILE with EMPTYFILE, and
then ALTER DATABASE.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #2

P: n/a
I've tried this:

use tempdb
go
dbcc shrinkfile(tempdev4, emptyfile)
go
alter database tempdb
remove file tempdev4
go
then it gives something like this:

Server: Msg 5042, Level 16, State 1, Line 1
The file 'tempdev4' cannot be removed because it is not empty.
File 'E:\Microsoft SQL Server\MSSQL\data\tempdev4.ndf' modified in
sysaltfiles. Delete old file after restarting SQL Server.
Don't know what does this mean. Does it mean that the file will be
dropped after I restarted the server?

Nov 23 '05 #3

P: n/a
New MSSQL DBA (bo*******@gmail.com) writes:
I've tried this:

use tempdb
go
dbcc shrinkfile(tempdev4, emptyfile)
go
alter database tempdb
remove file tempdev4
go
then it gives something like this:

Server: Msg 5042, Level 16, State 1, Line 1
The file 'tempdev4' cannot be removed because it is not empty.
File 'E:\Microsoft SQL Server\MSSQL\data\tempdev4.ndf' modified in
sysaltfiles. Delete old file after restarting SQL Server.
Don't know what does this mean. Does it mean that the file will be
dropped after I restarted the server?


I don't know, but why not give it a try?

If this is a production machine, you may want to first play with a
test server, to see the effects of the commands.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.