Finding long-running blocks

Can anyone point me to a script (or alternative method) which will find bloc
king that has been going on for a while?
TIA,
John BrownJohn
http://support.microsoft.com/defaul...224453-- :
Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
(Q224453)
"John Brown" <anonymous@.discussions.microsoft.com> wrote in message
news:9F0A15E4-4DBB-4F4F-8DDE-1E822E0300E3@.microsoft.com...
> Can anyone point me to a script (or alternative method) which will find
blocking that has been going on for a while?
> TIA,
> John Brown|||Hi.
You can utilize the SQL Profiler [SQL Enterprise Manager -> Tools -> SQL
Profiler] or The Windows 2000 Monitor to verify the blocks of your database
. Through the Monitor can see a histograma graphic.
Hermilson.
****************************************
******************************
Can anyone point me to a script (or alternative method) which will find bloc
king that has been going on for a while?|||Spot on - thanks!
John

Finding long-running blocks

Can anyone point me to a script (or alternative method) which will find blocking that has been going on for a while
TIA
John BrownJohn
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224453-- :
Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
(Q224453)
"John Brown" <anonymous@.discussions.microsoft.com> wrote in message
news:9F0A15E4-4DBB-4F4F-8DDE-1E822E0300E3@.microsoft.com...
> Can anyone point me to a script (or alternative method) which will find
blocking that has been going on for a while?
> TIA,
> John Brown|||Hi
You can utilize the SQL Profiler [SQL Enterprise Manager -> Tools -> SQL Profiler] or The Windows 2000 Monitor to verify the blocks of your database. Through the Monitor can see a histograma graphic
Hermilson
*********************************************************************
Can anyone point me to a script (or alternative method) which will find blocking that has been going on for a while|||Spot on - thanks
Johnsql

Find Non-Matching End Points

Hello,

I'm trying to manipulate some data to find occurances where a certain end point it missing. For example, in my table I have the following record:

Sample

SHAPE_ID

SEQ_NUM

X_COORDINATE

Y_COORDINATE

82

0

606494.606

4820354.142

82

1

606487.996

4820344.571

82

2

606512.455

4820319.828

82

3

606590.877

4820420.208

82

4

606611.069

4820404.432

82

5

606634.138

4820434.514

82

6

606614.812

4820449.179

82

7

606670.587

4820520.016

82

8

606638.161

4820546.216

82

9

606500.606

4820400.142

For shape_ id = 82 (I have thousands of these to process), Seq_num = 0 is the beginning of the item, while seq_num 9 is the end point. The end point (x_coordinate/y_coordinate)i.e. Seq_num=9 is supposed to match the begin x_coordinate/y_coordinate values, but in this example, they do not match.

How do I find each occurance of the same shape_id where the end seq_num (#9, or whatever number is has been incremented to) x_coordinate/y_cooordinate, does not match the begin x_coordinate/y_coordinate seq_num (#0) ?

As well, if any of these records are indeed found, how do I increment to the next highest sequence number and update that record with the begin point (seq_num=0) x_coordinate/y_coordinate values?

I would appreciate any help with this problem....Thx. in advance...Walt

Moving to the Transact-SQL forum. I believe that's better equipped to provide an answer.

|||

Walt:

Is this close to what you are looking for:

set nocount on

declare @.sample table
( shape_id integer,
seq_num integer,
x_coordinate numeric (12,3),
y_coordinate numeric (12,3),
primary key (shape_id, seq_num)
)
insert into @.sample values (82, 0, 606494.606, 4820354.142 )
insert into @.sample values (82, 1, 606487.996, 4820344.571 )
insert into @.sample values (82, 2, 606512.455, 4820319.828 )
insert into @.sample values (82, 3, 606590.877, 4820420.208 )
insert into @.sample values (82, 4, 606611.069, 4820404.432 )
insert into @.sample values (82, 5, 606634.138, 4820434.514 )
insert into @.sample values (82, 6, 606614.812, 4820449.179 )
insert into @.sample values (82, 7, 606670.587, 4820520.016 )
insert into @.sample values (82, 8, 606638.161, 4820546.216 )
insert into @.sample values (82, 9, 606500.606, 4820400.142 )
--insert into @.sample values (82,10, 606494.606, 4820354.142 )

--select * from @.sample

select a.shape_id,
a.seq_num,
a.x_coordinate as [Last X],
a.y_coordinate as [Last Y],
z.x_coordinate as [Zero X],
z.y_coordinate as [Zero Y]
from ( select shape_id,
seq_num,
x_coordinate,
y_coordinate
from @.sample p
where seq_num
= ( select max (seq_num) from @.sample q
where p.shape_id = q.shape_id
and q.seq_num > 0
)
) a
inner join @.sample z
on a.shape_id = z.shape_id
and z.seq_num = 0
and ( a.x_coordinate <> z.x_coordinate or
a.y_coordinate <> z.y_coordinate
)

-- shape_id seq_num Last X Last Y Zero X Zero Y
-- -- -- -- -- -- --
-- 82 9 606500.606 4820400.142 606494.606 4820354.142

update @.sample
set x_coordinate = a.x_coordinate,
y_coordinate = a.y_coordinate
from ( select shape_id,
seq_num,
x_coordinate,
y_coordinate
from @.sample p
where seq_num
= ( select max (seq_num) from @.sample q
where p.shape_id = q.shape_id
and q.seq_num > 0
)
) a
inner join @.sample z
on a.shape_id = z.shape_id
and z.seq_num = 0
and ( a.x_coordinate <> z.x_coordinate or
a.y_coordinate <> z.y_coordinate
)

select * from @.sample where seq_num = 0

-- shape_id seq_num x_coordinate y_coordinate
-- -- -- -- --
-- 82 0 606500.606 4820400.142

Dave

|||

Dave,

Thanks for your prompt reply. I'll hopefully give your code a try tomorrow.

Is this a sql statement that I can paste into the Sql View of a new sql statement?

Sorry for such a dumb question......Walt

|||

Dave,

I'm in the process of importing my table into my Oracle schema and am just in the process of creating a new table.

I have over 500,000 records to load so I'll give your reply a try.

thx again....Walt

Find databases in FULL recovery mode

I am maintaining the backend SQL 2000 Server for a number of Share
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.comWhy don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>