Monitoring tempdb usage.

I want to monitor the number of times that tempdb was accessed, wether it
grew or shrunk. What counters of perfmon can I use for this? Or should I use
the profiler instead ? There are db file related parameters over there.
Cheers
SQLCatZ
IN the database section of Perfmon there are shrink and growth counters, as
well as file size... I'd go there...
You could also take some code out of sp_spaceused and do your own thing for
size..
As to access, you could use physical reads/writes if the files are on their
own hard drive... otherwise it would be a profiler thing..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> I want to monitor the number of times that tempdb was accessed, wether it
> grew or shrunk. What counters of perfmon can I use for this? Or should I
use
> the profiler instead ? There are db file related parameters over there.
> Cheers
> SQLCatZ
>
|||to add on Wayne's comment, you could use profiler to monitor events of
SP:starting, T-SQL:StmtStarting and RPC:SPStarting, filtering on database id
= 2.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uhzwXRlSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> IN the database section of Perfmon there are shrink and growth counters,
> as
> well as file size... I'd go there...
> You could also take some code out of sp_spaceused and do your own thing
> for
> size..
> As to access, you could use physical reads/writes if the files are on
> their
> own hard drive... otherwise it would be a profiler thing..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> use
>

Monitoring tempdb usage.

I want to monitor the number of times that tempdb was accessed, wether it
grew or shrunk. What counters of perfmon can I use for this? Or should I use
the profiler instead ? There are db file related parameters over there.
Cheers
SQLCatZIN the database section of Perfmon there are shrink and growth counters, as
well as file size... I'd go there...
You could also take some code out of sp_spaceused and do your own thing for
size..
As to access, you could use physical reads/writes if the files are on their
own hard drive... otherwise it would be a profiler thing..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> I want to monitor the number of times that tempdb was accessed, wether it
> grew or shrunk. What counters of perfmon can I use for this? Or should I
use
> the profiler instead ? There are db file related parameters over there.
> Cheers
> SQLCatZ
>|||to add on Wayne's comment, you could use profiler to monitor events of
SP:starting, T-SQL:StmtStarting and RPC:SPStarting, filtering on database id
= 2.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uhzwXRlSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> IN the database section of Perfmon there are shrink and growth counters,
> as
> well as file size... I'd go there...
> You could also take some code out of sp_spaceused and do your own thing
> for
> size..
> As to access, you could use physical reads/writes if the files are on
> their
> own hard drive... otherwise it would be a profiler thing..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
>> I want to monitor the number of times that tempdb was accessed, wether it
>> grew or shrunk. What counters of perfmon can I use for this? Or should I
> use
>> the profiler instead ? There are db file related parameters over there.
>> Cheers
>> SQLCatZ
>

Monitoring tempdb usage.

I want to monitor the number of times that tempdb was accessed, wether it
grew or shrunk. What counters of perfmon can I use for this? Or should I use
the profiler instead ? There are db file related parameters over there.
Cheers
SQLCatZIN the database section of Perfmon there are shrink and growth counters, as
well as file size... I'd go there...
You could also take some code out of sp_spaceused and do your own thing for
size..
As to access, you could use physical reads/writes if the files are on their
own hard drive... otherwise it would be a profiler thing..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> I want to monitor the number of times that tempdb was accessed, wether it
> grew or shrunk. What counters of perfmon can I use for this? Or should I
use
> the profiler instead ? There are db file related parameters over there.
> Cheers
> SQLCatZ
>|||to add on Wayne's comment, you could use profiler to monitor events of
SP:starting, T-SQL:StmtStarting and RPC:SPStarting, filtering on database id
= 2.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uhzwXRlSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> IN the database section of Perfmon there are shrink and growth counters,
> as
> well as file size... I'd go there...
> You could also take some code out of sp_spaceused and do your own thing
> for
> size..
> As to access, you could use physical reads/writes if the files are on
> their
> own hard drive... otherwise it would be a profiler thing..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> use
>sql

Monitoring remaining space?

Is there a way to send e-mail alerts on the remaining space based on the
percentage space remaining?
I have autogrow turned *OFF* on tempdb & all the tranlogs (so that runaway
transactions & tempdb queries don't chew up the server resources/disk
space), and I want to monitor the data files for percentage use as well
before they grow. I was also thinking about using raw devices for the
tranlogs.
Thanx in advance,
/lYou can get some stuff from www.dbmaint.com (free utilities). There are two stored procedures that
can be of interest. One warn you if there is less then n% free space. The other one warn if you had
autogrow. You might need to modify them to suit your needs.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:skQlb.24$xv4.10@.twister.austin.rr.com...
> Is there a way to send e-mail alerts on the remaining space based on the
> percentage space remaining?
> I have autogrow turned *OFF* on tempdb & all the tranlogs (so that runaway
> transactions & tempdb queries don't chew up the server resources/disk
> space), and I want to monitor the data files for percentage use as well
> before they grow. I was also thinking about using raw devices for the
> tranlogs.
> Thanx in advance,
> /l
>
>|||Thanx!!!|||Is probably overkill for you requirements but we use MOM to do just that...
--
HTH
Ryan Waight, MCDBA, MCSE
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:skQlb.24$xv4.10@.twister.austin.rr.com...
> Is there a way to send e-mail alerts on the remaining space based on the
> percentage space remaining?
> I have autogrow turned *OFF* on tempdb & all the tranlogs (so that runaway
> transactions & tempdb queries don't chew up the server resources/disk
> space), and I want to monitor the data files for percentage use as well
> before they grow. I was also thinking about using raw devices for the
> tranlogs.
> Thanx in advance,
> /l
>
>|||> I was also thinking about using raw devices for the tranlogs.
In general, that is not advisable. NTFS files are must easier to manage.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:skQlb.24$xv4.10@.twister.austin.rr.com...
> Is there a way to send e-mail alerts on the remaining space based on the
> percentage space remaining?
> I have autogrow turned *OFF* on tempdb & all the tranlogs (so that runaway
> transactions & tempdb queries don't chew up the server resources/disk
> space), and I want to monitor the data files for percentage use as well
> before they grow. I was also thinking about using raw devices for the
> tranlogs.
> Thanx in advance,
> /l
>
>

MOM Alert when tempdb size grows > 40GB

I am trying to setup a MOM Alert for when a tempdb grows in size above 40GB.
How can this be setup? thanks for any helpI have no idea what MOM is, but here is a code snipit that will get you the
raw data.
-- cut --
use tempdb
if object_id('#FileStats') is not null and
objectproperty(object_id('#FileStats'), 'IsTable') = 1
drop table #FileStats
DECLARE @.DB SYSNAME
DECLARE @.SQL NVARCHAR(4000)
CREATE TABLE #FileStats(
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
SET @.DB = 'tempdb'
DELETE FROM #FileStats
SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
SHOWFILESTATS WITH NO_INFOMSGS '')'
EXEC (@.SQL)
SELECT 'Size of tempdb is',
TotalExtents * 64/1024.,
'MB'
FROM #FileStats
-- cut --
"alastairn" <alastairn@.discussions.microsoft.com> wrote in message
news:D0242787-B639-4C5B-9426-437E1234F9DF@.microsoft.com...
>I am trying to setup a MOM Alert for when a tempdb grows in size above
>40GB.
> How can this be setup? thanks for any help
>|||MOM is Microsoft Operations Manager - an application for monitoring windows
OS and various server applications (such as exchange, sql server, etc).
I would investigate what is available from the SQL Server management pack
for MOM.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Jay" <spam@.nospam.org> wrote in message
news:e2BWVfMEIHA.4684@.TK2MSFTNGP06.phx.gbl...
>I have no idea what MOM is, but here is a code snipit that will get you the
>raw data.
> -- cut --
> use tempdb
> if object_id('#FileStats') is not null and
> objectproperty(object_id('#FileStats'), 'IsTable') = 1
> drop table #FileStats
> DECLARE @.DB SYSNAME
> DECLARE @.SQL NVARCHAR(4000)
> CREATE TABLE #FileStats(
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> SET @.DB = 'tempdb'
> DELETE FROM #FileStats
> SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
> SHOWFILESTATS WITH NO_INFOMSGS '')'
> EXEC (@.SQL)
> SELECT 'Size of tempdb is',
> TotalExtents * 64/1024.,
> 'MB'
> FROM #FileStats
> -- cut --
> "alastairn" <alastairn@.discussions.microsoft.com> wrote in message
> news:D0242787-B639-4C5B-9426-437E1234F9DF@.microsoft.com...
>>I am trying to setup a MOM Alert for when a tempdb grows in size above
>>40GB.
>> How can this be setup? thanks for any help
>>
>