Monitoring database space usage

I'm interesting in monitoring database space usage in SQL Server 2005.
Ideally, once a day I'd like a job to run that looks for databases that are
within 20% of capacity. If one or more exists, I get an email. I don't
need/want it to monitor constantly as our business processes do not require
that. I do not want to have to manually monitor. For other business
reasons, our databases will be set to fixed size with autogrowth disabled,
hence our interest in monitoring.
What do you recommend?
Thanks,
Mark
Mark,
Take a look at the view sys.database_files, it should be fairly easy
to create a SP off of this data and then as long as you have your
database mail setup, you can e-mail yourself.
Here is an example to run:
use DBNAME
go
select
physical_name,
size * 8 AS [Current Size in KB],
max_size * 8 [Maximum Size in KB]
from sys.database_files
Note that size and max_size are the number of PAGES the files have,
and pages in SQL Server are 8K.
Hope this helps,
-Sean
On Mar 28, 10:58Xam, "Mark" <m...@.idonotlikespam.com> wrote:
> I'm interesting in monitoring database space usage in SQL Server 2005.
> Ideally, once a day I'd like a job to run that looks for databases that are
> within 20% of capacity. XIf one or more exists, I get an email. XI don't
> need/want it to monitor constantly as our business processes do not require
> that. XI do not want to have to manually monitor. XFor other business
> reasons, our databases will be set to fixed size with autogrowth disabled,
> hence our interest in monitoring.
> What do you recommend?
> Thanks,
> Mark
|||On Mar 28, 8:31Xam, Sean <ColdFusion...@.gmail.com> wrote:
> Mark,
> Take a look at the view sys.database_files, it should be fairly easy
> to create a SP off of this data and then as long as you have your
> database mail setup, you can e-mail yourself.
> Here is an example to run:
> use DBNAME
> go
> select
> physical_name,
> size * 8 AS [Current Size in KB],
> max_size * 8 [Maximum Size in KB]
> from sys.database_files
> Note that size and max_size are the number of PAGES the files have,
> and pages in SQL Server are 8K.
> Hope this helps,
> -Sean
> On Mar 28, 10:58Xam, "Mark" <m...@.idonotlikespam.com> wrote:
>
>
>
> - Show quoted text -
Mark, I don't think this is going to work. This will only tell you
what the size of the database file is - not how much space is actually
being used within that file.
There are a couple of options:
1) Look up FILEPROPERTY - this function has a property for returning
SpaceUsed.
2) Review the stored procedure sp_spaceused - create your own version
using the same logic
3) Look up DataSpaceUsage, IndexSpaceUsage and SpaceAvailable in
SMO. You can either create a program, or use Powershell to create a
script to capture the data.
I prefer the script method myself and use Powershell to pull this data
from all of our SQL Servers.
HTH,
Jeff

Monitoring database space usage

I'm interesting in monitoring database space usage in SQL Server 2005.
Ideally, once a day I'd like a job to run that looks for databases that are
within 20% of capacity. If one or more exists, I get an email. I don't
need/want it to monitor constantly as our business processes do not require
that. I do not want to have to manually monitor. For other business
reasons, our databases will be set to fixed size with autogrowth disabled,
hence our interest in monitoring.
What do you recommend?
Thanks,
MarkMark,
Take a look at the view sys.database_files, it should be fairly easy
to create a SP off of this data and then as long as you have your
database mail setup, you can e-mail yourself.
Here is an example to run:
use DBNAME
go
select
physical_name,
size * 8 AS [Current Size in KB],
max_size * 8 [Maximum Size in KB]
from sys.database_files
Note that size and max_size are the number of PAGES the files have,
and pages in SQL Server are 8K.
Hope this helps,
-Sean
On Mar 28, 10:58=A0am, "Mark" <m...@.idonotlikespam.com> wrote:
> I'm interesting in monitoring database space usage in SQL Server 2005.
> Ideally, once a day I'd like a job to run that looks for databases that ar=e
> within 20% of capacity. =A0If one or more exists, I get an email. =A0I don='t
> need/want it to monitor constantly as our business processes do not requir=e
> that. =A0I do not want to have to manually monitor. =A0For other business
> reasons, our databases will be set to fixed size with autogrowth disabled,=
> hence our interest in monitoring.
> What do you recommend?
> Thanks,
> Mark|||On Mar 28, 8:31=A0am, Sean <ColdFusion...@.gmail.com> wrote:
> Mark,
> Take a look at the view sys.database_files, it should be fairly easy
> to create a SP off of this data and then as long as you have your
> database mail setup, you can e-mail yourself.
> Here is an example to run:
> use DBNAME
> go
> select
> physical_name,
> size * 8 AS [Current Size in KB],
> max_size * 8 [Maximum Size in KB]
> from sys.database_files
> Note that size and max_size are the number of PAGES the files have,
> and pages in SQL Server are 8K.
> Hope this helps,
> -Sean
> On Mar 28, 10:58=A0am, "Mark" <m...@.idonotlikespam.com> wrote:
>
> > I'm interesting in monitoring database space usage in SQL Server 2005.
> > Ideally, once a day I'd like a job to run that looks for databases that =are
> > within 20% of capacity. =A0If one or more exists, I get an email. =A0I d=on't
> > need/want it to monitor constantly as our business processes do not requ=ire
> > that. =A0I do not want to have to manually monitor. =A0For other busines=s
> > reasons, our databases will be set to fixed size with autogrowth disable=d,
> > hence our interest in monitoring.
> > What do you recommend?
> > Thanks,
> > Mark- Hide quoted text -
> - Show quoted text -
Mark, I don't think this is going to work. This will only tell you
what the size of the database file is - not how much space is actually
being used within that file.
There are a couple of options:
1) Look up FILEPROPERTY - this function has a property for returning
SpaceUsed.
2) Review the stored procedure sp_spaceused - create your own version
using the same logic
3) Look up DataSpaceUsage, IndexSpaceUsage and SpaceAvailable in
SMO. You can either create a program, or use Powershell to create a
script to capture the data.
I prefer the script method myself and use Powershell to pull this data
from all of our SQL Servers.
HTH,
Jeff

monitoring a job

Hello Group,
Can I monitor a job say JOB A using another job say JOB B, and restart JOB A if it stops or fails?
Can anyone help or suggest me how to accomplish this task.
Thanks in advance
qAnandIn the advanced section of the job steps you can set many options , one of which is the no of retries in case the job fails. You can use this for the same,

Monitor SQL Agent job in ASP.Net

Hi all,

I have a web page which calls a SQL Agent job to initiate an SSIS package. Is there any way i can monitor the job and detect once it has completed successfully? At present all i can seem to get is the return status of whether the job has started or not.

Any help would be most appreciated,

Many thanks in advance.

Grant

You could query the sysjobhistory table. When steps complete, rows get written to this table. You could probably access this via SMO as well, I haven't checked. There is an instance_id which will allow you to track your specific instance, assuming you get that as a token when starting. It may depend on how you are doing this, T-SQL or SMO, I haven't looked vbery far into this, but start with sysjobhistory.

Monitor SQL agent

Hi,
we know that SQL agent controls the job's schedule & running, but if SQL
agent stop working, all jobs will not be able to be executed, is there any
tool/method to monitor SQL agent if it stopped, hung or wrong from another
SQL server, so the other SQL server will send operator notification?
Thanks in advance.
JackNot directly. But you can code it yourself. Use a combination of xp_cmdshell and netsvc (I believe
that the utility is called with which you can check and start/stop services on another machine). You
can pull the result into a table and then check against that table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <chongh.hc@.gmail.com> wrote in message news:eHqYn73PFHA.3076@.tk2msftngp13.phx.gbl...
> Hi,
> we know that SQL agent controls the job's schedule & running, but if SQL agent stop working, all
> jobs will not be able to be executed, is there any tool/method to monitor SQL agent if it stopped,
> hung or wrong from another SQL server, so the other SQL server will send operator notification?
> Thanks in advance.
> Jack
>

Monitor SQL agent

Hi,
we know that SQL agent controls the job's schedule & running, but if SQL
agent stop working, all jobs will not be able to be executed, is there any
tool/method to monitor SQL agent if it stopped, hung or wrong from another
SQL server, so the other SQL server will send operator notification?
Thanks in advance.
JackNot directly. But you can code it yourself. Use a combination of xp_cmdshell
and netsvc (I believe
that the utility is called with which you can check and start/stop services
on another machine). You
can pull the result into a table and then check against that table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <chongh.hc@.gmail.com> wrote in message news:eHqYn73PFHA.3076@.tk2msftngp13.phx.gbl...[
vbcol=seagreen]
> Hi,
> we know that SQL agent controls the job's schedule & running, but if SQL a
gent stop working, all
> jobs will not be able to be executed, is there any tool/method to monitor
SQL agent if it stopped,
> hung or wrong from another SQL server, so the other SQL server will send o
perator notification?
> Thanks in advance.
> Jack
>[/vbcol]

monitor backup jobs on MSDE

We have several backup jobs on MSDE.
How do I monitor these jobs and get notified when a job fails. What are the
different notification possibilites with MSDE./*
Was not able to have the normal way (using SQL Agent notification system) to
work with MSDE but it works great on SQL Server. I suggest you switch to
Windows scheduler and batch driven maintenance tasks. SQLExpress will not
come with SQL Agent service. If you want to have schedules you will have to
use Windows SCHTASKS or AT command.
Create a script with your backup command, then a batch file to execute it
using OSQL or SQLCMD, a net send command can be embeded into the batch file.
Create a Windows task to execute the batch file.
Example of a batch file that would be called by the Winmdows Scheduler
OSQL -b -h-1 -s~ -w8000 -E -SCHC-6X9VQ31-XP -dmaster
-i"YourScriptFileNameAndLocation" -o"ALogFileNameAndLocationToStoreErrors"
if errorlevel == 1 goto errhand
goto end
:errhand
net send chc-6x9vq31-xp "The database back up failed ..."
:end
*/
USE msdb
-- Create a message to store in the sysmessages table
exec sp_addmessage @.msgnum = 55001 ,
@.severity = 1 ,
@.msgtext = 'Error 55001 has occurred. The database back up failed ...'
-- Because the message must be logged for the net send to fire
exec sp_altermessage 55001, 'WITH_LOG', 'true'
-- define an operator and how it is going to get notified
exec sp_add_operator @.name = 'Your Name'
, @.enabled = 1
, @.netsend_address = 'chc-6x9vq31-xp'
-- create and alert for the message.
EXEC sp_add_alert @.name = 'Test Alert', @.message_id = 55001, @.enabled = 1
-- define the notofocation procedure
-- in facts it joins the operator to an alert and how to join it
exec sp_add_notification @.alert_name = 'Test Alert' ,
@.operator_name = 'Your Name' ,
@.notification_method = 4
-- this will do the netsend
raiserror(55001,1,1)
"inquisite" wrote:

> We have several backup jobs on MSDE.
> How do I monitor these jobs and get notified when a job fails. What are th
e
> different notification possibilites with MSDE.
>

monitor backup jobs on MSDE

We have several backup jobs on MSDE.
How do I monitor these jobs and get notified when a job fails. What are the
different notification possibilites with MSDE./*
Was not able to have the normal way (using SQL Agent notification system) to
work with MSDE but it works great on SQL Server. I suggest you switch to
Windows scheduler and batch driven maintenance tasks. SQLExpress will not
come with SQL Agent service. If you want to have schedules you will have to
use Windows SCHTASKS or AT command.
Create a script with your backup command, then a batch file to execute it
using OSQL or SQLCMD, a net send command can be embeded into the batch file.
Create a Windows task to execute the batch file.
Example of a batch file that would be called by the Winmdows Scheduler
OSQL -b -h-1 -s~ -w8000 -E -SCHC-6X9VQ31-XP -dmaster
-i"YourScriptFileNameAndLocation" -o"ALogFileNameAndLocationToStoreErrors"
if errorlevel == 1 goto errhand
goto end
:errhand
net send chc-6x9vq31-xp "The database back up failed ..."
:end
*/
USE msdb
-- Create a message to store in the sysmessages table
exec sp_addmessage @.msgnum = 55001 ,
@.severity = 1 ,
@.msgtext = 'Error 55001 has occurred. The database back up failed ...'
-- Because the message must be logged for the net send to fire
exec sp_altermessage 55001, 'WITH_LOG', 'true'
-- define an operator and how it is going to get notified
exec sp_add_operator @.name = 'Your Name'
, @.enabled = 1
, @.netsend_address = 'chc-6x9vq31-xp'
-- create and alert for the message.
EXEC sp_add_alert @.name = 'Test Alert', @.message_id = 55001, @.enabled = 1
-- define the notofocation procedure
-- in facts it joins the operator to an alert and how to join it
exec sp_add_notification @.alert_name = 'Test Alert' ,
@.operator_name = 'Your Name' ,
@.notification_method = 4
-- this will do the netsend
raiserror(55001,1,1)
"inquisite" wrote:
> We have several backup jobs on MSDE.
> How do I monitor these jobs and get notified when a job fails. What are the
> different notification possibilites with MSDE.
>

monitor backup jobs on MSDE

We have several backup jobs on MSDE.
How do I monitor these jobs and get notified when a job fails. What are the
different notification possibilites with MSDE.
/*
Was not able to have the normal way (using SQL Agent notification system) to
work with MSDE but it works great on SQL Server. I suggest you switch to
Windows scheduler and batch driven maintenance tasks. SQLExpress will not
come with SQL Agent service. If you want to have schedules you will have to
use Windows SCHTASKS or AT command.
Create a script with your backup command, then a batch file to execute it
using OSQL or SQLCMD, a net send command can be embeded into the batch file.
Create a Windows task to execute the batch file.
Example of a batch file that would be called by the Winmdows Scheduler
OSQL -b -h-1 -s~ -w8000 -E -SCHC-6X9VQ31-XP -dmaster
-i"YourScriptFileNameAndLocation" -o"ALogFileNameAndLocationToStoreErrors"
if errorlevel == 1 goto errhand
goto end
:errhand
net send chc-6x9vq31-xp "The database back up failed ..."
:end
*/
USE msdb
-- Create a message to store in the sysmessages table
exec sp_addmessage @.msgnum = 55001 ,
@.severity = 1 ,
@.msgtext = 'Error 55001 has occurred. The database back up failed ...'
-- Because the message must be logged for the net send to fire
exec sp_altermessage 55001, 'WITH_LOG', 'true'
-- define an operator and how it is going to get notified
exec sp_add_operator @.name = 'Your Name'
, @.enabled = 1
, @.netsend_address = 'chc-6x9vq31-xp'
-- create and alert for the message.
EXEC sp_add_alert @.name = 'Test Alert', @.message_id = 55001, @.enabled = 1
-- define the notofocation procedure
-- in facts it joins the operator to an alert and how to join it
exec sp_add_notification @.alert_name = 'Test Alert' ,
@.operator_name = 'Your Name' ,
@.notification_method = 4
-- this will do the netsend
raiserror(55001,1,1)
"inquisite" wrote:

> We have several backup jobs on MSDE.
> How do I monitor these jobs and get notified when a job fails. What are the
> different notification possibilites with MSDE.
>

monitor backup jobs on MSDE

We have several backup jobs on MSDE.
How do I monitor these jobs and get notified when a job fails. What are the
different notification possibilites with MSDE.
inquisite wrote:
> We have several backup jobs on MSDE.
> How do I monitor these jobs and get notified when a job fails. What
> are the different notification possibilites with MSDE.
please do not multipost... if you really need it, do Xpost instead...
answered in .msde
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

monitor backup jobs

We have several backup jobs on MSDE.
How do I monitor these jobs and get notified when a job fails. What are the
different notification possibilites with MSDE.
hi,
inquisite wrote:
> We have several backup jobs on MSDE.
> How do I monitor these jobs and get notified when a job fails. What
> are the different notification possibilites with MSDE.
MSDE does not support SQLMail, so you have to resort on NetSend ... but...
:D
at http://sqldev.net/xp/xpsmtp.htm you can find a well known extended stored
procedure that is able to send mails via SMPT and you can take advantage of
that...
have a look at http://www.karaszi.com/sqlserver/info_no_mapi.asp , SQL
Server MVP Tibor Karaszi web site, for some info about how to set such a
process, in the section "Job notifications from SQL Server Agent"
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Monitor Autogrow

Is there a way to setup a job to monitor when an autogrow occurs?
AF
SQL Server Profiler?
"AF" <af.at.work@.gmail.com> wrote in message
news:1168521633.974360.119620@.i39g2000hsf.googlegr oups.com...
> Is there a way to setup a job to monitor when an autogrow occurs?
>
|||If you are using SQL Server 2005, you can capture this with event
notifications, or by checking the background trace files. Here is an
example of the latter:
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/01/11/reviewing-autogrow-events-from-the-default-trace.aspx
Otherwise, you should start by telling us version / edition, because the
solution will depend on that.
A
"AF" <af.at.work@.gmail.com> wrote in message
news:1168521633.974360.119620@.i39g2000hsf.googlegr oups.com...
> Is there a way to setup a job to monitor when an autogrow occurs?
>
|||Aaron
Can you provide me what DDL event should I use to capture autogrow file with
Event Notification ?
Thanks
CREATE EVENT NOTIFICATION log_ddl1
ON SERVER --or database
FOR WhatITypeHere
TO SERVICE '//Adventure-Works.com/ArchiveService', 'current database' ;
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ucgZ2fYNHHA.4848@.TK2MSFTNGP04.phx.gbl...
> If you are using SQL Server 2005, you can capture this with event
> notifications, or by checking the background trace files. Here is an
> example of the latter:
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/01/11/reviewing-autogrow-events-from-the-default-trace.aspx
> Otherwise, you should start by telling us version / edition, because the
> solution will depend on that.
> A
>
>
> "AF" <af.at.work@.gmail.com> wrote in message
> news:1168521633.974360.119620@.i39g2000hsf.googlegr oups.com...
>
|||Uri Dimant wrote:[vbcol=seagreen]
> AF
> SQL Server Profiler?
>
>
> "AF" <af.at.work@.gmail.com> wrote in message
> news:1168521633.974360.119620@.i39g2000hsf.googlegr oups.com...
Sorry, I meant on a schedule to alert via email ...
|||Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> If you are using SQL Server 2005, you can capture this with event
> notifications, or by checking the background trace files. Here is an
> example of the latter:
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/01/11/reviewing-autogrow-events-from-the-default-trace.aspx
> Otherwise, you should start by telling us version / edition, because the
> solution will depend on that.
> A
>
>
> "AF" <af.at.work@.gmail.com> wrote in message
> news:1168521633.974360.119620@.i39g2000hsf.googlegr oups.com...
SQL 2000 ENT / WIN2K3 ENT

Monitor Autogrow

Is there a way to setup a job to monitor when an autogrow occurs?AF
SQL Server Profiler?
"AF" <af.at.work@.gmail.com> wrote in message
news:1168521633.974360.119620@.i39g2000hsf.googlegroups.com...
> Is there a way to setup a job to monitor when an autogrow occurs?
>|||If you are using SQL Server 2005, you can capture this with event
notifications, or by checking the background trace files. Here is an
example of the latter:
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/01/11/reviewing-autogrow-events-from-the-default-trace.aspx
Otherwise, you should start by telling us version / edition, because the
solution will depend on that.
A
"AF" <af.at.work@.gmail.com> wrote in message
news:1168521633.974360.119620@.i39g2000hsf.googlegroups.com...
> Is there a way to setup a job to monitor when an autogrow occurs?
>|||Aaron
Can you provide me what DDL event should I use to capture autogrow file with
Event Notification ?
Thanks
CREATE EVENT NOTIFICATION log_ddl1
ON SERVER --or database
FOR WhatITypeHere
TO SERVICE '//Adventure-Works.com/ArchiveService', 'current database' ;
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ucgZ2fYNHHA.4848@.TK2MSFTNGP04.phx.gbl...
> If you are using SQL Server 2005, you can capture this with event
> notifications, or by checking the background trace files. Here is an
> example of the latter:
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/01/11/reviewing-autogrow-events-from-the-default-trace.aspx
> Otherwise, you should start by telling us version / edition, because the
> solution will depend on that.
> A
>
>
> "AF" <af.at.work@.gmail.com> wrote in message
> news:1168521633.974360.119620@.i39g2000hsf.googlegroups.com...
>> Is there a way to setup a job to monitor when an autogrow occurs?
>|||> Can you provide me what DDL event should I use to capture autogrow file
> with Event Notification ?
Well, it's not a DDL event, it's a trace event (DATA_FILE_AUTO_GROW or
LOG_FILE_AUTO_GROW).
See:
http://msdn2.microsoft.com/en-us/library/ms190655.aspx|||Uri Dimant wrote:
> AF
> SQL Server Profiler?
>
>
> "AF" <af.at.work@.gmail.com> wrote in message
> news:1168521633.974360.119620@.i39g2000hsf.googlegroups.com...
> > Is there a way to setup a job to monitor when an autogrow occurs?
> >
Sorry, I meant on a schedule to alert via email ...|||Aaron Bertrand [SQL Server MVP] wrote:
> If you are using SQL Server 2005, you can capture this with event
> notifications, or by checking the background trace files. Here is an
> example of the latter:
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/01/11/reviewing-autogrow-events-from-the-default-trace.aspx
> Otherwise, you should start by telling us version / edition, because the
> solution will depend on that.
> A
>
>
> "AF" <af.at.work@.gmail.com> wrote in message
> news:1168521633.974360.119620@.i39g2000hsf.googlegroups.com...
> > Is there a way to setup a job to monitor when an autogrow occurs?
> >
SQL 2000 ENT / WIN2K3 ENT

Monitor Autogrow

Is there a way to setup a job to monitor when an autogrow occurs?AF
SQL Server Profiler?
"AF" <af.at.work@.gmail.com> wrote in message
news:1168521633.974360.119620@.i39g2000hsf.googlegroups.com...
> Is there a way to setup a job to monitor when an autogrow occurs?
>|||If you are using SQL Server 2005, you can capture this with event
notifications, or by checking the background trace files. Here is an
example of the latter:
http://sqlblog.com/blogs/aaron_bert...ault-trace.aspx
Otherwise, you should start by telling us version / edition, because the
solution will depend on that.
A
"AF" <af.at.work@.gmail.com> wrote in message
news:1168521633.974360.119620@.i39g2000hsf.googlegroups.com...
> Is there a way to setup a job to monitor when an autogrow occurs?
>|||Aaron
Can you provide me what DDL event should I use to capture autogrow file with
Event Notification ?
Thanks
CREATE EVENT NOTIFICATION log_ddl1
ON SERVER --or database
FOR WhatITypeHere
TO SERVICE '//Adventure-Works.com/ArchiveService', 'current database' ;
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:ucgZ2fYNHHA.4848@.TK2MSFTNGP04.phx.gbl...
> If you are using SQL Server 2005, you can capture this with event
> notifications, or by checking the background trace files. Here is an
> example of the latter:
> http://sqlblog.com/blogs/aaron_bert...ault-trace.aspx
> Otherwise, you should start by telling us version / edition, because the
> solution will depend on that.
> A
>
>
> "AF" <af.at.work@.gmail.com> wrote in message
> news:1168521633.974360.119620@.i39g2000hsf.googlegroups.com...
>|||> Can you provide me what DDL event should I use to capture autogrow file
> with Event Notification ?
Well, it's not a DDL event, it's a trace event (DATA_FILE_AUTO_GROW or
LOG_FILE_AUTO_GROW).
See:
http://msdn2.microsoft.com/en-us/library/ms190655.aspx|||Uri Dimant wrote:[vbcol=seagreen]
> AF
> SQL Server Profiler?
>
>
> "AF" <af.at.work@.gmail.com> wrote in message
> news:1168521633.974360.119620@.i39g2000hsf.googlegroups.com...
Sorry, I meant on a schedule to alert via email ...|||Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> If you are using SQL Server 2005, you can capture this with event
> notifications, or by checking the background trace files. Here is an
> example of the latter:
> http://sqlblog.com/blogs/aaron_bert...ault-trace.aspx
> Otherwise, you should start by telling us version / edition, because the
> solution will depend on that.
> A
>
>
> "AF" <af.at.work@.gmail.com> wrote in message
> news:1168521633.974360.119620@.i39g2000hsf.googlegroups.com...
SQL 2000 ENT / WIN2K3 ENT

Momitoring when job has executed

Is there a technique for determining when a job has been executed - using T-SQL? I tried using 'sp_help_job' by inserting the output into a table (e.g. INSERT jobtable EXEC sp_help_job...) but I get the following error:

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 68

An INSERT EXEC statement cannot be nested.

Any ideas anyone?

Rgds

Bertrand

You can all the info from msdb..sysjobs and msdb..sysjobsteps table. I suggest you take a look at the following article (thanks Gert) so you can handle the sqlagent datetime.
http://sqldev.net/sqlagent/SQLAgentDateTime.htm