August 18, 2011 at 2:19 pm
I was messing around with creating multiple .NDF files for tempdb on a test server. Now I want to just go back to one .MDF file, and I don't know how. Any idea?
Thanks!!
August 18, 2011 at 2:28 pm
Make sure the file is empty (move contents to another file) and then drop the extra files.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2011 at 2:49 pm
Since it's tempDB it's a little different from user databases.
ALTER DATABASE TempDB DROP FILE ....
Then restart SQL. The change will only be effective after a restart, until then it's just a metadata change.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2011 at 2:54 pm
Gila - I could not get that to work, but this does work:
--Switch to tempdb:
USE tempdb
GO
--empty out the .NDF file (I think you should start with the highest number)
DBCC SHRINKFILE ( tempdb7, EMPTYFILE )
--Now REMOVE that file:
ALTER DATABASE tempdb REMOVE FILE tempdb7
August 18, 2011 at 3:44 pm
Drop, remove, what's the difference 😀
You shouldn't need the shrink with emptyfile for TempDB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply