Long table locks

Hi
There is an application that runs on sql server.
The application selects/updates some few tables frequently
Once there is even a select on this table .It blocks other users
sometimes for very long.
Is there anything that can be done to reduce this?
The table has 18000 rows and does not seem to have an index
I thought indexing might help but 18000 rows without an index is
no reason for 30 minutes of lock time.
I will appreciate your help as usual
VinceVincento Harris (wumutek@.yahoo.com) writes:
> There is an application that runs on sql server.
> The application selects/updates some few tables frequently
> Once there is even a select on this table .It blocks other users
> sometimes for very long.
> Is there anything that can be done to reduce this?
> The table has 18000 rows and does not seem to have an index
> I thought indexing might help but 18000 rows without an index is
> no reason for 30 minutes of lock time.

Yes, something can probably be done, but the information you've provided
is not sufficient to say what should be done.

The key to nail down performance problems is information. Exactly
what is taking long time? And are there any transactions that are
open for all this time? You might get some useful information by
tracing this process with the Profiler.

Is this ia an in-house app, or a something you've bought from a vendor?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Long Running OPENXML Query

Hi,
I have a long running OPENXML query in a SP that runs for around 7 mins, it
runs 6 queries doing inserts and updates from the XML to the DB. The XML is
around 2.5MB in size.
On my test server which has 1 physical XEON CPU, SQL Server uses 50% of both
logical processors and all works fine.
However my live server has 2 physical CPUs but we only have a single proc
license. The SQL Server is configured to use 2 logical CPUs but just the 1
physical CPU. When the long running query begins the query runs on just 1
logical processor and all other connections to the SQL server timeout.
Does anyone know how to get the query to use both logical processors at 50%
and thus allow other connections to the server?
Thanks
Jason
Hi Jason,
Thanks for your posting!
From your descriptions, I understood you can make two CPU balance the
workload in test environment but failed to do so in live machine. If I have
misunderstood your concern, please feel free to point it out.
Based on my knowlegde, please check SQL Server Properties to see whether
you are using that two logic processor in SQL Server and selected Use.
However, we do not have a way to compose the query to use both logical
processor.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||What version of SQL Server 2000 are you running? Have you upgraded to SP4
(which should give you some improvement on the OpenXML front).
Best regards
Michael
"Jason" <techno@.noemail.nospam> wrote in message
news:%23JxasEJiFHA.3960@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a long running OPENXML query in a SP that runs for around 7 mins,
> it
> runs 6 queries doing inserts and updates from the XML to the DB. The XML
> is
> around 2.5MB in size.
> On my test server which has 1 physical XEON CPU, SQL Server uses 50% of
> both
> logical processors and all works fine.
> However my live server has 2 physical CPUs but we only have a single proc
> license. The SQL Server is configured to use 2 logical CPUs but just the 1
> physical CPU. When the long running query begins the query runs on just 1
> logical processor and all other connections to the SQL server timeout.
> Does anyone know how to get the query to use both logical processors at
> 50%
> and thus allow other connections to the server?
> Thanks
> Jason
>
|||Hi
Thanks for your replies.
We are running SP4. I have rewritten the query which has improved the speed
and also used NOLOCK to avoid locks being taken out for the duration of the
transaction.
The problem I still have is that SQL Server is only using 1 logical
processor for everything that it does. On the processor tab of the SQL
Properties window I have the following settings,
CPU 0 and 1 are checked, 2 and 3 are not.=20
Max worker threads is 255
Boost SQL server priority is checked
Parallism
Use all available processors is checked
Min query plan threshold is 5
When any SQL activity occurs the Task Manager performance window shows 25%
CPU usage on the left and out of the 4 available logical processors only 1
spikes.
Any ideas why its not using 50%?
Thanks
Jason
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23KJXNUdiFHA.576@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> What version of SQL Server 2000 are you running? Have you upgraded to SP4
> (which should give you some improvement on the OpenXML front).
> Best regards
> Michael
> "Jason" <techno@.noemail.nospam> wrote in message
> news:%23JxasEJiFHA.3960@.TK2MSFTNGP12.phx.gbl...
proc[vbcol=seagreen]
1[vbcol=seagreen]
1
>
|||OpenXML is implemented as a remote provider, so I believe parallelism is
disabled for OpenXML. I'm hardly an expert on the way SQL schedules, though.
Best regards
Michael
"Jason" <techno@.noemail.nospam> wrote in message
news:O$m92M3iFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks for your replies.
> We are running SP4. I have rewritten the query which has improved the
> speed
> and also used NOLOCK to avoid locks being taken out for the duration of
> the
> transaction.
> The problem I still have is that SQL Server is only using 1 logical
> processor for everything that it does. On the processor tab of the SQL
> Properties window I have the following settings,
> CPU 0 and 1 are checked, 2 and 3 are not.=20
> Max worker threads is 255
> Boost SQL server priority is checked
> Parallism
> Use all available processors is checked
> Min query plan threshold is 5
> When any SQL activity occurs the Task Manager performance window shows 25%
> CPU usage on the left and out of the 4 available logical processors only 1
> spikes.
> Any ideas why its not using 50%?
> Thanks
> Jason
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23KJXNUdiFHA.576@.tk2msftngp13.phx.gbl...
> proc
> 1
> 1
>

Long response time at a certain time each week

Our server is experiencing random slowdowns (for specific
inserts, updates or selects) at a certain time each week.
I've checked to make sure something else is not running
and do not see any scheduled jobs during this time. I've
captured a trace file and don't see any reason why it
would take a long time. It is not a busy time on the
server, either.
Any suggestions for some obvious things I could check?
Our environment is SQL Server 2000 with Service Pack 3 on
Windows 2000 with Service Pack 3.
TIA,
Jeanne UngerIs your database configured for auto-shrink? Is there some other activity on
your network that could be causing a slow down?
Jim
"Jeanne Unger" <anonymous@.discussions.microsoft.com> wrote in message
news:0a9601c3fb02$5f1bf540$a101280a@.phx.gbl...
> Our server is experiencing random slowdowns (for specific
> inserts, updates or selects) at a certain time each week.
> I've checked to make sure something else is not running
> and do not see any scheduled jobs during this time. I've
> captured a trace file and don't see any reason why it
> would take a long time. It is not a busy time on the
> server, either.
> Any suggestions for some obvious things I could check?
> Our environment is SQL Server 2000 with Service Pack 3 on
> Windows 2000 with Service Pack 3.
> TIA,
> Jeanne Unger|||It was configured to auto shrink, so I turned that off
yesterday. I will find out Sunday night whether or not it
made a difference.
I haven't been able to find anything on the network that
is causing the slowdown, and it is specific to this
particular database. For example, the queries usually
take 0-16 milliseconds but at 00:30 GMT it can take 3-4
minutes. No other applications that run across the
network are affected.

>--Original Message--
>Is your database configured for auto-shrink? Is there
some other activity on
>your network that could be causing a slow down?
>Jim
>"Jeanne Unger" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a9601c3fb02$5f1bf540$a101280a@.phx.gbl...
specific
week.
I've
on
>
>.
>

Long response time at a certain time each week

Our server is experiencing random slowdowns (for specific
inserts, updates or selects) at a certain time each week.
I've checked to make sure something else is not running
and do not see any scheduled jobs during this time. I've
captured a trace file and don't see any reason why it
would take a long time. It is not a busy time on the
server, either.
Any suggestions for some obvious things I could check?
Our environment is SQL Server 2000 with Service Pack 3 on
Windows 2000 with Service Pack 3.
TIA,
Jeanne UngerIs your database configured for auto-shrink? Is there some other activity on
your network that could be causing a slow down?
Jim
"Jeanne Unger" <anonymous@.discussions.microsoft.com> wrote in message
news:0a9601c3fb02$5f1bf540$a101280a@.phx.gbl...
> Our server is experiencing random slowdowns (for specific
> inserts, updates or selects) at a certain time each week.
> I've checked to make sure something else is not running
> and do not see any scheduled jobs during this time. I've
> captured a trace file and don't see any reason why it
> would take a long time. It is not a busy time on the
> server, either.
> Any suggestions for some obvious things I could check?
> Our environment is SQL Server 2000 with Service Pack 3 on
> Windows 2000 with Service Pack 3.
> TIA,
> Jeanne Unger|||It was configured to auto shrink, so I turned that off
yesterday. I will find out Sunday night whether or not it
made a difference.
I haven't been able to find anything on the network that
is causing the slowdown, and it is specific to this
particular database. For example, the queries usually
take 0-16 milliseconds but at 00:30 GMT it can take 3-4
minutes. No other applications that run across the
network are affected.
>--Original Message--
>Is your database configured for auto-shrink? Is there
some other activity on
>your network that could be causing a slow down?
>Jim
>"Jeanne Unger" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a9601c3fb02$5f1bf540$a101280a@.phx.gbl...
>> Our server is experiencing random slowdowns (for
specific
>> inserts, updates or selects) at a certain time each
week.
>> I've checked to make sure something else is not running
>> and do not see any scheduled jobs during this time.
I've
>> captured a trace file and don't see any reason why it
>> would take a long time. It is not a busy time on the
>> server, either.
>> Any suggestions for some obvious things I could check?
>> Our environment is SQL Server 2000 with Service Pack 3
on
>> Windows 2000 with Service Pack 3.
>> TIA,
>> Jeanne Unger
>
>.
>

Logs/Restore Logs

Whats contained the the Backup Log Files ? What goes on when a restore of
the log takes place on say a standby server ?
If I do some inserts/updates and deletes against DB A ... And when I backup
the log after executing those statements, will the log contain those DML
statements or will it contain all the data/index pages that were being
affected by those DML ?
And if it does contain the DML , does that mean that when a restore log
takes place, the DML gets fired on the standby ?
And if the backup logs do not contain DML statements, what would it contain
and how does it do the restore ?
Please let me know the technical details...Using SQL 2000.
Also curious to know what happens when some DBCC commands are run such as
dbreindex or indexdefrag ...Hassan,
The log files contain changes made to data in the database. If the DML is
changed it updates (for example) sysobjects, syscolumns, syscomments, etc.
depending on the DML. If you ALTER TABLE and add a column with a default,
the log will contain (1) changes to the system objects, (2) every row in the
table with its new default value, (3) any indexes that are updated in the
process, (4) and so forth.
The commands that you issued are not recorded in the log, the results that
you obtained are recorded there.
Ditto on DBCC, any changes go into the logs.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Whats contained the the Backup Log Files ? What goes on when a restore of
> the log takes place on say a standby server ?
> If I do some inserts/updates and deletes against DB A ... And when I
backup
> the log after executing those statements, will the log contain those DML
> statements or will it contain all the data/index pages that were being
> affected by those DML ?
> And if it does contain the DML , does that mean that when a restore log
> takes place, the DML gets fired on the standby ?
> And if the backup logs do not contain DML statements, what would it
contain
> and how does it do the restore ?
> Please let me know the technical details...Using SQL 2000.
> Also curious to know what happens when some DBCC commands are run such as
> dbreindex or indexdefrag ...
>|||Ok.. thats informational.. How does the restore take place then ? So ive got
all the changes which im assuming that the data and index pages are captured
in the log.. Does it just about and replacing the data and index pages with
the new ones on the standby server.. Is that how the restore logs work
Thanks
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
> Hassan,
> The log files contain changes made to data in the database. If the DML is
> changed it updates (for example) sysobjects, syscolumns, syscomments, etc.
> depending on the DML. If you ALTER TABLE and add a column with a default,
> the log will contain (1) changes to the system objects, (2) every row in
the
> table with its new default value, (3) any indexes that are updated in the
> process, (4) and so forth.
> The commands that you issued are not recorded in the log, the results that
> you obtained are recorded there.
> Ditto on DBCC, any changes go into the logs.
> Russell Fields
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
of
> backup
> contain
as
>|||Row changes are in the log as well as page allocations, splits, etc.
So, no it does not just replace the pages since that would greatly increase
the log size. (For example, in that case if I updated 10 200-byte rows on
10 pages I would log about 160,000 bytes instead of about 4000 bytes.
Before and after images.)
The transactions are rolled forward one at a time.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#3UnFiy9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Ok.. thats informational.. How does the restore take place then ? So ive
got
> all the changes which im assuming that the data and index pages are
captured
> in the log.. Does it just about and replacing the data and index pages
with
> the new ones on the standby server.. Is that how the restore logs work
> Thanks
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
is
etc.
default,
> the
the
that
> of
DML
log
> as
>|||Hassan,
If you really want to know how most products implement transaction logging,
I recommend the books "Transaction Processing Concepts and Techniques" by
Reuter and Gray. Not light reading, though. But it goes through what type of
information that need to be logged in order for a product to use the log for
roll forward and roll back. Of course, vendors are probably tweaking these
algorithms a bit, but my guess is that most products uses these base
techniques in one form or another.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%233UnFiy9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Ok.. thats informational.. How does the restore take place then ? So ive
got
> all the changes which im assuming that the data and index pages are
captured
> in the log.. Does it just about and replacing the data and index pages
with
> the new ones on the standby server.. Is that how the restore logs work
> Thanks
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
is
etc.
default,
> the
the
that
> of
DML
log
> as
>sql

Logs/Restore Logs

Whats contained the the Backup Log Files ? What goes on when a restore of
the log takes place on say a standby server ?
If I do some inserts/updates and deletes against DB A ... And when I backup
the log after executing those statements, will the log contain those DML
statements or will it contain all the data/index pages that were being
affected by those DML ?
And if it does contain the DML , does that mean that when a restore log
takes place, the DML gets fired on the standby ?
And if the backup logs do not contain DML statements, what would it contain
and how does it do the restore ?
Please let me know the technical details...Using SQL 2000.
Also curious to know what happens when some DBCC commands are run such as
dbreindex or indexdefrag ...Hassan,
The log files contain changes made to data in the database. If the DML is
changed it updates (for example) sysobjects, syscolumns, syscomments, etc.
depending on the DML. If you ALTER TABLE and add a column with a default,
the log will contain (1) changes to the system objects, (2) every row in the
table with its new default value, (3) any indexes that are updated in the
process, (4) and so forth.
The commands that you issued are not recorded in the log, the results that
you obtained are recorded there.
Ditto on DBCC, any changes go into the logs.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Whats contained the the Backup Log Files ? What goes on when a restore of
> the log takes place on say a standby server ?
> If I do some inserts/updates and deletes against DB A ... And when I
backup
> the log after executing those statements, will the log contain those DML
> statements or will it contain all the data/index pages that were being
> affected by those DML ?
> And if it does contain the DML , does that mean that when a restore log
> takes place, the DML gets fired on the standby ?
> And if the backup logs do not contain DML statements, what would it
contain
> and how does it do the restore ?
> Please let me know the technical details...Using SQL 2000.
> Also curious to know what happens when some DBCC commands are run such as
> dbreindex or indexdefrag ...
>|||Ok.. thats informational.. How does the restore take place then ? So ive got
all the changes which im assuming that the data and index pages are captured
in the log.. Does it just about and replacing the data and index pages with
the new ones on the standby server.. Is that how the restore logs work
Thanks
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
> Hassan,
> The log files contain changes made to data in the database. If the DML is
> changed it updates (for example) sysobjects, syscolumns, syscomments, etc.
> depending on the DML. If you ALTER TABLE and add a column with a default,
> the log will contain (1) changes to the system objects, (2) every row in
the
> table with its new default value, (3) any indexes that are updated in the
> process, (4) and so forth.
> The commands that you issued are not recorded in the log, the results that
> you obtained are recorded there.
> Ditto on DBCC, any changes go into the logs.
> Russell Fields
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > Whats contained the the Backup Log Files ? What goes on when a restore
of
> > the log takes place on say a standby server ?
> >
> > If I do some inserts/updates and deletes against DB A ... And when I
> backup
> > the log after executing those statements, will the log contain those DML
> > statements or will it contain all the data/index pages that were being
> > affected by those DML ?
> >
> > And if it does contain the DML , does that mean that when a restore log
> > takes place, the DML gets fired on the standby ?
> > And if the backup logs do not contain DML statements, what would it
> contain
> > and how does it do the restore ?
> >
> > Please let me know the technical details...Using SQL 2000.
> >
> > Also curious to know what happens when some DBCC commands are run such
as
> > dbreindex or indexdefrag ...
> >
> >
>|||Row changes are in the log as well as page allocations, splits, etc.
So, no it does not just replace the pages since that would greatly increase
the log size. (For example, in that case if I updated 10 200-byte rows on
10 pages I would log about 160,000 bytes instead of about 4000 bytes.
Before and after images.)
The transactions are rolled forward one at a time.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#3UnFiy9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Ok.. thats informational.. How does the restore take place then ? So ive
got
> all the changes which im assuming that the data and index pages are
captured
> in the log.. Does it just about and replacing the data and index pages
with
> the new ones on the standby server.. Is that how the restore logs work
> Thanks
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
> > Hassan,
> >
> > The log files contain changes made to data in the database. If the DML
is
> > changed it updates (for example) sysobjects, syscolumns, syscomments,
etc.
> > depending on the DML. If you ALTER TABLE and add a column with a
default,
> > the log will contain (1) changes to the system objects, (2) every row in
> the
> > table with its new default value, (3) any indexes that are updated in
the
> > process, (4) and so forth.
> >
> > The commands that you issued are not recorded in the log, the results
that
> > you obtained are recorded there.
> >
> > Ditto on DBCC, any changes go into the logs.
> >
> > Russell Fields
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > Whats contained the the Backup Log Files ? What goes on when a restore
> of
> > > the log takes place on say a standby server ?
> > >
> > > If I do some inserts/updates and deletes against DB A ... And when I
> > backup
> > > the log after executing those statements, will the log contain those
DML
> > > statements or will it contain all the data/index pages that were being
> > > affected by those DML ?
> > >
> > > And if it does contain the DML , does that mean that when a restore
log
> > > takes place, the DML gets fired on the standby ?
> > > And if the backup logs do not contain DML statements, what would it
> > contain
> > > and how does it do the restore ?
> > >
> > > Please let me know the technical details...Using SQL 2000.
> > >
> > > Also curious to know what happens when some DBCC commands are run such
> as
> > > dbreindex or indexdefrag ...
> > >
> > >
> >
> >
>|||Hassan,
If you really want to know how most products implement transaction logging,
I recommend the books "Transaction Processing Concepts and Techniques" by
Reuter and Gray. Not light reading, though. But it goes through what type of
information that need to be logged in order for a product to use the log for
roll forward and roll back. Of course, vendors are probably tweaking these
algorithms a bit, but my guess is that most products uses these base
techniques in one form or another.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%233UnFiy9DHA.3176@.TK2MSFTNGP11.phx.gbl...
> Ok.. thats informational.. How does the restore take place then ? So ive
got
> all the changes which im assuming that the data and index pages are
captured
> in the log.. Does it just about and replacing the data and index pages
with
> the new ones on the standby server.. Is that how the restore logs work
> Thanks
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:e1F77cy9DHA.2308@.TK2MSFTNGP09.phx.gbl...
> > Hassan,
> >
> > The log files contain changes made to data in the database. If the DML
is
> > changed it updates (for example) sysobjects, syscolumns, syscomments,
etc.
> > depending on the DML. If you ALTER TABLE and add a column with a
default,
> > the log will contain (1) changes to the system objects, (2) every row in
> the
> > table with its new default value, (3) any indexes that are updated in
the
> > process, (4) and so forth.
> >
> > The commands that you issued are not recorded in the log, the results
that
> > you obtained are recorded there.
> >
> > Ditto on DBCC, any changes go into the logs.
> >
> > Russell Fields
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:#UUxvUx9DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > Whats contained the the Backup Log Files ? What goes on when a restore
> of
> > > the log takes place on say a standby server ?
> > >
> > > If I do some inserts/updates and deletes against DB A ... And when I
> > backup
> > > the log after executing those statements, will the log contain those
DML
> > > statements or will it contain all the data/index pages that were being
> > > affected by those DML ?
> > >
> > > And if it does contain the DML , does that mean that when a restore
log
> > > takes place, the DML gets fired on the standby ?
> > > And if the backup logs do not contain DML statements, what would it
> > contain
> > > and how does it do the restore ?
> > >
> > > Please let me know the technical details...Using SQL 2000.
> > >
> > > Also curious to know what happens when some DBCC commands are run such
> as
> > > dbreindex or indexdefrag ...
> > >
> > >
> >
> >
>

logs - configuration option changed

Using SS2000. I often see a line in the logs that says "configuration opton 'allow updates' changed from 0 to 1. Run the RECONFIGURE s Error 15457, serverity 0, State 1"
Another entry will change the option from 1 to 0. What's going on? Is a dts package doing this as part of a normal run?
Thanks,
Dan D.
That is usually associated with someone using EM to view certain options.
It is nothing to be concerned with.
Andrew J. Kelly SQL MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FAD761D0-4D1B-4EB4-BF9E-18155CC0ED9C@.microsoft.com...
> Using SS2000. I often see a line in the logs that says "configuration
opton 'allow updates' changed from 0 to 1. Run the RECONFIGURE s Error
15457, serverity 0, State 1"
> Another entry will change the option from 1 to 0. What's going on? Is a
dts package doing this as part of a normal run?
> Thanks,
> --
> Dan D.
|||No one claims to have been on the system at that time (1 AM local time). Could some system process make the changes?
Thanks,
Dan D.
"Andrew J. Kelly" wrote:

> That is usually associated with someone using EM to view certain options.
> It is nothing to be concerned with.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:FAD761D0-4D1B-4EB4-BF9E-18155CC0ED9C@.microsoft.com...
> opton 'allow updates' changed from 0 to 1. Run the RECONFIGURE s Error
> 15457, serverity 0, State 1"
> dts package doing this as part of a normal run?
>
>
|||Do you have a maintenance plan set up and if so does it have the option to
fix minor errors? This may cause an entry like this but I am not 100 %
sure. Are there any scheduled jobs that collect information from the server
with say sp_dboption?
Andrew J. Kelly SQL MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:95E34CD1-BDFA-423B-B05B-C319F7E8E3E3@.microsoft.com...
> No one claims to have been on the system at that time (1 AM local time).
Could some system process make the changes?[vbcol=seagreen]
> Thanks,
> --
> Dan D.
>
> "Andrew J. Kelly" wrote:
options.[vbcol=seagreen]
a[vbcol=seagreen]
|||There are no maintenance plans. There was one dts package that was running at the time of the message. I'll look through it and see what I can find.
Thanks,
Dan D.
"Andrew J. Kelly" wrote:

> Do you have a maintenance plan set up and if so does it have the option to
> fix minor errors? This may cause an entry like this but I am not 100 %
> sure. Are there any scheduled jobs that collect information from the server
> with say sp_dboption?
> --
> Andrew J. Kelly SQL MVP
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:95E34CD1-BDFA-423B-B05B-C319F7E8E3E3@.microsoft.com...
> Could some system process make the changes?
> options.
> a
>
>

logs - configuration option changed

Using SS2000. I often see a line in the logs that says "configuration opton
'allow updates' changed from 0 to 1. Run the RECONFIGURE s Error 15457, serv
erity 0, State 1"
Another entry will change the option from 1 to 0. What's going on? Is a dts
package doing this as part of a normal run?
Thanks,
--
Dan D.That is usually associated with someone using EM to view certain options.
It is nothing to be concerned with.
Andrew J. Kelly SQL MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FAD761D0-4D1B-4EB4-BF9E-18155CC0ED9C@.microsoft.com...
> Using SS2000. I often see a line in the logs that says "configuration
opton 'allow updates' changed from 0 to 1. Run the RECONFIGURE s Error
15457, serverity 0, State 1"
> Another entry will change the option from 1 to 0. What's going on? Is a
dts package doing this as part of a normal run?
> Thanks,
> --
> Dan D.|||No one claims to have been on the system at that time (1 AM local time). Cou
ld some system process make the changes?
Thanks,
--
Dan D.
"Andrew J. Kelly" wrote:

