can not delete tempdb

Hi,
after a huge ODBC operation to read some data from a DB table, my tempdb
is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
right mouse click and delete does not work, dbcc checks does not work. I
even stopped the service and started it again, but tempdb did not
regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
log file but after I started the MSSQLSERVER service, they are all
recovered as 2.8 GB.
How can I get rid of this tempdb?
regards,
atxTempdb can not be dropped. In fact, your concern is just to reduce size of
the tempdb. You can use DBCC Shrinkdatabase or DBCC Shrinkfile for that.
"atx" wrote:

> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx
>|||Look at trying "Method 1 to Shrink Tempdb" in the KB article below
http://support.microsoft.com/?id=307487
"atx" <atx@.xerox.com> wrote in message
news:uBQHOwC9FHA.2832@.TK2MSFTNGP14.phx.gbl...
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx

can not delete tempdb

Hi,
after a huge ODBC operation to read some data from a DB table, my tempdb
is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
right mouse click and delete does not work, dbcc checks does not work. I
even stopped the service and started it again, but tempdb did not
regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
log file but after I started the MSSQLSERVER service, they are all
recovered as 2.8 GB.
How can I get rid of this tempdb?
regards,
atxTempdb can not be dropped. In fact, your concern is just to reduce size of
the tempdb. You can use DBCC Shrinkdatabase or DBCC Shrinkfile for that.
"atx" wrote:
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx
>|||Look at trying "Method 1 to Shrink Tempdb" in the KB article below
http://support.microsoft.com/?id=307487
"atx" <atx@.xerox.com> wrote in message
news:uBQHOwC9FHA.2832@.TK2MSFTNGP14.phx.gbl...
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx

can not delete tempdb

Hi,
after a huge ODBC operation to read some data from a DB table, my tempdb
is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
right mouse click and delete does not work, dbcc checks does not work. I
even stopped the service and started it again, but tempdb did not
regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
log file but after I started the MSSQLSERVER service, they are all
recovered as 2.8 GB.
How can I get rid of this tempdb?
regards,
atx
Tempdb can not be dropped. In fact, your concern is just to reduce size of
the tempdb. You can use DBCC Shrinkdatabase or DBCC Shrinkfile for that.
"atx" wrote:

> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx
>
|||Look at trying "Method 1 to Shrink Tempdb" in the KB article below
http://support.microsoft.com/?id=307487
"atx" <atx@.xerox.com> wrote in message
news:uBQHOwC9FHA.2832@.TK2MSFTNGP14.phx.gbl...
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx

Can I/Should I turn off logging on some tempdb transactions?

I am doing some tempdb work for reporting only, and the trans log is getting to 5GB and I dont really see why I need rollback.

If it fails I can just tell the user and the #temp tables will be deleted when the SP returns anyway.

So, can I tell SQL Server not to bother with the transaction logging on these tempdb inserts/updates.

Have to admit something feels a bit iffy about doing this, but I cant see the problem logically.

Someone please tell me I'm being supid!!

There is noway to turn off logging. Logging is essential to maintaining data consistency and integrity. Though, there are cases where you do not need full logged, you might consider bulk or simple logged (for user database). See book online for more info.

Also, take a look at the following kb. This article lists a few reason why tempdb gets bloated and how you could shrink it. http://support.microsoft.com/kb/307487/en-us

|||

TempDb is set for a 'Simple' recovery model, and you cannot change the Recovery model in TempDb.

TempDb requires space, as you noticed, for the users to do their work. that space is released when the connection is released. It is often a 'struggle' to find the 'right' size for TempDb for normal operations. Reports can take a lot of space for gathering the data.

|||

actually, i was thinking of bulk-load/copy (aka minimal logged operations). so, i'm wrong on speaking of the recovery model. tks.

|||

Presumably I can only use bulk copy load for loading from external sources. I'd like to use it for inserting the return data from a table valued UDF.

Can someone explain WHY I need the transaction log in my case?

|||if I cant turn it off entirely, is there anyway of ensuring it is truncated between sections of code (I could then break up huge operations into smaler operations and truncate between them)|||

You can issue this command. Do your work in batches, and then truncate the log (I will sometimes batch 50k/100k rows at a time).

If other users are active, or if there are open transactions, this may not have any noticible effect.

Code Snippet

BACKUP Log TempDb
WITH TRUNCATE_ONLY

Can I move tempdb?

Is there a way to relocate tempdb to another drive?
Read this article:
http://support.microsoft.com/default...b;en-us;224071
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"aerosnoop" <anonymous@.somewhere.net> wrote in message
news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
Is there a way to relocate tempdb to another drive?
|||You can move tempdb files with ALTER DATABASE ... MODIFY FILE, specifying
the desired file locations. Delete the old files after restarting SQL
Server. For example:
ALTER DATABASE tempdb
MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
ALTER DATABASE tempdb
MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
Hope this helps.
Dan Guzman
SQL Server MVP
"aerosnoop" <anonymous@.somewhere.net> wrote in message
news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Is there a way to relocate tempdb to another drive?
|||There is an error in the script I posted. The logical name of the second
ALTER DATABASE should be 'templog'.
ALTER DATABASE tempdb
MODIFY FILE(NAME='templog', FILENAME='G:\LogFiles\templog.ldf')
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can move tempdb files with ALTER DATABASE ... MODIFY FILE, specifying
> the desired file locations. Delete the old files after restarting SQL
> Server. For example:
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aerosnoop" <anonymous@.somewhere.net> wrote in message
> news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
>
|||Thankyou.

> Read this article:
> http://support.microsoft.com/default...b;en-us;224071
|||Does the location of the model db also need to be altered for the changes to
remain after restarting SQL? Or perhaps a job configured to run at startup
so that other create database statements aren't affected would be more
appropriate if the poster wants the change persisted. I'm actually not sure
whether or not the change would be lost after restart - perhaps SQL has some
way of remembering location of tempdb files?
Regards,
Greg Linwood
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can move tempdb files with ALTER DATABASE ... MODIFY FILE, specifying
> the desired file locations. Delete the old files after restarting SQL
> Server. For example:
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aerosnoop" <anonymous@.somewhere.net> wrote in message
> news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
>
|||The physical file names are properties of the database that are not derived
from the model database. The location of the primary data file for each
database are stored in master.dbo.sysdatabases. The primary datafile for
each database contains the system tables, amongst them sysfiles, which
contains the locations of the log file(s) and the secondary data file(s). So
that's how the location of the tempdb files is remembered by SQL Server.
Jacco Schalkwijk
SQL Server MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23z%23r06WdEHA.2408@.tk2msftngp13.phx.gbl...
> Does the location of the model db also need to be altered for the changes
> to
> remain after restarting SQL? Or perhaps a job configured to run at
> startup
> so that other create database statements aren't affected would be more
> appropriate if the poster wants the change persisted. I'm actually not
> sure
> whether or not the change would be lost after restart - perhaps SQL has
> some
> way of remembering location of tempdb files?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
>
|||Tempdb file name changes take affect after SQL Server is restarted and are
permanent. The file locations are stored in the master database sysaltfiles
table and the primary file path is also stored in sysdatabases. If only
tempdb needs to be moved, no other changes need to be made. The KB article
reference posted by details procedures for moving other database files.
Model isn't used to determine database file locations. The default location
for new database data and log files is stored in the registry and the EM GUI
can be used to change these. However, tempdb is a special case because the
file locations are known at startup and those are used if new tempdb files
need to be created.
Hope this helps.
Dan Guzman
SQL Server MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23z%23r06WdEHA.2408@.tk2msftngp13.phx.gbl...
> Does the location of the model db also need to be altered for the changes
to
> remain after restarting SQL? Or perhaps a job configured to run at
startup
> so that other create database statements aren't affected would be more
> appropriate if the poster wants the change persisted. I'm actually not
sure
> whether or not the change would be lost after restart - perhaps SQL has
some[vbcol=seagreen]
> way of remembering location of tempdb files?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
specifying
>
|||ahh, thx!
Regards,
Greg Linwood
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OUFytLXdEHA.1048@.tk2msftngp13.phx.gbl...
> Tempdb file name changes take affect after SQL Server is restarted and are
> permanent. The file locations are stored in the master database
sysaltfiles
> table and the primary file path is also stored in sysdatabases. If only
> tempdb needs to be moved, no other changes need to be made. The KB
article
> reference posted by details procedures for moving other database files.
> Model isn't used to determine database file locations. The default
location
> for new database data and log files is stored in the registry and the EM
GUI
> can be used to change these. However, tempdb is a special case because
the[vbcol=seagreen]
> file locations are known at startup and those are used if new tempdb files
> need to be created.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:%23z%23r06WdEHA.2408@.tk2msftngp13.phx.gbl...
changes
> to
> startup
> sure
> some
> specifying
>

Can I move tempdb?

Is there a way to relocate tempdb to another drive?Read this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"aerosnoop" <anonymous@.somewhere.net> wrote in message
news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
Is there a way to relocate tempdb to another drive?|||You can move tempdb files with ALTER DATABASE ... MODIFY FILE, specifying
the desired file locations. Delete the old files after restarting SQL
Server. For example:
ALTER DATABASE tempdb
MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
ALTER DATABASE tempdb
MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"aerosnoop" <anonymous@.somewhere.net> wrote in message
news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Is there a way to relocate tempdb to another drive?|||There is an error in the script I posted. The logical name of the second
ALTER DATABASE should be 'templog'.
ALTER DATABASE tempdb
MODIFY FILE(NAME='templog', FILENAME='G:\LogFiles\templog.ldf')
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can move tempdb files with ALTER DATABASE ... MODIFY FILE, specifying
> the desired file locations. Delete the old files after restarting SQL
> Server. For example:
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aerosnoop" <anonymous@.somewhere.net> wrote in message
> news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Is there a way to relocate tempdb to another drive?
>|||Thankyou.
> Read this article:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;224071|||Does the location of the model db also need to be altered for the changes to
remain after restarting SQL? Or perhaps a job configured to run at startup
so that other create database statements aren't affected would be more
appropriate if the poster wants the change persisted. I'm actually not sure
whether or not the change would be lost after restart - perhaps SQL has some
way of remembering location of tempdb files?
Regards,
Greg Linwood
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can move tempdb files with ALTER DATABASE ... MODIFY FILE, specifying
> the desired file locations. Delete the old files after restarting SQL
> Server. For example:
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aerosnoop" <anonymous@.somewhere.net> wrote in message
> news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Is there a way to relocate tempdb to another drive?
>|||The physical file names are properties of the database that are not derived
from the model database. The location of the primary data file for each
database are stored in master.dbo.sysdatabases. The primary datafile for
each database contains the system tables, amongst them sysfiles, which
contains the locations of the log file(s) and the secondary data file(s). So
that's how the location of the tempdb files is remembered by SQL Server.
--
Jacco Schalkwijk
SQL Server MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23z%23r06WdEHA.2408@.tk2msftngp13.phx.gbl...
> Does the location of the model db also need to be altered for the changes
> to
> remain after restarting SQL? Or perhaps a job configured to run at
> startup
> so that other create database statements aren't affected would be more
> appropriate if the poster wants the change persisted. I'm actually not
> sure
> whether or not the change would be lost after restart - perhaps SQL has
> some
> way of remembering location of tempdb files?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
>> You can move tempdb files with ALTER DATABASE ... MODIFY FILE, specifying
>> the desired file locations. Delete the old files after restarting SQL
>> Server. For example:
>> ALTER DATABASE tempdb
>> MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
>> ALTER DATABASE tempdb
>> MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "aerosnoop" <anonymous@.somewhere.net> wrote in message
>> news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
>> > Is there a way to relocate tempdb to another drive?
>>
>|||Tempdb file name changes take affect after SQL Server is restarted and are
permanent. The file locations are stored in the master database sysaltfiles
table and the primary file path is also stored in sysdatabases. If only
tempdb needs to be moved, no other changes need to be made. The KB article
reference posted by details procedures for moving other database files.
Model isn't used to determine database file locations. The default location
for new database data and log files is stored in the registry and the EM GUI
can be used to change these. However, tempdb is a special case because the
file locations are known at startup and those are used if new tempdb files
need to be created.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23z%23r06WdEHA.2408@.tk2msftngp13.phx.gbl...
> Does the location of the model db also need to be altered for the changes
to
> remain after restarting SQL? Or perhaps a job configured to run at
startup
> so that other create database statements aren't affected would be more
> appropriate if the poster wants the change persisted. I'm actually not
sure
> whether or not the change would be lost after restart - perhaps SQL has
some
> way of remembering location of tempdb files?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
> > You can move tempdb files with ALTER DATABASE ... MODIFY FILE,
specifying
> > the desired file locations. Delete the old files after restarting SQL
> > Server. For example:
> >
> > ALTER DATABASE tempdb
> > MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
> >
> > ALTER DATABASE tempdb
> > MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "aerosnoop" <anonymous@.somewhere.net> wrote in message
> > news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > > Is there a way to relocate tempdb to another drive?
> >
> >
>|||ahh, thx!
Regards,
Greg Linwood
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OUFytLXdEHA.1048@.tk2msftngp13.phx.gbl...
> Tempdb file name changes take affect after SQL Server is restarted and are
> permanent. The file locations are stored in the master database
sysaltfiles
> table and the primary file path is also stored in sysdatabases. If only
> tempdb needs to be moved, no other changes need to be made. The KB
article
> reference posted by details procedures for moving other database files.
> Model isn't used to determine database file locations. The default
location
> for new database data and log files is stored in the registry and the EM
GUI
> can be used to change these. However, tempdb is a special case because
the
> file locations are known at startup and those are used if new tempdb files
> need to be created.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:%23z%23r06WdEHA.2408@.tk2msftngp13.phx.gbl...
> > Does the location of the model db also need to be altered for the
changes
> to
> > remain after restarting SQL? Or perhaps a job configured to run at
> startup
> > so that other create database statements aren't affected would be more
> > appropriate if the poster wants the change persisted. I'm actually not
> sure
> > whether or not the change would be lost after restart - perhaps SQL has
> some
> > way of remembering location of tempdb files?
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
> > > You can move tempdb files with ALTER DATABASE ... MODIFY FILE,
> specifying
> > > the desired file locations. Delete the old files after restarting SQL
> > > Server. For example:
> > >
> > > ALTER DATABASE tempdb
> > > MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
> > >
> > > ALTER DATABASE tempdb
> > > MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "aerosnoop" <anonymous@.somewhere.net> wrote in message
> > > news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > > > Is there a way to relocate tempdb to another drive?
> > >
> > >
> >
> >
>

Can I move tempdb?

Is there a way to relocate tempdb to another drive?Read this article:
http://support.microsoft.com/defaul...kb;en-us;224071
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"aerosnoop" <anonymous@.somewhere.net> wrote in message
news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
Is there a way to relocate tempdb to another drive?|||You can move tempdb files with ALTER DATABASE ... MODIFY FILE, specifying
the desired file locations. Delete the old files after restarting SQL
Server. For example:
ALTER DATABASE tempdb
MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
ALTER DATABASE tempdb
MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
Hope this helps.
Dan Guzman
SQL Server MVP
"aerosnoop" <anonymous@.somewhere.net> wrote in message
news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Is there a way to relocate tempdb to another drive?|||There is an error in the script I posted. The logical name of the second
ALTER DATABASE should be 'templog'.
ALTER DATABASE tempdb
MODIFY FILE(NAME='templog', FILENAME='G:\LogFiles\templog.ldf')
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can move tempdb files with ALTER DATABASE ... MODIFY FILE, specifying
> the desired file locations. Delete the old files after restarting SQL
> Server. For example:
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aerosnoop" <anonymous@.somewhere.net> wrote in message
> news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
>|||Thankyou.

> Read this article:
> http://support.microsoft.com/defaul...kb;en-us;224071|||Does the location of the model db also need to be altered for the changes to
remain after restarting SQL? Or perhaps a job configured to run at startup
so that other create database statements aren't affected would be more
appropriate if the poster wants the change persisted. I'm actually not sure
whether or not the change would be lost after restart - perhaps SQL has some
way of remembering location of tempdb files?
Regards,
Greg Linwood
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You can move tempdb files with ALTER DATABASE ... MODIFY FILE, specifying
> the desired file locations. Delete the old files after restarting SQL
> Server. For example:
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='F:\DataFiles\tempdb.mdf')
> ALTER DATABASE tempdb
> MODIFY FILE(NAME='tempdev', FILENAME='G:\LogFiles\templog.ldf')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aerosnoop" <anonymous@.somewhere.net> wrote in message
> news:OTMkgiWdEHA.4092@.TK2MSFTNGP10.phx.gbl...
>|||The physical file names are properties of the database that are not derived
from the model database. The location of the primary data file for each
database are stored in master.dbo.sysdatabases. The primary datafile for
each database contains the system tables, amongst them sysfiles, which
contains the locations of the log file(s) and the secondary data file(s). So
that's how the location of the tempdb files is remembered by SQL Server.
Jacco Schalkwijk
SQL Server MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23z%23r06WdEHA.2408@.tk2msftngp13.phx.gbl...
> Does the location of the model db also need to be altered for the changes
> to
> remain after restarting SQL? Or perhaps a job configured to run at
> startup
> so that other create database statements aren't affected would be more
> appropriate if the poster wants the change persisted. I'm actually not
> sure
> whether or not the change would be lost after restart - perhaps SQL has
> some
> way of remembering location of tempdb files?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
>|||Tempdb file name changes take affect after SQL Server is restarted and are
permanent. The file locations are stored in the master database sysaltfiles
table and the primary file path is also stored in sysdatabases. If only
tempdb needs to be moved, no other changes need to be made. The KB article
reference posted by details procedures for moving other database files.
Model isn't used to determine database file locations. The default location
for new database data and log files is stored in the registry and the EM GUI
can be used to change these. However, tempdb is a special case because the
file locations are known at startup and those are used if new tempdb files
need to be created.
Hope this helps.
Dan Guzman
SQL Server MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23z%23r06WdEHA.2408@.tk2msftngp13.phx.gbl...
> Does the location of the model db also need to be altered for the changes
to
> remain after restarting SQL? Or perhaps a job configured to run at
startup
> so that other create database statements aren't affected would be more
> appropriate if the poster wants the change persisted. I'm actually not
sure
> whether or not the change would be lost after restart - perhaps SQL has
some
> way of remembering location of tempdb files?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:e6kwkqWdEHA.1152@.TK2MSFTNGP09.phx.gbl...
specifying[vbcol=seagreen]
>|||ahh, thx!
Regards,
Greg Linwood
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OUFytLXdEHA.1048@.tk2msftngp13.phx.gbl...
> Tempdb file name changes take affect after SQL Server is restarted and are
> permanent. The file locations are stored in the master database
sysaltfiles
> table and the primary file path is also stored in sysdatabases. If only
> tempdb needs to be moved, no other changes need to be made. The KB
article
> reference posted by details procedures for moving other database files.
> Model isn't used to determine database file locations. The default
location
> for new database data and log files is stored in the registry and the EM
GUI
> can be used to change these. However, tempdb is a special case because
the
> file locations are known at startup and those are used if new tempdb files
> need to be created.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:%23z%23r06WdEHA.2408@.tk2msftngp13.phx.gbl...
changes[vbcol=seagreen]
> to
> startup
> sure
> some
> specifying
>

