Monitor Jobs on SQL Server

Hi,

I'd like to know if I can monitor all the jobs in SQL Server 2000 or 2005 using SMO Objects. Specifically, I'd like to know if the jobs has been run, executed suceefully or not (recording to Log files). I also like to view/enumerate all the existing jobs. I'd like to know if all these are possible if I use SMO Object with Visual Basic.net.. If possible please give me sample code or web site so I can learn how to do it. Thanks for reading my post.

The method you'll want to look at is the EnumJobHistory method of the JobServer object. This method returns a DataTable, which you can bind to a DataGrid and see the results. You'll need to import Microsoft.SqlServer.Management.SMO.Agent as well as the .SMO and .Common namespaces.

Dim tblServerJobHist As DataTable

' Connect to the server
Dim srvMgmtServer As Server
srvMgmtServer = New Server("MyServer")
Dim srvConn As ServerConnection
srvConn = srvMgmtServer.ConnectionContext
srvConn.LoginSecure = True

tblServerJobHist = srvMgmtServer.JobServer.EnumJobHistory

Hope that helps you get started.

|||

Thanks for reply my post. Furthermore, can i monitor the jobs that has been run successfully or not by using SMO object? for example, i like to have the SMO object to tell me if the jobs has run successfully or failed after a certain period.

Thanks.

|||You don't need SMO to notify you if a job has failed, because you can set up the job to send you notifications if that's what you need.|||Hi folks

Now I have SQL Server 2000 & Visual Studio 2003

How can i Import this NameSpace "Microsoft.SqlServer.Management.SMO.Agent as well as the .SMO and .Common namespaces." which Reference i want to add this?

If its Not possible than How can i run this program in VB.NET 2003 or VB6.0 Please Help me... Its Very Urgent for me......


|||You can't use SMO with Visual Studio 2003, it is only available using Visual Studio 2005 or later. The SMO namespaces are only available in Visual Studio 2005.

Monitor Index Usage.

Hi,
Is there any tool out there that can monitor what indexes are being used in
SQL Server? Example a filter in SQL Server Profiler for index usage?
Marcel
mpighin wrote:
> Hi,
> Is there any tool out there that can monitor what indexes are being
> used in SQL Server? Example a filter in SQL Server Profiler for index
> usage?
I don't know a direct way at the moment, but you can get Profiler to write
text execution plans and search them for your index's name. HTH
Kind regards
robert

Monitor i/o, cpu etc. without traces

Any suggestions on how I can monitor the following without using traces? I am a dba/developer working as a developer on a contract, and I'm supposed to be tuning. However, I can't run traces. I've got my own procs that monitor locking, etc. But I would like to get at least i/o and cpu throughout the day. It would also be nice to get the query executed. Basically, the type of stuff you'd normally use traces for.

I know about @.@.cpu, @.@.io etc., but these are basically useless (no?) since they only record since the server was started. There is a stored proc but it only monitors these things since the last time it was run.

Does anyone know how I could utilize the above? I tried to write a script but I couldn't get it to work. :(

I realize that in general this is a ridiculous request, but I thought I would ask anyway.

Hello, Rottengeek.

Are you using SQL Server 2005? If so, you might want to use some of the new DMVs.

sys.dm_exec_query_stats gives you I/O and execution statistics for each plan. You can use the columns in this view to relate the plan handle and statement handle back to the actual SQL text. You'll find that the DMV has lots of neat data; each of the statistics carries a total, min and max value for the plan since it was last compiled.

If you checkout the dm_exec_sql_text() function in books online, it shows how to get the statement text given a sql_handle (one of the columns in dm_exec_query_stats) and has a couple of neat sample queries.

I hope that's enough to get you going in the right direction. If you have follow-up questions, please let me know.

.B ekiM

|||

Below are some of the system functions/virtual tables that you can use in SQL Server 2000:

fn_virtualfilestats - To get I/O counters per database/file

sysprocesses

sysperfinfo - Perfmon counters

But whether you can access these or not depends on your permission levels. You can also take a look at the links below for other pointers:

http://support.microsoft.com/kb/298475/

http://support.microsoft.com/kb/243588/

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

|||

I'm on SQL Server 2000.

This was VERY helpful.

Unfortunately, I can't query sysperfinfo, however, I may be able to get a workaround by submitting a script to the DBA...who knows. At any rate, as a consultant (who is normally a DBA) these will come in VERY HANDY. One of the links instructs you how to create sp_blocker_pss80, which I will modify but will be much better than running a trace.

I'm typically pretty familiar with the system tables, but sysperfinfo I did not know about.

Thanks! If anyone else has some more ideas, I'd love to hear them...

Monitor free space

I want an SQL script that will monitor free space on certain drives of
my SQL2000 server.
Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
a
SELECT freespace FROM xxxx WHERE (mydrive='C')
method that I can use?
TIA
Dave.
Another way
CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184658104.570475.151950@.e16g2000pri.googlegr oups.com...
>I want an SQL script that will monitor free space on certain drives of
> my SQL2000 server.
> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
> a
> SELECT freespace FROM xxxx WHERE (mydrive='C')
> method that I can use?
>
> TIA
> Dave.
>
|||Sorry, change TotalSize to FreeSpace.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23oHy2rEyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Another way
> CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
> RETURNS NUMERIC(20)
> BEGIN
> DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
> @.drivesize VARCHAR(20)
> SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
> EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
> IF @.rs<> 0
> SET @.drivesize = NULL
> EXEC sp_OADestroy @.drv
> EXEC sp_OADestroy @.fso
> RETURN @.drivesize
> END
> GO
> SELECT dbo.GetDriveSize('C')
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184658104.570475.151950@.e16g2000pri.googlegr oups.com...
>

Monitor free space

I want an SQL script that will monitor free space on certain drives of
my SQL2000 server.
Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
a
SELECT freespace FROM xxxx WHERE (mydrive='C')
method that I can use?
TIA
Dave.Another way
CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>I want an SQL script that will monitor free space on certain drives of
> my SQL2000 server.
> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
> a
> SELECT freespace FROM xxxx WHERE (mydrive='C')
> method that I can use?
>
> TIA
> Dave.
>|||Sorry, change TotalSize to FreeSpace.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23oHy2rEyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Another way
> CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
> RETURNS NUMERIC(20)
> BEGIN
> DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
> @.drivesize VARCHAR(20)
> SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
> EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
> IF @.rs<> 0
> SET @.drivesize = NULL
> EXEC sp_OADestroy @.drv
> EXEC sp_OADestroy @.fso
> RETURN @.drivesize
> END
> GO
> SELECT dbo.GetDriveSize('C')
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>

Monitor free space

I want an SQL script that will monitor free space on certain drives of
my SQL2000 server.
Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
a
SELECT freespace FROM xxxx WHERE (mydrive='C')
method that I can use?
TIA
Dave.Another way
CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>I want an SQL script that will monitor free space on certain drives of
> my SQL2000 server.
> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
> a
> SELECT freespace FROM xxxx WHERE (mydrive='C')
> method that I can use?
>
> TIA
> Dave.
>|||Sorry, change TotalSize to FreeSpace.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23oHy2rEyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Another way
> CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
> RETURNS NUMERIC(20)
> BEGIN
> DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
> @.drivesize VARCHAR(20)
> SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
> EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
> IF @.rs = 0
> EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
> IF @.rs<> 0
> SET @.drivesize = NULL
> EXEC sp_OADestroy @.drv
> EXEC sp_OADestroy @.fso
> RETURN @.drivesize
> END
> GO
> SELECT dbo.GetDriveSize('C')
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>>I want an SQL script that will monitor free space on certain drives of
>> my SQL2000 server.
>> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
>> a
>> SELECT freespace FROM xxxx WHERE (mydrive='C')
>> method that I can use?
>>
>> TIA
>> Dave.
>

Monitor for 60% server utilization...

