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 transactions w/ other users needing to read data

I have a very long transaction that runs on the same database that
other users need to use for existing data. I don't care if they see
data from the transaction before it is done and am only using the
transaction because I need a way to roll it back if any errors happen
during the transaction. Unfortunately all tables affected in the long
running transaction are completely locked and nobody else can access
any of the affected tables while it is running. I am using the
transaction isolation level of read uncommitted, which from my limited
understanding of isolation levels is the least strict. What can I do to
prevent this from happening?

Below is the output from sp_who2 and sp_lock while the process is
running and another process is being blocked by it.

SPID Status Login
HostName BlkBy DBName Command CPUTime
DiskIO LastBatch ProgramName SPID
-- ----------
--------------- ---- --
---- ------ --- -- -----
--------- --
1 BACKGROUND sa
. . NULL LAZY WRITER 0 0
06/09 15:42:52 1
2 sleeping sa
. . NULL LOG WRITER 10 0
06/09 15:42:52 2
3 BACKGROUND sa
. . master SIGNAL HANDLER 0 0
06/09 15:42:52 3
4 BACKGROUND sa
. . NULL LOCK MONITOR 0 0
06/09 15:42:52 4
5 BACKGROUND sa
. . master TASK MANAGER 0 5
06/09 15:42:52 5
6 BACKGROUND sa
. . master TASK MANAGER 0 0
06/09 15:42:52 6
7 sleeping sa
. . NULL CHECKPOINT SLEEP 0 12
06/09 15:42:52 7
8 BACKGROUND sa
. . master TASK MANAGER 0 2
06/09 15:42:52 8
9 BACKGROUND sa
. . master TASK MANAGER 0 0
06/09 15:42:52 9
10 BACKGROUND sa
. . master TASK MANAGER 0 0
06/09 15:42:52 10
11 BACKGROUND sa
. . master TASK MANAGER 0 1
06/09 15:42:52 11
12 BACKGROUND sa
. . master TASK MANAGER 0 0
06/09 15:42:52 12
51 sleeping SUPERPABLO\Administrator
SUPERPABLO . PM AWAITING COMMAND 1813
307 06/09 16:10:34 .Net SqlClient Data Provider 51
52 sleeping SUPERPABLO\Administrator
SUPERPABLO 54 PM SELECT 30 5
06/09 16:10:16 .Net SqlClient Data Provider 52
53 RUNNABLE SUPERPABLO\Administrator
SUPERPABLO . master SELECT 0 3
06/09 16:09:44 SQL Profiler 53
54 RUNNABLE SUPERPABLO\Administrator
SUPERPABLO . PM UPDATE 10095
206 06/09 16:10:02 .Net SqlClient Data Provider 54
56 RUNNABLE SUPERPABLO\Administrator
SUPERPABLO . PM SELECT INTO 151 27
06/09 16:10:33 SQL Query Analyzer 56

(17 row(s) affected)

spid dbid ObjId IndId Type Resource Mode Status
-- -- ---- -- -- ------ --- --
51 5 0 0 DB S GRANT
52 5 0 0 DB S GRANT
52 5 1117963059 4 PAG 1:7401 IS GRANT
52 5 1117963059 4 KEY (5301214e6d62) S WAIT
52 5 1117963059 0 TAB IS GRANT
54 5 1117963059 0 TAB IX GRANT
54 5 1852025829 0 TAB IX GRANT
54 5 1181963287 3 PAG 1:9017 IX GRANT
54 5 1117963059 4 KEY (5301934930a4) X GRANT
54 5 1117963059 3 KEY (530187fc93f3) X GRANT
54 5 1117963059 4 KEY (530154df71eb) X GRANT
54 5 0 0 DB [BULK-OP-LOG] NULL GRANT
54 5 0 0 FIL 2:0:d U GRANT
54 5 1117963059 2 KEY (1d0096c50a7d) X GRANT
54 5 1117963059 2 KEY (1b004a9a6158) X GRANT
54 5 1117963059 2 KEY (1800a435d44a) X GRANT
54 5 1181963287 6 PAG 1:8745 IX GRANT
54 5 1181963287 4 PAG 1:8923 IX GRANT
54 5 1181963287 2 PAG 1:8937 IX GRANT
54 5 1117963059 4 KEY (5301112b0696) X GRANT
54 5 0 0 PAG 1:10889 IX GRANT
54 5 1181963287 5 PAG 1:8859 IX GRANT
54 5 1181963287 6 PAG 1:10888 IX GRANT
54 5 0 0 PAG 1:10891 IX GRANT
54 5 0 0 PAG 1:10893 IX GRANT
54 5 0 0 PAG 1:10892 IX GRANT
54 5 0 0 PAG 1:10894 IX GRANT
54 5 0 0 PAG 1:10882 IX GRANT
54 5 1117963059 3 KEY (530135fbce35) X GRANT
54 5 1117963059 0 RID 1:7387:57 X GRANT
54 5 1117963059 0 RID 1:7387:59 X GRANT
54 5 1117963059 0 RID 1:7387:61 X GRANT
54 5 1117963059 3 KEY (5301406ad2bc) X GRANT
54 5 1117963059 4 PAG 1:7401 IX GRANT
54 5 0 0 PAG 1:7387 IX GRANT
54 5 1117963059 2 PAG 1:7389 IX GRANT
54 5 1117963059 3 PAG 1:7391 IX GRANT
54 5 1117963059 0 RID 1:7387:10 X GRANT
54 5 1117963059 0 RID 1:7387:56 X GRANT
54 5 1117963059 0 RID 1:7387:58 X GRANT
54 5 1117963059 0 RID 1:7387:60 X GRANT
54 5 1117963059 3 KEY (530144afbed8) X GRANT
54 5 1117963059 4 KEY (530115ee6af2) X GRANT
54 5 1117963059 3 KEY (5301c6cd88ea) X GRANT
54 5 1149963173 0 TAB IX GRANT
54 5 1181963287 0 TAB X GRANT
54 5 1117963059 4 KEY (5301d2782bbd) X GRANT
54 5 1117963059 3 KEY (5301015bc9a5) X GRANT
54 5 0 0 DB S GRANT
54 5 0 0 DB [BULK-OP-DB] NULL GRANT
54 5 1117963059 4 KEY (5301501a1d8f) X GRANT
54 5 1117963059 2 KEY (1c00f3a2b6c5) X GRANT
54 5 1117963059 2 KEY (1a002ffddde0) X GRANT
54 5 0 0 PAG 1:7411 IX GRANT
54 5 1117963059 2 KEY (1900c15268f2) X GRANT
54 5 0 0 PAG 1:10840 IX GRANT
54 5 1181963287 4 PAG 1:10841 IX GRANT
54 5 0 0 PAG 1:10842 IX GRANT
54 5 1117963059 3 KEY (5301059ea5c1) X GRANT
54 5 0 0 PAG 1:10820 IX GRANT
54 5 1181963287 4 PAG 1:10821 IX GRANT
54 5 1181963287 5 PAG 1:10874 IX GRANT
54 5 1181963287 5 PAG 1:10876 IX GRANT
54 5 0 0 PAG 1:10877 IX GRANT
54 5 1181963287 5 PAG 1:10878 IX GRANT
54 5 0 0 PAG 1:10849 IX GRANT
54 5 0 0 PAG 1:10850 IX GRANT
54 5 1117963059 2 KEY (1700f225b712) X GRANT
54 5 1117963059 4 KEY (5301214e6d62) X GRANT
56 5 0 0 DB S GRANT
56 1 85575343 0 TAB IS GRANTAccording to the sp_who2 and sp_lock output, spid54 has the long running
transaction. It has an X lock on Key (5301214e6d62). Spid 52 is trying to
get S lock on the key and got blocked. The read uncommited isolation level
doesn't prevent spid 54 from getting X lock on the key as the X lock is
likely obtained as a result of a modification(insert/delete/update), for
which the transaction can't skip locking. The only way I see to prevent
spid52 from blocking is to apply read uncommited isolation level to spid
52(rather than spid 54), so spid 52 can read dirty uncommitted data modified
by spid 54. Not sure whether your app's semantics allow it though.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"pb648174" <google@.webpaul.net> wrote in message
news:1118351890.044392.321950@.g43g2000cwa.googlegr oups.com...
> I have a very long transaction that runs on the same database that
> other users need to use for existing data. I don't care if they see
> data from the transaction before it is done and am only using the
> transaction because I need a way to roll it back if any errors happen
> during the transaction. Unfortunately all tables affected in the long
> running transaction are completely locked and nobody else can access
> any of the affected tables while it is running. I am using the
> transaction isolation level of read uncommitted, which from my limited
> understanding of isolation levels is the least strict. What can I do to
> prevent this from happening?
> Below is the output from sp_who2 and sp_lock while the process is
> running and another process is being blocked by it.
> SPID Status Login
> HostName BlkBy DBName Command CPUTime
> DiskIO LastBatch ProgramName SPID
> -- ----------
> --------------- ---- --
> ---- ------ --- -- -----
> --------- --
> 1 BACKGROUND sa
> . . NULL LAZY WRITER 0 0
> 06/09 15:42:52 1
> 2 sleeping sa
> . . NULL LOG WRITER 10 0
> 06/09 15:42:52 2
> 3 BACKGROUND sa
> . . master SIGNAL HANDLER 0 0
> 06/09 15:42:52 3
> 4 BACKGROUND sa
> . . NULL LOCK MONITOR 0 0
> 06/09 15:42:52 4
> 5 BACKGROUND sa
> . . master TASK MANAGER 0 5
> 06/09 15:42:52 5
> 6 BACKGROUND sa
> . . master TASK MANAGER 0 0
> 06/09 15:42:52 6
> 7 sleeping sa
> . . NULL CHECKPOINT SLEEP 0 12
> 06/09 15:42:52 7
> 8 BACKGROUND sa
> . . master TASK MANAGER 0 2
> 06/09 15:42:52 8
> 9 BACKGROUND sa
> . . master TASK MANAGER 0 0
> 06/09 15:42:52 9
> 10 BACKGROUND sa
> . . master TASK MANAGER 0 0
> 06/09 15:42:52 10
> 11 BACKGROUND sa
> . . master TASK MANAGER 0 1
> 06/09 15:42:52 11
> 12 BACKGROUND sa
> . . master TASK MANAGER 0 0
> 06/09 15:42:52 12
> 51 sleeping SUPERPABLO\Administrator
> SUPERPABLO . PM AWAITING COMMAND 1813
> 307 06/09 16:10:34 .Net SqlClient Data Provider 51
> 52 sleeping SUPERPABLO\Administrator
> SUPERPABLO 54 PM SELECT 30 5
> 06/09 16:10:16 .Net SqlClient Data Provider 52
> 53 RUNNABLE SUPERPABLO\Administrator
> SUPERPABLO . master SELECT 0 3
> 06/09 16:09:44 SQL Profiler 53
> 54 RUNNABLE SUPERPABLO\Administrator
> SUPERPABLO . PM UPDATE 10095
> 206 06/09 16:10:02 .Net SqlClient Data Provider 54
> 56 RUNNABLE SUPERPABLO\Administrator
> SUPERPABLO . PM SELECT INTO 151 27
> 06/09 16:10:33 SQL Query Analyzer 56
> (17 row(s) affected)
> spid dbid ObjId IndId Type Resource Mode Status
> -- -- ---- -- -- ------ --- --
> 51 5 0 0 DB S GRANT
> 52 5 0 0 DB S GRANT
> 52 5 1117963059 4 PAG 1:7401 IS GRANT
> 52 5 1117963059 4 KEY (5301214e6d62) S WAIT
> 52 5 1117963059 0 TAB IS GRANT
> 54 5 1117963059 0 TAB IX GRANT
> 54 5 1852025829 0 TAB IX GRANT
> 54 5 1181963287 3 PAG 1:9017 IX GRANT
> 54 5 1117963059 4 KEY (5301934930a4) X GRANT
> 54 5 1117963059 3 KEY (530187fc93f3) X GRANT
> 54 5 1117963059 4 KEY (530154df71eb) X GRANT
> 54 5 0 0 DB [BULK-OP-LOG] NULL GRANT
> 54 5 0 0 FIL 2:0:d U GRANT
> 54 5 1117963059 2 KEY (1d0096c50a7d) X GRANT
> 54 5 1117963059 2 KEY (1b004a9a6158) X GRANT
> 54 5 1117963059 2 KEY (1800a435d44a) X GRANT
> 54 5 1181963287 6 PAG 1:8745 IX GRANT
> 54 5 1181963287 4 PAG 1:8923 IX GRANT
> 54 5 1181963287 2 PAG 1:8937 IX GRANT
> 54 5 1117963059 4 KEY (5301112b0696) X GRANT
> 54 5 0 0 PAG 1:10889 IX GRANT
> 54 5 1181963287 5 PAG 1:8859 IX GRANT
> 54 5 1181963287 6 PAG 1:10888 IX GRANT
> 54 5 0 0 PAG 1:10891 IX GRANT
> 54 5 0 0 PAG 1:10893 IX GRANT
> 54 5 0 0 PAG 1:10892 IX GRANT
> 54 5 0 0 PAG 1:10894 IX GRANT
> 54 5 0 0 PAG 1:10882 IX GRANT
> 54 5 1117963059 3 KEY (530135fbce35) X GRANT
> 54 5 1117963059 0 RID 1:7387:57 X GRANT
> 54 5 1117963059 0 RID 1:7387:59 X GRANT
> 54 5 1117963059 0 RID 1:7387:61 X GRANT
> 54 5 1117963059 3 KEY (5301406ad2bc) X GRANT
> 54 5 1117963059 4 PAG 1:7401 IX GRANT
> 54 5 0 0 PAG 1:7387 IX GRANT
> 54 5 1117963059 2 PAG 1:7389 IX GRANT
> 54 5 1117963059 3 PAG 1:7391 IX GRANT
> 54 5 1117963059 0 RID 1:7387:10 X GRANT
> 54 5 1117963059 0 RID 1:7387:56 X GRANT
> 54 5 1117963059 0 RID 1:7387:58 X GRANT
> 54 5 1117963059 0 RID 1:7387:60 X GRANT
> 54 5 1117963059 3 KEY (530144afbed8) X GRANT
> 54 5 1117963059 4 KEY (530115ee6af2) X GRANT
> 54 5 1117963059 3 KEY (5301c6cd88ea) X GRANT
> 54 5 1149963173 0 TAB IX GRANT
> 54 5 1181963287 0 TAB X GRANT
> 54 5 1117963059 4 KEY (5301d2782bbd) X GRANT
> 54 5 1117963059 3 KEY (5301015bc9a5) X GRANT
> 54 5 0 0 DB S GRANT
> 54 5 0 0 DB [BULK-OP-DB] NULL GRANT
> 54 5 1117963059 4 KEY (5301501a1d8f) X GRANT
> 54 5 1117963059 2 KEY (1c00f3a2b6c5) X GRANT
> 54 5 1117963059 2 KEY (1a002ffddde0) X GRANT
> 54 5 0 0 PAG 1:7411 IX GRANT
> 54 5 1117963059 2 KEY (1900c15268f2) X GRANT
> 54 5 0 0 PAG 1:10840 IX GRANT
> 54 5 1181963287 4 PAG 1:10841 IX GRANT
> 54 5 0 0 PAG 1:10842 IX GRANT
> 54 5 1117963059 3 KEY (5301059ea5c1) X GRANT
> 54 5 0 0 PAG 1:10820 IX GRANT
> 54 5 1181963287 4 PAG 1:10821 IX GRANT
> 54 5 1181963287 5 PAG 1:10874 IX GRANT
> 54 5 1181963287 5 PAG 1:10876 IX GRANT
> 54 5 0 0 PAG 1:10877 IX GRANT
> 54 5 1181963287 5 PAG 1:10878 IX GRANT
> 54 5 0 0 PAG 1:10849 IX GRANT
> 54 5 0 0 PAG 1:10850 IX GRANT
> 54 5 1117963059 2 KEY (1700f225b712) X GRANT
> 54 5 1117963059 4 KEY (5301214e6d62) X GRANT
> 56 5 0 0 DB S GRANT
> 56 1 85575343 0 TAB IS GRANT|||The thing is though, the long running transaction is the only
transaction running. All the other processes are just running non
transactional queries.|||I've cheated with large INSERT statements by inserting blocks of them
in a batch inside a transaction, with a WAITFOR DELAY of a few seconds.
This makes the individual transactions shorter, and allows the other
statements to sneak in and get some work done while doing it. I've
found that inserting 5 batches of of increasing percentage sizes (20,
25, 30, 50, 100) usually ends up inserting near equivelent batch sizes.

The psuedo-code for this method would be something like the following:

BEGIN TRANSACTION

INSERT INTO holdingTable
SELECT PrimaryKey
FROM Table

-- first 2000 records of 10000 records
INSERT INTO DestinationTable
SELECT TOP 20 PERCENT Cols
FROM SourceTable JOIN HoldingTable ON a=b
ORDER BY HoldingTable PrimaryKey

DELETE
FROM HoldingTable
WHERE PrimaryKey IN (SELECT TOP 20 PERCENT PrimaryKey
FROM HoldingTable ORDER BY Primary KEY)

--pause for 10 seconds
WAITFOR DELAY '00:00:10'

--first 2000 records of remaining 8000 records
INSERT INTO DestinationTable
SELECT TOP 25 PERCENT Cols
FROM SourceTable JOIN HoldingTable ON a=b
ORDER BY HoldingTable PrimaryKey

DELETE
FROM HoldingTable
WHERE PrimaryKey IN (SELECT TOP 25 PERCENT PrimaryKey
FROM HoldingTable ORDER BY Primary KEY)

WAITFOR DELAY '00:00:10'

....

--remaining records
INSERT INTO DestinationTable
SELECT TOP 100 PERCENT Cols
FROM SourceTable JOIN HoldingTable ON a=b
ORDER BY HoldingTable PrimaryKey

drop HoldingTable --assumes it's a temp table or table variable

COMMIT TRANSACTION

A similar concept should work for UPDATES.

There may be other solutions out there; this works for me.|||pb648174 wrote:
> The thing is though, the long running transaction is the only
> transaction running. All the other processes are just running non
> transactional queries.

If you read the BOL section "SET IMPLICIT_TRANSACTIONS", You should be
able to see that *every* statement is part of a transaction - there are
no "non transactional" queries.

What actually happens when you execute a query when there is no active
transaction is that a new transaction is started. When the query
completes, the behaviour is affected by the "IMPLICIT_TRANSACTIONS"
settings. When it is OFF (the default), the transaction is
automatically committed, provided the query caused no error. When it is
ON, the transaction is kept open, and must be manually committed at a
later stage.

So, on to your problem. You *may* be able to fix it by issueing the
"SET TRANSACTION ISOLATION LEVEL" statement on each of the other
processes connections. by setting it to READ UNCOMMITTED, this will
affect every transaction which is started by this connection (including
these automatic transactions which your statements are
opening/committing).

This will mean that each of these processes may see data in any
imaginable (read: illegal) state. If you are sure that it is safe for
these processes to see (and process) such data, then this may be the
way to go.

HTH,

Damien|||This large copy is happening from application code and involved 30 or
40 separate stored procedure being called. It is being done
asynchronously via a c# web app and there is a pause of 2 seconds
before every copy command. Unfortunately this two second pause doesn't
help because of the locking situation, it just makes everything block
for longer.

Isn't this a somewhat common scenario? What is the standard way around
this situation? Can somebody tell me why the entire table is being
locked instead of just the new records? I have no problem with new
records being locked, but I do not want it to lock the records it is
reading or the entire table.

Would disaster ensue if I submitted the following command?
SP_INDEXOPTION 'table_name', 'AllowTableLocks', FALSE
GO|||pb648174 wrote:
> The thing is though, the long running transaction is the only
> transaction running. All the other processes are just running non
> transactional queries.

If you read the BOL section "SET IMPLICIT_TRANSACTIONS", You should be
able to see that *every* statement is part of a transaction - there are
no "non transactional" queries.

What actually happens when you execute a query when there is no active
transaction is that a new transaction is started. When the query
completes, the behaviour is affected by the "IMPLICIT_TRANSACTIONS"
settings. When it is OFF (the default), the transaction is
automatically committed, provided the query caused no error. When it is
ON, the transaction is kept open, and must be manually committed at a
later stage.

So, on to your problem. You *may* be able to fix it by issueing the
"SET TRANSACTION ISOLATION LEVEL" statement on each of the other
processes connections. by setting it to READ UNCOMMITTED, this will
affect every transaction which is started by this connection (including
these automatic transactions which your statements are
opening/committing).

This will mean that each of these processes may see data in any
imaginable (read: illegal) state. If you are sure that it is safe for
these processes to see (and process) such data, then this may be the
way to go.

HTH,

Damien|||This large copy is happening from application code and involved 30 or
40 separate stored procedure being called. It is being done
asynchronously via a c# web app and there is a pause of 2 seconds
before every copy command. Unfortunately this two second pause doesn't
help because of the locking situation, it just makes everything block
for longer.

Isn't this a somewhat common scenario? What is the standard way around
this situation? Can somebody tell me why the entire table is being
locked instead of just the new records? I have no problem with new
records being locked, but I do not want it to lock the records it is
reading or the entire table.

Would disaster ensue if I submitted the following command?
SP_INDEXOPTION 'table_name', 'AllowTableLocks', FALSE
GO|||pb648174 (google@.webpaul.net) writes:
> This large copy is happening from application code and involved 30 or
> 40 separate stored procedure being called. It is being done
> asynchronously via a c# web app and there is a pause of 2 seconds
> before every copy command. Unfortunately this two second pause doesn't
> help because of the locking situation, it just makes everything block
> for longer.
> Isn't this a somewhat common scenario? What is the standard way around
> this situation? Can somebody tell me why the entire table is being
> locked instead of just the new records? I have no problem with new
> records being locked, but I do not want it to lock the records it is
> reading or the entire table.
> Would disaster ensue if I submitted the following command?
> SP_INDEXOPTION 'table_name', 'AllowTableLocks', FALSE

It would not have any effect.

The table you are inserting into is *not* completely locked. You posted
an output from sp_lock. According to this output, spid 54 holds an
exclusive table lock on table 1181963287, but that does not seem to
the main theatre for your problem. (You can use
"SELECT object_name(1181963287)" to see which table this is.)

Instead, the main part of the show appears to be table 1117963059, and
spid 54 does not any table locks on this table. But it does hold locks
on all newly inserted rows, as well as all new inserted index nodes.
Process 52 is blocked by spid 54, and this is why:

52 5 1117963059 4 KEY (5301214e6d62) S WAIT

Spid 52 is trying to get a shared lock on an index key, but is blocked.

Assume that the query spid 52 has submitted is "show how many items of
widget X we sold last week", and the optimizer decides to use the
non-clustered index over widget_id to access the sales numbers. And
among the new rows you insert, there are rows with widget_id in question.
When spid 52 tries to access those index nodes, it will be blocked.

So while you don't get a table locked, it is not as simple that other
processes can just read the existing data, and don't bother about the
new data.

The best way would be look into how to shorten the transaction length.
It sounds as if row are being inserted one-by-one, in which case there
are lots of possibilities for improvements.

Another possibility is insert the data into a staging table, and the
insert with one big INSERT statement at the end. But if that is many
rows, that could still block for considerable time.

A further development is to use partitioned views. Here, too, you copy
the data into an empty table, that no other process sees. The processes
that reads data, access the view, not the table. Once the table has been
loaded, you change the view definition to include the new table.

Then you can of course, use READ UNCOMMITTED for the readers, but then
may get some funky results that is not consistent. (READ UNCOMMITTED
on the writer has no effect.) A better alternative may be READPAST,
in which cases locked rows are simply skipped. But this is only a locking
hint, and is not settable as a transaction level.

In SQL 2005, you would probably use the new SNAPSHOT isolattion level,
in which case the readers would see the data as it was before the long
transaction started.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pb648174 (google@.webpaul.net) writes:
> This large copy is happening from application code and involved 30 or
> 40 separate stored procedure being called. It is being done
> asynchronously via a c# web app and there is a pause of 2 seconds
> before every copy command. Unfortunately this two second pause doesn't
> help because of the locking situation, it just makes everything block
> for longer.
> Isn't this a somewhat common scenario? What is the standard way around
> this situation? Can somebody tell me why the entire table is being
> locked instead of just the new records? I have no problem with new
> records being locked, but I do not want it to lock the records it is
> reading or the entire table.
> Would disaster ensue if I submitted the following command?
> SP_INDEXOPTION 'table_name', 'AllowTableLocks', FALSE

It would not have any effect.

The table you are inserting into is *not* completely locked. You posted
an output from sp_lock. According to this output, spid 54 holds an
exclusive table lock on table 1181963287, but that does not seem to
the main theatre for your problem. (You can use
"SELECT object_name(1181963287)" to see which table this is.)

Instead, the main part of the show appears to be table 1117963059, and
spid 54 does not any table locks on this table. But it does hold locks
on all newly inserted rows, as well as all new inserted index nodes.
Process 52 is blocked by spid 54, and this is why:

52 5 1117963059 4 KEY (5301214e6d62) S WAIT

Spid 52 is trying to get a shared lock on an index key, but is blocked.

Assume that the query spid 52 has submitted is "show how many items of
widget X we sold last week", and the optimizer decides to use the
non-clustered index over widget_id to access the sales numbers. And
among the new rows you insert, there are rows with widget_id in question.
When spid 52 tries to access those index nodes, it will be blocked.

So while you don't get a table locked, it is not as simple that other
processes can just read the existing data, and don't bother about the
new data.

The best way would be look into how to shorten the transaction length.
It sounds as if row are being inserted one-by-one, in which case there
are lots of possibilities for improvements.

Another possibility is insert the data into a staging table, and the
insert with one big INSERT statement at the end. But if that is many
rows, that could still block for considerable time.

A further development is to use partitioned views. Here, too, you copy
the data into an empty table, that no other process sees. The processes
that reads data, access the view, not the table. Once the table has been
loaded, you change the view definition to include the new table.

Then you can of course, use READ UNCOMMITTED for the readers, but then
may get some funky results that is not consistent. (READ UNCOMMITTED
on the writer has no effect.) A better alternative may be READPAST,
in which cases locked rows are simply skipped. But this is only a locking
hint, and is not settable as a transaction level.

In SQL 2005, you would probably use the new SNAPSHOT isolattion level,
in which case the readers would see the data as it was before the long
transaction started.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Wow, that helped clear things up quite a bit, thanks. Can I perhaps do
some work on the indexes in order to fix it? Would changing to a
clustered index make it less likely to block?

The rows aren't being inserted one row at a time, but they are being
done one "project" at a time(maybe a few thousand records), with pretty
standard Insert Into..Select statements. I have my application pausing
for a few seconds between projects to try and reduce stress on the
server and give other processes a chance for freedom, but from what you
are saying, perhaps I need to remove the wait times in order to get the
transaction over as quickly as possible.|||Wow, that helped clear things up quite a bit, thanks. Can I perhaps do
some work on the indexes in order to fix it? Would changing to a
clustered index make it less likely to block?

The rows aren't being inserted one row at a time, but they are being
done one "project" at a time(maybe a few thousand records), with pretty
standard Insert Into..Select statements. I have my application pausing
for a few seconds between projects to try and reduce stress on the
server and give other processes a chance for freedom, but from what you
are saying, perhaps I need to remove the wait times in order to get the
transaction over as quickly as possible.|||On 9 Jun 2005 14:18:10 -0700, "pb648174" <google@.webpaul.net> wrote:

>I have a very long transaction that runs on the same database that
>other users need to use for existing data. I don't care if they see
>data from the transaction before it is done and am only using the
>transaction because I need a way to roll it back if any errors happen
>during the transaction. Unfortunately all tables affected in the long
>running transaction are completely locked and nobody else can access
>any of the affected tables while it is running. I am using the
>transaction isolation level of read uncommitted, which from my limited
>understanding of isolation levels is the least strict. What can I do to
>prevent this from happening?

Personally, I don't abide long-running database transactions in my
applications. If the issue comes up, I reingineer until the need for the
long-running transaction goes away.

Here's one approach I've used...
1. For each table that will be affected, add 2 links to batch records, one for
initial, and one for final.
2. Add a table of batches with a batch ID, and a status that may be pending or
completed.
3. For each batch process, create a new batch record with a status of pending.
4. When writing to the database, point the initial batch of each new record to
the batch record, and point the final batch of each deleted record to the
batch record. Do not modify existing records - instead, add a new modifed
copy, and finalize the old copy.
5. Once all batch updates are completed, change the batch record status from
pending to completed.

When querying the data, to see only data that is current, simply join to the
batch table, and exclude any records that have final batch links to a
completed batch record. Every once in a while, purge these outdated records,
so they don't pile up, and slow down the system.

If a transaction fails, you can roll back by deleting the records with initial
batch references to your batch record, and set any final batch references to
your batch record back to Null, then delete your batch record.

This approach only works if there can only be one batch processor at a time
affecting a particular group of tables, but it has the benefit of not
requiring server transactions to be maintained for long periods of time. A
batch could take several days and have no negative impact on anything. You
could even halt the batch, and continue it on a different meachine if
necessary.|||On 9 Jun 2005 14:18:10 -0700, "pb648174" <google@.webpaul.net> wrote:

>I have a very long transaction that runs on the same database that
>other users need to use for existing data. I don't care if they see
>data from the transaction before it is done and am only using the
>transaction because I need a way to roll it back if any errors happen
>during the transaction. Unfortunately all tables affected in the long
>running transaction are completely locked and nobody else can access
>any of the affected tables while it is running. I am using the
>transaction isolation level of read uncommitted, which from my limited
>understanding of isolation levels is the least strict. What can I do to
>prevent this from happening?

Personally, I don't abide long-running database transactions in my
applications. If the issue comes up, I reingineer until the need for the
long-running transaction goes away.

Here's one approach I've used...
1. For each table that will be affected, add 2 links to batch records, one for
initial, and one for final.
2. Add a table of batches with a batch ID, and a status that may be pending or
completed.
3. For each batch process, create a new batch record with a status of pending.
4. When writing to the database, point the initial batch of each new record to
the batch record, and point the final batch of each deleted record to the
batch record. Do not modify existing records - instead, add a new modifed
copy, and finalize the old copy.
5. Once all batch updates are completed, change the batch record status from
pending to completed.

When querying the data, to see only data that is current, simply join to the
batch table, and exclude any records that have final batch links to a
completed batch record. Every once in a while, purge these outdated records,
so they don't pile up, and slow down the system.

If a transaction fails, you can roll back by deleting the records with initial
batch references to your batch record, and set any final batch references to
your batch record back to Null, then delete your batch record.

This approach only works if there can only be one batch processor at a time
affecting a particular group of tables, but it has the benefit of not
requiring server transactions to be maintained for long periods of time. A
batch could take several days and have no negative impact on anything. You
could even halt the batch, and continue it on a different meachine if
necessary.|||pb648174 (google@.webpaul.net) writes:
> Wow, that helped clear things up quite a bit, thanks. Can I perhaps do
> some work on the indexes in order to fix it? Would changing to a
> clustered index make it less likely to block?

I sort of assumed that all new rows were inserted at the end of the
clustered index. But if the rows you insert are not aligned with the
clustered index, then the problem becomes a lot worse. Any process
that finds itself in need ot a table scan would be blocked.

> The rows aren't being inserted one row at a time, but they are being
> done one "project" at a time(maybe a few thousand records), with pretty
> standard Insert Into..Select statements. I have my application pausing
> for a few seconds between projects to try and reduce stress on the
> server and give other processes a chance for freedom, but from what you
> are saying, perhaps I need to remove the wait times in order to get the
> transaction over as quickly as possible.

Had you committed after each batch, the pause could make some sense. But
if you don't commit until the end, then you should get away with those
pauses.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pb648174 (google@.webpaul.net) writes:
> Wow, that helped clear things up quite a bit, thanks. Can I perhaps do
> some work on the indexes in order to fix it? Would changing to a
> clustered index make it less likely to block?

I sort of assumed that all new rows were inserted at the end of the
clustered index. But if the rows you insert are not aligned with the
clustered index, then the problem becomes a lot worse. Any process
that finds itself in need ot a table scan would be blocked.

> The rows aren't being inserted one row at a time, but they are being
> done one "project" at a time(maybe a few thousand records), with pretty
> standard Insert Into..Select statements. I have my application pausing
> for a few seconds between projects to try and reduce stress on the
> server and give other processes a chance for freedom, but from what you
> are saying, perhaps I need to remove the wait times in order to get the
> transaction over as quickly as possible.

Had you committed after each batch, the pause could make some sense. But
if you don't commit until the end, then you should get away with those
pauses.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The only thing about taking out the pauses is that it hammers on the
database so hard, it pegs the CPU.. With the pauses other processes
have a chance to go forward, but with this locking it doesn't seem to
matter.|||The only thing about taking out the pauses is that it hammers on the
database so hard, it pegs the CPU.. With the pauses other processes
have a chance to go forward, but with this locking it doesn't seem to
matter.|||Isn't this "batch" approach sort of ridiculous, given that databases
are supposed to be able to run transactions?? We have hundreds of
tables and the above approach would be extremely onerous. I don't think
it would work anyway since there are identity columns in most of the
tables which need to match up between parent and child relationships.
If somebody inserted a single record while the transaction was running,
that would hose the whole process.|||Isn't this "batch" approach sort of ridiculous, given that databases
are supposed to be able to run transactions?? We have hundreds of
tables and the above approach would be extremely onerous. I don't think
it would work anyway since there are identity columns in most of the
tables which need to match up between parent and child relationships.
If somebody inserted a single record while the transaction was running,
that would hose the whole process.|||This is interesting - adding a clustered index on the identity column
fixed the previous problem, so I picked one module (which has about 5
or 6 tables) to see if I could free up that module while the copy
process was running. I went and put clustered indexes on all the
identity columns of those tables, but now another table has a locking
problem, although this time, not on the index. The output from that
line from sp_lock is below:

54 8 2101582525 1 PAG 1:26568 S WAIT

So now it is a page lock, which from what I've read is a group of
records. That seems resonable given that I am copying large amounts of
data, but why is it stopping my other process?|||This is interesting - adding a clustered index on the identity column
fixed the previous problem, so I picked one module (which has about 5
or 6 tables) to see if I could free up that module while the copy
process was running. I went and put clustered indexes on all the
identity columns of those tables, but now another table has a locking
problem, although this time, not on the index. The output from that
line from sp_lock is below:

54 8 2101582525 1 PAG 1:26568 S WAIT

So now it is a page lock, which from what I've read is a group of
records. That seems resonable given that I am copying large amounts of
data, but why is it stopping my other process?|||pb648174 (google@.webpaul.net) writes:
> The only thing about taking out the pauses is that it hammers on the
> database so hard, it pegs the CPU..

CPU:s are humans. Just keep them working!

(But, OK, if you see a puff of smoke, it's probably time for a pause.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pb648174 (google@.webpaul.net) writes:
> The only thing about taking out the pauses is that it hammers on the
> database so hard, it pegs the CPU..

CPU:s are humans. Just keep them working!

(But, OK, if you see a puff of smoke, it's probably time for a pause.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pb648174 (google@.webpaul.net) writes:
> The only thing about taking out the pauses is that it hammers on the
> database so hard, it pegs the CPU.. With the pauses other processes
> have a chance to go forward, but with this locking it doesn't seem to
> matter.

On a little more serious note... if the machine has more than one
CPU, you could consider to reduce the degree of parallelism, to leave
some CPUs to the rest of the pack. You do this by adding

OPTION (MAXDOP n)

at the end of the query. In fact you can even try 1, to abort parallelism
entirely. SQL Server appears to be over-optimistic by the benefits of
parallelism, and non-parallel plans may be better.

If your CPU(s) are hyper-threaded, you should server-configuration
parameter "Max degree of parallelism" to the number of physical CPUs.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pb648174 (google@.webpaul.net) writes:
> The only thing about taking out the pauses is that it hammers on the
> database so hard, it pegs the CPU.. With the pauses other processes
> have a chance to go forward, but with this locking it doesn't seem to
> matter.

On a little more serious note... if the machine has more than one
CPU, you could consider to reduce the degree of parallelism, to leave
some CPUs to the rest of the pack. You do this by adding

OPTION (MAXDOP n)

at the end of the query. In fact you can even try 1, to abort parallelism
entirely. SQL Server appears to be over-optimistic by the benefits of
parallelism, and non-parallel plans may be better.

If your CPU(s) are hyper-threaded, you should server-configuration
parameter "Max degree of parallelism" to the number of physical CPUs.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pb648174 (google@.webpaul.net) writes:
> This is interesting - adding a clustered index on the identity column
> fixed the previous problem, so I picked one module (which has about 5
> or 6 tables) to see if I could free up that module while the copy
> process was running. I went and put clustered indexes on all the
> identity columns of those tables, but now another table has a locking
> problem, although this time, not on the index. The output from that
> line from sp_lock is below:
> 54 8 2101582525 1 PAG 1:26568 S WAIT
> So now it is a page lock, which from what I've read is a group of
> records. That seems resonable given that I am copying large amounts of
> data, but why is it stopping my other process?

Lacking telepathic abilities, I can't say why.

What you could try is to use aba_lockinfo, which you find on my web
site, http://www.sommarskog.se/sqlutil/aba_lockinfo.html. This procedure
give you an overview active process, which object they lock, and also
current statements. This gives a little better idea of what is going
on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pb648174 (google@.webpaul.net) writes:
> This is interesting - adding a clustered index on the identity column
> fixed the previous problem, so I picked one module (which has about 5
> or 6 tables) to see if I could free up that module while the copy
> process was running. I went and put clustered indexes on all the
> identity columns of those tables, but now another table has a locking
> problem, although this time, not on the index. The output from that
> line from sp_lock is below:
> 54 8 2101582525 1 PAG 1:26568 S WAIT
> So now it is a page lock, which from what I've read is a group of
> records. That seems resonable given that I am copying large amounts of
> data, but why is it stopping my other process?

Lacking telepathic abilities, I can't say why.

What you could try is to use aba_lockinfo, which you find on my web
site, http://www.sommarskog.se/sqlutil/aba_lockinfo.html. This procedure
give you an overview active process, which object they lock, and also
current statements. This gives a little better idea of what is going
on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have no idea if this will help or not...
A vendor gave me a giant sql select that has 200 fields and 25 joins.
The sample of the join:
LEFT OUTER JOIN view_TPSEnum_10145 WITH(NOLOCK) ON
view_TPSEnum_10145.nIndex = [tblObjectType5001_1].[FldNumeric21251]
I'm told the WITH(NOLOCK) wont lock the source table *shrug*. Read the
docs that it allows dirty reads and is used only for selects.|||I have no idea if this will help or not...
A vendor gave me a giant sql select that has 200 fields and 25 joins.
The sample of the join:
LEFT OUTER JOIN view_TPSEnum_10145 WITH(NOLOCK) ON
view_TPSEnum_10145.nIndex = [tblObjectType5001_1].[FldNumeric21251]
I'm told the WITH(NOLOCK) wont lock the source table *shrug*. Read the
docs that it allows dirty reads and is used only for selects.

Long running report fails with http error

Hi,
I have a report that takes about an hour to run. When I run it, it runs for
an hour or so with "Generating Report", then the result page is the standard
IE "Action Canceled" page, and there is a javascript error. The javascript
error is Permission denied.
All other reports running on this server (which don't take as long to run)
are fine. I've checked IIS settings and blown the connection timeout to 9000
seconds.
It's as if the report is taking too long...
I've set all the URL stuff in the config files, but like I say, its only one
out of about a hundred reports that is failing.
Any ideas?
SteveHi,
I think this is something to do with your queries some where it is not
optimized. Just check whether the same query / procedure runs in your
management studio.
Amarnath
"Steve" wrote:
> Hi,
> I have a report that takes about an hour to run. When I run it, it runs for
> an hour or so with "Generating Report", then the result page is the standard
> IE "Action Canceled" page, and there is a javascript error. The javascript
> error is Permission denied.
> All other reports running on this server (which don't take as long to run)
> are fine. I've checked IIS settings and blown the connection timeout to 9000
> seconds.
> It's as if the report is taking too long...
> I've set all the URL stuff in the config files, but like I say, its only one
> out of about a hundred reports that is failing.
> Any ideas?
> Steve

Long running query

Hello group,
We have a query that calculates certain dates, etc. It runs very slow on
this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
Windows 2003 server and SQL 2000. If you copy the database over to another
server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
and run the same query it runs in 2-3 minutes. This is a huge gap of time
and I don't see a whole lot of performance changes using perfmon on the
troubled server.
Any ideas what I can use to troubleshoot this? Do you think this may be
hardware related or database or ?
TIA,
Jason
There can be many reasons. Did you verify using sp_who that this query is
not getting blocked? Did you monitor the physical disk counters to see if
there are any issues with disk subsystem performance?
What about the execution plans? Did you compare the execution plan of the
queries between your server and workstation? This will help in determining
any missing indexes or out of date stats.
Worth checking the fragmentation on your bigger tables and also updating the
statistics.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jason King" <jasonk@.bham.wednet.edu> wrote in message
news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Hello group,
> We have a query that calculates certain dates, etc. It runs very slow on
> this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
> Windows 2003 server and SQL 2000. If you copy the database over to another
> server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
> and run the same query it runs in 2-3 minutes. This is a huge gap of time
> and I don't see a whole lot of performance changes using perfmon on the
> troubled server.
> Any ideas what I can use to troubleshoot this? Do you think this may be
> hardware related or database or ?
> TIA,
> Jason
>
|||Thanks. We found the problem. It was due to passing NULL parameters within
the SP for retrieving certain dates. If we call the dates first then execute
the procedure without any NULL parameters, it runs very fast.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uIDsfRnOFHA.3280@.TK2MSFTNGP10.phx.gbl...
> There can be many reasons. Did you verify using sp_who that this query is
> not getting blocked? Did you monitor the physical disk counters to see if
> there are any issues with disk subsystem performance?
> What about the execution plans? Did you compare the execution plan of the
> queries between your server and workstation? This will help in determining
> any missing indexes or out of date stats.
> Worth checking the fragmentation on your bigger tables and also updating
the[vbcol=seagreen]
> statistics.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Jason King" <jasonk@.bham.wednet.edu> wrote in message
> news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
running[vbcol=seagreen]
another[vbcol=seagreen]
ram)[vbcol=seagreen]
time
>
sql

Long running query

Hello group,
We have a query that calculates certain dates, etc. It runs very slow on
this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
Windows 2003 server and SQL 2000. If you copy the database over to another
server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
and run the same query it runs in 2-3 minutes. This is a huge gap of time
and I don't see a whole lot of performance changes using perfmon on the
troubled server.
Any ideas what I can use to troubleshoot this? Do you think this may be
hardware related or database or '
TIA,
JasonThere can be many reasons. Did you verify using sp_who that this query is
not getting blocked? Did you monitor the physical disk counters to see if
there are any issues with disk subsystem performance?
What about the execution plans? Did you compare the execution plan of the
queries between your server and workstation? This will help in determining
any missing indexes or out of date stats.
Worth checking the fragmentation on your bigger tables and also updating the
statistics.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jason King" <jasonk@.bham.wednet.edu> wrote in message
news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Hello group,
> We have a query that calculates certain dates, etc. It runs very slow on
> this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
> Windows 2003 server and SQL 2000. If you copy the database over to another
> server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
> and run the same query it runs in 2-3 minutes. This is a huge gap of time
> and I don't see a whole lot of performance changes using perfmon on the
> troubled server.
> Any ideas what I can use to troubleshoot this? Do you think this may be
> hardware related or database or '
> TIA,
> Jason
>|||Thanks. We found the problem. It was due to passing NULL parameters within
the SP for retrieving certain dates. If we call the dates first then execute
the procedure without any NULL parameters, it runs very fast.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uIDsfRnOFHA.3280@.TK2MSFTNGP10.phx.gbl...
> There can be many reasons. Did you verify using sp_who that this query is
> not getting blocked? Did you monitor the physical disk counters to see if
> there are any issues with disk subsystem performance?
> What about the execution plans? Did you compare the execution plan of the
> queries between your server and workstation? This will help in determining
> any missing indexes or out of date stats.
> Worth checking the fragmentation on your bigger tables and also updating
the
> statistics.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Jason King" <jasonk@.bham.wednet.edu> wrote in message
> news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
running[vbcol=seagreen]
another[vbcol=seagreen]
ram)[vbcol=seagreen]
time[vbcol=seagreen]
>

Long running query

Hello group,
We have a query that calculates certain dates, etc. It runs very slow on
this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
Windows 2003 server and SQL 2000. If you copy the database over to another
server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
and run the same query it runs in 2-3 minutes. This is a huge gap of time
and I don't see a whole lot of performance changes using perfmon on the
troubled server.
Any ideas what I can use to troubleshoot this? Do you think this may be
hardware related or database or '
TIA,
JasonThere can be many reasons. Did you verify using sp_who that this query is
not getting blocked? Did you monitor the physical disk counters to see if
there are any issues with disk subsystem performance?
What about the execution plans? Did you compare the execution plan of the
queries between your server and workstation? This will help in determining
any missing indexes or out of date stats.
Worth checking the fragmentation on your bigger tables and also updating the
statistics.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jason King" <jasonk@.bham.wednet.edu> wrote in message
news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Hello group,
> We have a query that calculates certain dates, etc. It runs very slow on
> this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
> Windows 2003 server and SQL 2000. If you copy the database over to another
> server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
> and run the same query it runs in 2-3 minutes. This is a huge gap of time
> and I don't see a whole lot of performance changes using perfmon on the
> troubled server.
> Any ideas what I can use to troubleshoot this? Do you think this may be
> hardware related or database or '
> TIA,
> Jason
>|||Thanks. We found the problem. It was due to passing NULL parameters within
the SP for retrieving certain dates. If we call the dates first then execute
the procedure without any NULL parameters, it runs very fast.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uIDsfRnOFHA.3280@.TK2MSFTNGP10.phx.gbl...
> There can be many reasons. Did you verify using sp_who that this query is
> not getting blocked? Did you monitor the physical disk counters to see if
> there are any issues with disk subsystem performance?
> What about the execution plans? Did you compare the execution plan of the
> queries between your server and workstation? This will help in determining
> any missing indexes or out of date stats.
> Worth checking the fragmentation on your bigger tables and also updating
the
> statistics.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Jason King" <jasonk@.bham.wednet.edu> wrote in message
> news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
> > Hello group,
> >
> > We have a query that calculates certain dates, etc. It runs very slow on
> > this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM
running
> > Windows 2003 server and SQL 2000. If you copy the database over to
another
> > server or XP workstation (even running a single 2.6 GHZ CPU with 1gb
ram)
> > and run the same query it runs in 2-3 minutes. This is a huge gap of
time
> > and I don't see a whole lot of performance changes using perfmon on the
> > troubled server.
> >
> > Any ideas what I can use to troubleshoot this? Do you think this may be
> > hardware related or database or '
> >
> > TIA,
> >
> > Jason
> >
> >
>

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 running jobs crashing the box

Ive a got an sql server that crashes whenever a job runs for a minimum of
5 - 10 minutes. It doesnt happen 100% of the time, probably 80-90%. It
doesnt matter if the job is scheduled, run manually, a backup, a reindex, a
data transfer, whatever, if it runs between 5 and 10 minutes, the box
crashes. Of course it happens most frquently during backups as they are run
the most. All ideas are appreicated.
--
SQL2K SP3
TIA, ChrisR"ChrisR" <bla@.noemail.com> wrote in message
news:u%23$pQDT4EHA.936@.TK2MSFTNGP12.phx.gbl...
> Ive a got an sql server that crashes whenever a job runs for a minimum of
> 5 - 10 minutes. It doesnt happen 100% of the time, probably 80-90%. It
> doesnt matter if the job is scheduled, run manually, a backup, a reindex,
> a
> data transfer, whatever, if it runs between 5 and 10 minutes, the box
> crashes. Of course it happens most frquently during backups as they are
> run
> the most. All ideas are appreicated.
> --
> SQL2K SP3
> TIA, ChrisR
>
What do you mean by crashes? Does the O/S die and you get a blue screen?
Does SQL Server stop running, but the O/S is okay?
What version of O/S, what version of SQL Server? What service packs have
you installed.
What are you using for backups? Is it a standard SQL Server backup or are
you using some other program?
More information would be helpful.
Rick Sawtell
MCT, MCSD, MCDBA|||> What do you mean by crashes? Does the O/S die and you get a blue screen?
> Does SQL Server stop running, but the O/S is okay?
Blue screen.
> What version of O/S, what version of SQL Server? What service packs have
> you installed.
Win2K sp4
SQL2K sp3a
> What are you using for backups? Is it a standard SQL Server backup or are
> you using some other program?
Standard SQL.
Let me know if more info is required.
Thanks, ChrisR
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:OZwiGUT4EHA.1452@.TK2MSFTNGP11.phx.gbl...
> "ChrisR" <bla@.noemail.com> wrote in message
> news:u%23$pQDT4EHA.936@.TK2MSFTNGP12.phx.gbl...
> > Ive a got an sql server that crashes whenever a job runs for a minimum
of
> > 5 - 10 minutes. It doesnt happen 100% of the time, probably 80-90%. It
> > doesnt matter if the job is scheduled, run manually, a backup, a
reindex,
> > a
> > data transfer, whatever, if it runs between 5 and 10 minutes, the box
> > crashes. Of course it happens most frquently during backups as they are
> > run
> > the most. All ideas are appreicated.
> >
> > --
> > SQL2K SP3
> >
> > TIA, ChrisR
> >
> >
> What do you mean by crashes? Does the O/S die and you get a blue screen?
> Does SQL Server stop running, but the O/S is okay?
> What version of O/S, what version of SQL Server? What service packs have
> you installed.
> What are you using for backups? Is it a standard SQL Server backup or are
> you using some other program?
> More information would be helpful.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||What does the Application Log say?
Do you have anything else running on that server besides SQL 2k? (Exchange
maybe?).
Have you tried setting a max server memory for SQL Server? Tune it down a
bit and ensure that you leave enough RAM for the O/S.
(EM-> right-click on Server, Properties)
Rick Sawtell
MCT, MCSD, MCDBA|||The Event Viewer just says "the shut down at (time) was unexpected."
I just changed the memory so we'll see if that fixes anything.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:OPykeeU4EHA.4092@.TK2MSFTNGP14.phx.gbl...
> What does the Application Log say?
> Do you have anything else running on that server besides SQL 2k?
(Exchange
> maybe?).
> Have you tried setting a max server memory for SQL Server? Tune it down a
> bit and ensure that you leave enough RAM for the O/S.
> (EM-> right-click on Server, Properties)
> Rick Sawtell
> MCT, MCSD, MCDBA
>

long running jobs crashing the box

Ive a got an sql server that crashes whenever a job runs for a minimum of
5 - 10 minutes. It doesnt happen 100% of the time, probably 80-90%. It
doesnt matter if the job is scheduled, run manually, a backup, a reindex, a
data transfer, whatever, if it runs between 5 and 10 minutes, the box
crashes. Of course it happens most frquently during backups as they are run
the most. All ideas are appreicated.
SQL2K SP3
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:u%23$pQDT4EHA.936@.TK2MSFTNGP12.phx.gbl...
> Ive a got an sql server that crashes whenever a job runs for a minimum of
> 5 - 10 minutes. It doesnt happen 100% of the time, probably 80-90%. It
> doesnt matter if the job is scheduled, run manually, a backup, a reindex,
> a
> data transfer, whatever, if it runs between 5 and 10 minutes, the box
> crashes. Of course it happens most frquently during backups as they are
> run
> the most. All ideas are appreicated.
> --
> SQL2K SP3
> TIA, ChrisR
>
What do you mean by crashes? Does the O/S die and you get a blue screen?
Does SQL Server stop running, but the O/S is okay?
What version of O/S, what version of SQL Server? What service packs have
you installed.
What are you using for backups? Is it a standard SQL Server backup or are
you using some other program?
More information would be helpful.
Rick Sawtell
MCT, MCSD, MCDBA
|||> What do you mean by crashes? Does the O/S die and you get a blue screen?
> Does SQL Server stop running, but the O/S is okay?
Blue screen.

> What version of O/S, what version of SQL Server? What service packs have
> you installed.
Win2K sp4
SQL2K sp3a

> What are you using for backups? Is it a standard SQL Server backup or are
> you using some other program?
Standard SQL.
Let me know if more info is required.
Thanks, ChrisR
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:OZwiGUT4EHA.1452@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "ChrisR" <bla@.noemail.com> wrote in message
> news:u%23$pQDT4EHA.936@.TK2MSFTNGP12.phx.gbl...
of[vbcol=seagreen]
reindex,
> What do you mean by crashes? Does the O/S die and you get a blue screen?
> Does SQL Server stop running, but the O/S is okay?
> What version of O/S, what version of SQL Server? What service packs have
> you installed.
> What are you using for backups? Is it a standard SQL Server backup or are
> you using some other program?
> More information would be helpful.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||What does the Application Log say?
Do you have anything else running on that server besides SQL 2k? (Exchange
maybe?).
Have you tried setting a max server memory for SQL Server? Tune it down a
bit and ensure that you leave enough RAM for the O/S.
(EM-> right-click on Server, Properties)
Rick Sawtell
MCT, MCSD, MCDBA
|||The Event Viewer just says "the shut down at (time) was unexpected."
I just changed the memory so we'll see if that fixes anything.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:OPykeeU4EHA.4092@.TK2MSFTNGP14.phx.gbl...
> What does the Application Log say?
> Do you have anything else running on that server besides SQL 2k?
(Exchange
> maybe?).
> Have you tried setting a max server memory for SQL Server? Tune it down a
> bit and ensure that you leave enough RAM for the O/S.
> (EM-> right-click on Server, Properties)
> Rick Sawtell
> MCT, MCSD, MCDBA
>
sql

Long running DTS package

Hello,
I have a DTS package that took 52 hours to run. Within the package is a
task that runs a sproc which is a procedure to calculate premiums recvd and
has to run through over 2 million records. The procedure uses a cursor to
move through the records. My question is - is it possible that there were
memory leaks in the server while the package ran (there are a lot of other
things that go on on that server) making some of the data incorrect and some
of it correct? Because, I can take a portion of the data (a months worth)
and run that same process and it works just fine. Any ideas would help
tremendously.
Thanks,
PatriceMy goodness, that is a long-running process. I've had DTS packages going
through millions of records in a matter of 2-3 hours on an older machine.
There was some cursor activity in those processes as well. My guess is that
with some thoughtful redesign you can eliminate the cursor on a bunch of it.
(Can you precalculate some information and then handle the rest with joins
and aggregate functions?) That will make it easier to debug, as well as
run faster.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:1EC80DAD-CC19-447F-B9B5-A344A5E417E5@.microsoft.com...
> Hello,
> I have a DTS package that took 52 hours to run. Within the package is a
> task that runs a sproc which is a procedure to calculate premiums recvd
> and
> has to run through over 2 million records. The procedure uses a cursor to
> move through the records. My question is - is it possible that there were
> memory leaks in the server while the package ran (there are a lot of other
> things that go on on that server) making some of the data incorrect and
> some
> of it correct? Because, I can take a portion of the data (a months worth)
> and run that same process and it works just fine. Any ideas would help
> tremendously.
>
> Thanks,
> Patrice
>|||On Thu, 29 Dec 2005 07:28:02 -0800, "Patrice"
<Patrice@.discussions.microsoft.com> wrote:
>Hello,
>I have a DTS package that took 52 hours to run. Within the package is a
>task that runs a sproc which is a procedure to calculate premiums recvd and
>has to run through over 2 million records. The procedure uses a cursor to
>move through the records. My question is - is it possible that there were
>memory leaks in the server while the package ran (there are a lot of other
>things that go on on that server) making some of the data incorrect and som
e
>of it correct? Because, I can take a portion of the data (a months worth)
>and run that same process and it works just fine. Any ideas would help
>tremendously.
Can you post the cursor code?
Please look into the forward_only and fast_forward options. When you
have a (default) dynamic cursor, it can scale very poorly.
Josh

Logs with timestamp

Hi,

I'm using the SSIS log feature. However, I want a timestamp on the file everytime the IS package runs so as to keep track also that this particular log file is for this specific execution of IS.

How do i do that?

thanks a lot

cherriesh

Are you logging to SQL Server? If so, you can add a column to the dbo.sysdtslog90 table with a datetime datatype and set its default to GetDate().

I recommend that you read Jamie Thomson's blog about custom logging: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

|||

Here's a post from Jamie that shows how to set the log file name dynamically through an expression:

http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx

Logon Failed and Subscriptions

Anyone know why I keep getting a logon failed when my email subscription
runs? I have used all users imaginable on my data source and none seems to
work.
SQLServer Agent is running and my users have RSExec role enabled.Is the logon failure an SMTP error? Perhaps you do not have your SMTP
settings correct. Can you post the error message?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:6B033AF7-7476-4DDC-9873-BA6F2A6F1CF9@.microsoft.com...
> Anyone know why I keep getting a logon failed when my email subscription
> runs? I have used all users imaginable on my data source and none seems to
> work.
> SQLServer Agent is running and my users have RSExec role enabled.
>|||Below is my Report services file errors. But to add to by question.
I created a system user named reportuser. reportUser has access to Report
Server and the database I am making my query. this user has RSExec
permissions. This user is also setup in Report Manager to store credential
securely in Report Server on my shared datasource. I don't know what I have
to do next. Can you help me?
Report Services file is as follows:
ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:07:: EventPolling
processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!1994!12/9/2005-15:17:07:: EventPolling
processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
e4d454cf-4bae-44f6-bd2b-32b14ac12347 executed at 12/9/2005 3:17:08 PM.
ReportingServicesService!schedule!1994!12/9/2005-15:17:08:: Creating Time
based subscription notification for subscription:
7d229c62-b339-471b-8622-358857f2870a
ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
e4d454cf-4bae-44f6-bd2b-32b14ac12347 execution completed at 12/9/2005 3:17:08
PM.
ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08:: EventPolling
finished processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:08::
NotificationPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08::
NotificationPolling processing item 70a496c4-b071-44c5-8f7d-f2e063dc41dc
ReportingServicesService!library!1994!12/09/2005-15:17:11:: i INFO:
Initializing EnableIntegratedSecurity to 'True' as specified in Server
system properties.
ReportingServicesService!library!1994!12/09/2005-15:17:12:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Logon
failed., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Logon
failed. --> System.Runtime.InteropServices.COMException (0x8007052E): Logon
failure: unknown user name or bad password.
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo)
at RSRemoteRpcClient.RemoteLogon.GetRemoteImpToken(String pUserName,
String pDomain, String pPassword, Boolean bTryRemote, IntPtr& pImpToken)
at
Microsoft.ReportingServices.Diagnostics.DatasourceRuntimeContext.MakeUserToken(String userName, String userPwd, String domain)
-- End of inner exception stack trace --
ReportingServicesService!notification!1994!12/09/2005-15:17:12:: e ERROR:
Error occured processing notification. Logon failed.
ReportingServicesService!notification!1994!12/09/2005-15:17:12::
Notification 70a496c4-b071-44c5-8f7d-f2e063dc41dc completed. Success: False,
Status: Logon failed., DeliveryExtension: Report Server Email, Report:
Incomplete Loan Applications, Attempt 0
ReportingServicesService!dbpolling!1994!12/09/2005-15:17:12::
NotificationPolling finished processing item
70a496c4-b071-44c5-8f7d-f2e063dc41dc
ReportingServicesService!library!1994!12/09/2005-15:19:04:: i INFO: Cleaned
0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
"Daniel Reib [MSFT]" wrote:
> Is the logon failure an SMTP error? Perhaps you do not have your SMTP
> settings correct. Can you post the error message?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:6B033AF7-7476-4DDC-9873-BA6F2A6F1CF9@.microsoft.com...
> > Anyone know why I keep getting a logon failed when my email subscription
> > runs? I have used all users imaginable on my data source and none seems to
> > work.
> > SQLServer Agent is running and my users have RSExec role enabled.
> >
> >
>
>|||For some reason the system is not able to use the username and password that
you supplied. RS is attempting to logon using these credentials. Is this a
valid user on the computer that RS is installed on? Have you tried to
logon to that computer with these credentials? If all of these things are
correct then I would ensure that you have correctly set the password in the
datasource.
Can you view the report just fine? I would think you could not view it as
well.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:F18FFFED-2D43-4AAA-970E-BDDE5164A79D@.microsoft.com...
> Below is my Report services file errors. But to add to by question.
> I created a system user named reportuser. reportUser has access to Report
> Server and the database I am making my query. this user has RSExec
> permissions. This user is also setup in Report Manager to store credential
> securely in Report Server on my shared datasource. I don't know what I
> have
> to do next. Can you help me?
> Report Services file is as follows:
> ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:07:: EventPolling
> processing 1 more items. 1 Total items in internal queue.
> ReportingServicesService!dbpolling!1994!12/9/2005-15:17:07:: EventPolling
> processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
> ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
> e4d454cf-4bae-44f6-bd2b-32b14ac12347 executed at 12/9/2005 3:17:08 PM.
> ReportingServicesService!schedule!1994!12/9/2005-15:17:08:: Creating Time
> based subscription notification for subscription:
> 7d229c62-b339-471b-8622-358857f2870a
> ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
> e4d454cf-4bae-44f6-bd2b-32b14ac12347 execution completed at 12/9/2005
> 3:17:08
> PM.
> ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08:: EventPolling
> finished processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
> ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:08::
> NotificationPolling processing 1 more items. 1 Total items in internal
> queue.
> ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08::
> NotificationPolling processing item 70a496c4-b071-44c5-8f7d-f2e063dc41dc
> ReportingServicesService!library!1994!12/09/2005-15:17:11:: i INFO:
> Initializing EnableIntegratedSecurity to 'True' as specified in Server
> system properties.
> ReportingServicesService!library!1994!12/09/2005-15:17:12:: e ERROR:
> Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException:
> Logon
> failed., ;
> Info:
> Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException:
> Logon
> failed. --> System.Runtime.InteropServices.COMException (0x8007052E):
> Logon
> failure: unknown user name or bad password.
> at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo)
> at RSRemoteRpcClient.RemoteLogon.GetRemoteImpToken(String pUserName,
> String pDomain, String pPassword, Boolean bTryRemote, IntPtr& pImpToken)
> at
> Microsoft.ReportingServices.Diagnostics.DatasourceRuntimeContext.MakeUserToken(String
> userName, String userPwd, String domain)
> -- End of inner exception stack trace --
> ReportingServicesService!notification!1994!12/09/2005-15:17:12:: e ERROR:
> Error occured processing notification. Logon failed.
> ReportingServicesService!notification!1994!12/09/2005-15:17:12::
> Notification 70a496c4-b071-44c5-8f7d-f2e063dc41dc completed. Success:
> False,
> Status: Logon failed., DeliveryExtension: Report Server Email, Report:
> Incomplete Loan Applications, Attempt 0
> ReportingServicesService!dbpolling!1994!12/09/2005-15:17:12::
> NotificationPolling finished processing item
> 70a496c4-b071-44c5-8f7d-f2e063dc41dc
> ReportingServicesService!library!1994!12/09/2005-15:19:04:: i INFO:
> Cleaned
> 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> "Daniel Reib [MSFT]" wrote:
>> Is the logon failure an SMTP error? Perhaps you do not have your SMTP
>> settings correct. Can you post the error message?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "dillig" <dillig@.discussions.microsoft.com> wrote in message
>> news:6B033AF7-7476-4DDC-9873-BA6F2A6F1CF9@.microsoft.com...
>> > Anyone know why I keep getting a logon failed when my email
>> > subscription
>> > runs? I have used all users imaginable on my data source and none seems
>> > to
>> > work.
>> > SQLServer Agent is running and my users have RSExec role enabled.
>> >
>> >
>>|||The rptuser I set up is a SQL user not a domain user. It is able to connect
to the server and view the report, but doesn't email and I get a logon failed
error. I do see the Job ran successfully under the SQLServer Agent jobs in
Enterprise manager. Does The rpt user I set up have to be a domain user?
"Daniel Reib [MSFT]" wrote:
> For some reason the system is not able to use the username and password that
> you supplied. RS is attempting to logon using these credentials. Is this a
> valid user on the computer that RS is installed on? Have you tried to
> logon to that computer with these credentials? If all of these things are
> correct then I would ensure that you have correctly set the password in the
> datasource.
>
> Can you view the report just fine? I would think you could not view it as
> well.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:F18FFFED-2D43-4AAA-970E-BDDE5164A79D@.microsoft.com...
> > Below is my Report services file errors. But to add to by question.
> > I created a system user named reportuser. reportUser has access to Report
> > Server and the database I am making my query. this user has RSExec
> > permissions. This user is also setup in Report Manager to store credential
> > securely in Report Server on my shared datasource. I don't know what I
> > have
> > to do next. Can you help me?
> >
> > Report Services file is as follows:
> >
> > ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:07:: EventPolling
> > processing 1 more items. 1 Total items in internal queue.
> > ReportingServicesService!dbpolling!1994!12/9/2005-15:17:07:: EventPolling
> > processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
> > ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
> > e4d454cf-4bae-44f6-bd2b-32b14ac12347 executed at 12/9/2005 3:17:08 PM.
> > ReportingServicesService!schedule!1994!12/9/2005-15:17:08:: Creating Time
> > based subscription notification for subscription:
> > 7d229c62-b339-471b-8622-358857f2870a
> > ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
> > e4d454cf-4bae-44f6-bd2b-32b14ac12347 execution completed at 12/9/2005
> > 3:17:08
> > PM.
> > ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08:: EventPolling
> > finished processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
> > ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:08::
> > NotificationPolling processing 1 more items. 1 Total items in internal
> > queue.
> > ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08::
> > NotificationPolling processing item 70a496c4-b071-44c5-8f7d-f2e063dc41dc
> > ReportingServicesService!library!1994!12/09/2005-15:17:11:: i INFO:
> > Initializing EnableIntegratedSecurity to 'True' as specified in Server
> > system properties.
> > ReportingServicesService!library!1994!12/09/2005-15:17:12:: e ERROR:
> > Throwing
> > Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException:
> > Logon
> > failed., ;
> > Info:
> > Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException:
> > Logon
> > failed. --> System.Runtime.InteropServices.COMException (0x8007052E):
> > Logon
> > failure: unknown user name or bad password.
> > at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> > errorCode, IntPtr errorInfo)
> > at RSRemoteRpcClient.RemoteLogon.GetRemoteImpToken(String pUserName,
> > String pDomain, String pPassword, Boolean bTryRemote, IntPtr& pImpToken)
> > at
> > Microsoft.ReportingServices.Diagnostics.DatasourceRuntimeContext.MakeUserToken(String
> > userName, String userPwd, String domain)
> > -- End of inner exception stack trace --
> > ReportingServicesService!notification!1994!12/09/2005-15:17:12:: e ERROR:
> > Error occured processing notification. Logon failed.
> > ReportingServicesService!notification!1994!12/09/2005-15:17:12::
> > Notification 70a496c4-b071-44c5-8f7d-f2e063dc41dc completed. Success:
> > False,
> > Status: Logon failed., DeliveryExtension: Report Server Email, Report:
> > Incomplete Loan Applications, Attempt 0
> > ReportingServicesService!dbpolling!1994!12/09/2005-15:17:12::
> > NotificationPolling finished processing item
> > 70a496c4-b071-44c5-8f7d-f2e063dc41dc
> > ReportingServicesService!library!1994!12/09/2005-15:19:04:: i INFO:
> > Cleaned
> > 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> Is the logon failure an SMTP error? Perhaps you do not have your SMTP
> >> settings correct. Can you post the error message?
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> >> news:6B033AF7-7476-4DDC-9873-BA6F2A6F1CF9@.microsoft.com...
> >> > Anyone know why I keep getting a logon failed when my email
> >> > subscription
> >> > runs? I have used all users imaginable on my data source and none seems
> >> > to
> >> > work.
> >> > SQLServer Agent is running and my users have RSExec role enabled.
> >> >
> >> >
> >>
> >>
> >>
>
>|||No report user does not need to be a domain user. You may want to use
rsconfig.exe to set an unattended account. Perhaps this account was set
incorrectly.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:BC2653B2-59A1-4141-ADD6-CB203A7F34F3@.microsoft.com...
> The rptuser I set up is a SQL user not a domain user. It is able to
> connect
> to the server and view the report, but doesn't email and I get a logon
> failed
> error. I do see the Job ran successfully under the SQLServer Agent jobs in
> Enterprise manager. Does The rpt user I set up have to be a domain user?
> "Daniel Reib [MSFT]" wrote:
>> For some reason the system is not able to use the username and password
>> that
>> you supplied. RS is attempting to logon using these credentials. Is
>> this a
>> valid user on the computer that RS is installed on? Have you tried to
>> logon to that computer with these credentials? If all of these things
>> are
>> correct then I would ensure that you have correctly set the password in
>> the
>> datasource.
>>
>> Can you view the report just fine? I would think you could not view it
>> as
>> well.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "dillig" <dillig@.discussions.microsoft.com> wrote in message
>> news:F18FFFED-2D43-4AAA-970E-BDDE5164A79D@.microsoft.com...
>> > Below is my Report services file errors. But to add to by question.
>> > I created a system user named reportuser. reportUser has access to
>> > Report
>> > Server and the database I am making my query. this user has RSExec
>> > permissions. This user is also setup in Report Manager to store
>> > credential
>> > securely in Report Server on my shared datasource. I don't know what I
>> > have
>> > to do next. Can you help me?
>> >
>> > Report Services file is as follows:
>> >
>> > ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:07::
>> > EventPolling
>> > processing 1 more items. 1 Total items in internal queue.
>> > ReportingServicesService!dbpolling!1994!12/9/2005-15:17:07::
>> > EventPolling
>> > processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
>> > ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
>> > e4d454cf-4bae-44f6-bd2b-32b14ac12347 executed at 12/9/2005 3:17:08 PM.
>> > ReportingServicesService!schedule!1994!12/9/2005-15:17:08:: Creating
>> > Time
>> > based subscription notification for subscription:
>> > 7d229c62-b339-471b-8622-358857f2870a
>> > ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
>> > e4d454cf-4bae-44f6-bd2b-32b14ac12347 execution completed at 12/9/2005
>> > 3:17:08
>> > PM.
>> > ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08::
>> > EventPolling
>> > finished processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
>> > ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:08::
>> > NotificationPolling processing 1 more items. 1 Total items in internal
>> > queue.
>> > ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08::
>> > NotificationPolling processing item
>> > 70a496c4-b071-44c5-8f7d-f2e063dc41dc
>> > ReportingServicesService!library!1994!12/09/2005-15:17:11:: i INFO:
>> > Initializing EnableIntegratedSecurity to 'True' as specified in Server
>> > system properties.
>> > ReportingServicesService!library!1994!12/09/2005-15:17:12:: e ERROR:
>> > Throwing
>> > Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException:
>> > Logon
>> > failed., ;
>> > Info:
>> > Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException:
>> > Logon
>> > failed. --> System.Runtime.InteropServices.COMException (0x8007052E):
>> > Logon
>> > failure: unknown user name or bad password.
>> > at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
>> > errorCode, IntPtr errorInfo)
>> > at RSRemoteRpcClient.RemoteLogon.GetRemoteImpToken(String pUserName,
>> > String pDomain, String pPassword, Boolean bTryRemote, IntPtr&
>> > pImpToken)
>> > at
>> > Microsoft.ReportingServices.Diagnostics.DatasourceRuntimeContext.MakeUserToken(String
>> > userName, String userPwd, String domain)
>> > -- End of inner exception stack trace --
>> > ReportingServicesService!notification!1994!12/09/2005-15:17:12:: e
>> > ERROR:
>> > Error occured processing notification. Logon failed.
>> > ReportingServicesService!notification!1994!12/09/2005-15:17:12::
>> > Notification 70a496c4-b071-44c5-8f7d-f2e063dc41dc completed. Success:
>> > False,
>> > Status: Logon failed., DeliveryExtension: Report Server Email, Report:
>> > Incomplete Loan Applications, Attempt 0
>> > ReportingServicesService!dbpolling!1994!12/09/2005-15:17:12::
>> > NotificationPolling finished processing item
>> > 70a496c4-b071-44c5-8f7d-f2e063dc41dc
>> > ReportingServicesService!library!1994!12/09/2005-15:19:04:: i INFO:
>> > Cleaned
>> > 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots,
>> > 0
>> > chunks, 0 running jobs
>> >
>> > "Daniel Reib [MSFT]" wrote:
>> >
>> >> Is the logon failure an SMTP error? Perhaps you do not have your SMTP
>> >> settings correct. Can you post the error message?
>> >>
>> >> --
>> >> -Daniel
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "dillig" <dillig@.discussions.microsoft.com> wrote in message
>> >> news:6B033AF7-7476-4DDC-9873-BA6F2A6F1CF9@.microsoft.com...
>> >> > Anyone know why I keep getting a logon failed when my email
>> >> > subscription
>> >> > runs? I have used all users imaginable on my data source and none
>> >> > seems
>> >> > to
>> >> > work.
>> >> > SQLServer Agent is running and my users have RSExec role enabled.
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>