In the event that you find yourself needing to rename the WSS_UsageApplication database in your SharePoint 2010 farm, you’re in luck, as this database is one of the few service application databases that can be moved without having to re-provision the service application! Unlike most of the other databases in the farm, this one isn’t operated on by SharePoint services, but rather by direct calls from SharePoint or via SharePoint timer jobs. Since there are no services to stop, you can dive right in.
The first challenge is actually re-naming the database, as the service application account will still be connected to the database, preventing SQL Management Studio from obtaining an exclusive lock on the database. This is handled with a T-SQL statement that switches the database into single-user mode, renames it, and then switches it back to multi-user mode.
ALTER DATABASE WSS_UsageApplication SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE WSS_UsageApplication Modify Name = WSS_UsageApplication_NewName ;
ALTER DATABASE WSS_UsageApplication_NewName SET MULTI_USER;
Then, the WSS_UsageApplication service application needs to be updated to use the new database name. This is accomplished with a single Powershell command.
Set-SPUsageApplication -Identity "WSS_UsageApplication" -DatabaseName "WSS_UsageApplication_NewName" -DatabaseServer "<sql server name>"
And that’s all it takes! You can also use the Set-SPUsageApplication command to move the database to a different SQL server, but keep in mind that you will need to re-create the SQL login on the new server and fix the orphaned database user.