> That is usually associated with someone using EM to view certain options.
> It is nothing to be concerned with.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:FAD761D0-4D1B-4EB4-BF9E-18155CC0ED9C@.microsoft.com...
> opton 'allow updates' changed from 0 to 1. Run the RECONFIGURE s Error
> 15457, serverity 0, State 1"
> dts package doing this as part of a normal run?
>
>|||Do you have a maintenance plan set up and if so does it have the option to
fix minor errors? This may cause an entry like this but I am not 100 %
sure. Are there any scheduled jobs that collect information from the server
with say sp_dboption?
Andrew J. Kelly SQL MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:95E34CD1-BDFA-423B-B05B-C319F7E8E3E3@.microsoft.com...
> No one claims to have been on the system at that time (1 AM local time).
Could some system process make the changes?[vbcol=seagreen]
> Thanks,
> --
> Dan D.
>
> "Andrew J. Kelly" wrote:
>
options.[vbcol=seagreen]
a[vbcol=seagreen]|||There are no maintenance plans. There was one dts package that was running a
t the time of the message. I'll look through it and see what I can find.
Thanks,
--
Dan D.
"Andrew J. Kelly" wrote:

> Do you have a maintenance plan set up and if so does it have the option to
> fix minor errors? This may cause an entry like this but I am not 100 %
> sure. Are there any scheduled jobs that collect information from the serv
er
> with say sp_dboption?
> --
> Andrew J. Kelly SQL MVP
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:95E34CD1-BDFA-423B-B05B-C319F7E8E3E3@.microsoft.com...
> Could some system process make the changes?
> options.
> a
>
>

Logon auditing through trigger on sysprocesses?

I need to do auditing of logons, and selectively (depending on who does
them) of updates to a single table as well.
Furthermore, to make remote access to the audit log easier, I would prefer
to log all this in a table on the same server.
Full c2 auditing would give me far too much information (and I haven't even
checked if it is supported in MSDE). It would probably reduce performance
as well.
Full auditing of all updates to just that one table where I need it, would
give too much information too: one certain application that's running on
the local machine should be left out.
Now I think I could get everything I want done through triggers, but
there's one problem.
The 'sysprocesses' table seems like a good place to detect logons, if I
could create an INSERT trigger that copies username and hostname to a
logging table for each new record.
The problem: the db engine won't let me create a trigger on the
sysprocesses table, it keeps saying "access denied" no matter how I try to
override and change permissions.
I realize that there could be some danger too, if executing a trigger would
create a new process. But will it? I would expect that a trigger is
executed in the context of the process that initiated the change.
hi,
Lucvdv wrote:
>...
> The 'sysprocesses' table seems like a good place to detect logons, if
> I could create an INSERT trigger that copies username and hostname to
> a logging table for each new record.
> The problem: the db engine won't let me create a trigger on the
> sysprocesses table, it keeps saying "access denied" no matter how I
> try to override and change permissions.
>
sysprocesses is a "fake" table, in the sense it is materialized on demand,
and you can't perform INSERT, UPDATE, or DELETE operations on this kind of
table no matter how much privilege you have on the system, and of course you
can not write trigger on it..
as regard auditing, try perhaps having a look at
http://www.windowsitpro.com/Article/...434/26434.html ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea Montanari wrote:
> hi,
> Lucvdv wrote:
> sysprocesses is a "fake" table, in the sense it is materialized on demand,
> and you can't perform INSERT, UPDATE, or DELETE operations on this kind of
> table no matter how much privilege you have on the system, and of course you
> can not write trigger on it..
> as regard auditing, try perhaps having a look at
> http://www.windowsitpro.com/Article/...434/26434.html ...
Thanks.
It wasn't of much help though
The article is about finding out whodunnit in a situation where
everyone has the sa password, and suggests giving each user his own
database to connect to.
In my case each user has his own logon credentials, all changes except
those made by a certain account should be logged, and changes made by
any user should be visible to all.
That part shouldn't be too hard to do, the difficulty is knowing when
someone logs on and from where.
|||On Tue, 30 Aug 2005 19:31:30 +0200, Lucvdv <name@.null.net> wrote:

> In my case each user has his own logon credentials, all changes except
> those made by a certain account should be logged, and changes made by
> any user should be visible to all.
> That part shouldn't be too hard to do, the difficulty is knowing when
> someone logs on and from where.
The change logging part is working, with a simple trigger.
There is a performance hit of course, but it doesn't seem to be too much
(I've tested it in a real live database for about an hour, and didn't see
any noticeable system slowdown or abnormally high CPU use).
In case someone wants to use it as a model, this is the 'simple' version
with a test table I started from:
The 'Test' table just has 2 columns Col1 and Col2, both integer, with Col1
as primary key.
The 'TestLog' table must exist before the trigger is added, with columns
(in this case) DT DateTime, spid smallint, Col1 int, oldCol2 int, newCol2
int, user varchar(128), host varchar(128), prog varchar(128).
In the real test I included an identity column, so sequence numbers will be
missing if someone deletes log lines.
CREATE TRIGGER [trgTest] ON dbo.Test
FOR UPDATE
AS
DECLARE @.user varchar(128), @.host varchar(128), @.prog varchar(128)
BEGIN
SELECT @.user = RTRIM([nt_username]), @.host = RTRIM([hostname]),
@.prog = RTRIM([program_name])
FROM [master].[dbo].[sysprocesses] WHERE spid=@.@.SPID
IF @.prog<>'My application'
INSERT INTO [TestLog]
SELECT GetDate(), @.@.SPID, [Inserted].[Col1], [Deleted].[Col2],
[Inserted].[Col2], @.user, @.host, @.prog
FROM [Inserted] INNER JOIN [Deleted]
ON [Inserted].[Col1]=[Deleted].[Col1]
WHERE [Inserted].[Col2]<>[Deleted].[Col2]
END
|||have a look at
http://www.sqlservercentral.com/colu...qlprofiler.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Login to SQL Server for Web Application

Hello everyone,

I am currently developing a web application that retrieves and updates contact information in a database. I am coding the application with VB.Net and ASP.Net with a little bit of javascript. It is accessing a SQL Server Database.

Everything is working fine when I run the web application from within the VS.Net environment. However after publishing the website to our local Intranet (where the app will be stored) I get the following error:

Server Error in '/ollodev1/RIC' Application.

---

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Source Error:

An unhandled exception was generated during the execution of thecurrent web request. Information regarding the origin and location ofthe exception can be identified using the exception stack trace below.

I assume that this message appears because I am not retrieving setting the proper access rights from the DB but I don't know how to go about doing this. Could someone provide me with a link to a few examples to help resolve my issue?

Thanks,

Eric

Also, I've been testing to resolve the matter and I assume it is because of this.

When I added the line of: label1.text = Request.Servervariables("AUTH_USER")

it shows: domain1\<winnt id>

But when I published the website to our intranet:

The label is blanked...

I'm trying to add as much info as possible.

Thanks again

|||

The Null user error is related to your SQL Server installed with Windows Authentication and your application connecting to SQL Server with SQL Server authentication, you can solve this by changing SQL Server to mixed authentication. That is covered in the link below.

http://msdn2.microsoft.com/en-us/library/ms188670.aspx

Asp.net account in IIS5 that is Win2k and XP, and Network service account in Win2003 needs permissions in SQL Server, there are two permissions in SQL Server the server permissions in the security section under management in Management Studio and the database permissions in the new security section within the database in Management Studio. When you have finished creating the permissions adjust your Web.Config as in the link below. Hope this helps.

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx