How to drop one of the tempdb files

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.

New MSSQL DBA (boscong88@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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\te
mpdev4.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?

New MSSQL DBA (boscong88@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\te
mpdev4.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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx