Repairing a Corrupt MMSC User Database

Topic Keywords: operator MMSC

We recently had a customer whose MMSC stopped automatically provisioning new users.

The MMSC user database had become corrupt, preventing new users from being able to be automatically provisioned.  While it is not known why this corruption occurred, the most common potential cause is an abnormal system shutdown, such as power failure.

Future versions of the MMSC will include start-up logic to detect and repair corrupt MMSC user databases automatically.  However, the following instructions for manual repair may be helpful if someone else encounters this problem.

Copy the MMSCUSERS.DB file (normally located in \ProgramData\NowSMS) to another location. Also copy SQLITE3.EXE from the NowSMS program directory.

From a command line prompt, issue the following command:

sqlite3 mmscusers.db

This command  should bring you to an sqlite> prompt.

Issue the following command:

select name from sqlite_master where type='table';

The system should respond back with:

Users

sqlite>

Issue the following command:

select count (*) from users;

The system should report how many users are in the database.

Issue the following command:

pragma integrity_check;

If the database is corrupt, this command should return an error.  If there is no corruption problem, this command will return OK.

Use the .quit command to exit the sqlite3 utility.

To attempt to recover the database, issue the following command:

sqlite3 mmscusers.db .dump > mmscusers.txt

mmscusers.txt will be a text file of SQL commands containing a database dump.

Inspect this text file to confirm that it has close to the number of users expected. If it does not, please contact NowSMS support for further analysis.

To create a new repaired database, issue the following command:

sqlite3 mmscusersnew.db < mmscusers.txt

The repaired database will be named mmscusersnew.db.

Check the size of mmscusersnew.db. If it is 0 bytes, it may be necessary to edit the mmscusers.txt file. With some database corruption problems, the last line of the file may be ROLLBACK; If this is the case, change ROLLBACK; to END TRANSACTION; and then repeat the previous step to create mmscusersnew.db.

Rename mmscusersnew.db to mmscusers.db and replace the original database in the NowSMS data directory. It is necessary to stop the NowSMS services in order to do this.