Today I was moving the tempDB on a new server from the drive where every other DB data file sits to its own dedicated drive, with the intention of preventing drive contention.
To cut a long story short (and anyway I'm not even sure how I got myself into such a tangle) here's a couple of annoyances and their fixes:
Annoyance #1 - Reducing the Initial File Size of TempDB: If you right click a database, go into Properties, and Files, you can see the Initial file size of the database. Let's say that someone else set this DB up, and you'd like the initial size to be smaller. This is only really a problem you're going to get with TempDB, which re-creates each time you restart the SQL Service btw. So you type in a new value, and hit OK. Well, not OK, because if you re-open that same GUI, you'll see that your changes have not been kept! ARGH!
Solution #1 - Issue a DBCC SHRINKFILE (N'file' , size in MB) where the size in MB is an amount LESS than the initial size you wish to set. Then, ALTER DATABASE tempdb MODIFY FILE (NAME = 'file', SIZE = new initial size)
Annoyance #2 - Reducing the Number of Database Files: So you've got TempDB split up into too many files. So you go into the Properties, Files etc and remove the file(s), hit OK and they file's disappeared from the hard drive. But upon restarting the SQL Service for whatever reason, the files get recreated on the hard drive!
Solution #2 - They've got to be explicity removed from the system catalogue it seems. ALTER DATABASE tempdb
REMOVE FILE (NAME = tempdev2, FILENAME = 'c:\tempdev2.ndf')
Little secrets.
No comments:
Post a Comment