November 11, 2013 at 7:04 am
we have a SQL 2008 R2 instance where tempdb data file was full and the drive also didnt have any space to allow the tempdb to grow.
so, we created a secondary data file on a different drive.
Even now, the drive seems to be full.
I didnt restart the sql server though(I dont think adding a new file needs a reboot)
November 11, 2013 at 7:35 am
SQL Server will not move the contents of one file to another file just because it was added. Instead what is going to happen is that all new data will be written to the new file and the old file will retain it's size. What you want to have happen is to have all files of equal size (and the data evenly distributed) and then SQL Server will use a "round robin" approach to where to store data. Check out this resource for some TempDB best practices:
http://www.confio.com/logicalread/sql-server-tempdb-best-practices-multiple-files-w01/#.UoD2mfmsi-0
November 11, 2013 at 8:35 am
I'm not sure I follow. Adding a new file will not force SQL to release space taken by the tempdb. The safest way to resize tempdb files is the change the default file size and restart SQL Server. The whole db will be created using these settings.
If this is a production database server I suggest you do some tempdb performance tuning research first as the general recommendation is not to have a single tempdb file but several depending on your needs.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply