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.

Monitor Filegrowth

Hi All
With SQL Server 2005 what is the best way to be alerted when autogrowth on a
file occurs?
Thanks
Hello use system monitor to do this. See the below URL for more details:-
http://support.microsoft.com/?kbid=299921
Thanks
Hari
"David" <David@.discussions.microsoft.com> wrote in message
news:BDFD49B2-D639-4573-885A-EC64AFE56153@.microsoft.com...
> Hi All
> With SQL Server 2005 what is the best way to be alerted when autogrowth on
> a
> file occurs?
> Thanks
|||David
You can run SQL Server Profiler to monitor it
"David" <David@.discussions.microsoft.com> wrote in message
news:BDFD49B2-D639-4573-885A-EC64AFE56153@.microsoft.com...
> Hi All
> With SQL Server 2005 what is the best way to be alerted when autogrowth on
> a
> file occurs?
> Thanks
|||I believe the default trace in SQL 2005 already monitors for this event. You
can poll the trace files periodically to see if this event has occurred.
Andrew J. Kelly SQL MVP
"David" <David@.discussions.microsoft.com> wrote in message
news:BDFD49B2-D639-4573-885A-EC64AFE56153@.microsoft.com...
> Hi All
> With SQL Server 2005 what is the best way to be alerted when autogrowth on
> a
> file occurs?
> Thanks
|||The default trace does monitor this however it does not alert.
Events in the Default Trace:
EventID(event class), Event Name, Description
18
Audit Server Starts and Stops
Occurs when the SQL Server service state is modified.
20
Audit Login Failed
Indicates that a login attempt to SQL Server from a client failed.
22
ErrorLog
Indicates that error events have been logged in the SQL Server error
log.
46
Object:Created
Indicates that an object has been created, such as for CREATE INDEX,
CREATE TABLE, and CREATE DATABASE statements.
47
Object:Deleted
Indicates that an object has been deleted, such as in DROP INDEX and
DROP TABLE statements.
55
Hash Warning
Indicates that a hashing operation (for example, hash join, hash
aggregate, hash union, and hash distinct) that is not processing on a
buffer partition has reverted to an alternate plan. This can occur
because of recursion depth, data skew, trace flags, or bit counting.
69
Sort Warnings
Indicates sort operations that do not fit into memory. Does not
include sort operations involving the creating of indexes; only sort
operations within a query (such as an ORDER BY clause used in a SELECT
statement).
79
Missing Column Statistics
Column statistics that could have been useful for the optimizer are
not available
80
Missing Join Predicate
Query that has no join predicate is being executed. This could result
in a long-running query.
81
Server Memory Change
SQL Server memory usage has increased or decreased by either 1
megabyte (MB) or 5 percent of the maximum server memory, whichever is
greater.
92
Data File Auto Grow
Indicates that a data file was extended automatically by the server.
93
Log File Auto Grow
Indicates that a data file was extended automatically by the server
94
Data File Auto Shrink
Indicates that a data file was shrunk automatically by the server.
95
Log File Auto Shrink
Indicates that a log file was shrunk automatically by the server.
102
Audit Statement GDR Event
Occurs every time a GRANT, DENY, REVOKE for a statement permission is
issued by any user in SQL Server.
103
Audit Object GDR Event
Occurs every time a GRANT, DENY, REVOKE for an object permission is
issued by any user in SQL Server.
104
Audit AddLogin Event
Occurs when a SQL Server login is added or removed; for sp_addlogin
and sp_droplogin.
105
Audit Login GDR Event
Occurs when a Windows login right is added or removed; for
sp_grantlogin, sp_revokelogin, and sp_denylogin.
106
Audit Login Change Property Event
Occurs when a property of a login, except passwords, is modified; for
sp_defaultdb and sp_defaultlanguage.
108
Audit Add Login to Server Role Event
Occurs when a login is added or removed from a fixed server role; for
sp_addsrvrolemember, and sp_dropsrvrolemember.
109
Audit Add DB User Event
Occurs when a login is added or removed as a database user (Windows
or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess,
sp_adduser, and sp_dropuser.
110
Audit Add Member to DB Role Event
Occurs when a login is added or removed as a database user (fixed or
user-defined) to a database; for sp_addrolemember, sp_droprolemember,
and sp_changegroup.
111
Audit Add Role Event
Occurs when a login is added or removed as a database user to a
database; for sp_addrole and sp_droprole.
115
Audit Backup/Restore Event
Occurs when a BACKUP or RESTORE command is issued.
116
Audit DBCC Event
Occurs when DBCC commands are issued.
152
Audit Change Database Owner
Occurs when ALTER AUTHORIZATION is used to change the owner of a
database and permissions are checked to do that.
153
Audit Schema Object Take Ownership Event
Occurs when ALTER AUTHORIZATION is used to assign an owner to an
object and permissions are checked to do that.
155
FT:Crawl Started
Occurs when a full-text crawl (population) starts. Use to check if a
crawl request is picked up by worker tasks.
156
FT:Crawl Stopped
Occurs when a full-text crawl (population) stops. Stops occur when a
crawl completes successfully or when a fatal error occurs.
157
FT:Crawl Aborted
Occurs when an exception is encountered during a full-text crawl.
Usually causes the full-text crawl to stop.
164
Object:Altered
Occurs when a database object is altered.
167
Database Mirroring State Change
Occurs when the state of a mirrored database changes.

Monitor Filegrowth

Hi All
With SQL Server 2005 what is the best way to be alerted when autogrowth on a
file occurs?
ThanksHello use system monitor to do this. See the below URL for more details:-
http://support.microsoft.com/?kbid=299921
Thanks
Hari
"David" <David@.discussions.microsoft.com> wrote in message
news:BDFD49B2-D639-4573-885A-EC64AFE56153@.microsoft.com...
> Hi All
> With SQL Server 2005 what is the best way to be alerted when autogrowth on
> a
> file occurs?
> Thanks|||David
You can run SQL Server Profiler to monitor it
"David" <David@.discussions.microsoft.com> wrote in message
news:BDFD49B2-D639-4573-885A-EC64AFE56153@.microsoft.com...
> Hi All
> With SQL Server 2005 what is the best way to be alerted when autogrowth on
> a
> file occurs?
> Thanks|||I believe the default trace in SQL 2005 already monitors for this event. You
can poll the trace files periodically to see if this event has occurred.
--
Andrew J. Kelly SQL MVP
"David" <David@.discussions.microsoft.com> wrote in message
news:BDFD49B2-D639-4573-885A-EC64AFE56153@.microsoft.com...
> Hi All
> With SQL Server 2005 what is the best way to be alerted when autogrowth on
> a
> file occurs?
> Thanks|||The default trace does monitor this however it does not alert.
Events in the Default Trace:
EventID(event class), Event Name, Description
18
Audit Server Starts and Stops
Occurs when the SQL Server service state is modified.
20
Audit Login Failed
Indicates that a login attempt to SQL Server from a client failed.
22
ErrorLog
Indicates that error events have been logged in the SQL Server error
log.
46
Object:Created
Indicates that an object has been created, such as for CREATE INDEX,
CREATE TABLE, and CREATE DATABASE statements.
47
Object:Deleted
Indicates that an object has been deleted, such as in DROP INDEX and
DROP TABLE statements.
55
Hash Warning
Indicates that a hashing operation (for example, hash join, hash
aggregate, hash union, and hash distinct) that is not processing on a
buffer partition has reverted to an alternate plan. This can occur
because of recursion depth, data skew, trace flags, or bit counting.
69
Sort Warnings
Indicates sort operations that do not fit into memory. Does not
include sort operations involving the creating of indexes; only sort
operations within a query (such as an ORDER BY clause used in a SELECT
statement).
79
Missing Column Statistics
Column statistics that could have been useful for the optimizer are
not available
80
Missing Join Predicate
Query that has no join predicate is being executed. This could result
in a long-running query.
81
Server Memory Change
SQL Server memory usage has increased or decreased by either 1
megabyte (MB) or 5 percent of the maximum server memory, whichever is
greater.
92
Data File Auto Grow
Indicates that a data file was extended automatically by the server.
93
Log File Auto Grow
Indicates that a data file was extended automatically by the server
94
Data File Auto Shrink
Indicates that a data file was shrunk automatically by the server.
95
Log File Auto Shrink
Indicates that a log file was shrunk automatically by the server.
102
Audit Statement GDR Event
Occurs every time a GRANT, DENY, REVOKE for a statement permission is
issued by any user in SQL Server.
103
Audit Object GDR Event
Occurs every time a GRANT, DENY, REVOKE for an object permission is
issued by any user in SQL Server.
104
Audit AddLogin Event
Occurs when a SQL Server login is added or removed; for sp_addlogin
and sp_droplogin.
105
Audit Login GDR Event
Occurs when a Windows login right is added or removed; for
sp_grantlogin, sp_revokelogin, and sp_denylogin.
106
Audit Login Change Property Event
Occurs when a property of a login, except passwords, is modified; for
sp_defaultdb and sp_defaultlanguage.
108
Audit Add Login to Server Role Event
Occurs when a login is added or removed from a fixed server role; for
sp_addsrvrolemember, and sp_dropsrvrolemember.
109
Audit Add DB User Event
Occurs when a login is added or removed as a database user (Windows
or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess,
sp_adduser, and sp_dropuser.
110
Audit Add Member to DB Role Event
Occurs when a login is added or removed as a database user (fixed or
user-defined) to a database; for sp_addrolemember, sp_droprolemember,
and sp_changegroup.
111
Audit Add Role Event
Occurs when a login is added or removed as a database user to a
database; for sp_addrole and sp_droprole.
115
Audit Backup/Restore Event
Occurs when a BACKUP or RESTORE command is issued.
116
Audit DBCC Event
Occurs when DBCC commands are issued.
152
Audit Change Database Owner
Occurs when ALTER AUTHORIZATION is used to change the owner of a
database and permissions are checked to do that.
153
Audit Schema Object Take Ownership Event
Occurs when ALTER AUTHORIZATION is used to assign an owner to an
object and permissions are checked to do that.
155
FT:Crawl Started
Occurs when a full-text crawl (population) starts. Use to check if a
crawl request is picked up by worker tasks.
156
FT:Crawl Stopped
Occurs when a full-text crawl (population) stops. Stops occur when a
crawl completes successfully or when a fatal error occurs.
157
FT:Crawl Aborted
Occurs when an exception is encountered during a full-text crawl.
Usually causes the full-text crawl to stop.
164
Object:Altered
Occurs when a database object is altered.
167
Database Mirroring State Change
Occurs when the state of a mirrored database changes.

Monitor Filegrowth

Hi All
With SQL Server 2005 what is the best way to be alerted when autogrowth on a
file occurs?
ThanksHello use system monitor to do this. See the below URL for more details:-
http://support.microsoft.com/?kbid=299921
Thanks
Hari
"David" <David@.discussions.microsoft.com> wrote in message
news:BDFD49B2-D639-4573-885A-EC64AFE56153@.microsoft.com...
> Hi All
> With SQL Server 2005 what is the best way to be alerted when autogrowth on
> a
> file occurs?
> Thanks|||David
You can run SQL Server Profiler to monitor it
"David" <David@.discussions.microsoft.com> wrote in message
news:BDFD49B2-D639-4573-885A-EC64AFE56153@.microsoft.com...
> Hi All
> With SQL Server 2005 what is the best way to be alerted when autogrowth on
> a
> file occurs?
> Thanks|||I believe the default trace in SQL 2005 already monitors for this event. You
can poll the trace files periodically to see if this event has occurred.
Andrew J. Kelly SQL MVP
"David" <David@.discussions.microsoft.com> wrote in message
news:BDFD49B2-D639-4573-885A-EC64AFE56153@.microsoft.com...
> Hi All
> With SQL Server 2005 what is the best way to be alerted when autogrowth on
> a
> file occurs?
> Thanks|||The default trace does monitor this however it does not alert.
Events in the Default Trace:
EventID(event class), Event Name, Description
18
Audit Server Starts and Stops
Occurs when the SQL Server service state is modified.
20
Audit Login Failed
Indicates that a login attempt to SQL Server from a client failed.
22
ErrorLog
Indicates that error events have been logged in the SQL Server error
log.
46
Object:Created
Indicates that an object has been created, such as for CREATE INDEX,
CREATE TABLE, and CREATE DATABASE statements.
47
Object:Deleted
Indicates that an object has been deleted, such as in DROP INDEX and
DROP TABLE statements.
55
Hash Warning
Indicates that a hashing operation (for example, hash join, hash
aggregate, hash union, and hash distinct) that is not processing on a
buffer partition has reverted to an alternate plan. This can occur
because of recursion depth, data skew, trace flags, or bit counting.
69
Sort Warnings
Indicates sort operations that do not fit into memory. Does not
include sort operations involving the creating of indexes; only sort
operations within a query (such as an ORDER BY clause used in a SELECT
statement).
79
Missing Column Statistics
Column statistics that could have been useful for the optimizer are
not available
80
Missing Join Predicate
Query that has no join predicate is being executed. This could result
in a long-running query.
81
Server Memory Change
SQL Server memory usage has increased or decreased by either 1
megabyte (MB) or 5 percent of the maximum server memory, whichever is
greater.
92
Data File Auto Grow
Indicates that a data file was extended automatically by the server.
93
Log File Auto Grow
Indicates that a data file was extended automatically by the server
94
Data File Auto Shrink
Indicates that a data file was shrunk automatically by the server.
95
Log File Auto Shrink
Indicates that a log file was shrunk automatically by the server.
102
Audit Statement GDR Event
Occurs every time a GRANT, DENY, REVOKE for a statement permission is
issued by any user in SQL Server.
103
Audit Object GDR Event
Occurs every time a GRANT, DENY, REVOKE for an object permission is
issued by any user in SQL Server.
104
Audit AddLogin Event
Occurs when a SQL Server login is added or removed; for sp_addlogin
and sp_droplogin.
105
Audit Login GDR Event
Occurs when a Windows login right is added or removed; for
sp_grantlogin, sp_revokelogin, and sp_denylogin.
106
Audit Login Change Property Event
Occurs when a property of a login, except passwords, is modified; for
sp_defaultdb and sp_defaultlanguage.
108
Audit Add Login to Server Role Event
Occurs when a login is added or removed from a fixed server role; for
sp_addsrvrolemember, and sp_dropsrvrolemember.
109
Audit Add DB User Event
Occurs when a login is added or removed as a database user (Windows
or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess,
sp_adduser, and sp_dropuser.
110
Audit Add Member to DB Role Event
Occurs when a login is added or removed as a database user (fixed or
user-defined) to a database; for sp_addrolemember, sp_droprolemember,
and sp_changegroup.
111
Audit Add Role Event
Occurs when a login is added or removed as a database user to a
database; for sp_addrole and sp_droprole.
115
Audit Backup/Restore Event
Occurs when a BACKUP or RESTORE command is issued.
116
Audit DBCC Event
Occurs when DBCC commands are issued.
152
Audit Change Database Owner
Occurs when ALTER AUTHORIZATION is used to change the owner of a
database and permissions are checked to do that.
153
Audit Schema Object Take Ownership Event
Occurs when ALTER AUTHORIZATION is used to assign an owner to an
object and permissions are checked to do that.
155
FT:Crawl Started
Occurs when a full-text crawl (population) starts. Use to check if a
crawl request is picked up by worker tasks.
156
FT:Crawl Stopped
Occurs when a full-text crawl (population) stops. Stops occur when a
crawl completes successfully or when a fatal error occurs.
157
FT:Crawl Aborted
Occurs when an exception is encountered during a full-text crawl.
Usually causes the full-text crawl to stop.
164
Object:Altered
Occurs when a database object is altered.
167
Database Mirroring State Change
Occurs when the state of a mirrored database changes.

Monitor DiskSpace

I have discovered that there is an extended procedure called xp_fixeddrives, which can check how much space you have on your SQL Server's drives, it works fine. Can some one tell me what can I use to monitor the above (space used on drive) on servers that do not have SQL at all. Remember I want to call that from my SQL NOT from VBWMI - Windows Management Instrumentation is what you need.
This provides you with the ability to monitor all sorts of metrics in a server which are outside of the realm of SQL Server. The notation for addressing the components takes some getting used to, but if you're writing a serious app, it's worth the effort.
Useful link : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/using_wmi.asp

Try searching from www.alltheweb.com or other for +WMI +<whatever language you use>

Enjoy :)

Monitor DB Usage at Column Level

I'm looking for a tool that will compile historical usage information for a
specific SQL Server 2000 database down to the column-level. This is a
read-only database and I need to know how many times each column of each
table has been included in a query over a period of time. This is a
production database, so it has to be a tool that won't have a major impact on
performance.
Any suggestions?
Thanks,
Hari
Hari Seldon (HariSeldon@.discussions.microsoft.com) writes:
> I'm looking for a tool that will compile historical usage information
> for a specific SQL Server 2000 database down to the column-level. This
> is a read-only database and I need to know how many times each column of
> each table has been included in a query over a period of time. This is
> a production database, so it has to be a tool that won't have a major
> impact on performance.
You would have to run a server-side trace that captures the SP:StmtCompleted
and SQL:StmtCompleted events. Exactly what columns you should include
in the trace depends on your needs, but a minimum you would have to
include TextData. The trace should save data to a file.
The performance impact on such a query is not negligible, particularly if
users run many small queries. (If they main run long-running queries, the
number of events to track is much smaller.)
The easiest way to set up such a trace is to use Profiler, and then save
the trace as a script.
That was the easy part. Once you have the trace data, you need to analyse
it, one way or another. Unless you application generates command in a way
that makes things easy for you, for instance it always uses
tablename.columnname, this is a difficult task, and I don't know
of any tool that does this, thereby not said that it does not exist.
Presumably, a program would have to read the file, and parse each query to
determine which columns that are referred to in the query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||"Erland Sommarskog" wrote:

> Hari Seldon (HariSeldon@.discussions.microsoft.com) writes:
> You would have to run a server-side trace that captures the SP:StmtCompleted
> and SQL:StmtCompleted events. Exactly what columns you should include
> in the trace depends on your needs, but a minimum you would have to
> include TextData. The trace should save data to a file.
> The performance impact on such a query is not negligible, particularly if
> users run many small queries. (If they main run long-running queries, the
> number of events to track is much smaller.)
> The easiest way to set up such a trace is to use Profiler, and then save
> the trace as a script.
> That was the easy part. Once you have the trace data, you need to analyse
> it, one way or another. Unless you application generates command in a way
> that makes things easy for you, for instance it always uses
> tablename.columnname, this is a difficult task, and I don't know
> of any tool that does this, thereby not said that it does not exist.
> Presumably, a program would have to read the file, and parse each query to
> determine which columns that are referred to in the query.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Thanks. I really appreciate the info. I had a feeling this wasn't going to
be easy.
Hari
"Erland Sommarskog" wrote:

> Hari Seldon (HariSeldon@.discussions.microsoft.com) writes:
> You would have to run a server-side trace that captures the SP:StmtCompleted
> and SQL:StmtCompleted events. Exactly what columns you should include
> in the trace depends on your needs, but a minimum you would have to
> include TextData. The trace should save data to a file.
> The performance impact on such a query is not negligible, particularly if
> users run many small queries. (If they main run long-running queries, the
> number of events to track is much smaller.)
> The easiest way to set up such a trace is to use Profiler, and then save
> the trace as a script.
> That was the easy part. Once you have the trace data, you need to analyse
> it, one way or another. Unless you application generates command in a way
> that makes things easy for you, for instance it always uses
> tablename.columnname, this is a difficult task, and I don't know
> of any tool that does this, thereby not said that it does not exist.
> Presumably, a program would have to read the file, and parse each query to
> determine which columns that are referred to in the query.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

Monitor Database up or down and email to DBA

HI,

I like to know what different tools and scripts different people are using to monitor their SQL 2005 Databases.

We need to monito two services they are up and running all the time.

MSSQLServer & SQLAgent

Currently we have 3 different sql 2005 server and each one have 4 databases, we like to monitor.

Thanks in advance

I hear good things about Quest's Spotlight and Idera's Diagnostic Manager, but haven't worked with either enough to be able to recommend them myself.

Ask in the tools group (http://groups.google.com/group/microsoft.public.sqlserver.tools) or the Tools forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1)

HTH...

Joe

Monitor database not backup

Hello,

I'm monitor my SQL 2005 using MOM 2005.

I would like to create a VB script to get database list that are not backup in the last X days.

how can I do that ?

http://www.extremeexperts.com/SQL/Scripts/Lastbackup.aspx fyi.|||

Thanks,

How do I get a list of SQL instance in order to monitor all the instance I have

Also I think this not retun database that are never backup

|||

isql -L

Lists the locally configured servers and the names of the servers broadcasting on the network.

Monitor Database Mirroring

Does database mirroring have any monitoring capabilities like Log shipping
to let the DBAs know the current status of the mirrored server i.e. how far
behind the principal it is,etc. ?
See the DMV dm_db_mirroring_connections. See details from below URL:-
http://msdn2.microsoft.com/en-us/library/ms189796.aspx
Also see:-
http://msdn2.microsoft.com/en-us/library/ms365781.aspx
Thanks
Hari
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23IQSavNeHHA.2332@.TK2MSFTNGP04.phx.gbl...
> Does database mirroring have any monitoring capabilities like Log shipping
> to let the DBAs know the current status of the mirrored server i.e. how
> far behind the principal it is,etc. ?
>

Monitor Database Mirroring

Does database mirroring have any monitoring capabilities like Log shipping
to let the DBAs know the current status of the mirrored server i.e. how far
behind the principal it is,etc. ?See the DMV dm_db_mirroring_connections. See details from below URL:-
http://msdn2.microsoft.com/en-us/library/ms189796.aspx
Also see:-
http://msdn2.microsoft.com/en-us/library/ms365781.aspx
Thanks
Hari
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23IQSavNeHHA.2332@.TK2MSFTNGP04.phx.gbl...
> Does database mirroring have any monitoring capabilities like Log shipping
> to let the DBAs know the current status of the mirrored server i.e. how
> far behind the principal it is,etc. ?
>

Monitor Database Mirroring

Does database mirroring have any monitoring capabilities like Log shipping
to let the DBAs know the current status of the mirrored server i.e. how far
behind the principal it is,etc. ?See the DMV dm_db_mirroring_connections. See details from below URL:-
http://msdn2.microsoft.com/en-us/library/ms189796.aspx
Also see:-
http://msdn2.microsoft.com/en-us/library/ms365781.aspx
Thanks
Hari
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23IQSavNeHHA.2332@.TK2MSFTNGP04.phx.gbl...
> Does database mirroring have any monitoring capabilities like Log shipping
> to let the DBAs know the current status of the mirrored server i.e. how
> far behind the principal it is,etc. ?
>

Monitor Database Growth

At the present moment, is there any software in-place to give us the ability
to perform database growth monitoring, database sizes. charts etc.
thanks
Farrukh"FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
news:3E4CD58C-736D-4F35-A574-1FE52DEBB25D@.microsoft.com...
> At the present moment, is there any software in-place to give us the
> ability
> to perform database growth monitoring, database sizes. charts etc.
> thanks
> Farrukh
The performance monitor in Windows will have SQL Server specific objects and
counters in it. Take a look at these counters.
Rick Sawtell|||Hi
Take a look into
dbname.sys.database_files system table
"FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
news:3E4CD58C-736D-4F35-A574-1FE52DEBB25D@.microsoft.com...
> At the present moment, is there any software in-place to give us the
> ability
> to perform database growth monitoring, database sizes. charts etc.
> thanks
> Farrukh

Monitor database file size

Is there a best practice for monitoring how much space is free in a database
file? I can get this info for the log file using the performance monitor
and send me a message when the free space is low. I don't want to have to
manually check this everyday, so if there is an automated means that would
be great.
Thanks.Hi,
Take a look into this article, will help you to automate to db space
monitoring.
http://www.sql-server-performance.c...with_dts_05.asp
Thanks
Hari
SQL Server MVP
"Tim Kelley" wrote:

> Is there a best practice for monitoring how much space is free in a databa
se
> file? I can get this info for the log file using the performance monitor
> and send me a message when the free space is low. I don't want to have to
> manually check this everyday, so if there is an automated means that would
> be great.
> Thanks.
>
>

Monitor database file size

Is there a best practice for monitoring how much space is free in a database
file? I can get this info for the log file using the performance monitor
and send me a message when the free space is low. I don't want to have to
manually check this everyday, so if there is an automated means that would
be great.
Thanks.Hi,
Take a look into this article, will help you to automate to db space
monitoring.
http://www.sql-server-performance.com/nn_monitor_with_dts_05.asp
Thanks
Hari
SQL Server MVP
"Tim Kelley" wrote:
> Is there a best practice for monitoring how much space is free in a database
> file? I can get this info for the log file using the performance monitor
> and send me a message when the free space is low. I don't want to have to
> manually check this everyday, so if there is an automated means that would
> be great.
> Thanks.
>
>

Monitor data per session - available?

Hiya!

I've combed the SQL Server 2000 online help but I don't see anything
akin to Sybase ASE's 'montor' views - equivalent to Oracle's V$
views. The views are performance views which contain per session
information such as:

- SPID
- number of logical read/write and physical read I/Os accumulated
thus far
- CPU accumulated
- Memory

etc.

Does anyone know if I can snarf that data from SQL Server 2000?

Thx!
--
Pablo Sanchez, Blueoak Database Engineering
http://www.blueoakdb.comErland Sommarskog <sommar@.algonet.se> wrote in
news:Xns93E5EFFB099AEYazorman@.127.0.0.1:

> What's wrong with sysprocesses?

In the 'olden' days (Sybase 4.x/10.x) the values weren't valid. Are
they considered valid nowadays?

Thx!
--
Pablo Sanchez, Blueoak Database Engineering
http://www.blueoakdb.com|||Pablo Sanchez (honeypot@.blueoakdb.com) writes:
> Erland Sommarskog <sommar@.algonet.se> wrote in
> news:Xns93E5EFFB099AEYazorman@.127.0.0.1:
>> What's wrong with sysprocesses?
> In the 'olden' days (Sybase 4.x/10.x) the values weren't valid. Are
> they considered valid nowadays?

Sorry for the delay, but I tried to consult with my MVP colleagues and
the Microsoft people, if I would get any response.

I can't vouch for that all these values are extremely valid, but then
again, I don't think you will find anything better.

The ones on memory and CPU may be OK. Logical reads is a problem, because
with the new query strategies in SQL 7 and SQL 2000, the value reflected
by logical reads in SET STATISTICS IO may not always be accurate. And if
it's not accurate there, it is not likely to be accurate in sysprocesses
either. Still the value you see is probably good indication.

Of course, you can also use the performance monitor, but I don't think
you will find per-spid values there.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in
news:Xns93EAE79435FA0Yazorman@.127.0.0.1:

> Pablo Sanchez (honeypot@.blueoakdb.com) writes:
>> Erland Sommarskog <sommar@.algonet.se> wrote in
>> news:Xns93E5EFFB099AEYazorman@.127.0.0.1:
>>
>> In the 'olden' days (Sybase 4.x/10.x) the values weren't valid.
>> Are they considered valid nowadays?
> Sorry for the delay, but I tried to consult with my MVP colleagues
> and the Microsoft people, if I would get any response.

'preciate that _very_ much.

> [ sage info snipped ]

Thank you for the data Erland. FWIW, Sybase ASE has gone the way of
Oracle and now provides in-memory views of the metrics. Perhaps SQL
Server will soon follow.

Later!
--
Pablo Sanchez, Blueoak Database Engineering
http://www.blueoakdb.com

monitor data change of merge replication

Nathan,
you can take advantage of the history table:
select publisher_insertcount, publisher_updatecount,
publisher_deletecount,
subscriber_insertcount, subscriber_updatecount,
subscriber_deletecount from
dbo.MSmerge_history
Also, you can use performance monitor for a less granular
approach:
SQL Server:Replication Merge - Downloaded Changes
SQL Server:Replication Merge - Uploaded Changes
SQL Server:Replication Merge - Conflicts
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks Paul.
Is it possible that I can trace which record and what data is changed?
Thanks
Nathan
"Paul Ibison" wrote:

> Nathan,
> you can take advantage of the history table:
> select publisher_insertcount, publisher_updatecount,
> publisher_deletecount,
> subscriber_insertcount, subscriber_updatecount,
> subscriber_deletecount from
> dbo.MSmerge_history
> Also, you can use performance monitor for a less granular
> approach:
> SQL Server:Replication Merge - Downloaded Changes
> SQL Server:Replication Merge - Uploaded Changes
> SQL Server:Replication Merge - Conflicts
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Not really. On a particular computer you could look at
the MSmerge_contents and MSmerge_tombstone tables and map
back the GUIDS. But to sit on a particular computer and
to determine if a row has been updated/inserted from the
last merge synchronization process is not possible,
AFAIK. I suppose if you really wanted this functionality,
you could use triggers to make your own audit table.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Monitor Data and Log File Growth

Hi,
We are having more than 20 databases and we are merge replicating the
databases also. Currently for all databases we set the Data and Log file
growth as Automatically by percent. The size of each database is around 1277
MB.
Now if we Change the Data and Log File growth to resctricted mode is there
is any problem will cause, if not then how we will be able to monitor the
Data and Log file growth and when we should increase the File Size.
Thanks,
HerbertHerbert
If you open SQL Server Profiler you will find an event AutoGrowth (If I
remember well) , so you can track the info as well as querying sysfiles
system table (size column) (not recomended)
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> Hi,
> We are having more than 20 databases and we are merge replicating the
> databases also. Currently for all databases we set the Data and Log file
> growth as Automatically by percent. The size of each database is around
1277
> MB.
> Now if we Change the Data and Log File growth to resctricted mode is there
> is any problem will cause, if not then how we will be able to monitor the
> Data and Log file growth and when we should increase the File Size.
> Thanks,
> Herbert|||Hi,
As a good practice, I recommend you to monitor the MDF and LDF files daily
once or twice manually. The best approach for automatic monitoring is :-
1. Set the DB Size to a maximum size and make unrestricted growth by
percentage or MB
2. Then set up hard disk free space monitoring alert. So when ever your hard
disk goes beyond a specific amount you will get an alert.
See the below link to configure hard disk monitoring.
http://support.microsoft.com/?kbid=299921
Thanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> Herbert
> If you open SQL Server Profiler you will find an event AutoGrowth (If I
> remember well) , so you can track the info as well as querying sysfiles
> system table (size column) (not recomended)
>
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
>> Hi,
>> We are having more than 20 databases and we are merge replicating the
>> databases also. Currently for all databases we set the Data and Log file
>> growth as Automatically by percent. The size of each database is around
> 1277
>> MB.
>> Now if we Change the Data and Log File growth to resctricted mode is
>> there
>> is any problem will cause, if not then how we will be able to monitor the
>> Data and Log file growth and when we should increase the File Size.
>> Thanks,
>> Herbert
>|||Also read the following link
http://www.databasejournal.com/features/mssql/article.php/3339681
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eSLV51dOFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As a good practice, I recommend you to monitor the MDF and LDF files daily
> once or twice manually. The best approach for automatic monitoring is :-
> 1. Set the DB Size to a maximum size and make unrestricted growth by
> percentage or MB
> 2. Then set up hard disk free space monitoring alert. So when ever your
hard
> disk goes beyond a specific amount you will get an alert.
> See the below link to configure hard disk monitoring.
> http://support.microsoft.com/?kbid=299921
> Thanks
> Hari
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> > Herbert
> > If you open SQL Server Profiler you will find an event AutoGrowth (If I
> > remember well) , so you can track the info as well as querying sysfiles
> > system table (size column) (not recomended)
> >
> >
> >
> >
> >
> > "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> > news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> >> Hi,
> >>
> >> We are having more than 20 databases and we are merge replicating
the
> >> databases also. Currently for all databases we set the Data and Log
file
> >> growth as Automatically by percent. The size of each database is
around
> > 1277
> >> MB.
> >>
> >> Now if we Change the Data and Log File growth to resctricted mode is
> >> there
> >> is any problem will cause, if not then how we will be able to monitor
the
> >> Data and Log file growth and when we should increase the File Size.
> >>
> >> Thanks,
> >> Herbert
> >
> >
>|||When your free space percentage on the disk gets below 20%, it is time to
start thinking about a new allocation.
For trend analysis, use the Backup repository in msdb. Every time a backup
is written, the size of the database files is logged.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eSLV51dOFHA.2604@.TK2MSFTNGP10.phx.gbl...
Hi,
As a good practice, I recommend you to monitor the MDF and LDF files daily
once or twice manually. The best approach for automatic monitoring is :-
1. Set the DB Size to a maximum size and make unrestricted growth by
percentage or MB
2. Then set up hard disk free space monitoring alert. So when ever your hard
disk goes beyond a specific amount you will get an alert.
See the below link to configure hard disk monitoring.
http://support.microsoft.com/?kbid=299921
Thanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> Herbert
> If you open SQL Server Profiler you will find an event AutoGrowth (If I
> remember well) , so you can track the info as well as querying sysfiles
> system table (size column) (not recomended)
>
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
>> Hi,
>> We are having more than 20 databases and we are merge replicating the
>> databases also. Currently for all databases we set the Data and Log file
>> growth as Automatically by percent. The size of each database is around
> 1277
>> MB.
>> Now if we Change the Data and Log File growth to resctricted mode is
>> there
>> is any problem will cause, if not then how we will be able to monitor the
>> Data and Log file growth and when we should increase the File Size.
>> Thanks,
>> Herbert
>

Monitor Data and Log File Growth

Hi,
We are having more than 20 databases and we are merge replicating the
databases also. Currently for all databases we set the Data and Log file
growth as Automatically by percent. The size of each database is around 127
7
MB.
Now if we Change the Data and Log File growth to resctricted mode is there
is any problem will cause, if not then how we will be able to monitor the
Data and Log file growth and when we should increase the File Size.
Thanks,
HerbertHerbert
If you open SQL Server Profiler you will find an event AutoGrowth (If I
remember well) , so you can track the info as well as querying sysfiles
system table (size column) (not recomended)
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> Hi,
> We are having more than 20 databases and we are merge replicating the
> databases also. Currently for all databases we set the Data and Log file
> growth as Automatically by percent. The size of each database is around
1277
> MB.
> Now if we Change the Data and Log File growth to resctricted mode is there
> is any problem will cause, if not then how we will be able to monitor the
> Data and Log file growth and when we should increase the File Size.
> Thanks,
> Herbert|||Hi,
As a good practice, I recommend you to monitor the MDF and LDF files daily
once or twice manually. The best approach for automatic monitoring is :-
1. Set the DB Size to a maximum size and make unrestricted growth by
percentage or MB
2. Then set up hard disk free space monitoring alert. So when ever your hard
disk goes beyond a specific amount you will get an alert.
See the below link to configure hard disk monitoring.
http://support.microsoft.com/?kbid=299921
Thanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> Herbert
> If you open SQL Server Profiler you will find an event AutoGrowth (If I
> remember well) , so you can track the info as well as querying sysfiles
> system table (size column) (not recomended)
>
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> 1277
>|||Also read the following link
http://www.databasejournal.com/feat...cle.php/3339681
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eSLV51dOFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As a good practice, I recommend you to monitor the MDF and LDF files daily
> once or twice manually. The best approach for automatic monitoring is :-
> 1. Set the DB Size to a maximum size and make unrestricted growth by
> percentage or MB
> 2. Then set up hard disk free space monitoring alert. So when ever your
hard
> disk goes beyond a specific amount you will get an alert.
> See the below link to configure hard disk monitoring.
> http://support.microsoft.com/?kbid=299921
> Thanks
> Hari
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
file[vbcol=seagreen]
around[vbcol=seagreen]
the[vbcol=seagreen]
>|||When your free space percentage on the disk gets below 20%, it is time to
start thinking about a new allocation.
For trend analysis, use the Backup repository in msdb. Every time a backup
is written, the size of the database files is logged.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eSLV51dOFHA.2604@.TK2MSFTNGP10.phx.gbl...
Hi,
As a good practice, I recommend you to monitor the MDF and LDF files daily
once or twice manually. The best approach for automatic monitoring is :-
1. Set the DB Size to a maximum size and make unrestricted growth by
percentage or MB
2. Then set up hard disk free space monitoring alert. So when ever your hard
disk goes beyond a specific amount you will get an alert.
See the below link to configure hard disk monitoring.
http://support.microsoft.com/?kbid=299921
Thanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> Herbert
> If you open SQL Server Profiler you will find an event AutoGrowth (If I
> remember well) , so you can track the info as well as querying sysfiles
> system table (size column) (not recomended)
>
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> 1277
>

Monitor Data and Log File Growth

Hi,
We are having more than 20 databases and we are merge replicating the
databases also. Currently for all databases we set the Data and Log file
growth as Automatically by percent. The size of each database is around 1277
MB.
Now if we Change the Data and Log File growth to resctricted mode is there
is any problem will cause, if not then how we will be able to monitor the
Data and Log file growth and when we should increase the File Size.
Thanks,
Herbert
Herbert
If you open SQL Server Profiler you will find an event AutoGrowth (If I
remember well) , so you can track the info as well as querying sysfiles
system table (size column) (not recomended)
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> Hi,
> We are having more than 20 databases and we are merge replicating the
> databases also. Currently for all databases we set the Data and Log file
> growth as Automatically by percent. The size of each database is around
1277
> MB.
> Now if we Change the Data and Log File growth to resctricted mode is there
> is any problem will cause, if not then how we will be able to monitor the
> Data and Log file growth and when we should increase the File Size.
> Thanks,
> Herbert
|||Hi,
As a good practice, I recommend you to monitor the MDF and LDF files daily
once or twice manually. The best approach for automatic monitoring is :-
1. Set the DB Size to a maximum size and make unrestricted growth by
percentage or MB
2. Then set up hard disk free space monitoring alert. So when ever your hard
disk goes beyond a specific amount you will get an alert.
See the below link to configure hard disk monitoring.
http://support.microsoft.com/?kbid=299921
Thanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> Herbert
> If you open SQL Server Profiler you will find an event AutoGrowth (If I
> remember well) , so you can track the info as well as querying sysfiles
> system table (size column) (not recomended)
>
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> 1277
>
|||Also read the following link
http://www.databasejournal.com/featu...le.php/3339681
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eSLV51dOFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As a good practice, I recommend you to monitor the MDF and LDF files daily
> once or twice manually. The best approach for automatic monitoring is :-
> 1. Set the DB Size to a maximum size and make unrestricted growth by
> percentage or MB
> 2. Then set up hard disk free space monitoring alert. So when ever your
hard[vbcol=seagreen]
> disk goes beyond a specific amount you will get an alert.
> See the below link to configure hard disk monitoring.
> http://support.microsoft.com/?kbid=299921
> Thanks
> Hari
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
file[vbcol=seagreen]
around[vbcol=seagreen]
the
>
|||When your free space percentage on the disk gets below 20%, it is time to
start thinking about a new allocation.
For trend analysis, use the Backup repository in msdb. Every time a backup
is written, the size of the database files is logged.
Sincerely,
Anthony Thomas

"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eSLV51dOFHA.2604@.TK2MSFTNGP10.phx.gbl...
Hi,
As a good practice, I recommend you to monitor the MDF and LDF files daily
once or twice manually. The best approach for automatic monitoring is :-
1. Set the DB Size to a maximum size and make unrestricted growth by
percentage or MB
2. Then set up hard disk free space monitoring alert. So when ever your hard
disk goes beyond a specific amount you will get an alert.
See the below link to configure hard disk monitoring.
http://support.microsoft.com/?kbid=299921
Thanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> Herbert
> If you open SQL Server Profiler you will find an event AutoGrowth (If I
> remember well) , so you can track the info as well as querying sysfiles
> system table (size column) (not recomended)
>
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> 1277
>

Monitor Data Access Speeds & Time-Outs

I've been asked to produce charts that show data access & time-out
information for an instance of SQL Server.
I've very little experience with SQL Server.
My background is MI - so only know Analysis Services part of SQL
Server.
It would seem the previous person manually went through error logs
generated & rekeyed appropriate data into Excel to produce charts.
How can I produce the following charts automatically?
1. Data Access - ave. access time per process by day
2. SQL Errors - number of by day
3. Time-outs - number by hour
Or anyway of achieving effectively the same thing without any manual
involvement.
Many thanks.
We probably need more information on what you are trying to collect but here
is a start.
Setup a SQL trace. It will need to run 24hours a day writing to a file(s).
Have it stop and write out every so often 5 mins, 4 hours (depends on system
load and requirements). Start the next trace. Read the trace into a table.
Delete old trace files.
1. Data Access - Assuming you mean how long did each query take to be
serviced. Add RPC:Completed and SQL:Batch Completeed to trace events.
Select the avg(duration) of these events over the time period you need.
2. SQL Errors - Add the specific SQL errors you are looking for in the
trace. Select the cout of them over the time period.
3. Timeouts. Not sure where you are getting timeout information now.
There's lock timeout, remote query time, or connection timeout in SQL
Agent...Need more info.
<duvinrouge@.servihoo.com> wrote in message
news:1129629233.565048.215040@.g44g2000cwa.googlegr oups.com...
> I've been asked to produce charts that show data access & time-out
> information for an instance of SQL Server.
> I've very little experience with SQL Server.
> My background is MI - so only know Analysis Services part of SQL
> Server.
> It would seem the previous person manually went through error logs
> generated & rekeyed appropriate data into Excel to produce charts.
> How can I produce the following charts automatically?
> 1. Data Access - ave. access time per process by day
> 2. SQL Errors - number of by day
> 3. Time-outs - number by hour
> Or anyway of achieving effectively the same thing without any manual
> involvement.
> Many thanks.
>

Monitor Data Access Speeds & Time-Outs

I've been asked to produce charts that show data access & time-out
information for an instance of SQL Server.
I've very little experience with SQL Server.
My background is MI - so only know Analysis Services part of SQL
Server.
It would seem the previous person manually went through error logs
generated & rekeyed appropriate data into Excel to produce charts.
How can I produce the following charts automatically?
1. Data Access - ave. access time per process by day
2. SQL Errors - number of by day
3. Time-outs - number by hour
Or anyway of achieving effectively the same thing without any manual
involvement.
Many thanks.We probably need more information on what you are trying to collect but here
is a start.
Setup a SQL trace. It will need to run 24hours a day writing to a file(s).
Have it stop and write out every so often 5 mins, 4 hours (depends on system
load and requirements). Start the next trace. Read the trace into a table.
Delete old trace files.
1. Data Access - Assuming you mean how long did each query take to be
serviced. Add RPC:Completed and SQL:Batch Completeed to trace events.
Select the avg(duration) of these events over the time period you need.
2. SQL Errors - Add the specific SQL errors you are looking for in the
trace. Select the cout of them over the time period.
3. Timeouts. Not sure where you are getting timeout information now.
There's lock timeout, remote query time, or connection timeout in SQL
Agent...Need more info.
<duvinrouge@.servihoo.com> wrote in message
news:1129629233.565048.215040@.g44g2000cwa.googlegroups.com...
> I've been asked to produce charts that show data access & time-out
> information for an instance of SQL Server.
> I've very little experience with SQL Server.
> My background is MI - so only know Analysis Services part of SQL
> Server.
> It would seem the previous person manually went through error logs
> generated & rekeyed appropriate data into Excel to produce charts.
> How can I produce the following charts automatically?
> 1. Data Access - ave. access time per process by day
> 2. SQL Errors - number of by day
> 3. Time-outs - number by hour
> Or anyway of achieving effectively the same thing without any manual
> involvement.
> Many thanks.
>

Monitor Data Access Speeds & Time-Outs

I've been asked to produce charts that show data access & time-out
information for an instance of SQL Server.
I've very little experience with SQL Server.
My background is MI - so only know Analysis Services part of SQL
Server.
It would seem the previous person manually went through error logs
generated & rekeyed appropriate data into Excel to produce charts.
How can I produce the following charts automatically?
1. Data Access - ave. access time per process by day
2. SQL Errors - number of by day
3. Time-outs - number by hour
Or anyway of achieving effectively the same thing without any manual
involvement.
Many thanks.We probably need more information on what you are trying to collect but here
is a start.
Setup a SQL trace. It will need to run 24hours a day writing to a file(s).
Have it stop and write out every so often 5 mins, 4 hours (depends on system
load and requirements). Start the next trace. Read the trace into a table.
Delete old trace files.
1. Data Access - Assuming you mean how long did each query take to be
serviced. Add RPC:Completed and SQL:Batch Completeed to trace events.
Select the avg(duration) of these events over the time period you need.
2. SQL Errors - Add the specific SQL errors you are looking for in the
trace. Select the cout of them over the time period.
3. Timeouts. Not sure where you are getting timeout information now.
There's lock timeout, remote query time, or connection timeout in SQL
Agent...Need more info.
<duvinrouge@.servihoo.com> wrote in message
news:1129629233.565048.215040@.g44g2000cwa.googlegroups.com...
> I've been asked to produce charts that show data access & time-out
> information for an instance of SQL Server.
> I've very little experience with SQL Server.
> My background is MI - so only know Analysis Services part of SQL
> Server.
> It would seem the previous person manually went through error logs
> generated & rekeyed appropriate data into Excel to produce charts.
> How can I produce the following charts automatically?
> 1. Data Access - ave. access time per process by day
> 2. SQL Errors - number of by day
> 3. Time-outs - number by hour
> Or anyway of achieving effectively the same thing without any manual
> involvement.
> Many thanks.
>

Monitor CPU Usage by Query?

We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
setting up a monitor that can identify queries that are causing CPU
spikes.
Does anyone have any suggestions about going about this? I'm at a
complete loss.The Profiler can do this. Create a new trace using the SQLProfilerStandard
trace template and add the CPU column. This will show both the query and the
CPU time, plus some other stuff. If this is a super busy production server
this can degrade performance so you might want to play with it on a test db
instance first. I typically run it from my desktop or from a remote desktop
and save the results to a local file cause if you write the result to the db
you are monitoring it too can impact your findings.
Netmon
"quixster@.gmail.com" wrote:
> We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> setting up a monitor that can identify queries that are causing CPU
> spikes.
> Does anyone have any suggestions about going about this? I'm at a
> complete loss.
>|||Thanks for the tip! Do you know of any way to use this or some other
utility to create an alert for administrators to receive when a query
has risen above a certain threshold? I can see how I can filter the
trace for CPU usage of a certain level, but I don't see how I could use
that to create an alert. Any thoughts?
On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> The Profiler can do this. Create a new trace using the SQLProfilerStandard
> trace template and add the CPU column. This will show both the query and the
> CPU time, plus some other stuff. If this is a super busy production server
> this can degrade performance so you might want to play with it on a test db
> instance first. I typically run it from my desktop or from a remote desktop
> and save the results to a local file cause if you write the result to the db
> you are monitoring it too can impact your findings.
> Netmon
>
> "quixs...@.gmail.com" wrote:
> > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > setting up a monitor that can identify queries that are causing CPU
> > spikes.
> > Does anyone have any suggestions about going about this? I'm at a
> > complete loss.- Hide quoted text -- Show quoted text -|||You can also use this query to see what's using the CPU - highest to
lowest usage:
SELECT * FROM master..sysprocesses ORDER BY cpu DESC
Then, run this to see the actual query:
DBCC INPUTBUFFER (spid) -- spid being the # in the spid column from the
above query
hth
quixster@.gmail.com wrote:
> We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> setting up a monitor that can identify queries that are causing CPU
> spikes.
> Does anyone have any suggestions about going about this? I'm at a
> complete loss.|||You can also use the windows tool PerfMon on the server and set up your
own custom traces which can viewed realtime or logged to files and
replayed.
quixster@.gmail.com wrote:
> We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> setting up a monitor that can identify queries that are causing CPU
> spikes.
> Does anyone have any suggestions about going about this? I'm at a
> complete loss.|||No the trace is not going to do that. I'v heard that NetIQ has a product
that monitors for query execution but I'm not sure of the details on how it
works.
Like the other person (tootsuite) posted you could use perfmon however it
does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
alert. So anytime the CPU is above your threshold it will alert even if it
is a short lived spike you get alerted.
I'm not sure the context within which you want to filter the trace to
generate an alert. If you have any enterprise server monitoring tools like
ProIT, MOM or Nimbus available you could just monitor the CPU via those and
have it email your admin.
Are you hoping to find the rogue/cpu intense queries in action? Or are you
desiring to know when the CPU gets hammered so an admin can log in and kill
the offending query or find out the user running it?
"quixster@.gmail.com" wrote:
> Thanks for the tip! Do you know of any way to use this or some other
> utility to create an alert for administrators to receive when a query
> has risen above a certain threshold? I can see how I can filter the
> trace for CPU usage of a certain level, but I don't see how I could use
> that to create an alert. Any thoughts?
>
> On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > The Profiler can do this. Create a new trace using the SQLProfilerStandard
> > trace template and add the CPU column. This will show both the query and the
> > CPU time, plus some other stuff. If this is a super busy production server
> > this can degrade performance so you might want to play with it on a test db
> > instance first. I typically run it from my desktop or from a remote desktop
> > and save the results to a local file cause if you write the result to the db
> > you are monitoring it too can impact your findings.
> >
> > Netmon
> >
> >
> >
> > "quixs...@.gmail.com" wrote:
> > > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > > setting up a monitor that can identify queries that are causing CPU
> > > spikes.
> >
> > > Does anyone have any suggestions about going about this? I'm at a
> > > complete loss.- Hide quoted text -- Show quoted text -
>|||Perfmon is just a general monitoring tool... you cannot see individual
queries.
Usually it works like this:
1. end users complain
2. look at overall picture using tool like perfmon or Quest Performance
Analysis, or even profiler (I find profiler to be flaky/unpredictable
at times, at least in 2000 it was terrible)
3. isolate queries using Quest based on various factors
4. most of our performance problems in the past have stemmed from
inadequate memory, which then in turns affects cpu, disk i/o, which
leads to blocking locks, etc - fortunately this has been resolved with
the purchase of higher performance servers + memory
so it's not enough to just isolate cpu performance, as many factors are
usually involved
so your cpu might be hammered, but the real underlying problem could be
lack of memory, etc but you wouldn't know that unless you can look at
all the various possible factors
Netmon wrote:
> No the trace is not going to do that. I'v heard that NetIQ has a product
> that monitors for query execution but I'm not sure of the details on how it
> works.
> Like the other person (tootsuite) posted you could use perfmon however it
> does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> alert. So anytime the CPU is above your threshold it will alert even if it
> is a short lived spike you get alerted.
> I'm not sure the context within which you want to filter the trace to
> generate an alert. If you have any enterprise server monitoring tools like
> ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> have it email your admin.
> Are you hoping to find the rogue/cpu intense queries in action? Or are you
> desiring to know when the CPU gets hammered so an admin can log in and kill
> the offending query or find out the user running it?
> "quixster@.gmail.com" wrote:
> >
> > Thanks for the tip! Do you know of any way to use this or some other
> > utility to create an alert for administrators to receive when a query
> > has risen above a certain threshold? I can see how I can filter the
> > trace for CPU usage of a certain level, but I don't see how I could use
> > that to create an alert. Any thoughts?
> >
> >
> > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > The Profiler can do this. Create a new trace using the SQLProfilerStandard
> > > trace template and add the CPU column. This will show both the query and the
> > > CPU time, plus some other stuff. If this is a super busy production server
> > > this can degrade performance so you might want to play with it on a test db
> > > instance first. I typically run it from my desktop or from a remote desktop
> > > and save the results to a local file cause if you write the result to the db
> > > you are monitoring it too can impact your findings.
> > >
> > > Netmon
> > >
> > >
> > >
> > > "quixs...@.gmail.com" wrote:
> > > > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > > > setting up a monitor that can identify queries that are causing CPU
> > > > spikes.
> > >
> > > > Does anyone have any suggestions about going about this? I'm at a
> > > > complete loss.- Hide quoted text -- Show quoted text -
> >
> >|||The first; trying to find rogue/cpu intense queries in action so we can
identify not only the query causing the problem, but perhaps what else
is occuring simultaneously that could be contributing to the high CPU
situation. We have MOM, and it can alert us to CPU incidents, but I
was hoping to find something that could single out the individual
queries that are running to create the incident.
On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> No the trace is not going to do that. I'v heard that NetIQ has a product
> that monitors for query execution but I'm not sure of the details on how it
> works.
> Like the other person (tootsuite) posted you could use perfmon however it
> does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> alert. So anytime the CPU is above your threshold it will alert even if it
> is a short lived spike you get alerted.
> I'm not sure the context within which you want to filter the trace to
> generate an alert. If you have any enterprise server monitoring tools like
> ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> have it email your admin.
> Are you hoping to find the rogue/cpu intense queries in action? Or are you
> desiring to know when the CPU gets hammered so an admin can log in and kill
> the offending query or find out the user running it?
>
> "quixs...@.gmail.com" wrote:
> > Thanks for the tip! Do you know of any way to use this or some other
> > utility to create an alert for administrators to receive when a query
> > has risen above a certain threshold? I can see how I can filter the
> > trace for CPU usage of a certain level, but I don't see how I could use
> > that to create an alert. Any thoughts?
> > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > The Profiler can do this. Create a new trace using the SQLProfilerStandard
> > > trace template and add the CPU column. This will show both the query and the
> > > CPU time, plus some other stuff. If this is a super busy production server
> > > this can degrade performance so you might want to play with it on a test db
> > > instance first. I typically run it from my desktop or from a remote desktop
> > > and save the results to a local file cause if you write the result to the db
> > > you are monitoring it too can impact your findings.
> > > Netmon
> > > "quixs...@.gmail.com" wrote:
> > > > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > > > setting up a monitor that can identify queries that are causing CPU
> > > > spikes.
> > > > Does anyone have any suggestions about going about this? I'm at a
> > > > complete loss.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||Yes, you can do this easily using the query I provided in an earlier
post.
SELECT * FROM master..sysprocesses ORDER BY cpu DESC
DBCC INPUTBUFFER (spid) -- shows query
it can't get much clearer than this - it will list processes by CPU
usage, highest to lowest, at the given point in time you run the stmt
Or you can invest in some type of monitoring tool, like Quest
(performance analysis) or some other tool that you like if you need to
examine various performance indicators - this is what I would recommend
for a long term solution
hth
quixster@.gmail.com wrote:
> The first; trying to find rogue/cpu intense queries in action so we can
> identify not only the query causing the problem, but perhaps what else
> is occuring simultaneously that could be contributing to the high CPU
> situation. We have MOM, and it can alert us to CPU incidents, but I
> was hoping to find something that could single out the individual
> queries that are running to create the incident.
>
> On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > No the trace is not going to do that. I'v heard that NetIQ has a product
> > that monitors for query execution but I'm not sure of the details on how it
> > works.
> >
> > Like the other person (tootsuite) posted you could use perfmon however it
> > does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> > alert. So anytime the CPU is above your threshold it will alert even if it
> > is a short lived spike you get alerted.
> >
> > I'm not sure the context within which you want to filter the trace to
> > generate an alert. If you have any enterprise server monitoring tools like
> > ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> > have it email your admin.
> >
> > Are you hoping to find the rogue/cpu intense queries in action? Or are you
> > desiring to know when the CPU gets hammered so an admin can log in and kill
> > the offending query or find out the user running it?
> >
> >
> >
> > "quixs...@.gmail.com" wrote:
> >
> > > Thanks for the tip! Do you know of any way to use this or some other
> > > utility to create an alert for administrators to receive when a query
> > > has risen above a certain threshold? I can see how I can filter the
> > > trace for CPU usage of a certain level, but I don't see how I could use
> > > that to create an alert. Any thoughts?
> >
> > > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > The Profiler can do this. Create a new trace using the SQLProfilerStandard
> > > > trace template and add the CPU column. This will show both the query and the
> > > > CPU time, plus some other stuff. If this is a super busy production server
> > > > this can degrade performance so you might want to play with it on a test db
> > > > instance first. I typically run it from my desktop or from a remote desktop
> > > > and save the results to a local file cause if you write the result to the db
> > > > you are monitoring it too can impact your findings.
> >
> > > > Netmon
> >
> > > > "quixs...@.gmail.com" wrote:
> > > > > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > > > > setting up a monitor that can identify queries that are causing CPU
> > > > > spikes.
> >
> > > > > Does anyone have any suggestions about going about this? I'm at a
> > > > > complete loss.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||Thanks for all the help! I'm taking the tips mentioned on this post
and also talking to a Quest rep about "Spotlight on SQL Server
Enterprise" http://www.quest.com/spotlight_on_sql_server_enterprise/
This has been immensely helpful!
On Nov 1, 11:20 am, tootsu...@.gmail.com wrote:
> Yes, you can do this easily using the query I provided in an earlier
> post.
> SELECT * FROM master..sysprocesses ORDER BY cpu DESC
> DBCC INPUTBUFFER (spid) -- shows query
> it can't get much clearer than this - it will list processes by CPU
> usage, highest to lowest, at the given point in time you run the stmt
> Or you can invest in some type of monitoring tool, like Quest
> (performance analysis) or some other tool that you like if you need to
> examine various performance indicators - this is what I would recommend
> for a long term solution
> hth
>
> quixs...@.gmail.com wrote:
> > The first; trying to find rogue/cpu intense queries in action so we can
> > identify not only the query causing the problem, but perhaps what else
> > is occuring simultaneously that could be contributing to the high CPU
> > situation. We have MOM, and it can alert us to CPU incidents, but I
> > was hoping to find something that could single out the individual
> > queries that are running to create the incident.
> > On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > No the trace is not going to do that. I'v heard that NetIQ has a product
> > > that monitors for query execution but I'm not sure of the details on how it
> > > works.
> > > Like the other person (tootsuite) posted you could use perfmon however it
> > > does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> > > alert. So anytime the CPU is above your threshold it will alert even if it
> > > is a short lived spike you get alerted.
> > > I'm not sure the context within which you want to filter the trace to
> > > generate an alert. If you have any enterprise server monitoring tools like
> > > ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> > > have it email your admin.
> > > Are you hoping to find the rogue/cpu intense queries in action? Or are you
> > > desiring to know when the CPU gets hammered so an admin can log in and kill
> > > the offending query or find out the user running it?
> > > "quixs...@.gmail.com" wrote:
> > > > Thanks for the tip! Do you know of any way to use this or some other
> > > > utility to create an alert for administrators to receive when a query
> > > > has risen above a certain threshold? I can see how I can filter the
> > > > trace for CPU usage of a certain level, but I don't see how I could use
> > > > that to create an alert. Any thoughts?
> > > > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > > The Profiler can do this. Create a new trace using the SQLProfilerStandard
> > > > > trace template and add the CPU column. This will show both the query and the
> > > > > CPU time, plus some other stuff. If this is a super busy production server
> > > > > this can degrade performance so you might want to play with it on a test db
> > > > > instance first. I typically run it from my desktop or from a remote desktop
> > > > > and save the results to a local file cause if you write the result to the db
> > > > > you are monitoring it too can impact your findings.
> > > > > Netmon
> > > > > "quixs...@.gmail.com" wrote:
> > > > > > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > > > > > setting up a monitor that can identify queries that are causing CPU
> > > > > > spikes.
> > > > > > Does anyone have any suggestions about going about this? I'm at a
> > > > > > complete loss.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||Another product that you might want to consider is Idera Diagnostic Manager
http://www.idera.com/Products/SQLdm/Default.aspx
Lucas
"quixster@.gmail.com" wrote:
> Thanks for all the help! I'm taking the tips mentioned on this post
> and also talking to a Quest rep about "Spotlight on SQL Server
> Enterprise" http://www.quest.com/spotlight_on_sql_server_enterprise/
> This has been immensely helpful!
> On Nov 1, 11:20 am, tootsu...@.gmail.com wrote:
> > Yes, you can do this easily using the query I provided in an earlier
> > post.
> >
> > SELECT * FROM master..sysprocesses ORDER BY cpu DESC
> > DBCC INPUTBUFFER (spid) -- shows query
> >
> > it can't get much clearer than this - it will list processes by CPU
> > usage, highest to lowest, at the given point in time you run the stmt
> >
> > Or you can invest in some type of monitoring tool, like Quest
> > (performance analysis) or some other tool that you like if you need to
> > examine various performance indicators - this is what I would recommend
> > for a long term solution
> >
> > hth
> >
> >
> >
> > quixs...@.gmail.com wrote:
> > > The first; trying to find rogue/cpu intense queries in action so we can
> > > identify not only the query causing the problem, but perhaps what else
> > > is occuring simultaneously that could be contributing to the high CPU
> > > situation. We have MOM, and it can alert us to CPU incidents, but I
> > > was hoping to find something that could single out the individual
> > > queries that are running to create the incident.
> >
> > > On Oct 31, 4:30 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > No the trace is not going to do that. I'v heard that NetIQ has a product
> > > > that monitors for query execution but I'm not sure of the details on how it
> > > > works.
> >
> > > > Like the other person (tootsuite) posted you could use perfmon however it
> > > > does not take sustained thresholds, that is if CPU > 90% over 60 seconds then
> > > > alert. So anytime the CPU is above your threshold it will alert even if it
> > > > is a short lived spike you get alerted.
> >
> > > > I'm not sure the context within which you want to filter the trace to
> > > > generate an alert. If you have any enterprise server monitoring tools like
> > > > ProIT, MOM or Nimbus available you could just monitor the CPU via those and
> > > > have it email your admin.
> >
> > > > Are you hoping to find the rogue/cpu intense queries in action? Or are you
> > > > desiring to know when the CPU gets hammered so an admin can log in and kill
> > > > the offending query or find out the user running it?
> >
> > > > "quixs...@.gmail.com" wrote:
> >
> > > > > Thanks for the tip! Do you know of any way to use this or some other
> > > > > utility to create an alert for administrators to receive when a query
> > > > > has risen above a certain threshold? I can see how I can filter the
> > > > > trace for CPU usage of a certain level, but I don't see how I could use
> > > > > that to create an alert. Any thoughts?
> >
> > > > > On Oct 31, 2:21 pm, Netmon <Net...@.discussions.microsoft.com> wrote:
> > > > > > The Profiler can do this. Create a new trace using the SQLProfilerStandard
> > > > > > trace template and add the CPU column. This will show both the query and the
> > > > > > CPU time, plus some other stuff. If this is a super busy production server
> > > > > > this can degrade performance so you might want to play with it on a test db
> > > > > > instance first. I typically run it from my desktop or from a remote desktop
> > > > > > and save the results to a local file cause if you write the result to the db
> > > > > > you are monitoring it too can impact your findings.
> >
> > > > > > Netmon
> >
> > > > > > "quixs...@.gmail.com" wrote:
> > > > > > > We are currently running MS SQL 8.0 on Windows 2003. I've been tasked
> > > > > > > setting up a monitor that can identify queries that are causing CPU
> > > > > > > spikes.
> >
> > > > > > > Does anyone have any suggestions about going about this? I'm at a
> > > > > > > complete loss.- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -
>