How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?
stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

How busy is my tempdb ?

How can I tell if my tempdb is busy ?
Currently my tempdb is on the same drive as my user database data files and
want to know if i see some high IO on that drive, how do I contribute to
whether its tempdb activity or userdatabase activity..
In general, how can I tell how busy is my tempdb ?
I am using SQL 2005
Thanks
Hi
SQL Profiler will show you this if you look for a DBID of 2.
John
"Hassan" wrote:

> How can I tell if my tempdb is busy ?
> Currently my tempdb is on the same drive as my user database data files and
> want to know if i see some high IO on that drive, how do I contribute to
> whether its tempdb activity or userdatabase activity..
> In general, how can I tell how busy is my tempdb ?
> I am using SQL 2005
> Thanks
>
>
|||Well is that completely true ?
I mean if I am using temp tables or queries that may use temp worktables for
internal usage or row versioning info.. I dont think if I profile by DBid =
2, will I get it ..Will I ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:49632454-27C4-429C-82F0-619E5347666B@.microsoft.com...[vbcol=seagreen]
> Hi
> SQL Profiler will show you this if you look for a DBID of 2.
> John
> "Hassan" wrote:
|||Hi Hassan
You should see something, I suggest you try it.
John
"Hassan" wrote:

> Well is that completely true ?
> I mean if I am using temp tables or queries that may use temp worktables for
> internal usage or row versioning info.. I dont think if I profile by DBid =
> 2, will I get it ..Will I ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:49632454-27C4-429C-82F0-619E5347666B@.microsoft.com...
>
>
|||Hi
I think Aaron wrote this script
To determine the space used by objects in TempDB:
SELECT
SPID = s.session_id,
s.[host_name],
s.[program_name],
s.status,
s.memory_usage,
granted_memory = CONVERT(INT, r.granted_query_memory*8.00),
t.text,
sourcedb = DB_NAME(r.database_id),
workdb = DB_NAME(dt.database_id),
mg.*,
su.*
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_db_session_space_usage su
ON s.session_id = su.session_id
AND su.database_id = DB_ID('tempdb')
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.most_recent_session_id
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.session_id = s.session_id
LEFT OUTER JOIN (
SELECT
session_id,
database_id
FROM sys.dm_tran_session_transactions t
INNER JOIN sys.dm_tran_database_transactions dt
ON t.transaction_id = dt.transaction_id
WHERE dt.database_id = DB_ID('tempdb')
GROUP BY session_id, database_id
) dt
ON s.session_id = dt.session_id
CROSS APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle,
c.most_recent_sql_handle)) t
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
WHERE (r.database_id = DB_ID('tempdb')
OR dt.database_id = DB_ID('tempdb'))
AND s.status = 'running'
ORDER BY SPID;
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> You should see something, I suggest you try it.
> John
> "Hassan" wrote:
|||You can also view the transactions per second counter in Perfmon for Tempdb
to get an idea how active it is. The dmv for IO stats will tell you how much
disk access you have for Tempdb.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:%23XgRSvCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
> How can I tell if my tempdb is busy ?
> Currently my tempdb is on the same drive as my user database data files
> and want to know if i see some high IO on that drive, how do I contribute
> to whether its tempdb activity or userdatabase activity..
> In general, how can I tell how busy is my tempdb ?
> I am using SQL 2005
> Thanks
>
|||I have RPC and Batch completed and filtered for dbid = 2 and theres nothing
showing up... What events do I need ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> You should see something, I suggest you try it.
> John
> "Hassan" wrote:
|||If you have tempdb and your user database on the same drive and want to know
which one is contributing to the disk I/O activities on that drive, the
simplest way is to take snapshots of fn_virtualfilestats(NULL, NULL), and
compare the delta NumReads, delta NumWrites, delta BytesRead, and delta
ByteWritten. In particular, compare these delta's in terms of their relative
percentages. The I/O delta distribution among the databases tells you which
database accounts for how much of the I/O activities.
If your database has files on multiple drives, you have to look at the
percentage at the file level.
Linchi
"Hassan" wrote:

> How can I tell if my tempdb is busy ?
> Currently my tempdb is on the same drive as my user database data files and
> want to know if i see some high IO on that drive, how do I contribute to
> whether its tempdb activity or userdatabase activity..
> In general, how can I tell how busy is my tempdb ?
> I am using SQL 2005
> Thanks
>
>
|||Those things don't actually "execute" from within tempdb but rather from the
database they exist in or are executed from within as the spid's context.
The temporary OBJECTS they create, either explicitly or implicitly, will go
into tempdb.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:%23Iq2dqIJIHA.2064@.TK2MSFTNGP06.phx.gbl...
>I have RPC and Batch completed and filtered for dbid = 2 and theres nothing
>showing up... What events do I need ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...
>
|||Thanks.. Thats what I thought too..
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13jfceci1b96g8d@.corp.supernews.com...
> Those things don't actually "execute" from within tempdb but rather from
> the database they exist in or are executed from within as the spid's
> context. The temporary OBJECTS they create, either explicitly or
> implicitly, will go into tempdb.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Hassan" <hassan@.test.com> wrote in message
> news:%23Iq2dqIJIHA.2064@.TK2MSFTNGP06.phx.gbl...
>

How busy is my tempdb ?

How can I tell if my tempdb is busy ?
Currently my tempdb is on the same drive as my user database data files and
want to know if i see some high IO on that drive, how do I contribute to
whether its tempdb activity or userdatabase activity..
In general, how can I tell how busy is my tempdb ?
I am using SQL 2005
ThanksHi
SQL Profiler will show you this if you look for a DBID of 2.
John
"Hassan" wrote:

> How can I tell if my tempdb is busy ?
> Currently my tempdb is on the same drive as my user database data files an
d
> want to know if i see some high IO on that drive, how do I contribute to
> whether its tempdb activity or userdatabase activity..
> In general, how can I tell how busy is my tempdb ?
> I am using SQL 2005
> Thanks
>
>|||Well is that completely true ?
I mean if I am using temp tables or queries that may use temp worktables for
internal usage or row versioning info.. I dont think if I profile by DBid =
2, will I get it ..Will I ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:49632454-27C4-429C-82F0-619E5347666B@.microsoft.com...[vbcol=seagreen]
> Hi
> SQL Profiler will show you this if you look for a DBID of 2.
> John
> "Hassan" wrote:
>|||Hi Hassan
You should see something, I suggest you try it.
John
"Hassan" wrote:

> Well is that completely true ?
> I mean if I am using temp tables or queries that may use temp worktables f
or
> internal usage or row versioning info.. I dont think if I profile by DBid
=
> 2, will I get it ..Will I ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:49632454-27C4-429C-82F0-619E5347666B@.microsoft.com...
>
>|||Hi
I think Aaron wrote this script
To determine the space used by objects in TempDB:
SELECT
SPID = s.session_id,
s.[host_name],
s.[program_name],
s.status,
s.memory_usage,
granted_memory = CONVERT(INT, r.granted_query_memory*8.00),
t.text,
sourcedb = DB_NAME(r.database_id),
workdb = DB_NAME(dt.database_id),
mg.*,
su.*
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_db_session_space_usage su
ON s.session_id = su.session_id
AND su.database_id = DB_ID('tempdb')
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.most_recent_session_id
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.session_id = s.session_id
LEFT OUTER JOIN (
SELECT
session_id,
database_id
FROM sys.dm_tran_session_transactions t
INNER JOIN sys.dm_tran_database_transactions dt
ON t.transaction_id = dt.transaction_id
WHERE dt.database_id = DB_ID('tempdb')
GROUP BY session_id, database_id
) dt
ON s.session_id = dt.session_id
CROSS APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle,
c.most_recent_sql_handle)) t
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
WHERE (r.database_id = DB_ID('tempdb')
OR dt.database_id = DB_ID('tempdb'))
AND s.status = 'running'
ORDER BY SPID;
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> You should see something, I suggest you try it.
> John
> "Hassan" wrote:
>|||You can also view the transactions per second counter in Perfmon for Tempdb
to get an idea how active it is. The dmv for IO stats will tell you how much
disk access you have for Tempdb.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:%23XgRSvCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
> How can I tell if my tempdb is busy ?
> Currently my tempdb is on the same drive as my user database data files
> and want to know if i see some high IO on that drive, how do I contribute
> to whether its tempdb activity or userdatabase activity..
> In general, how can I tell how busy is my tempdb ?
> I am using SQL 2005
> Thanks
>|||I have RPC and Batch completed and filtered for dbid = 2 and theres nothing
showing up... What events do I need ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> You should see something, I suggest you try it.
> John
> "Hassan" wrote:
>|||If you have tempdb and your user database on the same drive and want to know
which one is contributing to the disk I/O activities on that drive, the
simplest way is to take snapshots of fn_virtualfilestats(NULL, NULL), and
compare the delta NumReads, delta NumWrites, delta BytesRead, and delta
ByteWritten. In particular, compare these delta's in terms of their relative
percentages. The I/O delta distribution among the databases tells you which
database accounts for how much of the I/O activities.
If your database has files on multiple drives, you have to look at the
percentage at the file level.
Linchi
"Hassan" wrote:

