TempDB mdf file has grown to 11GB

Question

TempDB mdf file has grown to 11GB

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

because checkdb uses tempdb heavily. I am not sure of details but I would hazard a guess this is to help maintain concurrency whilst checkdb runs. That is why I suggested you used the estimateonly clause to give you an idea of how much space you need in tempdb for the checkdb.

Now you know this you can size your tempdb accordingly. IF the 19gb you have available will cover it you are ok for now, but as others have said don't get caught short on this.

DO NOT stop running checkdb simply because it uses tempdb space, ensure tempdb has the space it needs instead.

---------------------------------------------------------------------

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

MS does recommend a SQL Server restart as documented at "How to shrink the tempdb database in SQL Server" http://support.microsoft.com/kb/307487

There is also this note:

If tempdb is in use and you attempt to shrink it by using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, you may receive multiple consistency errors. ... Restart SQL Server to re-create tempdb and clean up the consistency errors.

SQL = Scarcely Qualifies as a Language

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

Carl Federl (6/17/2009)
MS does recommend a SQL Server restart as documented at "How to shrink the tempdb database in SQL Server" http://support.microsoft.com/kb/307487
There is also this note:
If tempdb is in use and you attempt to shrink it by using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, you may receive multiple consistency errors. ... Restart SQL Server to re-create tempdb and clean up the consistency errors.

Thanks, Carl... I appreciate it. I'll take a look.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

Jeff Moden (6/17/2009)
Carl Federl (6/17/2009)
MS does recommend a SQL Server restart as documented at "How to shrink the tempdb database in SQL Server" http://support.microsoft.com/kb/307487
There is also this note:
If tempdb is in use and you attempt to shrink it by using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, you may receive multiple consistency errors. ... Restart SQL Server to re-create tempdb and clean up the consistency errors.
Thanks, Carl... I appreciate it. I'll take a look.

I took a look... yes, it identifies 3 different methods and 1 of them requires a restart and 1 of them recommends a restart, but no where does it say that restarting SQL Server is the "best practice". In fact, it lists several advantages of the 3rd method which doesn't require the restart at all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

Jeff, you are correct that nowhere does MS state that restarting SQL Server is the "best practice" to reduce the size of tempdb. But since the two non-restart method require that the SQL Server tempdb not be in-use and tempdb usage is ubiquitous, there is a problem.

Just for discussion, how do you stop anyone from using tempdb ?

Some things I tried:

Trying to set Tempdb offline give "Msg 5058, Level 16, State 4, Line 1 Option 'OFFLINE' cannot be set in database 'tempdb'."

The guest user cannot be removed from tempdb.

Setting maximum number of concurrent connections to 1 requires a restart.

Also tried setting tempdb to autoshrink but that was rejected.

SQL = Scarcely Qualifies as a Language

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Carl Federl (6/17/2009)
Just for discussion, how do you stop anyone from using tempdb ?

Do what any DBA does... setup a script to wait for the appropriate quiet time. 🙂

I've used the file shrink method while people are in the system and using TempDB. Doesn't always work, but a script can be setup to say, "Ok... that didn't work... wait 15 minutes and try again."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ss-457805 SSCertifiable Points: 5872 More actions · Answer 7

Thank you all again for ur responses;

Managed to get a planned downtime to restart the sql server.

Also i added the estimate only clause to CheckDB on our teste server which has helped. The file hasn't grown since then. Will be applying this change on to the Live system. Thank you George, i think u suggested this.:-)

blog: http://sarveshsingh.com Twitter: @sarveshsing

george sibbald SSC Guru Points: 104210 More actions · Answer 8

sarvesh singh (6/18/2009)
Thank you all again for ur responses;
Managed to get a planned downtime to restart the sql server.
Also i added the estimate only clause to CheckDB on our teste server which has helped. The file hasn't grown since then. Will be applying this change on to the Live system. Thank you George, i think u suggested this.:-)

thanks for the feedback. Make sure you read up on checkdb in BOL. with the estimateonly clause the checkdb is not actually performed.

---------------------------------------------------------------------

tosscrosby SSCoach Points: 18045 More actions · Answer 9

george sibbald (6/18/2009)
sarvesh singh (6/18/2009)
Thank you all again for ur responses;
Managed to get a planned downtime to restart the sql server.
Also i added the estimate only clause to CheckDB on our teste server which has helped. The file hasn't grown since then. Will be applying this change on to the Live system. Thank you George, i think u suggested this.:-)
thanks for the feedback. Make sure you read up on checkdb in BOL. with the estimateonly clause the checkdb is not actually performed.

Which, personally, I would be sure IS performed at LEAST once a week (more frequently if time permits)......

-- You can't be late until you show up.

george sibbald SSC Guru Points: 104210 More actions · Answer 10

tosscrosby (6/19/2009)
george sibbald (6/18/2009)
sarvesh singh (6/18/2009)
Thank you all again for ur responses;
Managed to get a planned downtime to restart the sql server.
Also i added the estimate only clause to CheckDB on our teste server which has helped. The file hasn't grown since then. Will be applying this change on to the Live system. Thank you George, i think u suggested this.:-)
thanks for the feedback. Make sure you read up on checkdb in BOL. with the estimateonly clause the checkdb is not actually performed.
Which, personally, I would be sure IS performed at LEAST once a week (more frequently if time permits)......

exactly. I didn't want the OP to put estimateonly in his production checkdb and thus not actually be running an integrity check.

---------------------------------------------------------------------

ss-457805 SSCertifiable Points: 5872 More actions · Answer 11

Guys,

I didn't explain it right in my previous post. My bad.

the problem was resolved by restarting the sql server.

We've run dbcc checkdb with estimateonly on the client server to see how much space is needed. They have agreed on increasing the tempdb space which will help.

DBCC checkdb is still scheduled to run every week.

blog: http://sarveshsingh.com Twitter: @sarveshsing

george sibbald SSC Guru Points: 104210 More actions · Answer 12

sarvesh singh (6/19/2009)
Guys,
I didn't explain it right in my previous post. My bad.
the problem was resolved by restarting the sql server.
We've run dbcc checkdb with estimateonly on the client server to see how much space is needed. They have agreed on increasing the tempdb space which will help.
DBCC checkdb is still scheduled to run every week.

🙂

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

sarvesh singh (6/19/2009)
the problem was resolved by restarting the sql server.

Heh... nope... just the immediate symptoms. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tosscrosby SSCoach Points: 18045 More actions · Answer 14

sarvesh singh (6/18/2009)
our teste server

I don't even want to know! 😉

-- You can't be late until you show up.

george sibbald SSC Guru Points: 104210 More actions · Answer 15

tosscrosby (6/19/2009)
sarvesh singh (6/18/2009)
our teste server
I don't even want to know! 😉

we always have a failover pair for those type of servers. 😉

---------------------------------------------------------------------