More Info:

We have setup a replication in SQL2000:

We have DTS package automatically pouring data into the publishing database(source tables). During this process, we want to temporary disable certain triggers. However, the command

Alter table 'tbl' disable trigger 'abc' errored out. The error message said:

''Cannot alter the table 'tbl' because it is being published for replication."

I've digged more into this and found although it's not allowed to disable a triggers,

the SQLServer do allow delete the trigger and recreate them.

Is there any way to disable the trigger directly?

Thanks in advance,

Don

BTW:

I've used the following sql directly, however the trigger still fires.

UPDATE
sysobjects
SET
status = status|2048
WHERE
type = 'TR'
AND
parent_obj = OBJECT_ID (@.table_name)

The only other way around now is to create stored procedures that dynamically create the trigger. Because our trigger is normmally larger than 8000 bytes. We have to create one stored procedure per trigger. This option is not acceptable because not only it takes quite a time, but also a maintainance nightmare.

More Info: We are using transactional publication.

And trying the sql command in query analyzer quickly give us an error on published article table.

alter table tbl disable trigger abc

|||have you tried adding NOT FOR REPLICATION in your trigger?|||

We did try added not for replication in my trigger. and it does not work.

This issue is for loading data for the master table.

Besides, we did not publish triggers.

|||Disabling the trigger is not the issue. An ALTER TABLE command of ANY kind is not supported in 2000 against a replicated table. The only option in this case is to drop the trigger and recreate it, which is allowed in 2000 against a replicated table.|||

We kown this and we've found a better way to create the triggers. Nobody like this approach through.

If dropping trigger is allowed, there's no reason to not allow disable trigger.

It looks to me it's kind of lack of insight of microsoft. they have sp_repladdcolumn...

they should have a sp_replenabletrigger...

If you look at me post, I tried to modify sysobject tables trigger flag to change it directly.

However, it does not work so my guesses microsoft have more tables involved in this.

I was expecting somebody from microsoft could give me a hint or give me a sp_replenabletrigger.

|||

sp_repladdcolumn and sp_repldropcolumn were added as very specific patches to very specific problems. The issue was quite simply not having enough time to redesign a major portion of the replication engine to allow an ALTER TABLE statement. There are no hints, no work arounds, and no bypasses. There isn't any code like you're looking for either. The reason for that is quite simple. For the last 6 or so years, all development efforts were focused on SQL Server 2005, so if SQL Server 2005 already had a feature that would address and eliminate this entire issue, why waste the time and resources to graft this into SQL Server 2000?

I saw the message in the post. You are more than welcome to hack the system tables if you choose to. I'm certainly not going to hand anyone code to do so. Hacking the system tables is completely unsupported. If you blow up the system, you will not get any support from Microsoft in fixing it.

You have exactly two options:

1. Stay with SQL Server 2000 and write the code as a drop/create trigger

2. Upgrade to SQL Server 2005 and use an ALTER TABLE to disable the trigger

|||

Thanks, Michael.

That's a clear message.

We do encounter other problems with recreating triggers. if we using cmdshell to create the trigger though.

For example, sp-a is the one to create the trigger.

if sp-b calls sp-a,

sp-b do this:

Begin transaction

exec sp-a

Commit Transaction

calling sp-b will stuck inthe command shell out.That's another unpleasant finding yesterday.

Every developer want to upgrade to sql2005. Unfortunately upgrade to sql2005 require massive re-testing of all existing applciations,

This could be a valid reason but it's not enough for the move to sql2005.

I have to considering other solution other than replication now since we are also tight on schedule.

Thanks again for the response.

Don

sql

More info on SQL Server 2005 Express problem install

When I run the setup there is a System Configuration Check page. I get 13
successes and 2 warnings. The 2 warnings are:
Minimum hardware requirement - there is about 37GB free space, 512MB memory,
an Intel Pentium 4 2.4GHz CPU.
IIS Feature requirement - says not installed. Is IIS REQUIRED? Help says
some features may be disabled - that's all.
Then after going through wizard it complained that the SQL Server native
client wasn't available - so ~I will try to download and install that
separately.
Then I get error SQL Server database Services - setup failed. Refer to
setup log - where is that?
There are lots of log files in c:\Program Files\Microsoft SQL Server
subdirectories.
Help please. This is proving a lot harder than I imagined.
AngusI was installing SQL Server 2005 Express Advanced and gave up on that and
installed just standard SQL Server 2005 Express - that worked without a
hitch.
"Angus" <nospam@.gmail.com> wrote in message
news:#mrYry7kHHA.680@.TK2MSFTNGP06.phx.gbl...
> When I run the setup there is a System Configuration Check page. I get 13
> successes and 2 warnings. The 2 warnings are:
> Minimum hardware requirement - there is about 37GB free space, 512MB
memory,
> an Intel Pentium 4 2.4GHz CPU.
> IIS Feature requirement - says not installed. Is IIS REQUIRED? Help says
> some features may be disabled - that's all.
> Then after going through wizard it complained that the SQL Server native
> client wasn't available - so ~I will try to download and install that
> separately.
> Then I get error SQL Server database Services - setup failed. Refer to
> setup log - where is that?
> There are lots of log files in c:\Program Files\Microsoft SQL Server
> subdirectories.
> Help please. This is proving a lot harder than I imagined.
> Angus
>
>|||Hi Angus
"Angus" wrote:
> I was installing SQL Server 2005 Express Advanced and gave up on that and
> installed just standard SQL Server 2005 Express - that worked without a
> hitch.
>
The differences are shown at
http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx if you
wanted reporting services then you would need advanced edition and IIS, but
it should not stop the install of the other components. I am wondering if you
installed the toolkit by mistake?
John

More info on SQL Server 2005 Express problem install

When I run the setup there is a System Configuration Check page. I get 13
successes and 2 warnings. The 2 warnings are:
Minimum hardware requirement - there is about 37GB free space, 512MB memory,
an Intel Pentium 4 2.4GHz CPU.
IIS Feature requirement - says not installed. Is IIS REQUIRED? Help says
some features may be disabled - that's all.
Then after going through wizard it complained that the SQL Server native
client wasn't available - so ~I will try to download and install that
separately.
Then I get error SQL Server database Services - setup failed. Refer to
setup log - where is that?
There are lots of log files in c:\Program Files\Microsoft SQL Server
subdirectories.
Help please. This is proving a lot harder than I imagined.
AngusI was installing SQL Server 2005 Express Advanced and gave up on that and
installed just standard SQL Server 2005 Express - that worked without a
hitch.
"Angus" <nospam@.gmail.com> wrote in message
news:#mrYry7kHHA.680@.TK2MSFTNGP06.phx.gbl...
> When I run the setup there is a System Configuration Check page. I get 13
> successes and 2 warnings. The 2 warnings are:
> Minimum hardware requirement - there is about 37GB free space, 512MB
memory,
> an Intel Pentium 4 2.4GHz CPU.
> IIS Feature requirement - says not installed. Is IIS REQUIRED? Help says
> some features may be disabled - that's all.
> Then after going through wizard it complained that the SQL Server native
> client wasn't available - so ~I will try to download and install that
> separately.
> Then I get error SQL Server database Services - setup failed. Refer to
> setup log - where is that?
> There are lots of log files in c:\Program Files\Microsoft SQL Server
> subdirectories.
> Help please. This is proving a lot harder than I imagined.
> Angus
>
>|||Hi Angus
"Angus" wrote:

> I was installing SQL Server 2005 Express Advanced and gave up on that and
> installed just standard SQL Server 2005 Express - that worked without a
> hitch.
>
The differences are shown at
http://msdn.microsoft.com/vstudio/e...re/default.aspx if you
wanted reporting services then you would need advanced edition and IIS, but
it should not stop the install of the other components. I am wondering if yo
u
installed the toolkit by mistake?
John

Monitoring setup

Hi,
I am having a question on how to setup the followings in our MS SQL 2000
server. We would like to setup some monitoring functions to monitor the CPU
usage, Memory usage and Disk usage in our MS SQL 2000 server. Is there any
way of doing this?
Thanks.
PS : Please bare with me if I am posting this question in the wrong groupYou can use one of many monitoring tools for this, NetIQ AppManager being
one and Microsoft Operations Manager being another example.
If you want to grow your own solution, your best bet would be to find a
command line utility that can give your 'cooked' performance counter values.
Microsoft command line utility logman.exe is such a utility. Note that this
command line works on XP. I've heard that it works on Win2K as well if you
copy the additional DLL along. But I have not tried it on Win2K.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xpehelp/html/PerformanceCommandLineTools.asp
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"TYM" <sniperlc@.yahoo.com> wrote in message
news:ecceScOpDHA.2588@.tk2msftngp13.phx.gbl...
> Hi,
> I am having a question on how to setup the followings in our MS SQL 2000
> server. We would like to setup some monitoring functions to monitor the
CPU
> usage, Memory usage and Disk usage in our MS SQL 2000 server. Is there any
> way of doing this?
> Thanks.
> PS : Please bare with me if I am posting this question in the wrong group
>

monitoring index usage with profiler

How can you setup the Profiler to monitor which indexes are being used in SQL Server 2000 ?. I'm trying with a simple ad-hoc, specifying the actual index but I can't output the object name, id ... etc anything that'll tell me which index(s) are being us
ed.
Need some help. Thanx.
You can catch the execution plan, there you can see indexes used. Just be
sure to filter events to only those you are really interested in, because
you can get a lot of info.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"cqlboy" <anonymous@.discussions.microsoft.com> wrote in message
news:C5206D68-A403-44DC-986C-305196E4CF6D@.microsoft.com...
> How can you setup the Profiler to monitor which indexes are being used in
SQL Server 2000 ?. I'm trying with a simple ad-hoc, specifying the actual
index but I can't output the object name, id ... etc anything that'll tell
me which index(s) are being used.
> Need some help. Thanx.
|||Take a look at the ITW, the docs below combined with BOL will tell you how
to gather a representative workload and then the ITW will help you with the
first pass at tuning.
311826 INF: Index Tuning Wizard Best Practices
http://support.microsoft.com/?id=311826
Index Tuning Wizard 2000,
http://msdn.microsoft.com/library/techart/itwforsql.htm
Index Tuning Wizard 7.0,
http://msdn.microsoft.com/library/te...n_sqlindex.htm
Troubleshooting the Index Tuning Wizard,
http://msdn.microsoft.com/library/ps...tools_48ro.htm
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Monitoring agent status

All -
I'm trying to setup a way to monitor the agent status for replication. I
tried using the built in "Alerts" and they are not working. I made sure that
they are enabled, but after enabling the Alerts, it does not show that the
event had ever happened when I check the history. I've made sure that the
alerts are enabled on the distributor and no where else.
So my next question would be is there a way programatically monitor agents?
I see the system view sys.dm_qn_subscriptions, but when I select * from it,
nothing is returned.
I am trying to monitor the failures and retries.
"Paul Ibison" wrote:

> Which event re you monitoring. I recall from another poster that we couldn't
> get the on success alert running as the event couldn't get raised to the
> windows log (couldn't change the message attributes, unlike in SQL 2000) ,
> but I am not aware of any others.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||This should be ok. Are you using SQL 2000 or 2005? I'd l'll take a look later
on, but for now as a stop-gap you could use a notification on the job itself.
Cheers,
Paul
|||We are using 2005. I'll try and give the notification on the specific job a
try and see if that works.
"Paul Ibison" wrote:

> This should be ok. Are you using SQL 2000 or 2005? I'd l'll take a look later
> on, but for now as a stop-gap you could use a notification on the job itself.
> Cheers,
> Paul
>

Monitor MemToLeave usage

Hi,
I am interested in detecting possible memory leaks in extended store
procedures.
Is there a way to monitor MemToLeave usage ?
setup:
win2k advanced server sp4 (cluster)
sql server 2000 advanced server sp3a
AWE + PEA
Total mem 7Gb
Mem for sql server 6Gb
/JanWe use a combination of VMSTAT.EXE to get the total free and xp_memory_size
(comes with SQL Litespeed) to get the max contiguous block. We use these to
populate 2 of the user settable SQL counters so that we can monitor the
levels and raise alerts if the max contiguous block falls below 5MB which
allows us enough time to schedule a restart of the instance.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jan Ahlbeck" <NoSpam@.dk.dk> wrote in message
news:OB%23ULJ8VFHA.2960@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am interested in detecting possible memory leaks in extended store
> procedures.
> Is there a way to monitor MemToLeave usage ?
> setup:
> win2k advanced server sp4 (cluster)
> sql server 2000 advanced server sp3a
> AWE + PEA
> Total mem 7Gb
> Mem for sql server 6Gb
> /Jan
>

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

MOM Alert when tempdb size grows > 40GB

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

MOM Alert when login failed for user 'sa'

I am trying to setup a MOM Alert when the SA account fails to login to SQL,
How can this be setup so SQL reports this into the windows event viewer and
not its own log?
Many thanks for any help,
AlastairIt does get logged in the application event log whenever login failure
happens for any account. (Standard install)
However, you can create a rule in MOM to capture error id:18456 with error
desc like "Login failed for user 'sa'".
--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
"alastairn" wrote:
> I am trying to setup a MOM Alert when the SA account fails to login to SQL,
> How can this be setup so SQL reports this into the windows event viewer and
> not its own log?
> Many thanks for any help,
> Alastair
>|||Thanks for the fast response and help :)
"Saleem Hakani" wrote:
> It does get logged in the application event log whenever login failure
> happens for any account. (Standard install)
> However, you can create a rule in MOM to capture error id:18456 with error
> desc like "Login failed for user 'sa'".
> --
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> Articles, SQL Clinic and a lot of SQL fun.
>
> "alastairn" wrote:
> > I am trying to setup a MOM Alert when the SA account fails to login to SQL,
> > How can this be setup so SQL reports this into the windows event viewer and
> > not its own log?
> >
> > Many thanks for any help,
> > Alastair
> >