tempdb become huge - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

tempdb become huge

Author

Topic

shohan_db
Starting Member

36 Posts

Posted - 2010-07-20 : 03:51:46

i am working with sql server 2000 & few days ago i found my tempdb database become so large. it was nearly 200MB but within 12 hours in became 15 GB!

>>My tempdb’s current properties are
a. Recovery model : Simple
b. Size: 14016 MB, Space available: 14015 MB
c. Automatically grow file & Unrestricted file growth
d. File growth rate: 10%

it should mentioned that no of select/insert/update or delete query not increase in my database within that period so it seems quite unusual to me…

Whatever I found some solution from net and one of them I chose following solution:

-------------------------------------------
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = target_size_in_MB)
--Deisred target size for the data file

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = target_size_in_MB)
--Desired target size for the log file

Stop and start SQL SERVER....
----------------------------------------------------

My questions are:

01. if I manually resize tempdb.mdf and tempdb.ldf file
Will it lose any data from tempdb?

02. Will it affect any on my user database?

I am very confused about that issue, if anyone has better solution please advice me.

Thanking you

Mainur Rahman Shohan

shohan

Kristen
Test

22859 Posts

Posted - 2010-07-20 : 04:06:00

Assuming this was a one-off (some massive operation) just restart the SQL Server and TEMPDB will revert to its normal start-up size.

(That doesn't always happen, so report back if not, but that is what is supposed to happen!)

shohan_db
Starting Member

36 Posts

Posted - 2010-07-20 : 04:12:08

I have restarted the SQL server service but tempdb remained same in size... it does not work :(

shohan

Kristen
Test

22859 Posts

Posted - 2010-07-20 : 04:26:49

The solution then is more complicated I'm afraid.

Just check that the startup size hasn't been changed to 12GB - in case some twit has twiddled with it. (If so when you restart SQL it will make a new file, that size)

Shrinking TEMPDB has the potential to corrupt other databases, so should specifically be done with the SQL Service started in single user mode.

http://support.microsoft.com/kb/307487/en-gb

shohan_db
Starting Member

36 Posts

Posted - 2010-07-20 : 04:59:49

thanks...
but Microsoft gave almost same solution wt i liked.... according to your link
"sqlservr -c -f" that command not working in command propmt!

the link also call for resize the tempdb by alter database statement

shohan

shohan_db
Starting Member

36 Posts

Posted - 2010-07-20 : 05:21:58

is there any advice?
please post

thanks

shohan

Kristen
Test

22859 Posts

Posted - 2010-07-20 : 06:48:24

"sqlservr -c -f" that command not working in command propmt!

Tells me nothing ...

Presumably your path is set correctly?

shohan_db
Starting Member

36 Posts

Posted - 2010-07-22 : 03:15:10

I have restarted SQL SERVER Service by the following
NET STOP MSSQLSERVER
NET START MSSQLSERVER

With theory, tempdb should re size and should start with its minimum starting size.
but it did not.
mdf file size is still 14GB!!!
please help me.

shohan

Kristen
Test

22859 Posts

Posted - 2010-07-22 : 04:14:34

You said:

""sqlservr -c -f" that command not working in command propmt!"

But "not working" doesn't tell us anything. Error message? What happens?

shohan_db
Starting Member

36 Posts

Posted - 2010-07-22 : 04:28:19

i put sqlserver -c -f and after clicking enter i got the following msg:

"sqlservr" is not recognize as an internal or external command, operation program or a batch file

what does -c -f mean?

shohan

Kristen
Test

22859 Posts

Posted - 2010-07-22 : 04:32:04

""sqlservr" is not recognize as an internal or external command, operation program or a batch file"

I said before:

"Presumably your path is set correctly?"

given the above error message then your path is probably NOT set correctly. Or just switch to the folder where the sqlserver command is and run it from there.

Kristen
Test

22859 Posts

Posted - 2010-07-22 : 04:37:01

"what does -c -f mean?"

See SQL Server documentation "Book online" - available on Microsoft site, or you can download and install on your PC

shohan_db
Starting Member

36 Posts

Posted - 2010-07-22 : 06:21:14

i have set the path of Binn and

To start the default instance of SQL Server with minimal configuration
From a command prompt, i entered the following command:

sqlservr.exe -f

it execute but after clearing and starting tempdb.. recovery also completed but then a warning comes: "Warning override, autoexec procedure skipped" and no other execution is working after that!

cursor is blinking on the black screen!

shohan

Kristen
Test

22859 Posts

Posted - 2010-07-22 : 07:48:44

Long time since Iv'e done it, but I think you can start Query Analyser (on that machine) and connect to the "single user" instance and then run the SHRINK commands as per the knowledge-base article

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources