monitoring using sql server

Hi,

Could u pls tell as to how to find the size of a transaction or monitor it on a server using sql server 2005?

Regards

Rohit

I am not sure what u mean by Size of a transaction. U can use Profiler or Trace to monitor activities on a server

Refer : http://msdn2.microsoft.com/en-us/library/aa173918(SQL.80).aspx

Madhu

Monitoring Transaction Time

How do I monitor how long a transaction/SQL Statement
takes in SQL Profiler ?. What is the unit of the
Duration? sec or ms ?
Thanks.
The unit of duration in SQL Profile is milliseconds.
The duration is the total duration of the transaction, form the moment the
request from the client came in until the moment that the receipt of the
last data has been acknowledged by the client. If you just want the
processor time, look at the CPU column in your profiler trace.
Jacco Schalkwijk
SQL Server MVP
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
> How do I monitor how long a transaction/SQL Statement
> takes in SQL Profiler ?. What is the unit of the
> Duration? sec or ms ?
> Thanks.
|||No. I just want to see how long each transaction/SQL
statement takes.
Thanks.

>--Original Message--
>The unit of duration in SQL Profile is milliseconds.
>The duration is the total duration of the transaction,
form the moment the
>request from the client came in until the moment that the
receipt of the
>last data has been acknowledged by the client. If you
just want the
>processor time, look at the CPU column in your profiler
trace.
>--
>Jacco Schalkwijk
>SQL Server MVP
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
>
>.
>
|||As a standard I always put start time and end time as the first two columns in my traces, this helps me do other things too.
Hope this helps

Monitoring Transaction Time

How do I monitor how long a transaction/SQL Statement
takes in SQL Profiler '. What is the unit of the
Duration? sec or ms ?
Thanks.The unit of duration in SQL Profile is milliseconds.
The duration is the total duration of the transaction, form the moment the
request from the client came in until the moment that the receipt of the
last data has been acknowledged by the client. If you just want the
processor time, look at the CPU column in your profiler trace.
Jacco Schalkwijk
SQL Server MVP
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
> How do I monitor how long a transaction/SQL Statement
> takes in SQL Profiler '. What is the unit of the
> Duration? sec or ms ?
> Thanks.|||No. I just want to see how long each transaction/SQL
statement takes.
Thanks.

>--Original Message--
>The unit of duration in SQL Profile is milliseconds.
>The duration is the total duration of the transaction,
form the moment the
>request from the client came in until the moment that the
receipt of the
>last data has been acknowledged by the client. If you
just want the
>processor time, look at the CPU column in your profiler
trace.
>--
>Jacco Schalkwijk
>SQL Server MVP
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
>
>.
>|||As a standard I always put start time and end time as the first two columns
in my traces, this helps me do other things too.
Hope this helps

Monitoring Transaction Time

How do I monitor how long a transaction/SQL Statement
takes in SQL Profiler '. What is the unit of the
Duration? sec or ms ?
Thanks.The unit of duration in SQL Profile is milliseconds.
The duration is the total duration of the transaction, form the moment the
request from the client came in until the moment that the receipt of the
last data has been acknowledged by the client. If you just want the
processor time, look at the CPU column in your profiler trace.
--
Jacco Schalkwijk
SQL Server MVP
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
> How do I monitor how long a transaction/SQL Statement
> takes in SQL Profiler '. What is the unit of the
> Duration? sec or ms ?
> Thanks.|||No. I just want to see how long each transaction/SQL
statement takes.
Thanks.
>--Original Message--
>The unit of duration in SQL Profile is milliseconds.
>The duration is the total duration of the transaction,
form the moment the
>request from the client came in until the moment that the
receipt of the
>last data has been acknowledged by the client. If you
just want the
>processor time, look at the CPU column in your profiler
trace.
>--
>Jacco Schalkwijk
>SQL Server MVP
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
>> How do I monitor how long a transaction/SQL Statement
>> takes in SQL Profiler '. What is the unit of the
>> Duration? sec or ms ?
>> Thanks.
>
>.
>|||As a standard I always put start time and end time as the first two columns in my traces, this helps me do other things too
Hope this helpssql

monitoring tool

any recommendations on a tool to monitor sql server 2000? i'm looking for
something to monitor jobs, dts packagese executing, etc..and be able to
email notifications on successes or failures...
thanks
I use Microsoft Operations Manager, popularly known as MOM. It works well
for my requirements.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark" <mark_kurten@.acordia.com> wrote in message
news:uDfSmkDoFHA.3120@.TK2MSFTNGP09.phx.gbl...
> any recommendations on a tool to monitor sql server 2000? i'm looking for
> something to monitor jobs, dts packagese executing, etc..and be able to
> email notifications on successes or failures...
> thanks
>
|||Check out http://www.agileinfollc.com DataStudio
"Mark" <mark_kurten@.acordia.com> wrote in message
news:uDfSmkDoFHA.3120@.TK2MSFTNGP09.phx.gbl...
> any recommendations on a tool to monitor sql server 2000? i'm looking for
> something to monitor jobs, dts packagese executing, etc..and be able to
> email notifications on successes or failures...
> thanks
>

monitoring tool

any recommendations on a tool to monitor sql server 2000? i'm looking for
something to monitor jobs, dts packagese executing, etc..and be able to
email notifications on successes or failures...
thanksI use Microsoft Operations Manager, popularly known as MOM. It works well
for my requirements.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark" <mark_kurten@.acordia.com> wrote in message
news:uDfSmkDoFHA.3120@.TK2MSFTNGP09.phx.gbl...
> any recommendations on a tool to monitor sql server 2000? i'm looking for
> something to monitor jobs, dts packagese executing, etc..and be able to
> email notifications on successes or failures...
> thanks
>|||Check out http://www.agileinfollc.com DataStudio
"Mark" <mark_kurten@.acordia.com> wrote in message
news:uDfSmkDoFHA.3120@.TK2MSFTNGP09.phx.gbl...
> any recommendations on a tool to monitor sql server 2000? i'm looking for
> something to monitor jobs, dts packagese executing, etc..and be able to
> email notifications on successes or failures...
> thanks
>

monitoring tool

any recommendations on a tool to monitor sql server 2000? i'm looking for
something to monitor jobs, dts packagese executing, etc..and be able to
email notifications on successes or failures...
thanksI use Microsoft Operations Manager, popularly known as MOM. It works well
for my requirements.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark" <mark_kurten@.acordia.com> wrote in message
news:uDfSmkDoFHA.3120@.TK2MSFTNGP09.phx.gbl...
> any recommendations on a tool to monitor sql server 2000? i'm looking for
> something to monitor jobs, dts packagese executing, etc..and be able to
> email notifications on successes or failures...
> thanks
>|||Check out http://www.agileinfollc.com DataStudio
"Mark" <mark_kurten@.acordia.com> wrote in message
news:uDfSmkDoFHA.3120@.TK2MSFTNGP09.phx.gbl...
> any recommendations on a tool to monitor sql server 2000? i'm looking for
> something to monitor jobs, dts packagese executing, etc..and be able to
> email notifications on successes or failures...
> thanks
>

Monitoring timeouts?

What's the best way to monitor the causes of timeouts on SQL 2005?
Michael MacGregor
Database Architect
Michael
In most cases bad written queries cause timeouts. I'd indentify a long
running queries and try to optimize them
select i.name
from sys.indexes i
where i.object_id=object_id('<table_name>') and
i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
--Index usage
select object_id, index_id, user_seeks, user_scans, user_lookups
from sys.dm_db_index_usage_stats
order by object_id, index_id
All indexes which havent been used yet can be retrieved with the following
statement:
select object_name(object_id), i.name
from sys.indexes i
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
order by object_name(object_id) asc
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:OaRypwmlHHA.2552@.TK2MSFTNGP06.phx.gbl...
> What's the best way to monitor the causes of timeouts on SQL 2005?
> Michael MacGregor
> Database Architect
>
|||Hi Michael
"Michael MacGregor" wrote:

> What's the best way to monitor the causes of timeouts on SQL 2005?
> Michael MacGregor
> Database Architect
What kind of timeout? For instance
http://msdn2.microsoft.com/en-us/library/ms190181.aspx or you may want to
look at SQL Profiler and lock escallations/deadlocks/query plans etc... to
narrow down what is causing the timeouts possibly with Performance counters
if the machine is generally underperforming e.g.
http://support.microsoft.com/kb/224587/EN-US/
Other resources:
http://blogs.msdn.com/sqlcat/archive/tags/Performance+and+Scalability/default.aspx
http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/TShootPerfProbs.doc
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
John
|||Thanks guys.
Really helpful.
Filed for future reference.
MTM

Monitoring timeouts?

What's the best way to monitor the causes of timeouts on SQL 2005?
Michael MacGregor
Database ArchitectMichael
In most cases bad written queries cause timeouts. I'd indentify a long
running queries and try to optimize them
select i.name
from sys.indexes i
where i.object_id=object_id('<table_name>') and
i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
--Index usage
select object_id, index_id, user_seeks, user_scans, user_lookups
from sys.dm_db_index_usage_stats
order by object_id, index_id
All indexes which havent been used yet can be retrieved with the following
statement:
select object_name(object_id), i.name
from sys.indexes i
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
order by object_name(object_id) asc
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:OaRypwmlHHA.2552@.TK2MSFTNGP06.phx.gbl...
> What's the best way to monitor the causes of timeouts on SQL 2005?
> Michael MacGregor
> Database Architect
>|||Hi Michael
"Michael MacGregor" wrote:

> What's the best way to monitor the causes of timeouts on SQL 2005?
> Michael MacGregor
> Database Architect
What kind of timeout? For instance
http://msdn2.microsoft.com/en-us/library/ms190181.aspx or you may want to
look at SQL Profiler and lock escallations/deadlocks/query plans etc... to
narrow down what is causing the timeouts possibly with Performance counters
if the machine is generally underperforming e.g.
http://support.microsoft.com/kb/224587/EN-US/
Other resources:
http://blogs.msdn.com/sqlcat/archiv...lt.aspx

http://download.microsoft.com/downl...otPerfProbs.doc
http://download.microsoft.com/downl...aits_Queues.doc
John|||Thanks guys.
Really helpful.
Filed for future reference.
MTM

Monitoring timeouts?

What's the best way to monitor the causes of timeouts on SQL 2005?
Michael MacGregor
Database ArchitectMichael
In most cases bad written queries cause timeouts. I'd indentify a long
running queries and try to optimize them
select i.name
from sys.indexes i
where i.object_id=object_id('<table_name>') and
i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
--Index usage
select object_id, index_id, user_seeks, user_scans, user_lookups
from sys.dm_db_index_usage_stats
order by object_id, index_id
All indexes which haven?t been used yet can be retrieved with the following
statement:
select object_name(object_id), i.name
from sys.indexes i
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
order by object_name(object_id) asc
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:OaRypwmlHHA.2552@.TK2MSFTNGP06.phx.gbl...
> What's the best way to monitor the causes of timeouts on SQL 2005?
> Michael MacGregor
> Database Architect
>|||Hi Michael
"Michael MacGregor" wrote:
> What's the best way to monitor the causes of timeouts on SQL 2005?
> Michael MacGregor
> Database Architect
What kind of timeout? For instance
http://msdn2.microsoft.com/en-us/library/ms190181.aspx or you may want to
look at SQL Profiler and lock escallations/deadlocks/query plans etc... to
narrow down what is causing the timeouts possibly with Performance counters
if the machine is generally underperforming e.g.
http://support.microsoft.com/kb/224587/EN-US/
Other resources:
http://blogs.msdn.com/sqlcat/archive/tags/Performance+and+Scalability/default.aspx
http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/TShootPerfProbs.doc
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
John|||Thanks guys.
Really helpful.
Filed for future reference.
MTM

Monitoring the server

