Hi All
For one of our SQL server 2000 database tempdb size is origionally set to
"8KB"(That is default value). After monitoring it for some time, I think we
need to change the default value to "650 KB".
Can I change the default value while the server is online (so that on next
reboot it automatically changes) or I need to reboot the server.
In any case what is the best way and commands to do this?
Thanks
Hi db
Where are you getting this value from? Are you sure you don't mean MB?
In any case, you can change the tempdb startup size with the ALTER DATABASE
command, to change the size of the primary file. Look up ALTER DATABASE in
the Books Online, and read about the MODIFY FILE option for the details
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"db" <db@.discussions.microsoft.com> wrote in message
news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
> Hi All
> For one of our SQL server 2000 database tempdb size is origionally set to
> "8KB"(That is default value). After monitoring it for some time, I think
> we
> need to change the default value to "650 KB".
> Can I change the default value while the server is online (so that on next
> reboot it automatically changes) or I need to reboot the server.
> In any case what is the best way and commands to do this?
> Thanks
> --
>
|||I got the values from Best practice analyzer tool results for sql server
2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
months back tempdb shot up to few GB, but that may be a bad query or
something.
From your response it looks like I need not reboot the server?
Thanks .. go through books on line now.
"Kalen Delaney" wrote:
> Hi db
> Where are you getting this value from? Are you sure you don't mean MB?
> In any case, you can change the tempdb startup size with the ALTER DATABASE
> command, to change the size of the primary file. Look up ALTER DATABASE in
> the Books Online, and read about the MODIFY FILE option for the details
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
>
>
|||> I got the values from Best practice analyzer tool results for sql server[vbcol=seagreen]
> 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
> months back tempdb shot up to few GB, but that may be a bad query or
> something.
> From your response it looks like I need not reboot the server?
> Thanks .. I will go through books on line for details.
> --
>
> "Kalen Delaney" wrote:
|||How did you get the value for YOUR tempdb from the BPA tool? How do you know
YOUR tempdb is 8KB?
650 KB is still way too small. Can you elaborate on how you got this value?
Whether or not you need to reboot your server depends on what you want to
accomplish. The default size of tempdb means the size it starts up with,
when you reboot. So if you change the size with ALTER DATABASE, it won't
take affect until you reboot. But if tempdb is large enough for right now,
maybe you don't need to reboot.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"db" <db@.discussions.microsoft.com> wrote in message
news:A7749815-A31F-453E-BC3F-2839977BA763@.microsoft.com...[vbcol=seagreen]
>I got the values from Best practice analyzer tool results for sql server
> 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
> months back tempdb shot up to few GB, but that may be a bad query or
> something.
> From your response it looks like I need not reboot the server?
> Thanks .. go through books on line now.
> --
>
> "Kalen Delaney" wrote:
|||My Typo. Tempdb.mdf file is 655 MB or 670,700 KB on disk. Tempdb is set to
autogrow and it grow from 8MB to 655MB. I do not want to reboot the server
now, but change the default value of tempdb(Whenever reboot happens it should
change) .
Can i change it from
sql enterprise manager --> database-->properties-->datafiles-->space
allocated (Change value here)
"Kalen Delaney" wrote:
> How did you get the value for YOUR tempdb from the BPA tool? How do you know
> YOUR tempdb is 8KB?
> 650 KB is still way too small. Can you elaborate on how you got this value?
> Whether or not you need to reboot your server depends on what you want to
> accomplish. The default size of tempdb means the size it starts up with,
> when you reboot. So if you change the size with ALTER DATABASE, it won't
> take affect until you reboot. But if tempdb is large enough for right now,
> maybe you don't need to reboot.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:A7749815-A31F-453E-BC3F-2839977BA763@.microsoft.com...
>
>
Showing posts with label origionally. Show all posts
Showing posts with label origionally. Show all posts
Monday, March 19, 2012
Changeing default value of tempdb
Hi All
For one of our SQL server 2000 database tempdb size is origionally set to
"8KB"(That is default value). After monitoring it for some time, I think we
need to change the default value to "650 KB".
Can I change the default value while the server is online (so that on next
reboot it automatically changes) or I need to reboot the server.
In any case what is the best way and commands to do this?
Thanks
--Hi db
Where are you getting this value from? Are you sure you don't mean MB?
In any case, you can change the tempdb startup size with the ALTER DATABASE
command, to change the size of the primary file. Look up ALTER DATABASE in
the Books Online, and read about the MODIFY FILE option for the details
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"db" <db@.discussions.microsoft.com> wrote in message
news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
> Hi All
> For one of our SQL server 2000 database tempdb size is origionally set to
> "8KB"(That is default value). After monitoring it for some time, I think
> we
> need to change the default value to "650 KB".
> Can I change the default value while the server is online (so that on next
> reboot it automatically changes) or I need to reboot the server.
> In any case what is the best way and commands to do this?
> Thanks
> --
>|||I got the values from Best practice analyzer tool results for sql server
2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
months back tempdb shot up to few GB, but that may be a bad query or
something.
From your response it looks like I need not reboot the server?
Thanks .. go through books on line now.
--
"Kalen Delaney" wrote:
> Hi db
> Where are you getting this value from? Are you sure you don't mean MB?
> In any case, you can change the tempdb startup size with the ALTER DATABASE
> command, to change the size of the primary file. Look up ALTER DATABASE in
> the Books Online, and read about the MODIFY FILE option for the details
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
> > Hi All
> >
> > For one of our SQL server 2000 database tempdb size is origionally set to
> > "8KB"(That is default value). After monitoring it for some time, I think
> > we
> > need to change the default value to "650 KB".
> >
> > Can I change the default value while the server is online (so that on next
> > reboot it automatically changes) or I need to reboot the server.
> >
> > In any case what is the best way and commands to do this?
> >
> > Thanks
> > --
> >
>
>|||> I got the values from Best practice analyzer tool results for sql server
> 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
> months back tempdb shot up to few GB, but that may be a bad query or
> something.
> From your response it looks like I need not reboot the server?
> Thanks .. I will go through books on line for details.
> --
>
> "Kalen Delaney" wrote:
> > Hi db
> >
> > Where are you getting this value from? Are you sure you don't mean MB?
> >
> > In any case, you can change the tempdb startup size with the ALTER DATABASE
> > command, to change the size of the primary file. Look up ALTER DATABASE in
> > the Books Online, and read about the MODIFY FILE option for the details
> >
> > --
> > HTH
> > Kalen Delaney, SQL Server MVP
> > www.InsideSQLServer.com
> > http://sqlblog.com
> >
> >
> > "db" <db@.discussions.microsoft.com> wrote in message
> > news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
> > > Hi All
> > >
> > > For one of our SQL server 2000 database tempdb size is origionally set to
> > > "8KB"(That is default value). After monitoring it for some time, I think
> > > we
> > > need to change the default value to "650 KB".
> > >
> > > Can I change the default value while the server is online (so that on next
> > > reboot it automatically changes) or I need to reboot the server.
> > >
> > > In any case what is the best way and commands to do this?
> > >
> > > Thanks
> > > --
> > >
> >
> >
> >|||How did you get the value for YOUR tempdb from the BPA tool? How do you know
YOUR tempdb is 8KB?
650 KB is still way too small. Can you elaborate on how you got this value?
Whether or not you need to reboot your server depends on what you want to
accomplish. The default size of tempdb means the size it starts up with,
when you reboot. So if you change the size with ALTER DATABASE, it won't
take affect until you reboot. But if tempdb is large enough for right now,
maybe you don't need to reboot.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"db" <db@.discussions.microsoft.com> wrote in message
news:A7749815-A31F-453E-BC3F-2839977BA763@.microsoft.com...
>I got the values from Best practice analyzer tool results for sql server
> 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
> months back tempdb shot up to few GB, but that may be a bad query or
> something.
> From your response it looks like I need not reboot the server?
> Thanks .. go through books on line now.
> --
>
> "Kalen Delaney" wrote:
>> Hi db
>> Where are you getting this value from? Are you sure you don't mean MB?
>> In any case, you can change the tempdb startup size with the ALTER
>> DATABASE
>> command, to change the size of the primary file. Look up ALTER DATABASE
>> in
>> the Books Online, and read about the MODIFY FILE option for the details
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
>> > Hi All
>> >
>> > For one of our SQL server 2000 database tempdb size is origionally set
>> > to
>> > "8KB"(That is default value). After monitoring it for some time, I
>> > think
>> > we
>> > need to change the default value to "650 KB".
>> >
>> > Can I change the default value while the server is online (so that on
>> > next
>> > reboot it automatically changes) or I need to reboot the server.
>> >
>> > In any case what is the best way and commands to do this?
>> >
>> > Thanks
>> > --
>> >
>>|||My Typo. Tempdb.mdf file is 655 MB or 670,700 KB on disk. Tempdb is set to
autogrow and it grow from 8MB to 655MB. I do not want to reboot the server
now, but change the default value of tempdb(Whenever reboot happens it should
change) .
Can i change it from
sql enterprise manager --> database-->properties-->datafiles-->space
allocated (Change value here)
--
"Kalen Delaney" wrote:
> How did you get the value for YOUR tempdb from the BPA tool? How do you know
> YOUR tempdb is 8KB?
> 650 KB is still way too small. Can you elaborate on how you got this value?
> Whether or not you need to reboot your server depends on what you want to
> accomplish. The default size of tempdb means the size it starts up with,
> when you reboot. So if you change the size with ALTER DATABASE, it won't
> take affect until you reboot. But if tempdb is large enough for right now,
> maybe you don't need to reboot.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:A7749815-A31F-453E-BC3F-2839977BA763@.microsoft.com...
> >I got the values from Best practice analyzer tool results for sql server
> > 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
> > months back tempdb shot up to few GB, but that may be a bad query or
> > something.
> >
> > From your response it looks like I need not reboot the server?
> >
> > Thanks .. go through books on line now.
> > --
> >
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi db
> >>
> >> Where are you getting this value from? Are you sure you don't mean MB?
> >>
> >> In any case, you can change the tempdb startup size with the ALTER
> >> DATABASE
> >> command, to change the size of the primary file. Look up ALTER DATABASE
> >> in
> >> the Books Online, and read about the MODIFY FILE option for the details
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "db" <db@.discussions.microsoft.com> wrote in message
> >> news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
> >> > Hi All
> >> >
> >> > For one of our SQL server 2000 database tempdb size is origionally set
> >> > to
> >> > "8KB"(That is default value). After monitoring it for some time, I
> >> > think
> >> > we
> >> > need to change the default value to "650 KB".
> >> >
> >> > Can I change the default value while the server is online (so that on
> >> > next
> >> > reboot it automatically changes) or I need to reboot the server.
> >> >
> >> > In any case what is the best way and commands to do this?
> >> >
> >> > Thanks
> >> > --
> >> >
> >>
> >>
> >>
>
>|||Yes, the change you do in EM will define the new size for tempdb for next startup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"db" <db@.discussions.microsoft.com> wrote in message
news:936E7677-57D2-4720-A537-84083B502622@.microsoft.com...
> My Typo. Tempdb.mdf file is 655 MB or 670,700 KB on disk. Tempdb is set to
> autogrow and it grow from 8MB to 655MB. I do not want to reboot the server
> now, but change the default value of tempdb(Whenever reboot happens it should
> change) .
> Can i change it from
> sql enterprise manager --> database-->properties-->datafiles-->space
> allocated (Change value here)
> --
>
> "Kalen Delaney" wrote:
>> How did you get the value for YOUR tempdb from the BPA tool? How do you know
>> YOUR tempdb is 8KB?
>> 650 KB is still way too small. Can you elaborate on how you got this value?
>> Whether or not you need to reboot your server depends on what you want to
>> accomplish. The default size of tempdb means the size it starts up with,
>> when you reboot. So if you change the size with ALTER DATABASE, it won't
>> take affect until you reboot. But if tempdb is large enough for right now,
>> maybe you don't need to reboot.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:A7749815-A31F-453E-BC3F-2839977BA763@.microsoft.com...
>> >I got the values from Best practice analyzer tool results for sql server
>> > 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
>> > months back tempdb shot up to few GB, but that may be a bad query or
>> > something.
>> >
>> > From your response it looks like I need not reboot the server?
>> >
>> > Thanks .. go through books on line now.
>> > --
>> >
>> >
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> Hi db
>> >>
>> >> Where are you getting this value from? Are you sure you don't mean MB?
>> >>
>> >> In any case, you can change the tempdb startup size with the ALTER
>> >> DATABASE
>> >> command, to change the size of the primary file. Look up ALTER DATABASE
>> >> in
>> >> the Books Online, and read about the MODIFY FILE option for the details
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.InsideSQLServer.com
>> >> http://sqlblog.com
>> >>
>> >>
>> >> "db" <db@.discussions.microsoft.com> wrote in message
>> >> news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
>> >> > Hi All
>> >> >
>> >> > For one of our SQL server 2000 database tempdb size is origionally set
>> >> > to
>> >> > "8KB"(That is default value). After monitoring it for some time, I
>> >> > think
>> >> > we
>> >> > need to change the default value to "650 KB".
>> >> >
>> >> > Can I change the default value while the server is online (so that on
>> >> > next
>> >> > reboot it automatically changes) or I need to reboot the server.
>> >> >
>> >> > In any case what is the best way and commands to do this?
>> >> >
>> >> > Thanks
>> >> > --
>> >> >
>> >>
>> >>
>> >>
>>
For one of our SQL server 2000 database tempdb size is origionally set to
"8KB"(That is default value). After monitoring it for some time, I think we
need to change the default value to "650 KB".
Can I change the default value while the server is online (so that on next
reboot it automatically changes) or I need to reboot the server.
In any case what is the best way and commands to do this?
Thanks
--Hi db
Where are you getting this value from? Are you sure you don't mean MB?
In any case, you can change the tempdb startup size with the ALTER DATABASE
command, to change the size of the primary file. Look up ALTER DATABASE in
the Books Online, and read about the MODIFY FILE option for the details
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"db" <db@.discussions.microsoft.com> wrote in message
news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
> Hi All
> For one of our SQL server 2000 database tempdb size is origionally set to
> "8KB"(That is default value). After monitoring it for some time, I think
> we
> need to change the default value to "650 KB".
> Can I change the default value while the server is online (so that on next
> reboot it automatically changes) or I need to reboot the server.
> In any case what is the best way and commands to do this?
> Thanks
> --
>|||I got the values from Best practice analyzer tool results for sql server
2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
months back tempdb shot up to few GB, but that may be a bad query or
something.
From your response it looks like I need not reboot the server?
Thanks .. go through books on line now.
--
"Kalen Delaney" wrote:
> Hi db
> Where are you getting this value from? Are you sure you don't mean MB?
> In any case, you can change the tempdb startup size with the ALTER DATABASE
> command, to change the size of the primary file. Look up ALTER DATABASE in
> the Books Online, and read about the MODIFY FILE option for the details
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
> > Hi All
> >
> > For one of our SQL server 2000 database tempdb size is origionally set to
> > "8KB"(That is default value). After monitoring it for some time, I think
> > we
> > need to change the default value to "650 KB".
> >
> > Can I change the default value while the server is online (so that on next
> > reboot it automatically changes) or I need to reboot the server.
> >
> > In any case what is the best way and commands to do this?
> >
> > Thanks
> > --
> >
>
>|||> I got the values from Best practice analyzer tool results for sql server
> 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
> months back tempdb shot up to few GB, but that may be a bad query or
> something.
> From your response it looks like I need not reboot the server?
> Thanks .. I will go through books on line for details.
> --
>
> "Kalen Delaney" wrote:
> > Hi db
> >
> > Where are you getting this value from? Are you sure you don't mean MB?
> >
> > In any case, you can change the tempdb startup size with the ALTER DATABASE
> > command, to change the size of the primary file. Look up ALTER DATABASE in
> > the Books Online, and read about the MODIFY FILE option for the details
> >
> > --
> > HTH
> > Kalen Delaney, SQL Server MVP
> > www.InsideSQLServer.com
> > http://sqlblog.com
> >
> >
> > "db" <db@.discussions.microsoft.com> wrote in message
> > news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
> > > Hi All
> > >
> > > For one of our SQL server 2000 database tempdb size is origionally set to
> > > "8KB"(That is default value). After monitoring it for some time, I think
> > > we
> > > need to change the default value to "650 KB".
> > >
> > > Can I change the default value while the server is online (so that on next
> > > reboot it automatically changes) or I need to reboot the server.
> > >
> > > In any case what is the best way and commands to do this?
> > >
> > > Thanks
> > > --
> > >
> >
> >
> >|||How did you get the value for YOUR tempdb from the BPA tool? How do you know
YOUR tempdb is 8KB?
650 KB is still way too small. Can you elaborate on how you got this value?
Whether or not you need to reboot your server depends on what you want to
accomplish. The default size of tempdb means the size it starts up with,
when you reboot. So if you change the size with ALTER DATABASE, it won't
take affect until you reboot. But if tempdb is large enough for right now,
maybe you don't need to reboot.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"db" <db@.discussions.microsoft.com> wrote in message
news:A7749815-A31F-453E-BC3F-2839977BA763@.microsoft.com...
>I got the values from Best practice analyzer tool results for sql server
> 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
> months back tempdb shot up to few GB, but that may be a bad query or
> something.
> From your response it looks like I need not reboot the server?
> Thanks .. go through books on line now.
> --
>
> "Kalen Delaney" wrote:
>> Hi db
>> Where are you getting this value from? Are you sure you don't mean MB?
>> In any case, you can change the tempdb startup size with the ALTER
>> DATABASE
>> command, to change the size of the primary file. Look up ALTER DATABASE
>> in
>> the Books Online, and read about the MODIFY FILE option for the details
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
>> > Hi All
>> >
>> > For one of our SQL server 2000 database tempdb size is origionally set
>> > to
>> > "8KB"(That is default value). After monitoring it for some time, I
>> > think
>> > we
>> > need to change the default value to "650 KB".
>> >
>> > Can I change the default value while the server is online (so that on
>> > next
>> > reboot it automatically changes) or I need to reboot the server.
>> >
>> > In any case what is the best way and commands to do this?
>> >
>> > Thanks
>> > --
>> >
>>|||My Typo. Tempdb.mdf file is 655 MB or 670,700 KB on disk. Tempdb is set to
autogrow and it grow from 8MB to 655MB. I do not want to reboot the server
now, but change the default value of tempdb(Whenever reboot happens it should
change) .
Can i change it from
sql enterprise manager --> database-->properties-->datafiles-->space
allocated (Change value here)
--
"Kalen Delaney" wrote:
> How did you get the value for YOUR tempdb from the BPA tool? How do you know
> YOUR tempdb is 8KB?
> 650 KB is still way too small. Can you elaborate on how you got this value?
> Whether or not you need to reboot your server depends on what you want to
> accomplish. The default size of tempdb means the size it starts up with,
> when you reboot. So if you change the size with ALTER DATABASE, it won't
> take affect until you reboot. But if tempdb is large enough for right now,
> maybe you don't need to reboot.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:A7749815-A31F-453E-BC3F-2839977BA763@.microsoft.com...
> >I got the values from Best practice analyzer tool results for sql server
> > 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
> > months back tempdb shot up to few GB, but that may be a bad query or
> > something.
> >
> > From your response it looks like I need not reboot the server?
> >
> > Thanks .. go through books on line now.
> > --
> >
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi db
> >>
> >> Where are you getting this value from? Are you sure you don't mean MB?
> >>
> >> In any case, you can change the tempdb startup size with the ALTER
> >> DATABASE
> >> command, to change the size of the primary file. Look up ALTER DATABASE
> >> in
> >> the Books Online, and read about the MODIFY FILE option for the details
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "db" <db@.discussions.microsoft.com> wrote in message
> >> news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
> >> > Hi All
> >> >
> >> > For one of our SQL server 2000 database tempdb size is origionally set
> >> > to
> >> > "8KB"(That is default value). After monitoring it for some time, I
> >> > think
> >> > we
> >> > need to change the default value to "650 KB".
> >> >
> >> > Can I change the default value while the server is online (so that on
> >> > next
> >> > reboot it automatically changes) or I need to reboot the server.
> >> >
> >> > In any case what is the best way and commands to do this?
> >> >
> >> > Thanks
> >> > --
> >> >
> >>
> >>
> >>
>
>|||Yes, the change you do in EM will define the new size for tempdb for next startup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"db" <db@.discussions.microsoft.com> wrote in message
news:936E7677-57D2-4720-A537-84083B502622@.microsoft.com...
> My Typo. Tempdb.mdf file is 655 MB or 670,700 KB on disk. Tempdb is set to
> autogrow and it grow from 8MB to 655MB. I do not want to reboot the server
> now, but change the default value of tempdb(Whenever reboot happens it should
> change) .
> Can i change it from
> sql enterprise manager --> database-->properties-->datafiles-->space
> allocated (Change value here)
> --
>
> "Kalen Delaney" wrote:
>> How did you get the value for YOUR tempdb from the BPA tool? How do you know
>> YOUR tempdb is 8KB?
>> 650 KB is still way too small. Can you elaborate on how you got this value?
>> Whether or not you need to reboot your server depends on what you want to
>> accomplish. The default size of tempdb means the size it starts up with,
>> when you reboot. So if you change the size with ALTER DATABASE, it won't
>> take affect until you reboot. But if tempdb is large enough for right now,
>> maybe you don't need to reboot.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:A7749815-A31F-453E-BC3F-2839977BA763@.microsoft.com...
>> >I got the values from Best practice analyzer tool results for sql server
>> > 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
>> > months back tempdb shot up to few GB, but that may be a bad query or
>> > something.
>> >
>> > From your response it looks like I need not reboot the server?
>> >
>> > Thanks .. go through books on line now.
>> > --
>> >
>> >
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> Hi db
>> >>
>> >> Where are you getting this value from? Are you sure you don't mean MB?
>> >>
>> >> In any case, you can change the tempdb startup size with the ALTER
>> >> DATABASE
>> >> command, to change the size of the primary file. Look up ALTER DATABASE
>> >> in
>> >> the Books Online, and read about the MODIFY FILE option for the details
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.InsideSQLServer.com
>> >> http://sqlblog.com
>> >>
>> >>
>> >> "db" <db@.discussions.microsoft.com> wrote in message
>> >> news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
>> >> > Hi All
>> >> >
>> >> > For one of our SQL server 2000 database tempdb size is origionally set
>> >> > to
>> >> > "8KB"(That is default value). After monitoring it for some time, I
>> >> > think
>> >> > we
>> >> > need to change the default value to "650 KB".
>> >> >
>> >> > Can I change the default value while the server is online (so that on
>> >> > next
>> >> > reboot it automatically changes) or I need to reboot the server.
>> >> >
>> >> > In any case what is the best way and commands to do this?
>> >> >
>> >> > Thanks
>> >> > --
>> >> >
>> >>
>> >>
>> >>
>>
Changeing default value of tempdb
Hi All
For one of our SQL server 2000 database tempdb size is origionally set to
"8KB"(That is default value). After monitoring it for some time, I think we
need to change the default value to "650 KB".
Can I change the default value while the server is online (so that on next
reboot it automatically changes) or I need to reboot the server.
In any case what is the best way and commands to do this?
Thanks
--Hi db
Where are you getting this value from? Are you sure you don't mean MB?
In any case, you can change the tempdb startup size with the ALTER DATABASE
command, to change the size of the primary file. Look up ALTER DATABASE in
the Books Online, and read about the MODIFY FILE option for the details
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"db" <db@.discussions.microsoft.com> wrote in message
news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
> Hi All
> For one of our SQL server 2000 database tempdb size is origionally set to
> "8KB"(That is default value). After monitoring it for some time, I think
> we
> need to change the default value to "650 KB".
> Can I change the default value while the server is online (so that on next
> reboot it automatically changes) or I need to reboot the server.
> In any case what is the best way and commands to do this?
> Thanks
> --
>|||I got the values from Best practice analyzer tool results for sql server
2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
months back tempdb shot up to few GB, but that may be a bad query or
something.
From your response it looks like I need not reboot the server?
Thanks .. go through books on line now.
--
"Kalen Delaney" wrote:
> Hi db
> Where are you getting this value from? Are you sure you don't mean MB?
> In any case, you can change the tempdb startup size with the ALTER DATABAS
E
> command, to change the size of the primary file. Look up ALTER DATABASE in
> the Books Online, and read about the MODIFY FILE option for the details
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
>
>|||> I got the values from Best practice analyzer tool results for sql server[vbcol=seagreen]
> 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
> months back tempdb shot up to few GB, but that may be a bad query or
> something.
> From your response it looks like I need not reboot the server?
> Thanks .. I will go through books on line for details.
> --
>
> "Kalen Delaney" wrote:
>|||How did you get the value for YOUR tempdb from the BPA tool? How do you know
YOUR tempdb is 8KB?
650 KB is still way too small. Can you elaborate on how you got this value?
Whether or not you need to reboot your server depends on what you want to
accomplish. The default size of tempdb means the size it starts up with,
when you reboot. So if you change the size with ALTER DATABASE, it won't
take affect until you reboot. But if tempdb is large enough for right now,
maybe you don't need to reboot.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"db" <db@.discussions.microsoft.com> wrote in message
news:A7749815-A31F-453E-BC3F-2839977BA763@.microsoft.com...[vbcol=seagreen]
>I got the values from Best practice analyzer tool results for sql server
> 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
> months back tempdb shot up to few GB, but that may be a bad query or
> something.
> From your response it looks like I need not reboot the server?
> Thanks .. go through books on line now.
> --
>
> "Kalen Delaney" wrote:
>|||My Typo. Tempdb.mdf file is 655 MB or 670,700 KB on disk. Tempdb is set to
autogrow and it grow from 8MB to 655MB. I do not want to reboot the server
now, but change the default value of tempdb(Whenever reboot happens it shoul
d
change) .
Can i change it from
sql enterprise manager --> database-->properties-->datafiles-->space
allocated (Change value here)
--
"Kalen Delaney" wrote:
> How did you get the value for YOUR tempdb from the BPA tool? How do you kn
ow
> YOUR tempdb is 8KB?
> 650 KB is still way too small. Can you elaborate on how you got this value
?
> Whether or not you need to reboot your server depends on what you want to
> accomplish. The default size of tempdb means the size it starts up with,
> when you reboot. So if you change the size with ALTER DATABASE, it won't
> take affect until you reboot. But if tempdb is large enough for right now,
> maybe you don't need to reboot.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:A7749815-A31F-453E-BC3F-2839977BA763@.microsoft.com...
>
>|||Yes, the change you do in EM will define the new size for tempdb for next st
artup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"db" <db@.discussions.microsoft.com> wrote in message
news:936E7677-57D2-4720-A537-84083B502622@.microsoft.com...[vbcol=seagreen]
> My Typo. Tempdb.mdf file is 655 MB or 670,700 KB on disk. Tempdb is set to
> autogrow and it grow from 8MB to 655MB. I do not want to reboot the server
> now, but change the default value of tempdb(Whenever reboot happens it sho
uld
> change) .
> Can i change it from
> sql enterprise manager --> database-->properties-->datafiles-->space
> allocated (Change value here)
> --
>
> "Kalen Delaney" wrote:
>
For one of our SQL server 2000 database tempdb size is origionally set to
"8KB"(That is default value). After monitoring it for some time, I think we
need to change the default value to "650 KB".
Can I change the default value while the server is online (so that on next
reboot it automatically changes) or I need to reboot the server.
In any case what is the best way and commands to do this?
Thanks
--Hi db
Where are you getting this value from? Are you sure you don't mean MB?
In any case, you can change the tempdb startup size with the ALTER DATABASE
command, to change the size of the primary file. Look up ALTER DATABASE in
the Books Online, and read about the MODIFY FILE option for the details
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"db" <db@.discussions.microsoft.com> wrote in message
news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
> Hi All
> For one of our SQL server 2000 database tempdb size is origionally set to
> "8KB"(That is default value). After monitoring it for some time, I think
> we
> need to change the default value to "650 KB".
> Can I change the default value while the server is online (so that on next
> reboot it automatically changes) or I need to reboot the server.
> In any case what is the best way and commands to do this?
> Thanks
> --
>|||I got the values from Best practice analyzer tool results for sql server
2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
months back tempdb shot up to few GB, but that may be a bad query or
something.
From your response it looks like I need not reboot the server?
Thanks .. go through books on line now.
--
"Kalen Delaney" wrote:
> Hi db
> Where are you getting this value from? Are you sure you don't mean MB?
> In any case, you can change the tempdb startup size with the ALTER DATABAS
E
> command, to change the size of the primary file. Look up ALTER DATABASE in
> the Books Online, and read about the MODIFY FILE option for the details
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:4F42449A-8D85-4287-906A-5B90F3AA115A@.microsoft.com...
>
>|||> I got the values from Best practice analyzer tool results for sql server[vbcol=seagreen]
> 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
> months back tempdb shot up to few GB, but that may be a bad query or
> something.
> From your response it looks like I need not reboot the server?
> Thanks .. I will go through books on line for details.
> --
>
> "Kalen Delaney" wrote:
>|||How did you get the value for YOUR tempdb from the BPA tool? How do you know
YOUR tempdb is 8KB?
650 KB is still way too small. Can you elaborate on how you got this value?
Whether or not you need to reboot your server depends on what you want to
accomplish. The default size of tempdb means the size it starts up with,
when you reboot. So if you change the size with ALTER DATABASE, it won't
take affect until you reboot. But if tempdb is large enough for right now,
maybe you don't need to reboot.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"db" <db@.discussions.microsoft.com> wrote in message
news:A7749815-A31F-453E-BC3F-2839977BA763@.microsoft.com...[vbcol=seagreen]
>I got the values from Best practice analyzer tool results for sql server
> 2000. Also I am monitoring size of tempdb.mdf/ldf files on the disk. Few
> months back tempdb shot up to few GB, but that may be a bad query or
> something.
> From your response it looks like I need not reboot the server?
> Thanks .. go through books on line now.
> --
>
> "Kalen Delaney" wrote:
>|||My Typo. Tempdb.mdf file is 655 MB or 670,700 KB on disk. Tempdb is set to
autogrow and it grow from 8MB to 655MB. I do not want to reboot the server
now, but change the default value of tempdb(Whenever reboot happens it shoul
d
change) .
Can i change it from
sql enterprise manager --> database-->properties-->datafiles-->space
allocated (Change value here)
--
"Kalen Delaney" wrote:
> How did you get the value for YOUR tempdb from the BPA tool? How do you kn
ow
> YOUR tempdb is 8KB?
> 650 KB is still way too small. Can you elaborate on how you got this value
?
> Whether or not you need to reboot your server depends on what you want to
> accomplish. The default size of tempdb means the size it starts up with,
> when you reboot. So if you change the size with ALTER DATABASE, it won't
> take affect until you reboot. But if tempdb is large enough for right now,
> maybe you don't need to reboot.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:A7749815-A31F-453E-BC3F-2839977BA763@.microsoft.com...
>
>|||Yes, the change you do in EM will define the new size for tempdb for next st
artup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"db" <db@.discussions.microsoft.com> wrote in message
news:936E7677-57D2-4720-A537-84083B502622@.microsoft.com...[vbcol=seagreen]
> My Typo. Tempdb.mdf file is 655 MB or 670,700 KB on disk. Tempdb is set to
> autogrow and it grow from 8MB to 655MB. I do not want to reboot the server
> now, but change the default value of tempdb(Whenever reboot happens it sho
uld
> change) .
> Can i change it from
> sql enterprise manager --> database-->properties-->datafiles-->space
> allocated (Change value here)
> --
>
> "Kalen Delaney" wrote:
>
Subscribe to:
Posts (Atom)