| Author |
tempdb.mdf too large intead of tempdb.ldf
|
|
| soonyu 2006-02-15, 3:23 am |
| Hi..
I find out tempdb.mdf too large.. around 100gb..
even through all transaction is stop..
how can we clear the content like clear log file?
or any solution..
Cheers
| |
| Mike Hodgson 2006-02-15, 3:23 am |
| tempdb is easy. Since it's recreated from the model database every time
you start SQL Server just stop & start the SQL service. Alternately you
can use DBCC SHRINKDATABASE or DBCC SHRINKFILE - look them up in BOL.
But you should be wary about shrinking DB files - see Tibor's article on
it (http://www.karaszi.com/*SQL*Server/...ont*_shrink.asp).
--
*mike hodgson*
http://sqlnerd.blogspot.com
soonyu wrote:
>Hi..
>I find out tempdb.mdf too large.. around 100gb..
>even through all transaction is stop..
>how can we clear the content like clear log file?
>or any solution..
>
>Cheers
>
>
| |
| Tibor Karaszi 2006-02-15, 3:23 am |
| > tempdb is easy. Since it's recreated from the model database every time
> you start SQL Server just stop & start the SQL service.
I agree, but want to push one important point. The size for the tempdb database files is picked up
from master..sysaltfiles. So, if you have large tempdb database files already at startup, don't only
check model. Also check master..sysaltfiles.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"Mike Hodgson" <e1minst3r@gmail.com> wrote in message news:OqxNPcgMGHA.740@TK2MSFTNGP12.phx.gbl...
> tempdb is easy. Since it's recreated from the model database every time
> you start SQL Server just stop & start the SQL service. Alternately you
> can use DBCC SHRINKDATABASE or DBCC SHRINKFILE - look them up in BOL.
> But you should be wary about shrinking DB files - see Tibor's article on
> it (http://www.karaszi.com/*SQL*Server/...ont*_shrink.asp).
>
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
>
>
> soonyu wrote:
>
>
| |
| soonyu 2006-02-15, 7:23 am |
| yes.. total agree from the 2 point given.
But problem is.. if tempdb is growth since server start until 100gb and that
is not possible to restart the sql service.. how we going to empty the
tempdb.mdf
"Tibor Karaszi" wrote:
>
> I agree, but want to push one important point. The size for the tempdb database files is picked up
> from master..sysaltfiles. So, if you have large tempdb database files already at startup, don't only
> check model. Also check master..sysaltfiles.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "Mike Hodgson" <e1minst3r@gmail.com> wrote in message news:OqxNPcgMGHA.740@TK2MSFTNGP12.phx.gbl...
>
>
| |
| Mike Hodgson 2006-02-15, 8:23 pm |
| If you're concerned about being able to restart the service then just
use DBCC SHRINKFILE (no need to stop & restart the service). Although
if both your model database and master.dbo.sysaltfiles table look
reasonable then there should be no reason that tempdb would prevent the
service from starting methinks.
There must be a reason your tempdb database has grown so large. It
might be a good idea to figure out when it grew to that size and what
caused it so you can avoid it in the future.
--
*mike hodgson*
http://sqlnerd.blogspot.com
soonyu wrote:
[color=darkred]
>yes.. total agree from the 2 point given.
>But problem is.. if tempdb is growth since server start until 100gb and that
>is not possible to restart the sql service.. how we going to empty the
>tempdb.mdf
>
>"Tibor Karaszi" wrote:
>
>
>
|
|
|
|