Not exactly a traditional gaps and islands problem but fun nevertheless. Here's a relatively simple alternative.

DECLARE @system_log TABLE(

PK_ID int PRIMARY KEY

,Sequence_ID int null

)

INSERT @system_log(

PK_ID

,Sequence_ID

)VALUES

(1035590, 35587),

(1035589, NULL),

(1035586, NULL),

(1035585, NULL),

(1035584, NULL),

(1035583, 35583),

(1035582, NULL),

(1035581, NULL),

(1035579, NULL),

(1035578, 35553),

(1035554, NULL),

(1035552, 35551),

(1035550, 35550)

SELECT PK_ID, Sequence_ID=CASE WHEN Sequence_ID IS NOT NULL THEN Sequence_ID ELSE

ROW_NUMBER() OVER (PARTITION BY rn2 ORDER BY PK_ID)-1 END

FROM

(

SELECT PK_ID, Sequence_ID

,rn2=MAX(Sequence_ID) OVER (ORDER BY PK_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

FROM @system_log

) a

ORDER by PK_ID desc;