Link to home
Create AccountLog in
Avatar of titanicx
titanicx

asked on

tempdb is growing in sql server 2008

we have free space on E drive because of tempdb_log is growing, the size of tempdb_log is about 35 GB so We have shrinked tempdb_log but there has not been any significant change in size and also we can not restart it due to production system..how can we reduce its size?
SOLUTION
Avatar of jonnidip
jonnidip
Flag of Italy image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of lcohan
tempdb cannot be shrinked and it is re-built every time SQL Starts.
I suggest yo move it to a different location where disk space is not an issue and you can easlily do that by a simple ALTER DATABASE command then a SQL Service restart that will rebuild/move and truncate it.
Also Microsoft suggests to have one tempdb mdf file per socket but no more than 4 files.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
It's unusual that log space on tempdb would grow so much.  I have some huge and very busy tempdbs that don't have nearly that big a log.

It looks like someone is creating large, permanent table(s) in tempdb.  You can run a query to find out.

I'll post the query as soon as I can.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account