March 20, 2008 at 7:38 am
I accidently made the INITIAL SIZE of my TEMPDB much too large. Now when I try to decrease the size, the entry is accepted in SQL-Server Mgmt Studio but the change is not made. How can I decrease the INITIAL SIZE?
Please note, that I am not talking about how to shrink TEMPDB but how to reduce the INITIAL SIZE.
March 20, 2008 at 7:44 am
Read http://support.microsoft.com/kb/307487
It lists three was to lower the configured size of tempdb (and it explains this better than I could in a post response 🙂
Regards,
Andras
March 20, 2008 at 7:47 am
That article deals with shrinking TEMPDB, which I have done as a temporary stopgap. As soon as I restart SQLServer TEMPDB will be recreated with the INITIAL SIZE which is much too large, therefor I want to decrease the INTIAL SIZE.
March 20, 2008 at 8:02 am
mjsteele (3/20/2008)
That article deals with shrinking TEMPDB, which I have done as a temporary stopgap. As soon as I restart SQLServer TEMPDB will be recreated with the INITIAL SIZE which is much too large, therefor I want to decrease the INTIAL SIZE.
Please read the full article 🙂 It changes the configured size of the tempdb, so the next time it is started, it will have the smaller size.
PS: what is the current size, what is the size you are trying to set it to?
Regards,
Andras
March 20, 2008 at 8:04 am
No it deals with SIZE, which is different than INTIAL SIZE.
March 20, 2008 at 8:20 am
The size cannot be smaller than the initial size. If you use the ALTER command - the SIZE will become the initial size.
If you don't like using that - then simply right-click the TempDB database and change the "initial size" setting there.
In either method - issuing that command will either grow or shrink the DB files to the specified size.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 20, 2008 at 8:21 am
mjsteele (3/20/2008)
No it deals with SIZE, which is different than INTIAL SIZE.
🙂 Well, one more try:
Management Studio will ignore your attempt to set the size of tempdb to an initial size that is smaller than the current one. If you do not have extra temp files, and as per method one of the article:
1: stop SQL Server
2: from the command prompt:
sqlservr -c -f
(note the extra switches!!! this will start it in minimal configuration, with a minimum size of tempdb)
(if you have various instances, use the -s and run it from the relevant Binn folder)
3: connect to SQL Server
4: ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 1) (or whatever size in MB you wish to have)
5: restart SQL Server
your initial size has been changed to a lower size 🙂 Even Management Studio reports it this way.
Please do correct me if I'm wrong, if so, I'm most likely misunderstanding you. However, following the above steps will change the tempdb's "INITIAL SIZE" as Management Studio reports it to a size that is smaller than both the current size and the current "initial size", and this is the size tempdb will be when SQL Server is restarted.
Regards,
Andras
March 20, 2008 at 8:28 am
Just one more comment, you cannot set the initial size of tempdb to be smaller than the size of your model database, so if you have modified the size of your model database, you may need to change that too.
Regards,
Andras
March 20, 2008 at 8:47 am
Andras Belokosztolszki (3/20/2008)
mjsteele (3/20/2008)
No it deals with SIZE, which is different than INTIAL SIZE.🙂 Well, one more try:
Management Studio will ignore your attempt to set the size of tempdb to an initial size that is smaller than the current one. If you do not have extra temp files, and as per method one of the article:
1: stop SQL Server
2: from the command prompt:
sqlservr -c -f
(note the extra switches!!! this will start it in minimal configuration, with a minimum size of tempdb)
(if you have various instances, use the -s and run it from the relevant Binn folder)
3: connect to SQL Server
4: ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 1) (or whatever size in MB you wish to have)
5: restart SQL Server
your initial size has been changed to a lower size 🙂 Even Management Studio reports it this way.
Please do correct me if I'm wrong, if so, I'm most likely misunderstanding you. However, following the above steps will change the tempdb's "INITIAL SIZE" as Management Studio reports it to a size that is smaller than both the current size and the current "initial size", and this is the size tempdb will be when SQL Server is restarted.
Regards,
Andras
Or - DBCC shrink to a size smaller than the initial size you wish. THEN issue the ALTER DATABASE command. No database service restart needed.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 20, 2008 at 8:51 am
Matt Miller (3/20/2008)
Or - DBCC shrink to a size smaller than the initial size you wish. THEN issue the ALTER DATABASE command. No database service restart needed.
Matt is right, and his suggested method is similar to 2 and 3 in the referenced article. The configured size is set by either and "alter database" or the "DBCC SHRINKFILE" statements.
Regards,
Andras
March 20, 2008 at 9:30 am
I am trying to test your proposed solution (the first one using ALTER database) but I do not have Query Analyzer and when I try to connect to the database in Mgmt Studio I get an error becuase it is in single user mode. So my question is from where to connect to the database and run the query?
March 20, 2008 at 9:32 am
Never mind I figured it out by using the Tools/Options settings.
March 20, 2008 at 9:38 am
Andras your solution worked. Thank you so much and I am sorry I doubted it at first.
March 20, 2008 at 9:58 am
mjsteele (3/20/2008)
Andras your solution worked. Thank you so much and I am sorry I doubted it at first.
No worries 🙂 I'm glad that your problem is now resolved,
Regards,
Andras
July 8, 2008 at 10:20 am
Hi there -
I have a similar problem - someone set our initial tempdb size to 16GB. I'm trying to follow the instructions given to stop SQL Server, start with a command prompt, go to a query window and execute the ALTER DATABASE command. Running the command line to stop the server runs fine. The part I'm stuck on - and quite frankly feel dumb about - is that I can't make a connection to a Query Analyzer window once I run the command line statement. I get an error that the server is in single user mode - assuming here that it means the command window. I tried loading up the query ahead of time but get a transport connection level error.
I feel like I'm missing something really obvious. Any ideas?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply