queries take a long time after sp2 installation

Has anyone had thsi issue, if so what was the fix?

We installed service pack2, the day after 2 of our production jobs started taking a long time to complete and causing a ton of blocking.

it went from running in 2 minutes to now taking 3 hours and 29 minutes to run. Can someone help?

Is it only certain queries running slower or is it all queries running slower? What high-level symptoms are you seeing? Does it look like CPU pressure, memory pressure, I/O pressure, blocking, etc.?

What kind of results do you get when you run these DMV queries?

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)

,'%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)

,'%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

FROM sys.dm_os_wait_stats

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

-- Isolate top waits

WITH Waits AS

(

SELECT

wait_type,

wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

FROM sys.dm_os_wait_stats

WHERE wait_type NOT LIKE '%SLEEP%'

-- filter out additional irrelevant waits

)

SELECT

W1.wait_type,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.rn <= W1.rn

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold

ORDER BY W1.rn;

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROM sys.dm_exec_requests AS blocked_query

INNER JOIN sys.dm_exec_requests AS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNER JOIN sys.dm_os_waiting_tasks AS waits

ON waits.session_id = blocking_query.session_id

|||After the SP2 Upgrade did you update the stats and index rebuilds on the tables?. You might want to perform some optimization and see if the problem still persists.|||

If you have not done so already, try running sp_UpdateStats on your database(s). This will run UPDATE STATISTICS with the default 10% sample rate on every table in the database (which may take a while, depending on your hardware and db size).

You should always do that after upgrading to SQL Server 2005

Rebuilding (as opposed to reorganizing) indexes will also automatically update statistics.

|||Hi,

We have experienced many slowness and trouble since the SP2 was installed.
Have a look at theses 2 KB:
http://support.microsoft.com/default.aspx/kb/937745
And
http://support.microsoft.com/default.aspx/kb/933564/en-us

Last point, run the following query when your server is slow:

select type, sum(single_pages_kb+multi_pages_kb) 'total memory' from sys.dm_os_memory_clerks ORDER by 2 DESC

This will help you to know what cache type is getting most of your SQL Ram.
If it's USERSTORE_TOKENPERM, consider using Trace flag 4618. It has had good impact on our system, but still not correct the case. I have conf call with MS Support on Monday about that point and will answer in this post if I have anymore informations.

Regards,
Jeremy
|||

Jeremy - Just wondering if you had any updates. We are currently hitting this issue. The clearing of ('TokenAndPermUserStore') does not work 100% of the time. We've experienced times when the system is so busy that we can not clear it fast enough.

We are considering using the trace flag.

Thanks -

Sam.

queries take a long time after sp2 installation

Has anyone had thsi issue, if so what was the fix?

We installed service pack2, the day after 2 of our production jobs started taking a long time to complete and causing a ton of blocking.

it went from running in 2 minutes to now taking 3 hours and 29 minutes to run. Can someone help?

Is it only certain queries running slower or is it all queries running slower? What high-level symptoms are you seeing? Does it look like CPU pressure, memory pressure, I/O pressure, blocking, etc.?

What kind of results do you get when you run these DMV queries?

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)

,'%signal (cpu) waits'=CAST(100.0 *SUM(signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)

,'%resource waits'=CAST(100.0 *SUM(wait_time_ms - signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

FROMsys.dm_os_wait_stats

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROMsys.dm_os_schedulers

WHERE scheduler_id < 255

-- Isolate top waits

WITH Waits AS

(

SELECT

wait_type,

wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms /SUM(wait_time_ms)OVER()AS pct,

ROW_NUMBER()OVER(ORDERBY wait_time_ms DESC)AS rn

FROMsys.dm_os_wait_stats

WHERE wait_type NOTLIKE'%SLEEP%'

-- filter out additional irrelevant waits

)

SELECT

W1.wait_type,

CAST(W1.wait_time_s ASDECIMAL(12, 2))AS wait_time_s,

CAST(W1.pct ASDECIMAL(12, 2))AS pct,

CAST(SUM(W2.pct)ASDECIMAL(12, 2))AS running_pct

FROM Waits AS W1

INNERJOIN Waits AS W2

ON W2.rn <= W1.rn

GROUPBY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVINGSUM(W2.pct)- W1.pct < 90 -- percentage threshold

ORDERBY W1.rn;

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROMsys.dm_exec_requestsAS blocked_query

INNERJOINsys.dm_exec_requestsAS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSSAPPLY

(SELECT*

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSSAPPLY

(SELECT*

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNERJOINsys.dm_os_waiting_tasksAS waits

ON waits.session_id = blocking_query.session_id

|||After the SP2 Upgrade did you update the stats and index rebuilds on the tables?. You might want to perform some optimization and see if the problem still persists.|||

If you have not done so already, try running sp_UpdateStats on your database(s). This will run UPDATE STATISTICS with the default 10% sample rate on every table in the database (which may take a while, depending on your hardware and db size).

You should always do that after upgrading to SQL Server 2005

Rebuilding (as opposed to reorganizing) indexes will also automatically update statistics.

|||Hi,

We have experienced many slowness and trouble since the SP2 was installed.
Have a look at theses 2 KB:
http://support.microsoft.com/default.aspx/kb/937745
And
http://support.microsoft.com/default.aspx/kb/933564/en-us

Last point, run the following query when your server is slow:

select type, sum(single_pages_kb+multi_pages_kb) 'total memory' from sys.dm_os_memory_clerks ORDER by 2 DESC

This will help you to know what cache type is getting most of your SQL Ram.
If it's USERSTORE_TOKENPERM, consider using Trace flag 4618. It has had good impact on our system, but still not correct the case. I have conf call with MS Support on Monday about that point and will answer in this post if I have anymore informations.

Regards,
Jeremy
|||

Jeremy - Just wondering if you had any updates. We are currently hitting this issue. The clearing of ('TokenAndPermUserStore') does not work 100% of the time. We've experienced times when the system is so busy that we can not clear it fast enough.

We are considering using the trace flag.

Thanks -

Sam.

queries take a long time after sp2 installation

Has anyone had thsi issue, if so what was the fix?

We installed service pack2, the day after 2 of our production jobs started taking a long time to complete and causing a ton of blocking.

it went from running in 2 minutes to now taking 3 hours and 29 minutes to run. Can someone help?

Is it only certain queries running slower or is it all queries running slower? What high-level symptoms are you seeing? Does it look like CPU pressure, memory pressure, I/O pressure, blocking, etc.?

What kind of results do you get when you run these DMV queries?

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)

,'%signal (cpu) waits'=CAST(100.0 *SUM(signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)

,'%resource waits'=CAST(100.0 *SUM(wait_time_ms - signal_wait_time_ms)/SUM(wait_time_ms)ASNUMERIC(20,2))

FROMsys.dm_os_wait_stats

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROMsys.dm_os_schedulers

WHERE scheduler_id < 255

-- Isolate top waits

WITH Waits AS

(

SELECT

wait_type,

wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms /SUM(wait_time_ms)OVER()AS pct,

ROW_NUMBER()OVER(ORDERBY wait_time_ms DESC)AS rn

FROMsys.dm_os_wait_stats

WHERE wait_type NOTLIKE'%SLEEP%'

-- filter out additional irrelevant waits

)

SELECT

W1.wait_type,

CAST(W1.wait_time_s ASDECIMAL(12, 2))AS wait_time_s,

CAST(W1.pct ASDECIMAL(12, 2))AS pct,

CAST(SUM(W2.pct)ASDECIMAL(12, 2))AS running_pct

FROM Waits AS W1

INNERJOIN Waits AS W2

ON W2.rn <= W1.rn

GROUPBY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVINGSUM(W2.pct)- W1.pct < 90 -- percentage threshold

ORDERBY W1.rn;

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROMsys.dm_exec_requestsAS blocked_query

INNERJOINsys.dm_exec_requestsAS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSSAPPLY

(SELECT*

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSSAPPLY

(SELECT*

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNERJOINsys.dm_os_waiting_tasksAS waits

ON waits.session_id = blocking_query.session_id

|||After the SP2 Upgrade did you update the stats and index rebuilds on the tables?. You might want to perform some optimization and see if the problem still persists.|||

If you have not done so already, try running sp_UpdateStats on your database(s). This will run UPDATE STATISTICS with the default 10% sample rate on every table in the database (which may take a while, depending on your hardware and db size).

You should always do that after upgrading to SQL Server 2005

Rebuilding (as opposed to reorganizing) indexes will also automatically update statistics.

|||Hi,

We have experienced many slowness and trouble since the SP2 was installed.
Have a look at theses 2 KB:
http://support.microsoft.com/default.aspx/kb/937745
And
http://support.microsoft.com/default.aspx/kb/933564/en-us

Last point, run the following query when your server is slow:

select type, sum(single_pages_kb+multi_pages_kb) 'total memory' from sys.dm_os_memory_clerks ORDER by 2 DESC

This will help you to know what cache type is getting most of your SQL Ram.
If it's USERSTORE_TOKENPERM, consider using Trace flag 4618. It has had good impact on our system, but still not correct the case. I have conf call with MS Support on Monday about that point and will answer in this post if I have anymore informations.

Regards,
Jeremy
|||

Jeremy - Just wondering if you had any updates. We are currently hitting this issue. The clearing of ('TokenAndPermUserStore') does not work 100% of the time. We've experienced times when the system is so busy that we can not clear it fast enough.

We are considering using the trace flag.

Thanks -

Sam.

Queries run slower in 2000 than 7

I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of r
am. I moved the database from a SQL Server 7 SP2 box with only 2 processors
and 1 gig of RAM. I inherited a monster report that runs in 30 seconds or
less on the sql 7 box, but
takes a minute and a half on the new box. This report uses a tremendous amo
unt of temp tables and dynamic sql (as I said I inherited it). By using sp_
executesql instead of EXEC for the dynamic sql, i was able to get the report
to run in 55 seconds. How
ever, I am confused as to why it would run so much slower on a much larder b
ox with 2000, especially considering i was the only one on the 2000 box, and
the SQL 7 box has 400 users on it.
Any ideas?Did you remember to update statistics on all tables, preferably with the
FULLSCAN option?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:6AA5B19E-7050-4FF7-914D-B0772F6E6ADC@.microsoft.com...
I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of
ram. I moved the database from a SQL Server 7 SP2 box with only 2
processors and 1 gig of RAM. I inherited a monster report that runs in 30
seconds or less on the sql 7 box, but takes a minute and a half on the new
box. This report uses a tremendous amount of temp tables and dynamic sql
(as I said I inherited it). By using sp_executesql instead of EXEC for the
dynamic sql, i was able to get the report to run in 55 seconds. However, I
am confused as to why it would run so much slower on a much larder box with
2000, especially considering i was the only one on the 2000 box, and the SQL
7 box has 400 users on it.
Any ideas?|||I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took a
lmost 12 minutes to run and actually rhe query came back at 57 seconds inste
ad of 55. It is the same exact database file as sql 7. i am so confused.
i am supposed to be releasi
ng this server to productiona t the end of the week, but my queries are runn
ing slower. the whole justification for this purchase was to make things fa
ster and now i have no explanation why things are slower. any toehr ideas a
re greatly appreciated.|||Could you please list your hardware as well as where you placed your data
files on each server?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:04C40E3B-AFD1-4862-8957-CA29F358B3FE@.microsoft.com...
I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took
almost 12 minutes to run and actually rhe query came back at 57 seconds
instead of 55. It is the same exact database file as sql 7. i am so
confused. i am supposed to be releasing this server to productiona t the
end of the week, but my queries are running slower. the whole justification
for this purchase was to make things faster and now i have no explanation
why things are slower. any toehr ideas are greatly appreciated.|||Can you list your hardware and DB configuration? Especially the disk config.
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:

> I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took almost
12 minutes to run and actually rhe query came back at 57 seconds instead of 55. It
is the same exact database file as sql 7. i am so confused. i am supposed to be re
lea
sing this server to productiona t the end of the week, but my queries are running slower. t
he whole justification for this purchase was to make things faster and now i have no explana
tion why things are slower. any toehr ideas are greatly appreciated.
>|||Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last
128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)|||Outside of using RAID0+1, instead of RAID 5, I'd expect this to be OK. What
did you have for hardware for your SQL 7 box? Also, for your particular
query, have you tried running off parallelism:
SELECT
*
FROM
MyTable
OPTION (MAXDOP 1)
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:BA36F91D-17C8-47A3-9E7F-C8DCBCC5F620@.microsoft.com...
Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last
128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)|||What is your SQL 7 box config.?
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:
> Here’s the specifications for this server.
> Compaq DL580 G2
> 4x 2800 MHz processors
> 2GB Ram – SQL is configured to dynamically use all of this except the la
st 128MB which is saved for the OS.
> 4GB Page file on C: drive
> C: - 34 GB Local Mirror (OS and SQL binn files only)
> E: - 34 GB Local Mirror (SQL Log Files Only)
> F: - 200 GB SAN RAID 5 (SQL Data Files Only)
> I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
> J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)|||I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box|||I just wanted to confirm that fact. Have you tried turning off parallelism
on the disaffected query?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:A4598E2A-D769-41FB-8FDC-E3FDD1459B2C@.microsoft.com...
I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box

Queries run slower in 2000 than 7

I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of ram. I moved the database from a SQL Server 7 SP2 box with only 2 processors and 1 gig of RAM. I inherited a monster report that runs in 30 seconds or less on the sql 7 box, but
takes a minute and a half on the new box. This report uses a tremendous amount of temp tables and dynamic sql (as I said I inherited it). By using sp_executesql instead of EXEC for the dynamic sql, i was able to get the report to run in 55 seconds. How
ever, I am confused as to why it would run so much slower on a much larder box with 2000, especially considering i was the only one on the 2000 box, and the SQL 7 box has 400 users on it.
Any ideas?
Did you remember to update statistics on all tables, preferably with the
FULLSCAN option?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:6AA5B19E-7050-4FF7-914D-B0772F6E6ADC@.microsoft.com...
I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of
ram. I moved the database from a SQL Server 7 SP2 box with only 2
processors and 1 gig of RAM. I inherited a monster report that runs in 30
seconds or less on the sql 7 box, but takes a minute and a half on the new
box. This report uses a tremendous amount of temp tables and dynamic sql
(as I said I inherited it). By using sp_executesql instead of EXEC for the
dynamic sql, i was able to get the report to run in 55 seconds. However, I
am confused as to why it would run so much slower on a much larder box with
2000, especially considering i was the only one on the 2000 box, and the SQL
7 box has 400 users on it.
Any ideas?
|||I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took almost 12 minutes to run and actually rhe query came back at 57 seconds instead of 55. It is the same exact database file as sql 7. i am so confused. i am supposed to be releasi
ng this server to productiona t the end of the week, but my queries are running slower. the whole justification for this purchase was to make things faster and now i have no explanation why things are slower. any toehr ideas are greatly appreciated.
|||Could you please list your hardware as well as where you placed your data
files on each server?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:04C40E3B-AFD1-4862-8957-CA29F358B3FE@.microsoft.com...
I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took
almost 12 minutes to run and actually rhe query came back at 57 seconds
instead of 55. It is the same exact database file as sql 7. i am so
confused. i am supposed to be releasing this server to productiona t the
end of the week, but my queries are running slower. the whole justification
for this purchase was to make things faster and now i have no explanation
why things are slower. any toehr ideas are greatly appreciated.
|||Can you list your hardware and DB configuration? Especially the disk config.
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:

> I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took almost 12 minutes to run and actually rhe query came back at 57 seconds instead of 55. It is the same exact database file as sql 7. i am so confused. i am supposed to be relea
sing this server to productiona t the end of the week, but my queries are running slower. the whole justification for this purchase was to make things faster and now i have no explanation why things are slower. any toehr ideas are greatly appreciated.
>
|||Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last 128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)
|||Outside of using RAID0+1, instead of RAID 5, I'd expect this to be OK. What
did you have for hardware for your SQL 7 box? Also, for your particular
query, have you tried running off parallelism:
SELECT
*
FROM
MyTable
OPTION (MAXDOP 1)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:BA36F91D-17C8-47A3-9E7F-C8DCBCC5F620@.microsoft.com...
Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last
128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)
|||What is your SQL 7 box config.?
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:
> Here’s the specifications for this server.
> Compaq DL580 G2
> 4x 2800 MHz processors
> 2GB Ram – SQL is configured to dynamically use all of this except the last 128MB which is saved for the OS.
> 4GB Page file on C: drive
> C: - 34 GB Local Mirror (OS and SQL binn files only)
> E: - 34 GB Local Mirror (SQL Log Files Only)
> F: - 200 GB SAN RAID 5 (SQL Data Files Only)
> I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
> J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)
|||I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box
|||I just wanted to confirm that fact. Have you tried turning off parallelism
on the disaffected query?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:A4598E2A-D769-41FB-8FDC-E3FDD1459B2C@.microsoft.com...
I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box

Qry Analyzer Debug broken: XP SP2

I read a post in this group that a hotfix (per KB839280) would take care of
this (posted by an MS support person). The post indicated this fix would take
care of the Visual Studio issue, and well as Query Analyzer.
I got the hotfix and installed it on my development server, however my XP
sp2 system still cannot remotely debug the server. I have always been able to
do this, until I installed XP sp2.
Any suggestions? Has anyone gotten remote debugging to work post XP sp2
installation?
Thanks for your assistance,
TomHi Tom,
Thanks for your posting!
From your descriptions, I understood that you could not use Query Analyzer
to debug after XP SP2 was upgraded. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.
Based on my scope, as you said it is a remotely debugging ,does it used a
linked server? If so, here is another document address the probelm you may
meet.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2
http://support.microsoft.com/default.aspx?kbid=839279
What's the error message when it fails to debugging? Could you tell me how
to reporduce it on my machine? I need more detailed information for your
scenario, which, I believe, will make us closer to the resolution.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
this prior to installing XP sp2 on the WinXP system. The sql server is at
sp3, and runs on a Windows 2003 system.
The message I get when running the debugger is:
Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
connection 58.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||This is the error in the event log of the SQL Server system:
Event Type: Error
Event Source: SQLDebugging98
Event Category: None
Event ID: 1
Date: 9/14/2004
Time: 9:45:19 PM
User: N/A
Computer: HESPERUS
Description:
SQL Server is running as 'Force3\SQLAdmin' and cannot connect to the
debugger on machine 'TOMHOME2' (error = 0x80070005 Access is denied. ). Use
one of the following options to fix this error. 1) Run SQL Server as "Local
System", as a domain account, or as a local account with identical usernames
and passwords on both machine 'HESPERUS' and 'TOMHOME2'. 2) Verify that
machine 'HESPERUS' can open files on machine 'TOMHOME2'. Debugging disabled
for connection 56.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This setup did work prior to XP sp2, the logon account for the sql server
service is a domain (admin) account.
The odd thing now is, when I run the debugger, it actually runs, but does
not "step" thru the procedure, it goes straight thru to the end, and does not
show any of the values for the variables, connections, etc. In the results
pane, I get a result, and at the very bottom it says completed.
Putting in break points makes no difference, it just runs on to the end
regardless...
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Tom,
Thanks for your detailed information and descriptions!
First of all, The reason for Error 508 and Error code 0x80070005 is
permission issue. As you said it works fine before upgrading to XP SP2,
have you tried disable the Firewall os XP SP2. Close the firewall and then
tell me whether it works. Checking the following documents to make sure
your SQL Server in XP XP2 is correctly configured
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
http://support.microsoft.com/?id=841249
Secondly, The error 0x800706ba generated is a Distributed COM communication
error when using RPC. The error code 0x800706ba translates to "RPC server
unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
allocation. By default, RPC dynamic port allocation randomly selects port
numbers above 1024, so the problem seems to be the firewall blocking the
rpc communication. Do the folloing things to make sure DCOM is running
fine.
* On the client machine
--run dcomcnfg.
--Go to the dcom config properties under default properties, there is a
check box stating "enable Distributed COM on this computer", if this was
unchecked on the client machine, this is a default setting that is checked.
make sure it is checked.
*Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
replcaed the old ones with these on the client machine and then ran the
regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
/RegServer on the client machine and rebooted the box and everything
started to work like a charm.Customer was able to debug the stored proc on
the server using SQL QA and t-sql debugger from his client machine.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I found the problem: after applying the hotfix to the server, the client side
tools were no longer the same version as the server. Applying the hotfix to
the client fixed the problem.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||I also have the same problem after installed Windows XP SP2,
the similar error message when running debugger from query analyzer.
Is there any solution for this?
"TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
> I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> this prior to installing XP sp2 on the WinXP system. The sql server is at
> sp3, and runs on a Windows 2003 system.
> The message I get when running the debugger is:
> Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> connection 58.
> ""Mingqing Cheng [MSFT]"" wrote:
> > Hi Tom,
> >
> > Thanks for your posting!
> >
> > From your descriptions, I understood that you could not use Query Analyzer
> > to debug after XP SP2 was upgraded. Have I understood you? If there is
> > anything I misunderstood, please feel free to let me know.
> >
> > Based on my scope, as you said it is a remotely debugging ,does it used a
> > linked server? If so, here is another document address the probelm you may
> > meet.
> >
> > You may receive a 7391 error message in SQL Server 2000 when you run a
> > distributed transaction against a linked server after you install Microsoft
> > Windows XP Service Pack 2
> > http://support.microsoft.com/default.aspx?kbid=839279
> >
> > What's the error message when it fails to debugging? Could you tell me how
> > to reporduce it on my machine? I need more detailed information for your
> > scenario, which, I believe, will make us closer to the resolution.
> >
> > Thank you for your patience and cooperation. If you have any questions or
> > concerns, don't hesitate to let me know. We are here to be of assistance!
> >
> >
> > Sincerely yours,
> >
> > Mingqing Cheng
> >
> > Online Partner Support Specialist
> > Partner Support Group
> > Microsoft Global Technical Support Center
> > ---
> > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> > This posting is provided "as is" with no warranties and confers no rights.
> > Please reply to newsgroups only, many thanks!
> >
> >|||Check my last entry on this topic, dated 9/16/2004
"duandd" wrote:
> I also have the same problem after installed Windows XP SP2,
> the similar error message when running debugger from query analyzer.
> Is there any solution for this?
> "TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
> > I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> > this prior to installing XP sp2 on the WinXP system. The sql server is at
> > sp3, and runs on a Windows 2003 system.
> >
> > The message I get when running the debugger is:
> >
> > Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> > on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> > SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> > connection 58.
> >
> > ""Mingqing Cheng [MSFT]"" wrote:
> >
> > > Hi Tom,
> > >
> > > Thanks for your posting!
> > >
> > > From your descriptions, I understood that you could not use Query Analyzer
> > > to debug after XP SP2 was upgraded. Have I understood you? If there is
> > > anything I misunderstood, please feel free to let me know.
> > >
> > > Based on my scope, as you said it is a remotely debugging ,does it used a
> > > linked server? If so, here is another document address the probelm you may
> > > meet.
> > >
> > > You may receive a 7391 error message in SQL Server 2000 when you run a
> > > distributed transaction against a linked server after you install Microsoft
> > > Windows XP Service Pack 2
> > > http://support.microsoft.com/default.aspx?kbid=839279
> > >
> > > What's the error message when it fails to debugging? Could you tell me how
> > > to reporduce it on my machine? I need more detailed information for your
> > > scenario, which, I believe, will make us closer to the resolution.
> > >
> > > Thank you for your patience and cooperation. If you have any questions or
> > > concerns, don't hesitate to let me know. We are here to be of assistance!
> > >
> > >
> > > Sincerely yours,
> > >
> > > Mingqing Cheng
> > >
> > > Online Partner Support Specialist
> > > Partner Support Group
> > > Microsoft Global Technical Support Center
> > > ---
> > > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> > > This posting is provided "as is" with no warranties and confers no rights.
> > > Please reply to newsgroups only, many thanks!
> > >
> > >
>