can I monitor what goes in tempdb

Hi,
I've noticed that our application occassionally causes the tempdb log file to grow quite large. I'm not a dba, nor do we have one onsite, so I'm trying to read up on things that cause the tempdb file to grow. Having done that, I don't see anything obvious that our application is doing (checked things like temp tables, cursors, group by, order by, etc).

So I was wondering if there is any monitoring that I could do on the tempdb itself to see when and how it is being accessed? And what kinds of things are getting stored in tempdb? If my application does create objects in the tempdb table and then deallocates the object, shouldn't that keep the size of the tempdb in check? Or would that not have any effect on the tempdb.log file?

Also, it is the tempdb.log file that grows quite large - is there a way programmatically to clear the log file? I know there is a shrinkdb option from the Enterprise Mgr. Should my application periodically be doing something to shrinkdb (assuming it does use tempdb appropriately) in order to keep the size from growing too much?

Thanks in advance,
Beth

there are many system maintenance commands (DBCC) consumes the tempdb. also check for any open transaction (DBCC Opentran). To monitor usage of Tempdb , either u can using tracing sp or Profiler with proper filter like database name etc. Also, you can consider a job which shrink the tempdb log file when less traffic is there.

refer : http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

the link is related to SQL Server 2005 but more or less it is applicable to other versions also

Madhu

|||I forgot to mention that we are still on SQL Server 2000. Thanks for the link - looks like it has some good info in it, so I'm glad most of it applies to other versions.

Beth