WSUS Cleanup Using SQL Script

Whenever we do a cleanup on WSUS we always get the below error.

Every update on WSUS (even if they are unapproved) get their metadata sent to clients unless the updates are marked as expired

WSUS does not clean unneeded (obsolete? superseded? unapproved?) updates itself. we had been running it as a "fire and forget" server: allow it to auto approve security updates and then leaving it alone. This fills the database with a bunch of updates that gets sent out

WSUS has a Cleanup Wizard which is supposed to expire obsolete and unneeded updates. We tried running this, but it would get stuck and hang forever

There is lots of advice about how to fix this problem: defragging hard drives, running the Cleanup Wizard multiple times, running weird PowerShell scripts that launch the Wizard via the command line. Most of the time noting will work 

Finally we found the below script which will help to do the cleanup through SQL

1. ·         We can’t access WSUS database through remote SQL Management Studio, so we have to install SQL Management Studio locally.
2. ·         To connect SUSDB - \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query For Server 2012 \\.\pipe\MICROSOFT##WID\tsql\query.
3. ·         Make a New Query

Run the below query to check the status

exec spGetObsoleteUpdatesToCleanup

Again execute the below query to clean up the unapproved, superseded and obsolete updates.

DECLARE @var1 INT

                                DECLARE @msg nvarchar(100)

                                 CREATE TABLE #results (Col1 INT)

                                INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup

                                 DECLARE WC Cursor

                                FOR

                                SELECT Col1 FROM #results

                                OPEN WC

                                FETCH NEXT FROM WC

                                INTO @var1

                                WHILE (@@FETCH_STATUS > -1)

                                BEGIN SET @msg = 'Deleting ' + CONVERT(varchar(10), @var1)

                                RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID=@var1

                                FETCH NEXT FROM WC INTO @var1 END

                                CLOSE WC

                                DEALLOCATE WC

DROP TABLE #results

Now check if you see any update using this query - exec spGetObsoleteUpdatesToCleanup

Finally, run the WSUS cleanup wizard now, you will not get any errors now.

Task Sequence - Restart Option - Error (0x00000032)

In SCCM image deployment if you get the below error during software/application installation, it could be a problem with restart step in task sequence

To confirm this you have to check the deployment log and you will be getting the below error after the restart step,

The operating system reported error 50: The request is not supported

If you check the restart properties in the task sequence the important thing to note here is that the Reboot must be set to restart "The currently installed default operating system" otherwise it will try and run the TS boot image again which obviously won't really like trying to install drivers/software/applications. In short, be sure any reboot which forms part of the Task Sequence has the below option selected,

SCCM Console Object Edit Error (Cannot edit the object which is used by...)

Sometimes when you try to edit any objects in 2012 Console, you probably get the below error, 

his because the ConfigMgr console was crashing while editing the object.

We have to run the following query in SQL to free the objects again

 select * from SEDO_LockState where LockStateID <> 0
(where SEDO stands for Serialized Editing of Data Objects)

Collect the Lock ID from the above query result and delete it with the below query,

DELETE from SEDO_LockState where LockID = ‘<LockID of the record identified in the previous query>’

Once the record is removed, We should be able to modify the object again. Hope it helps!