Qry Analyzer Debug broken: XP SP2

I read a post in this group that a hotfix (per KB839280) would take care of
this (posted by an MS support person). The post indicated this fix would take
care of the Visual Studio issue, and well as Query Analyzer.
I got the hotfix and installed it on my development server, however my XP
sp2 system still cannot remotely debug the server. I have always been able to
do this, until I installed XP sp2.
Any suggestions? Has anyone gotten remote debugging to work post XP sp2
installation?
Thanks for your assistance,
Tom
Hi Tom,
Thanks for your posting!
From your descriptions, I understood that you could not use Query Analyzer
to debug after XP SP2 was upgraded. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.
Based on my scope, as you said it is a remotely debugging ,does it used a
linked server? If so, here is another document address the probelm you may
meet.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2
http://support.microsoft.com/default.aspx?kbid=839279
What's the error message when it fails to debugging? Could you tell me how
to reporduce it on my machine? I need more detailed information for your
scenario, which, I believe, will make us closer to the resolution.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
this prior to installing XP sp2 on the WinXP system. The sql server is at
sp3, and runs on a Windows 2003 system.
The message I get when running the debugger is:
Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
connection 58.
""Mingqing Cheng [MSFT]"" wrote:

> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||This is the error in the event log of the SQL Server system:
Event Type:Error
Event Source:SQLDebugging98
Event Category:None
Event ID:1
Date:9/14/2004
Time:9:45:19 PM
User:N/A
Computer:HESPERUS
Description:
SQL Server is running as 'Force3\SQLAdmin' and cannot connect to the
debugger on machine 'TOMHOME2' (error = 0x80070005 Access is denied. ). Use
one of the following options to fix this error. 1) Run SQL Server as "Local
System", as a domain account, or as a local account with identical usernames
and passwords on both machine 'HESPERUS' and 'TOMHOME2'. 2) Verify that
machine 'HESPERUS' can open files on machine 'TOMHOME2'. Debugging disabled
for connection 56.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This setup did work prior to XP sp2, the logon account for the sql server
service is a domain (admin) account.
The odd thing now is, when I run the debugger, it actually runs, but does
not "step" thru the procedure, it goes straight thru to the end, and does not
show any of the values for the variables, connections, etc. In the results
pane, I get a result, and at the very bottom it says completed.
Putting in break points makes no difference, it just runs on to the end
regardless...
""Mingqing Cheng [MSFT]"" wrote:

> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Tom,
Thanks for your detailed information and descriptions!
First of all, The reason for Error 508 and Error code 0x80070005 is
permission issue. As you said it works fine before upgrading to XP SP2,
have you tried disable the Firewall os XP SP2. Close the firewall and then
tell me whether it works. Checking the following documents to make sure
your SQL Server in XP XP2 is correctly configured
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
http://support.microsoft.com/?id=841249
Secondly, The error 0x800706ba generated is a Distributed COM communication
error when using RPC. The error code 0x800706ba translates to "RPC server
unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
allocation. By default, RPC dynamic port allocation randomly selects port
numbers above 1024, so the problem seems to be the firewall blocking the
rpc communication. Do the folloing things to make sure DCOM is running
fine.
* On the client machine
--run dcomcnfg.
--Go to the dcom config properties under default properties, there is a
check box stating "enable Distributed COM on this computer", if this was
unchecked on the client machine, this is a default setting that is checked.
make sure it is checked.
*Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
replcaed the old ones with these on the client machine and then ran the
regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
/RegServer on the client machine and rebooted the box and everything
started to work like a charm.Customer was able to debug the stored proc on
the server using SQL QA and t-sql debugger from his client machine.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I have tried all the suggestions but the behavior remains the same: the
debugger connects and then runs thru the procedure beginning to end without
stopping, providing variable info, etc.
""Mingqing Cheng [MSFT]"" wrote:

> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I found the problem: after applying the hotfix to the server, the client side
tools were no longer the same version as the server. Applying the hotfix to
the client fixed the problem.
""Mingqing Cheng [MSFT]"" wrote:

> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I also have the same problem after installed Windows XP SP2,
the similar error message when running debugger from query analyzer.
Is there any solution for this?
"TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...[vbcol=seagreen]
> I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> this prior to installing XP sp2 on the WinXP system. The sql server is at
> sp3, and runs on a Windows 2003 system.
> The message I get when running the debugger is:
> Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> connection 58.
> ""Mingqing Cheng [MSFT]"" wrote:
|||Check my last entry on this topic, dated 9/16/2004
"duandd" wrote:

> I also have the same problem after installed Windows XP SP2,
> the similar error message when running debugger from query analyzer.
> Is there any solution for this?
> "TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
>

QA database change take too much time

Hi,
MSSQL 2K, SP4 on XP sp2
While connected to local SQL server, when I click on DatabBase drop down
Combo in Query Analyzer, it takes around 10+ seconds before displaying the
databases to select, however if I connect to another SQL server on my office
LAN, then list appears with no time.
appreciate your help to fix this.
Thanks
FalikAre the databases set to autoclose on your local SQL Server?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Falik Sher" <faliks@.hotmail.com> wrote in message
news:%23npQHWQFHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hi,
> MSSQL 2K, SP4 on XP sp2
> While connected to local SQL server, when I click on DatabBase drop down
> Combo in Query Analyzer, it takes around 10+ seconds before displaying the
> databases to select, however if I connect to another SQL server on my
> office LAN, then list appears with no time.
> appreciate your help to fix this.
> Thanks
> Falik
>|||To add on to Kalen;
You might be you have enabled the database option "AUTOCLOSE". THis will
close
the MDF and LDF as soon as the last user logs of the database. Again the MDF
and LDF will be opened once a user logins to the database.
How to check this option is checked:-
1. Enterprise manager -- Databases -- Select the database
2. Right click and select properties -- Choose options
3. Chek whether AUTOCLOSE option is "checked". If yes then remove it
This will ensure that MDF and LDF will never closed as soon as last user
logs off.
Thanks
Hari
"Falik Sher" <faliks@.hotmail.com> wrote in message
news:%23npQHWQFHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hi,
> MSSQL 2K, SP4 on XP sp2
> While connected to local SQL server, when I click on DatabBase drop down
> Combo in Query Analyzer, it takes around 10+ seconds before displaying the
> databases to select, however if I connect to another SQL server on my
> office LAN, then list appears with no time.
> appreciate your help to fix this.
> Thanks
> Falik
>

q; exit stored procedure

Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
follows:
Exec Sp1
Inserts, updates
Exec Sp2
Inserts, upadtes
Exec Sp3
If error Exit Exec Sp1
Can I end Sp1 if I catch an error in Sp3Jim
CREATE PROCEDURE BigOne
AS
DECLARE @.err integer
BEGIN TRANSACTION
EXEC @.err = sp1
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp2
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp3
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp4
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
COMMIT TRANSACION
GO
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> follows:
> Exec Sp1
> Inserts, updates
> Exec Sp2
> Inserts, upadtes
> Exec Sp3
> If error Exit Exec Sp1
> Can I end Sp1 if I catch an error in Sp3
>|||Hi Uri,
Thank you very much for your help. I am currently getting Deadlock message
from time to time, I was wondering how this commit rollback will effect it.
Since there will not be auto-commit for each update (I have many updates
before calling SP2, SP3, and one single commit for all the update, will this
increase the chance of deadlock or decrease it?
"Uri Dimant" wrote:
> Jim
> CREATE PROCEDURE BigOne
> AS
> DECLARE @.err integer
> BEGIN TRANSACTION
> EXEC @.err = sp1
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp2
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp3
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp4
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> COMMIT TRANSACION
> GO
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> > follows:
> >
> > Exec Sp1
> > Inserts, updates
> > Exec Sp2
> > Inserts, upadtes
> > Exec Sp3
> > If error Exit Exec Sp1
> >
> > Can I end Sp1 if I catch an error in Sp3
> >
>
>|||JIM
Read this article
http://www.sql-server-performance.com/deadlocks.asp
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:3FE71243-9EAA-4C17-BF27-781C41670925@.microsoft.com...
> Hi Uri,
> Thank you very much for your help. I am currently getting Deadlock message
> from time to time, I was wondering how this commit rollback will effect
> it.
> Since there will not be auto-commit for each update (I have many updates
> before calling SP2, SP3, and one single commit for all the update, will
> this
> increase the chance of deadlock or decrease it?
>
> "Uri Dimant" wrote:
>> Jim
>> CREATE PROCEDURE BigOne
>> AS
>> DECLARE @.err integer
>> BEGIN TRANSACTION
>> EXEC @.err = sp1
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp2
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp3
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp4
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> COMMIT TRANSACION
>> GO
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
>> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
>> > follows:
>> >
>> > Exec Sp1
>> > Inserts, updates
>> > Exec Sp2
>> > Inserts, upadtes
>> > Exec Sp3
>> > If error Exit Exec Sp1
>> >
>> > Can I end Sp1 if I catch an error in Sp3
>> >
>>|||Thanks Uri, yes I was reading that article, since the commit will be
performed for a long transaction I will probably get more deadlocks in this
case. Do you agree with me?
"Uri Dimant" wrote:
> JIM
> Read this article
> http://www.sql-server-performance.com/deadlocks.asp
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:3FE71243-9EAA-4C17-BF27-781C41670925@.microsoft.com...
> > Hi Uri,
> >
> > Thank you very much for your help. I am currently getting Deadlock message
> > from time to time, I was wondering how this commit rollback will effect
> > it.
> > Since there will not be auto-commit for each update (I have many updates
> > before calling SP2, SP3, and one single commit for all the update, will
> > this
> > increase the chance of deadlock or decrease it?
> >
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Jim
> >> CREATE PROCEDURE BigOne
> >> AS
> >> DECLARE @.err integer
> >> BEGIN TRANSACTION
> >> EXEC @.err = sp1
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp2
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp3
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp4
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> COMMIT TRANSACION
> >> GO
> >>
> >>
> >>
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> >> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> >> > follows:
> >> >
> >> > Exec Sp1
> >> > Inserts, updates
> >> > Exec Sp2
> >> > Inserts, upadtes
> >> > Exec Sp3
> >> > If error Exit Exec Sp1
> >> >
> >> > Can I end Sp1 if I catch an error in Sp3
> >> >
> >>
> >>
> >>
>
>

Q: SP2

Hello,
Is RS service packs cumulative, meaning can I just directly install SP2
without SP1?
Thanks,yes u can install the SP2 it includes the sp1
"JIM.H." wrote:
> Hello,
> Is RS service packs cumulative, meaning can I just directly install SP2
> without SP1?
> Thanks,
>