|
Home > Archive > Microsoft SQL Server forum > November 2005 > How to drop one of the tempdb files
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to drop one of the tempdb files
|
|
| New MSSQL DBA 2005-11-22, 3:23 am |
| 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.
| |
| Erland Sommarskog 2005-11-22, 8:23 pm |
| 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
| |
| New MSSQL DBA 2005-11-23, 7:24 am |
| 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?
| |
| Erland Sommarskog 2005-11-23, 7:24 am |
| 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
|
|
|
|
|