I am new to the forum and have a question. I would like to monitor the SQL
Server 2000 to make sure that it is running fine. Sometimes our server
couldn't be connected even the server is running. I have to re-start the SQL
Server in order to solve the problem. I am not sure why does this happen.
That would be nice if I could monitoring the server. Does anyone know how
to do it? Can I monitoring certain port(s)? Or ping those ports? Thanks in
advance for your help
"Skylander" <Skylander@.discussions.microsoft.com> wrote in message
news:10710AC1-A2C8-45D1-9CE5-A7032970D502@.microsoft.com...
> I am new to the forum and have a question. I would like to monitor the
SQL
> Server 2000 to make sure that it is running fine. Sometimes our server
> couldn't be connected even the server is running. I have to re-start the
SQL
> Server in order to solve the problem. I am not sure why does this happen.
> That would be nice if I could monitoring the server. Does anyone know how
> to do it? Can I monitoring certain port(s)? Or ping those ports? Thanks
in
> advance for your help
There are a variety of monitoring options available, from high-end to basic:
Microsoft Operation Monitor (MOM), network monitor, performance monitor,
home grown application.
Also, you'll want to give this article a read:
http://support.microsoft.com/default...b;en-us;827422
Steve
sql

Monitoring the server

I am new to the forum and have a question. I would like to monitor the SQL
Server 2000 to make sure that it is running fine. Sometimes our server
couldn't be connected even the server is running. I have to re-start the SQ
L
Server in order to solve the problem. I am not sure why does this happen.
That would be nice if I could monitoring the server. Does anyone know how
to do it? Can I monitoring certain port(s)? Or ping those ports? Thanks in
advance for your help "Skylander" <Skylander@.discussions.microsoft.com> wrote in message
news:10710AC1-A2C8-45D1-9CE5-A7032970D502@.microsoft.com...
> I am new to the forum and have a question. I would like to monitor the
SQL
> Server 2000 to make sure that it is running fine. Sometimes our server
> couldn't be connected even the server is running. I have to re-start the
SQL
> Server in order to solve the problem. I am not sure why does this happen.
> That would be nice if I could monitoring the server. Does anyone know how
> to do it? Can I monitoring certain port(s)? Or ping those ports? Thanks
in
> advance for your help
There are a variety of monitoring options available, from high-end to basic:
Microsoft Operation Monitor (MOM), network monitor, performance monitor,
home grown application.
Also, you'll want to give this article a read:
http://support.microsoft.com/defaul...kb;en-us;827422
Steve

Monitoring the server

I am new to the forum and have a question. I would like to monitor the SQL
Server 2000 to make sure that it is running fine. Sometimes our server
couldn't be connected even the server is running. I have to re-start the SQL
Server in order to solve the problem. I am not sure why does this happen.
That would be nice if I could monitoring the server. Does anyone know how
to do it? Can I monitoring certain port(s)? Or ping those ports? Thanks in
advance for your help
Hi
There are quite a few different ways to monitor your server, but for your
immendiate problem you will have to look at the SQL Server error log or the
event log to see if there are any messages relating to the times you are
having the problems. Going forward you may want to look at third party tools
such as Tivoli or Perfmon or SQL Server Alerts or your own scripts that
monitor various aspects of the system.
John
"Skylander" wrote:

