Moving SCCM DB from one drive to another

Recently i faced a problem on a SCCM 2012 R2 Primary site were SCCM & SQL was installed on a single server. The Server was having 2 drives C (60 GB) & D (200 GB) & SCCM was installed on C Drive soon this drive started filling up. Now, the best was to increase the free space in the drive was to move SCCM DP on another drive i.e. D.

The process of moving a SQL database is fairly simple :-

1. Stop the SCCM Services.
2. Detach SCCM Databse.
3. Move SCCM Database to another drive.
4. Attach the Database from the new location.
5. Restart SCCM Service.

But, even after the successful completion of above steps when you try to create an Application package or Software update group the process fails.

Let's first discuss the DB move process (from one drive to another) and then we will move to the troubleshooting part.

DB Move :-

1. Stop the SCCM Services.

You can go to services & turn off SMS services or open command prompt with admin rights & go to following location :-

C:\Program files\Microsoft Configuration Manager\Bin\x64\00000409

Run the following command - Preinst.exe /STOPSITE

Let the command run after some time all SMS service will be stopped.

2. Detach SCCM Databse.

Once the services are stopped, open SQL Management studio, Right click on the SCCM DB goto task/Detach :-

Check Drop Connections & Update Statistics :-

Once the DB is detached it will be removed from SQL Management studio :-

3. Move SCCM Database to another drive.

Now go to C:\Program files\Microsoft SQL server\MSSQL11.MSSQLSERVE\MSSQL\DATA & Cut the SCCM DB MDF & LDF files :-

Paste it in your desired location :-

Now, the DB is in E Drive :-

4. Attach the Database from the new location.

Open SQLManagement Studio, Right click on Database & click on Attach :-

Click on Add :-

Browse to the new SCCM DB location, Select the DB & click on OK :-
 

Click on OK :-

5. Restart SCCM Service.

Restart the highlighted SCCM Services :-

After the the successful completion of the above process, when you try to create an Application Package or Software Update Group it fails with the following error :-

This is a known issue & Microsoft has released a KB describing this issue, here's the link

Follow these steps to resolve the issue :-

1. Run this command on the SCCM DB

ALTER DATABASE <ConfigMgr DB>

SET TRUSTWORTHY ON

2. Check if SA account is the owner of SCCM DB.

3. Run this command to check if the Isolation level value is set to READ_COMMITTED_SNAPSHOT. 

DBCC USEROPTIONS

If the isolation value is not set to READ_COMMITTED_SNAPSHOT run the following command :-

ALTER DATABASE <ConfigMgr DB>

SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE <ConfigMgr DB>

SET READ_COMMITTED_SNAPSHOT ON