I want to monitor a SQL server to alert me when the
server is at 60% utilization. Ideally I want to know
which counters to look for, such as total memory, %
Processor Time, Available memory, anything you can think
of that is relevant. I know sql performance jumps but
maybe over a 15 or 30 second interval, average the
counters and if they average over 60% to send out an
alert.
Question one: Which Perfmon counters would you watch for
to determine what percentage utilized the server is
running at.
Question two: Can you think of an easier way to find
server utilization. Where I can say, "Check this, if its
at this level your server is at 60% utilized."
Question three: Can you tell perfmon to monitor counters
and average them over 30 second intervals and send alerts
when any consecutive 30 seconds maintains 60% utilized.
Are you looking to identify a problem? 60% of what? Disk? Memory? CPU? ?
"Tim" <anonymous@.discussions.microsoft.com> wrote in message
news:326e01c47e89$58f00200$a401280a@.phx.gbl...
>I want to monitor a SQL server to alert me when the
> server is at 60% utilization. Ideally I want to know
> which counters to look for, such as total memory, %
> Processor Time, Available memory, anything you can think
> of that is relevant. I know sql performance jumps but
> maybe over a 15 or 30 second interval, average the
> counters and if they average over 60% to send out an
> alert.
> Question one: Which Perfmon counters would you watch for
> to determine what percentage utilized the server is
> running at.
> Question two: Can you think of an easier way to find
> server utilization. Where I can say, "Check this, if its
> at this level your server is at 60% utilized."
> Question three: Can you tell perfmon to monitor counters
> and average them over 30 second intervals and send alerts
> when any consecutive 30 seconds maintains 60% utilized.
|||Memory and CPU. Just trying to figure out when the
server is going to needs some help, Either more memory or
another SQL Server.
>--Original Message--
>Are you looking to identify a problem? 60% of what?
Disk? Memory? CPU? ?
>
>"Tim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:326e01c47e89$58f00200$a401280a@.phx.gbl...
think[vbcol=seagreen]
for[vbcol=seagreen]
its[vbcol=seagreen]
counters[vbcol=seagreen]
alerts[vbcol=seagreen]
utilized.
>
>.
>
|||anonymous@.discussions.microsoft.com wrote:
> Memory and CPU. Just trying to figure out when the
> server is going to needs some help, Either more memory or
> another SQL Server.
Or more tuning...
David G.
|||Hi,
1] I often use the following counters to get an initial overview of CPU
and memory :
For CPU :
Processor.%ProcessorTime, and Process.%ProcessorTime[SQLServr]. This
tells me how busy the server is, and whether it is being used by SQL Server.
System.ProcessorQueueLength. Should not be more than 2 per processor.
For Memory :
Memory.PageFaults/s. This should be low, otherwise your system is
constantly going to disk for memory
SQLServer:BufferManager.BufferCacheHitRatio. This should be high,
meaning SQL is finding all its data in memory.
Total Memory and Available memory are not that meaningfull, as SQL
doesnt release memory until another application needs it.
2] There are some really good tools out there that monitor these
counters, and raise alerts when thresholds are reached. I've used Quest
Spotlight, and BMC's DBXray.
3] Some of the perfmon counters are averaged over your sample interval,
others are a 'snapshot' at the time the sample is taken. I dont think
this is configurable.
If you want to be alerted when a threshold value is exceeded, you can
configure perfmon "alerts" to alert you.
thanks
Ian
iank@.iworks.co.za
Tim wrote:
> I want to monitor a SQL server to alert me when the
> server is at 60% utilization. Ideally I want to know
> which counters to look for, such as total memory, %
> Processor Time, Available memory, anything you can think
> of that is relevant. I know sql performance jumps but
> maybe over a 15 or 30 second interval, average the
> counters and if they average over 60% to send out an
> alert.
> Question one: Which Perfmon counters would you watch for
> to determine what percentage utilized the server is
> running at.
> Question two: Can you think of an easier way to find
> server utilization. Where I can say, "Check this, if its
> at this level your server is at 60% utilized."
> Question three: Can you tell perfmon to monitor counters
> and average them over 30 second intervals and send alerts
> when any consecutive 30 seconds maintains 60% utilized.