> I am new to the forum and have a question. I would like to monitor the SQL
> Server 2000 to make sure that it is running fine. Sometimes our server
> couldn't be connected even the server is running. I have to re-start the SQL
> Server in order to solve the problem. I am not sure why does this happen.
> That would be nice if I could monitoring the server. Does anyone know how
> to do it? Can I monitoring certain port(s)? Or ping those ports? Thanks in
> advance for your help
>
|||Hi John,
Thanks for your quick reply. I have an application called 'SiteScope' that
can monitoring ports and stuff (don't know the detail function of this
application). Can I setup that to monitor certain SQL Server components to
make sure it's running fine?
BTW, is SQL Server Alert part of the components of SQL Server 2000? Thanks
Vince
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> There are quite a few different ways to monitor your server, but for your
> immendiate problem you will have to look at the SQL Server error log or the
> event log to see if there are any messages relating to the times you are
> having the problems. Going forward you may want to look at third party tools
> such as Tivoli or Perfmon or SQL Server Alerts or your own scripts that
> monitor various aspects of the system.
> John
> "Skylander" wrote:
|||Hi
Monitoring SQL Server is not really about monitoring the ports, that
tend to imply you have network issues or even a DOS attack!!!!
You can look at what alerts can do by reading books online or starting
at
http://msdn.microsoft.com/library/de...omaem_0uwk.asp
John
|||Hi
You may also find these useful
http://www.databasejournal.com/scrip...le.php/3494931
or SQLPing useful
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26
John
John Bell wrote:
> Hi
> Monitoring SQL Server is not really about monitoring the ports, that
> tend to imply you have network issues or even a DOS attack!!!!
> You can look at what alerts can do by reading books online or
starting
> at
>
http://msdn.microsoft.com/library/de...omaem_0uwk.asp
> John

Monitoring the server

I am new to the forum and have a question. I would like to monitor the SQL
Server 2000 to make sure that it is running fine. Sometimes our server
couldn't be connected even the server is running. I have to re-start the SQ
L
Server in order to solve the problem. I am not sure why does this happen.
That would be nice if I could monitoring the server. Does anyone know how
to do it? Can I monitoring certain port(s)? Or ping those ports? Thanks in
advance for your help Hi
There are quite a few different ways to monitor your server, but for your
immendiate problem you will have to look at the SQL Server error log or the
event log to see if there are any messages relating to the times you are
having the problems. Going forward you may want to look at third party tools
such as Tivoli or Perfmon or SQL Server Alerts or your own scripts that
monitor various aspects of the system.
John
"Skylander" wrote:

> I am new to the forum and have a question. I would like to monitor the SQ
L
> Server 2000 to make sure that it is running fine. Sometimes our server
> couldn't be connected even the server is running. I have to re-start the
SQL
> Server in order to solve the problem. I am not sure why does this happen.
> That would be nice if I could monitoring the server. Does anyone know how
> to do it? Can I monitoring certain port(s)? Or ping those ports? Thanks
in
> advance for your help
>|||Hi John,
Thanks for your quick reply. I have an application called 'SiteScope' that
can monitoring ports and stuff (don't know the detail function of this
application). Can I setup that to monitor certain SQL Server components to
make sure it's running fine?
BTW, is SQL Server Alert part of the components of SQL Server 2000? Thanks
Vince
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> There are quite a few different ways to monitor your server, but for your
> immendiate problem you will have to look at the SQL Server error log or th
e
> event log to see if there are any messages relating to the times you are
> having the problems. Going forward you may want to look at third party too
ls
> such as Tivoli or Perfmon or SQL Server Alerts or your own scripts that
> monitor various aspects of the system.
> John
> "Skylander" wrote:
>|||Hi
Monitoring SQL Server is not really about monitoring the ports, that
tend to imply you have network issues or even a DOS attack!!!!
You can look at what alerts can do by reading books online or starting
at
http://msdn.microsoft.com/library/d...>
aem_0uwk.asp
John|||Hi
You may also find these useful
http://www.databasejournal.com/scri...cle.php/3494931
or SQLPing useful
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26
John
John Bell wrote:
> Hi
> Monitoring SQL Server is not really about monitoring the ports, that
> tend to imply you have network issues or even a DOS attack!!!!
> You can look at what alerts can do by reading books online or
starting
> at
>
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_automaem_0uwk.asp[/ur
l]
> John

Monitoring the server