> How can I tell if my tempdb is busy ?
> Currently my tempdb is on the same drive as my user database data files an
d
> want to know if i see some high IO on that drive, how do I contribute to
> whether its tempdb activity or userdatabase activity..
> In general, how can I tell how busy is my tempdb ?
> I am using SQL 2005
> Thanks
>
>|||Those things don't actually "execute" from within tempdb but rather from the
database they exist in or are executed from within as the spid's context.
The temporary OBJECTS they create, either explicitly or implicitly, will go
into tempdb.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:%23Iq2dqIJIHA.2064@.TK2MSFTNGP06.phx.gbl...
>I have RPC and Batch completed and filtered for dbid = 2 and theres nothing
>showing up... What events do I need ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...
>|||Thanks.. Thats what I thought too..
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13jfceci1b96g8d@.corp.supernews.com...
> Those things don't actually "execute" from within tempdb but rather from
> the database they exist in or are executed from within as the spid's
> context. The temporary OBJECTS they create, either explicitly or
> implicitly, will go into tempdb.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Hassan" <hassan@.test.com> wrote in message
> news:%23Iq2dqIJIHA.2064@.TK2MSFTNGP06.phx.gbl...
>

How busy is my tempdb ?

How can I tell if my tempdb is busy ?
Currently my tempdb is on the same drive as my user database data files and
want to know if i see some high IO on that drive, how do I contribute to
whether its tempdb activity or userdatabase activity..
In general, how can I tell how busy is my tempdb ?
I am using SQL 2005
ThanksHi
SQL Profiler will show you this if you look for a DBID of 2.
John
"Hassan" wrote:
> How can I tell if my tempdb is busy ?
> Currently my tempdb is on the same drive as my user database data files and
> want to know if i see some high IO on that drive, how do I contribute to
> whether its tempdb activity or userdatabase activity..
> In general, how can I tell how busy is my tempdb ?
> I am using SQL 2005
> Thanks
>
>|||Well is that completely true ?
I mean if I am using temp tables or queries that may use temp worktables for
internal usage or row versioning info.. I dont think if I profile by DBid =2, will I get it ..Will I ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:49632454-27C4-429C-82F0-619E5347666B@.microsoft.com...
> Hi
> SQL Profiler will show you this if you look for a DBID of 2.
> John
> "Hassan" wrote:
>> How can I tell if my tempdb is busy ?
>> Currently my tempdb is on the same drive as my user database data files
>> and
>> want to know if i see some high IO on that drive, how do I contribute to
>> whether its tempdb activity or userdatabase activity..
>> In general, how can I tell how busy is my tempdb ?
>> I am using SQL 2005
>> Thanks
>>|||Hi Hassan
You should see something, I suggest you try it.
John
"Hassan" wrote:
> Well is that completely true ?
> I mean if I am using temp tables or queries that may use temp worktables for
> internal usage or row versioning info.. I dont think if I profile by DBid => 2, will I get it ..Will I ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:49632454-27C4-429C-82F0-619E5347666B@.microsoft.com...
> > Hi
> >
> > SQL Profiler will show you this if you look for a DBID of 2.
> >
> > John
> >
> > "Hassan" wrote:
> >
> >> How can I tell if my tempdb is busy ?
> >>
> >> Currently my tempdb is on the same drive as my user database data files
> >> and
> >> want to know if i see some high IO on that drive, how do I contribute to
> >> whether its tempdb activity or userdatabase activity..
> >>
> >> In general, how can I tell how busy is my tempdb ?
> >>
> >> I am using SQL 2005
> >>
> >> Thanks
> >>
> >>
> >>
>
>|||Hi
I think Aaron wrote this script
To determine the space used by objects in TempDB:
SELECT
SPID = s.session_id,
s.[host_name],
s.[program_name],
s.status,
s.memory_usage,
granted_memory = CONVERT(INT, r.granted_query_memory*8.00),
t.text,
sourcedb = DB_NAME(r.database_id),
workdb = DB_NAME(dt.database_id),
mg.*,
su.*
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_db_session_space_usage su
ON s.session_id = su.session_id
AND su.database_id = DB_ID('tempdb')
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.most_recent_session_id
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.session_id = s.session_id
LEFT OUTER JOIN (
SELECT
session_id,
database_id
FROM sys.dm_tran_session_transactions t
INNER JOIN sys.dm_tran_database_transactions dt
ON t.transaction_id = dt.transaction_id
WHERE dt.database_id = DB_ID('tempdb')
GROUP BY session_id, database_id
) dt
ON s.session_id = dt.session_id
CROSS APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle,
c.most_recent_sql_handle)) t
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
WHERE (r.database_id = DB_ID('tempdb')
OR dt.database_id = DB_ID('tempdb'))
AND s.status = 'running'
ORDER BY SPID;
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...
> Hi Hassan
> You should see something, I suggest you try it.
> John
> "Hassan" wrote:
>> Well is that completely true ?
>> I mean if I am using temp tables or queries that may use temp worktables
>> for
>> internal usage or row versioning info.. I dont think if I profile by DBid
>> =>> 2, will I get it ..Will I ?
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:49632454-27C4-429C-82F0-619E5347666B@.microsoft.com...
>> > Hi
>> >
>> > SQL Profiler will show you this if you look for a DBID of 2.
>> >
>> > John
>> >
>> > "Hassan" wrote:
>> >
>> >> How can I tell if my tempdb is busy ?
>> >>
>> >> Currently my tempdb is on the same drive as my user database data
>> >> files
>> >> and
>> >> want to know if i see some high IO on that drive, how do I contribute
>> >> to
>> >> whether its tempdb activity or userdatabase activity..
>> >>
>> >> In general, how can I tell how busy is my tempdb ?
>> >>
>> >> I am using SQL 2005
>> >>
>> >> Thanks
>> >>
>> >>
>> >>
>>|||You can also view the transactions per second counter in Perfmon for Tempdb
to get an idea how active it is. The dmv for IO stats will tell you how much
disk access you have for Tempdb.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:%23XgRSvCJIHA.4196@.TK2MSFTNGP04.phx.gbl...
> How can I tell if my tempdb is busy ?
> Currently my tempdb is on the same drive as my user database data files
> and want to know if i see some high IO on that drive, how do I contribute
> to whether its tempdb activity or userdatabase activity..
> In general, how can I tell how busy is my tempdb ?
> I am using SQL 2005
> Thanks
>|||I have RPC and Batch completed and filtered for dbid = 2 and theres nothing
showing up... What events do I need ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...
> Hi Hassan
> You should see something, I suggest you try it.
> John
> "Hassan" wrote:
>> Well is that completely true ?
>> I mean if I am using temp tables or queries that may use temp worktables
>> for
>> internal usage or row versioning info.. I dont think if I profile by DBid
>> =>> 2, will I get it ..Will I ?
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:49632454-27C4-429C-82F0-619E5347666B@.microsoft.com...
>> > Hi
>> >
>> > SQL Profiler will show you this if you look for a DBID of 2.
>> >
>> > John
>> >
>> > "Hassan" wrote:
>> >
>> >> How can I tell if my tempdb is busy ?
>> >>
>> >> Currently my tempdb is on the same drive as my user database data
>> >> files
>> >> and
>> >> want to know if i see some high IO on that drive, how do I contribute
>> >> to
>> >> whether its tempdb activity or userdatabase activity..
>> >>
>> >> In general, how can I tell how busy is my tempdb ?
>> >>
>> >> I am using SQL 2005
>> >>
>> >> Thanks
>> >>
>> >>
>> >>
>>|||If you have tempdb and your user database on the same drive and want to know
which one is contributing to the disk I/O activities on that drive, the
simplest way is to take snapshots of fn_virtualfilestats(NULL, NULL), and
compare the delta NumReads, delta NumWrites, delta BytesRead, and delta
ByteWritten. In particular, compare these delta's in terms of their relative
percentages. The I/O delta distribution among the databases tells you which
database accounts for how much of the I/O activities.
If your database has files on multiple drives, you have to look at the
percentage at the file level.
Linchi
"Hassan" wrote:
> How can I tell if my tempdb is busy ?
> Currently my tempdb is on the same drive as my user database data files and
> want to know if i see some high IO on that drive, how do I contribute to
> whether its tempdb activity or userdatabase activity..
> In general, how can I tell how busy is my tempdb ?
> I am using SQL 2005
> Thanks
>
>|||Those things don't actually "execute" from within tempdb but rather from the
database they exist in or are executed from within as the spid's context.
The temporary OBJECTS they create, either explicitly or implicitly, will go
into tempdb.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Hassan" <hassan@.test.com> wrote in message
news:%23Iq2dqIJIHA.2064@.TK2MSFTNGP06.phx.gbl...
>I have RPC and Batch completed and filtered for dbid = 2 and theres nothing
>showing up... What events do I need ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...
>> Hi Hassan
>> You should see something, I suggest you try it.
>> John
>> "Hassan" wrote:
>> Well is that completely true ?
>> I mean if I am using temp tables or queries that may use temp worktables
>> for
>> internal usage or row versioning info.. I dont think if I profile by
>> DBid =>> 2, will I get it ..Will I ?
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:49632454-27C4-429C-82F0-619E5347666B@.microsoft.com...
>> > Hi
>> >
>> > SQL Profiler will show you this if you look for a DBID of 2.
>> >
>> > John
>> >
>> > "Hassan" wrote:
>> >
>> >> How can I tell if my tempdb is busy ?
>> >>
>> >> Currently my tempdb is on the same drive as my user database data
>> >> files
>> >> and
>> >> want to know if i see some high IO on that drive, how do I contribute
>> >> to
>> >> whether its tempdb activity or userdatabase activity..
>> >>
>> >> In general, how can I tell how busy is my tempdb ?
>> >>
>> >> I am using SQL 2005
>> >>
>> >> Thanks
>> >>
>> >>
>> >>
>>
>|||Thanks.. Thats what I thought too..
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13jfceci1b96g8d@.corp.supernews.com...
> Those things don't actually "execute" from within tempdb but rather from
> the database they exist in or are executed from within as the spid's
> context. The temporary OBJECTS they create, either explicitly or
> implicitly, will go into tempdb.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Hassan" <hassan@.test.com> wrote in message
> news:%23Iq2dqIJIHA.2064@.TK2MSFTNGP06.phx.gbl...
>>I have RPC and Batch completed and filtered for dbid = 2 and theres
>>nothing showing up... What events do I need ?
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...
>> Hi Hassan
>> You should see something, I suggest you try it.
>> John
>> "Hassan" wrote:
>> Well is that completely true ?
>> I mean if I am using temp tables or queries that may use temp
>> worktables for
>> internal usage or row versioning info.. I dont think if I profile by
>> DBid =>> 2, will I get it ..Will I ?
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:49632454-27C4-429C-82F0-619E5347666B@.microsoft.com...
>> > Hi
>> >
>> > SQL Profiler will show you this if you look for a DBID of 2.
>> >
>> > John
>> >
>> > "Hassan" wrote:
>> >
>> >> How can I tell if my tempdb is busy ?
>> >>
>> >> Currently my tempdb is on the same drive as my user database data
>> >> files
>> >> and
>> >> want to know if i see some high IO on that drive, how do I
>> >> contribute to
>> >> whether its tempdb activity or userdatabase activity..
>> >>
>> >> In general, how can I tell how busy is my tempdb ?
>> >>
>> >> I am using SQL 2005
>> >>
>> >> Thanks
>> >>
>> >>
>> >>
>>
>>
>|||You should look at statement level and also look at transactions and locks.
John
"Hassan" wrote:
> I have RPC and Batch completed and filtered for dbid = 2 and theres nothing
> showing up... What events do I need ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:0BDBB16F-2CCE-4947-9FDD-FC74C4C80D45@.microsoft.com...
> > Hi Hassan
> >
> > You should see something, I suggest you try it.
> >
> > John
> >
> > "Hassan" wrote:
> >
> >> Well is that completely true ?
> >>
> >> I mean if I am using temp tables or queries that may use temp worktables
> >> for
> >> internal usage or row versioning info.. I dont think if I profile by DBid
> >> => >> 2, will I get it ..Will I ?
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:49632454-27C4-429C-82F0-619E5347666B@.microsoft.com...
> >> > Hi
> >> >
> >> > SQL Profiler will show you this if you look for a DBID of 2.
> >> >
> >> > John
> >> >
> >> > "Hassan" wrote:
> >> >
> >> >> How can I tell if my tempdb is busy ?
> >> >>
> >> >> Currently my tempdb is on the same drive as my user database data
> >> >> files
> >> >> and
> >> >> want to know if i see some high IO on that drive, how do I contribute
> >> >> to
> >> >> whether its tempdb activity or userdatabase activity..
> >> >>
> >> >> In general, how can I tell how busy is my tempdb ?
> >> >>
> >> >> I am using SQL 2005
> >> >>
> >> >> Thanks
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

