|
Home > Archive > MS SQL Server > March 2006 > Tempdb database is too big
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Tempdb database is too big
|
|
|
| Hi,
I have a SQL server database that is about 5G in size,
but why my SQL, tempdb database size is 3.5G in size?
TempDB never seem to shrink itself. I tried shrinking with no succcess. Is
it supposed to stay that size?
MC
| |
| Uri Dimant 2006-03-07, 9:23 am |
| MC
http://www.aspfaq.com/show.asp?id=2446
"MC" <webmaster@ozoptics.com> wrote in message
news:uG6LpDfQGHA.3804@TK2MSFTNGP15.phx.gbl...
> Hi,
>
> I have a SQL server database that is about 5G in size,
> but why my SQL, tempdb database size is 3.5G in size?
>
> TempDB never seem to shrink itself. I tried shrinking with no succcess.
> Is
> it supposed to stay that size?
> MC
>
>
| |
|
| Information on that link doesn't seemt to help me.
I have enough disk space. when I re-boot the server, tempdb was supposed to
be re-created. This is not the case. When re-booted server, tempdb stays the
same size.
MC
"Uri Dimant" <urid@iscar.co.il> wrote in message
news:O0jCvHfQGHA.3896@TK2MSFTNGP15.phx.gbl...
> MC
> http://www.aspfaq.com/show.asp?id=2446
>
>
>
>
> "MC" <webmaster@ozoptics.com> wrote in message
> news:uG6LpDfQGHA.3804@TK2MSFTNGP15.phx.gbl...
>
>
| |
|
| If I do this query
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev,
SIZE = 200MB)
GO
I get error Server: Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than current size.
But data usage is only 50M in size, and there are no current proccess or
transaction
MC
"Uri Dimant" <urid@iscar.co.il> wrote in message
news:O0jCvHfQGHA.3896@TK2MSFTNGP15.phx.gbl...
> MC
> http://www.aspfaq.com/show.asp?id=2446
>
>
>
>
> "MC" <webmaster@ozoptics.com> wrote in message
> news:uG6LpDfQGHA.3804@TK2MSFTNGP15.phx.gbl...
>
>
| |
| Francis 2006-03-07, 11:23 am |
| The ALTER database statement can be used to increase the files size but you
must use DBCC SHRINKDATABASE to shrink the database.
"MC" wrote:
> If I do this query
> USE master
> GO
> ALTER DATABASE tempdb
> MODIFY FILE
> (NAME = tempdev,
> SIZE = 200MB)
> GO
>
> I get error Server: Msg 5039, Level 16, State 1, Line 1
> MODIFY FILE failed. Specified size is less than current size.
> But data usage is only 50M in size, and there are no current proccess or
> transaction
> MC
>
>
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:O0jCvHfQGHA.3896@TK2MSFTNGP15.phx.gbl...
>
>
>
| |
| Tibor Karaszi 2006-03-07, 11:23 am |
| Tempdb is different from the other databases. Such an ALTER DATABASE will change the size in
master..sysaltfiles, which is what SQL Server is using at startup. So setting this to a smaller size
will mean a smaller tempdb at startup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"Francis" <Francis@discussions.microsoft.com> wrote in message
news:E8AFAB0F-BD75-4C7F-BA71- 2974CCDD7DE2@microso
ft.com...[color=darkred]
> The ALTER database statement can be used to increase the files size but you
> must use DBCC SHRINKDATABASE to shrink the database.
>
> "MC" wrote:
>
| |
|
| When use this procedure to shrink
******************
USE tempdb
GO
DBCC SHRINKFILE (Tempdev, 400)
GO
*************
I get the errors below. So what is the right procedure to drop this database
and let system re-create it?
(Re-booting server won't re-create this database, because something stuck in
there)
Server: Msg 8909, Level 16, State 1, Line 1
Table Corrupt: Object ID 1, index ID 0, page ID (1:443191). The PageId in
the page header = (1:7980628).
Server: Msg 8909, Level 16, State 1, Line 1
Table Corrupt: Object ID 0, index ID 0, page ID (1:443190). The PageId in
the page header = (1:7980628).
Server: Msg 8909, Level 16, State 1, Line 1
Table Corrupt: Object ID 51, index ID 0, page ID (1:443189). The PageId in
the page header = (53:3211313).
Server: Msg 8909, Level 16, State 1, Line 1
Table Corrupt: Object ID 1, index ID 0, page ID (1:443188). The PageId in
the page header = (1:7980628).
Server: Msg 8909, Level 16, State 1, Line 1
Table Corrupt: Object ID 0, index ID 0, page ID (1:443187). The PageId in
the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table Corrupt: Object ID 0, index ID 0, page ID (1:443186). The PageId in
the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table Corrupt: Object ID 0, index ID 0, page ID (1:443185). The PageId in
the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table Corrupt: Object ID 1, index ID 0, page ID (1:443175). The PageId in
the page header = (33:1312516392).
Server: Msg 8909, Level 16, State 1, Line 1
Table Corrupt: Object ID 1, index ID 0, page ID (1:443174). The PageId in
the page header = (32:1459118116).
Server: Msg 8909, Level 16, State 1, Line 1
.....
"Francis" <Francis@discussions.microsoft.com> wrote in message
news:E8AFAB0F-BD75-4C7F-BA71- 2974CCDD7DE2@microso
ft.com...
> The ALTER database statement can be used to increase the files size but
you[color=darkred]
> must use DBCC SHRINKDATABASE to shrink the database.
>
> "MC" wrote:
>
succcess.[color=darkred]
| |
| Tibor Karaszi 2006-03-07, 11:23 am |
| Restarting SQL Server should re-create the tempdb database. How do you determine that it doesn't?
Regarding corruptions, here are general recommendations:
http://www.karaszi.com/SQLServer/ i...t
_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"MC" <webmaster@ozoptics.com> wrote in message news:u5$m8AgQGHA.2692@TK2MSFTNGP14.phx.gbl...
> When use this procedure to shrink
> ******************
> USE tempdb
> GO
> DBCC SHRINKFILE (Tempdev, 400)
> GO
> *************
> I get the errors below. So what is the right procedure to drop this database
> and let system re-create it?
> (Re-booting server won't re-create this database, because something stuck in
> there)
>
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 1, index ID 0, page ID (1:443191). The PageId in
> the page header = (1:7980628).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 0, index ID 0, page ID (1:443190). The PageId in
> the page header = (1:7980628).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 51, index ID 0, page ID (1:443189). The PageId in
> the page header = (53:3211313).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 1, index ID 0, page ID (1:443188). The PageId in
> the page header = (1:7980628).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 0, index ID 0, page ID (1:443187). The PageId in
> the page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 0, index ID 0, page ID (1:443186). The PageId in
> the page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 0, index ID 0, page ID (1:443185). The PageId in
> the page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 1, index ID 0, page ID (1:443175). The PageId in
> the page header = (33:1312516392).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 1, index ID 0, page ID (1:443174). The PageId in
> the page header = (32:1459118116).
> Server: Msg 8909, Level 16, State 1, Line 1
> ....
>
>
> "Francis" <Francis@discussions.microsoft.com> wrote in message
> news:E8AFAB0F-BD75-4C7F-BA71- 2974CCDD7DE2@microso
ft.com...
> you
> succcess.
>
>
| |
| Francis 2006-03-07, 11:23 am |
| see http://support.microsoft.com/default.aspx/kb/307487 for Microsoft advice
on shrinking TEMPDB
"MC" wrote:
> When use this procedure to shrink
> ******************
> USE tempdb
> GO
> DBCC SHRINKFILE (Tempdev, 400)
> GO
> *************
> I get the errors below. So what is the right procedure to drop this database
> and let system re-create it?
> (Re-booting server won't re-create this database, because something stuck in
> there)
>
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 1, index ID 0, page ID (1:443191). The PageId in
> the page header = (1:7980628).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 0, index ID 0, page ID (1:443190). The PageId in
> the page header = (1:7980628).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 51, index ID 0, page ID (1:443189). The PageId in
> the page header = (53:3211313).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 1, index ID 0, page ID (1:443188). The PageId in
> the page header = (1:7980628).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 0, index ID 0, page ID (1:443187). The PageId in
> the page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 0, index ID 0, page ID (1:443186). The PageId in
> the page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 0, index ID 0, page ID (1:443185). The PageId in
> the page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 1, index ID 0, page ID (1:443175). The PageId in
> the page header = (33:1312516392).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table Corrupt: Object ID 1, index ID 0, page ID (1:443174). The PageId in
> the page header = (32:1459118116).
> Server: Msg 8909, Level 16, State 1, Line 1
> .....
>
>
> "Francis" <Francis@discussions.microsoft.com> wrote in message
> news:E8AFAB0F-BD75-4C7F-BA71- 2974CCDD7DE2@microso
ft.com...
> you
> succcess.
>
>
>
| |
|
| Right after I re-boot, I look at the size. It is impossible to increase
tempdb size to 3G when not too many connection and transactions for that
moment
MC
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:#SWayVgQGHA.2436@TK2MSFTNGP11.phx.gbl...
> Restarting SQL Server should re-create the tempdb database. How do you
determine that it doesn't?
> Regarding corruptions, here are general recommendations:
> http://www.karaszi.com/SQLServer/ i...t
_db.asp
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "MC" <webmaster@ozoptics.com> wrote in message
news:u5$m8AgQGHA.2692@TK2MSFTNGP14.phx.gbl...
database[color=darkr
ed]
stuck in[color=darkred]
in[color=darkred]
in[color=darkred]
in[color=darkred]
in[color=darkred]
in[color=darkred]
in[color=darkred]
in[color=darkred]
in[color=darkred]
in[color=darkred]
or[color=darkred]
>
| |
| Tibor Karaszi 2006-03-08, 3:23 am |
| > Right after I re-boot, I look at the size.
If it isn't the same size as master..sysaltfiles, then you have a problem and should open a case
with MS. If it is, and you want to make it smaller, use ALTER DATABASE. The size of tempdb database
files is picked up from what it say in master..sysaltfiles.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"MC" <webmaster@ozoptics.com> wrote in message news:ekfwCmjQGHA.1204@TK2MSFTNGP12.phx.gbl...
> Right after I re-boot, I look at the size. It is impossible to increase
> tempdb size to 3G when not too many connection and transactions for that
> moment
> MC
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
> message news:#SWayVgQGHA.2436@TK2MSFTNGP11.phx.gbl...
> determine that it doesn't?
> news:u5$m8AgQGHA.2692@TK2MSFTNGP14.phx.gbl...
> database
> stuck in
> in
> in
> in
> in
> in
> in
> in
> in
> in
> or
>
>
| |
|
| Used space is only 51.44M, but the file size is 3411M
I will re-boot the server, make tempDB single user mode, and try to reduce
some time this week, will post the result on this news group or contact
Microsoft support
MC
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:eH7CaKoQGHA.4900@TK2MSFTNGP09.phx.gbl...
>
> If it isn't the same size as master..sysaltfiles, then you have a problem
and should open a case
> with MS. If it is, and you want to make it smaller, use ALTER DATABASE.
The size of tempdb database
> files is picked up from what it say in master..sysaltfiles.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "MC" <webmaster@ozoptics.com> wrote in message
news:ekfwCmjQGHA.1204@TK2MSFTNGP12.phx.gbl...
in[color=darkred]
PageId[color=darkred
]
PageId[color=darkred
]
PageId[color=darkred
]
PageId[color=darkred
]
PageId[color=darkred
]
PageId[color=darkred
]
PageId[color=darkred
]
PageId[color=darkred
]
PageId[color=darkred
]
but[color=darkred]
proccess[color=darkr
ed]
>
>
| |
| Tibor Karaszi 2006-03-08, 9:23 am |
| Did you check what size is specified for tempdb in master..sysaltfiles?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"MC" <webmaster@ozoptics.com> wrote in message news:ud4dturQGHA.4608@tk2msftngp13.phx.gbl...
> Used space is only 51.44M, but the file size is 3411M
> I will re-boot the server, make tempDB single user mode, and try to reduce
> some time this week, will post the result on this news group or contact
> Microsoft support
> MC
>
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
> message news:eH7CaKoQGHA.4900@TK2MSFTNGP09.phx.gbl...
> and should open a case
> The size of tempdb database
> news:ekfwCmjQGHA.1204@TK2MSFTNGP12.phx.gbl...
> in
> PageId
> PageId
> PageId
> PageId
> PageId
> PageId
> PageId
> PageId
> PageId
> but
> proccess
>
>
| |
|
| Size on that table shows 443192
growth=10, status=1048578
Log size is 861, growth=10 status =1048642
MC
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:#1MOw#rQGHA.516@TK2MSFTNGP15.phx.gbl...
> Did you check what size is specified for tempdb in master..sysaltfiles?
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "MC" <webmaster@ozoptics.com> wrote in message
news:ud4dturQGHA.4608@tk2msftngp13.phx.gbl...
reduce[color=darkred
]
in[color=darkred]
problem[color=darkre
d]
increase[color=darkr
ed]
that[color=darkred]
wrote[color=darkred]
you[color=darkred]
this[color=darkred]
something[color=dark
red]
size[color=darkred]
no[color=darkred]
>
>
| |
| Tibor Karaszi 2006-03-08, 11:23 am |
| Use ALTER DATABASE to specify a smaller size for the tempdb mdf file (and ldf if you wish) and you
will see that tempdb is created with a smaller size next time you start SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"MC" <webmaster@ozoptics.com> wrote in message news:eBl$pHsQGHA.532@TK2MSFTNGP15.phx.gbl...
> Size on that table shows 443192
> growth=10, status=1048578
> Log size is 861, growth=10 status =1048642
> MC
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
> message news:#1MOw#rQGHA.516@TK2MSFTNGP15.phx.gbl...
> news:ud4dturQGHA.4608@tk2msftngp13.phx.gbl...
> reduce
> in
> problem
> increase
> that
> wrote
> you
> this
> something
> size
> no
>
>
| |
|
| Here is what I did.
I re-booted my SQL Server (7), went back to check the data file size, was
still 3411M. I used the query analyser to SHRINK it to 60M, restarted SQL
server, check it was successfully reduced in size. Most of the time, tried
Enterprise Manager to do it with no success. a couple of times tried using
query analyser, but gave errors, I guess running query analyser right after
re-boot was the key solution
Thanks for the great free help.
Regards,
Mehmet Camci
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:#WQ22dsQGHA.4144@TK2MSFTNGP11.phx.gbl...
> Use ALTER DATABASE to specify a smaller size for the tempdb mdf file (and
ldf if you wish) and you
> will see that tempdb is created with a smaller size next time you start
SQL Server.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "MC" <webmaster@ozoptics.com> wrote in message
news:eBl$pHsQGHA.532@TK2MSFTNGP15.phx.gbl...
in[color=darkred]
contact[color=darkre
d]
wrote[color=darkred]
DATABASE.[color=darkred]
for[color=darkred]
do[color=darkred]
The[color=darkred]
size.[color=darkred]
with[color=darkred]
>
>
|
|
|
|
|