I am new to the forum and have a question. I would like to monitor the SQL
Server 2000 to make sure that it is running fine. Sometimes our server
couldn't be connected even the server is running. I have to re-start the SQL
Server in order to solve the problem. I am not sure why does this happen.
That would be nice if I could monitoring the server. Does anyone know how
to do it? Can I monitoring certain port(s)? Or ping those ports? Thanks in
advance for your help :)Hi
There are quite a few different ways to monitor your server, but for your
immendiate problem you will have to look at the SQL Server error log or the
event log to see if there are any messages relating to the times you are
having the problems. Going forward you may want to look at third party tools
such as Tivoli or Perfmon or SQL Server Alerts or your own scripts that
monitor various aspects of the system.
John
"Skylander" wrote:
> I am new to the forum and have a question. I would like to monitor the SQL
> Server 2000 to make sure that it is running fine. Sometimes our server
> couldn't be connected even the server is running. I have to re-start the SQL
> Server in order to solve the problem. I am not sure why does this happen.
> That would be nice if I could monitoring the server. Does anyone know how
> to do it? Can I monitoring certain port(s)? Or ping those ports? Thanks in
> advance for your help :)
>|||Hi John,
Thanks for your quick reply. I have an application called 'SiteScope' that
can monitoring ports and stuff (don't know the detail function of this
application). Can I setup that to monitor certain SQL Server components to
make sure it's running fine?
BTW, is SQL Server Alert part of the components of SQL Server 2000? Thanks
Vince
"John Bell" wrote:
> Hi
> There are quite a few different ways to monitor your server, but for your
> immendiate problem you will have to look at the SQL Server error log or the
> event log to see if there are any messages relating to the times you are
> having the problems. Going forward you may want to look at third party tools
> such as Tivoli or Perfmon or SQL Server Alerts or your own scripts that
> monitor various aspects of the system.
> John
> "Skylander" wrote:
> > I am new to the forum and have a question. I would like to monitor the SQL
> > Server 2000 to make sure that it is running fine. Sometimes our server
> > couldn't be connected even the server is running. I have to re-start the SQL
> > Server in order to solve the problem. I am not sure why does this happen.
> >
> > That would be nice if I could monitoring the server. Does anyone know how
> > to do it? Can I monitoring certain port(s)? Or ping those ports? Thanks in
> > advance for your help :)
> >|||Hi
Monitoring SQL Server is not really about monitoring the ports, that
tend to imply you have network issues or even a DOS attack!!!!
You can look at what alerts can do by reading books online or starting
at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_automaem_0uwk.asp
John|||Hi
You may also find these useful
http://www.databasejournal.com/scripts/article.php/3494931
or SQLPing useful
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26
John
John Bell wrote:
> Hi
> Monitoring SQL Server is not really about monitoring the ports, that
> tend to imply you have network issues or even a DOS attack!!!!
> You can look at what alerts can do by reading books online or
starting
> at
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_automaem_0uwk.asp
> John

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 Statistics inside a DTS package

Hi,
Can some one tell me if I can monitor the statistics information for each
query
running inside a DTS package. The DTS package takes a long time to complete.
I need this help to fine tune the DTS pacakge.
Thanks and Regards,
Prasanth
You can
Set Statistics IO on
Set statistics Time on
etc...but save the output to a file..
you might also run Profiler to capture additional information.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:8E3F74FE-5505-4A44-9E1E-0F1948AF3D18@.microsoft.com...
> Hi,
> Can some one tell me if I can monitor the statistics information for each
> query
> running inside a DTS package. The DTS package takes a long time to
complete.
> I need this help to fine tune the DTS pacakge.
> --
> Thanks and Regards,
> Prasanth
sql

Monitoring Statistics inside a DTS package

Hi,
Can some one tell me if I can monitor the statistics information for each
query
running inside a DTS package. The DTS package takes a long time to complete.
I need this help to fine tune the DTS pacakge.
--
Thanks and Regards,
PrasanthYou can
Set Statistics IO on
Set statistics Time on
etc...but save the output to a file..
you might also run Profiler to capture additional information.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:8E3F74FE-5505-4A44-9E1E-0F1948AF3D18@.microsoft.com...
> Hi,
> Can some one tell me if I can monitor the statistics information for each
> query
> running inside a DTS package. The DTS package takes a long time to
complete.
> I need this help to fine tune the DTS pacakge.
> --
> Thanks and Regards,
> Prasanth