hotfix for: KB816939 (PRB: File Growth Value for TempDB is Not Per

Regarding KB816939 (PRB: File Growth Value for TempDB is Not Persistent When
Changed From Fixed Increments to Percentage):
Does anyone know if microsoft has included a fix for this in sp4, or has a
hotfix for this bug?
We tried the workaround listed in the KB, but it did not work...
Link:
http://support.microsoft.com/defaul...n-us;816939#kb4Can you give more info regarding how the work around did not work?
As far as I know the fix did not make it into SP4...
It is fixed in SQL 2005 though.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:FF219273-52D1-42C0-8CE5-35CDF27E01BD@.microsoft.com...
> Regarding KB816939 (PRB: File Growth Value for TempDB is Not Persistent
When
> Changed From Fixed Increments to Percentage):
> Does anyone know if microsoft has included a fix for this in sp4, or has a
> hotfix for this bug?
> We tried the workaround listed in the KB, but it did not work...
> Link:
> http://support.microsoft.com/defaul...n-us;816939#kb4

hotfix for: KB816939 (PRB: File Growth Value for TempDB is Not Per

Regarding KB816939 (PRB: File Growth Value for TempDB is Not Persistent When
Changed From Fixed Increments to Percentage):
Does anyone know if microsoft has included a fix for this in sp4, or has a
hotfix for this bug?
We tried the workaround listed in the KB, but it did not work...
Link:
http://support.microsoft.com/default...-us;816939#kb4
Can you give more info regarding how the work around did not work?
As far as I know the fix did not make it into SP4...
It is fixed in SQL 2005 though.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:FF219273-52D1-42C0-8CE5-35CDF27E01BD@.microsoft.com...
> Regarding KB816939 (PRB: File Growth Value for TempDB is Not Persistent
When
> Changed From Fixed Increments to Percentage):
> Does anyone know if microsoft has included a fix for this in sp4, or has a
> hotfix for this bug?
> We tried the workaround listed in the KB, but it did not work...
> Link:
> http://support.microsoft.com/default...-us;816939#kb4

hotfix for: KB816939 (PRB: File Growth Value for TempDB is Not Per

Regarding KB816939 (PRB: File Growth Value for TempDB is Not Persistent When
Changed From Fixed Increments to Percentage):
Does anyone know if microsoft has included a fix for this in sp4, or has a
hotfix for this bug?
We tried the workaround listed in the KB, but it did not work...
Link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;816939#kb4Can you give more info regarding how the work around did not work?
As far as I know the fix did not make it into SP4...
It is fixed in SQL 2005 though.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:FF219273-52D1-42C0-8CE5-35CDF27E01BD@.microsoft.com...
> Regarding KB816939 (PRB: File Growth Value for TempDB is Not Persistent
When
> Changed From Fixed Increments to Percentage):
> Does anyone know if microsoft has included a fix for this in sp4, or has a
> hotfix for this bug?
> We tried the workaround listed in the KB, but it did not work...
> Link:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;816939#kb4

hot to shrink tempdb?

7 Gb has been allocated for my tempdb (data file) initially, only 800 Mb are
being used. Is there any way I can change my tempdb data file to ~1 Gb? I
can't shut down the system, this is not an option.
Thank you in advance for your help
Leon Shargorodsky
--select * from tempdb.dbo.sysfiles
DBCC SHRINKFILE (<logicalfilename>,<mb>) -- datafile
DBCC SHRINKFILE (<logicalfilename>,<mb>) --logfile
sample:
DBCC SHRINKFILE (MSDBData,1000)
DBCC SHRINKFILE (MSDBLog,200)
"Leon Shargorodsky" wrote:

> 7 Gb has been allocated for my tempdb (data file) initially, only 800 Mb are
> being used. Is there any way I can change my tempdb data file to ~1 Gb? I
> can't shut down the system, this is not an option.
> Thank you in advance for your help
> Leon Shargorodsky
|||http://www.aspfaq.com/2446
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:88A36F06-8FF7-4483-9BDC-89F349E03BAD@.microsoft.com...
> 7 Gb has been allocated for my tempdb (data file) initially, only 800 Mb
are
> being used. Is there any way I can change my tempdb data file to ~1 Gb? I
> can't shut down the system, this is not an option.
> Thank you in advance for your help
> Leon Shargorodsky

hot to shrink tempdb?

7 Gb has been allocated for my tempdb (data file) initially, only 800 Mb are
being used. Is there any way I can change my tempdb data file to ~1 Gb? I
can't shut down the system, this is not an option.
Thank you in advance for your help
Leon Shargorodsky--select * from tempdb.dbo.sysfiles
DBCC SHRINKFILE (<logicalfilename>,<mb>) -- datafile
DBCC SHRINKFILE (<logicalfilename>,<mb>) --logfile
sample:
DBCC SHRINKFILE (MSDBData,1000)
DBCC SHRINKFILE (MSDBLog,200)
"Leon Shargorodsky" wrote:
> 7 Gb has been allocated for my tempdb (data file) initially, only 800 Mb are
> being used. Is there any way I can change my tempdb data file to ~1 Gb? I
> can't shut down the system, this is not an option.
> Thank you in advance for your help
> Leon Shargorodsky|||http://www.aspfaq.com/2446
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:88A36F06-8FF7-4483-9BDC-89F349E03BAD@.microsoft.com...
> 7 Gb has been allocated for my tempdb (data file) initially, only 800 Mb
are
> being used. Is there any way I can change my tempdb data file to ~1 Gb? I
> can't shut down the system, this is not an option.
> Thank you in advance for your help
> Leon Shargorodsky

hot to shrink tempdb?

7 Gb has been allocated for my tempdb (data file) initially, only 800 Mb are
being used. Is there any way I can change my tempdb data file to ~1 Gb? I
can't shut down the system, this is not an option.
Thank you in advance for your help
Leon Shargorodsky--select * from tempdb.dbo.sysfiles
DBCC SHRINKFILE (<logicalfilename>,<mb> ) -- datafile
DBCC SHRINKFILE (<logicalfilename>,<mb> ) --logfile
sample:
DBCC SHRINKFILE (MSDBData,1000)
DBCC SHRINKFILE (MSDBLog,200)
"Leon Shargorodsky" wrote:

> 7 Gb has been allocated for my tempdb (data file) initially, only 800 Mb a
re
> being used. Is there any way I can change my tempdb data file to ~1 Gb? I
> can't shut down the system, this is not an option.
> Thank you in advance for your help
> Leon Shargorodsky|||http://www.aspfaq.com/2446
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:88A36F06-8FF7-4483-9BDC-89F349E03BAD@.microsoft.com...
> 7 Gb has been allocated for my tempdb (data file) initially, only 800 Mb
are
> being used. Is there any way I can change my tempdb data file to ~1 Gb? I
> can't shut down the system, this is not an option.
> Thank you in advance for your help
> Leon Shargorodsky