I would like to lower the initial size of tempdb. I have tried running USE master GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 2048MB) GO but get error MODIFY FILE failed. Specified size is less than current size. thanks
Try this script from books online --Increase the size of the tempdb device DISK INIT name = 'tempdb1',physname = 'c:mssqldata empdb1.DAT',vdevno = 100, size = 12800 GO --Increase the size of tempdb ALTER DATABASE tempdb ON tempdb1 = 25 Regards, Sat
For expanding the dafualt logical file tempdev ua can also use the below use master ALTER DATABASE tempdb ON tempdev = 25 The above with increase it by 25 MB Regards Sat
I guess if the specifies size is less than current size u cannot use this command , as your error ,message states. I am not sure if restart and rebuild of system database can fix that, I hope gurus have something up there sleeve on this. Regars Sat
This worked for me , dbcc shrinkfile ('tempdev',6,TRUNCATEONLY) Traget size in MB = 6 TRUNCATEONLY = gives the reclaimed space to OS.
I would like to ask what is the reason to shrink the TEMPDB size, if the other processes needs space for calculations then TEMPDB is used extensively and it will grow again. So try to reduce number of round trips in this case and I'm sure we can suggest a workaround if you can explain the problem.
the tempdb was just created with the wrong size. It was created on our reporting database with the same size as the production DB. The rep DB does not need a tempdb with the same allocated size as production. The reason I need it smaller is that the Initial size is 17gb and is taking up space on disk that is becoming limited.
Ok, follow as suggested above and you might still get in to the issues in going upper size, you have to control usage of temp database within the queries or run in smaller batches.
I am not sure my problem is being understood correctly. I do not have a problem with the tempdb growing too large. It is almost never used. When sql server was installed on this server the settings for the tempdb were copied from our production environment (which does need a large tempdb). Since the tempdb was created at a large size I cannot decrease the size of the database beyond the specified Initial size. The size of the tempdb is 17gb which is unneccesarily large.
Thanks for the replies. I have found a solution to my problem. I started sql server from command line using sqlservr -c -f sql server started in minimum configuration mode with a tempdb size of 1MB. I then ran my statement above effectively increasing tempdb size from 1MB to 2048MB (but decreasing from 17GB). after restarting service tempdb size was 2048MB.
This problem is there for not only tempdb but any database, you cannot shrink it beyond the initial size, but I have tried a work around that has worked for me in the past. Try re-setting the initial size of the data file by increasing it by 5 or 10MB and then try to issue the shrink command, it has worked for me Satya
http://msdn2.microsoft.com/en-us/library/ms190768.aspx KBA http://support.microsoft.com/kb/307487 too
I have already found a solution, which I stated in my previous post. It is the same thing and the KB provided. thanks.