Outlook for Mac 2016 database basic sqlite3 commands

Outlook 2011 had a proprietary database format and we (as users) had very few options to work with it.
Outlook 2016 now uses a sqlite3 databases for its identity (just like Mail).
While there is very little we can run from the app itself in terms of maintenance or troubleshooting, in theory we should be able to tap straight into the database through sqlite3 itself.

********* NONE OF THIS IS PUBLISHED/APPROVED/SUPPORTED BY MICROSOFT FOR OUTLOOK FOR MAC *********

As none of this is officially supported, I would recommend
1) BACKUP the database before anything.
You need to backup the three files:

  • Outlook.sqlite
  • Outlook.sqlite-shm
  • Outlook.sqlite-wal

That are in the ~/Library/Group Containers/UBF8T346G9.Office/Outlook/Outlook 15 Profiles/Main Profile/Data folder.

2) QUIT Outlook. You can’t run anything safely on the identity if it’s still in use in Outlook itself.

• You can check the integrity of the database with the command:
(triple-click the command to select all, then copy)
/usr/bin/sqlite3 ~/Library/Group\ Containers/UBF8T346G9.Office/Outlook/Outlook\ 15\ Profiles/Main\ Profile/Data/Outlook.sqlite "PRAGMA integrity_check"

If everything is fine, the Terminal will return “OK”
If you get a warning there, then obviously something in wrong in the database. In theory, Outlook should detect issues and automatically attempt to fix them, but there are been reports in the forums of identity failures and users no longer able to open up their identities. It might be worth attempting other “rescue” operations in these cases using additional commands like “.dump” etc. (which is beyond the point of this post: search for your favorite sqlite How To or Forum page for additional information on how to repair a corrupted sqlite3 database).

• A number of websites have illustrated how “vacuuming” the envelope index could help speed things up and trim the database in Mail. I figured that the same should apply to Outlook too.
I ran the command in Outlook and the database shrunk a bit. No problem within Outlook whatsoever on my Mac, but who knows… (hence the very very strong suggestion to make a backup).
Vacuuming” will rebuild the database file, reclaiming free space and defragmenting tables and index. I’m not sure whether Outlook is currently using auto_vacuum or not, but I have read reports of abnormally large Outlook database files which makes me suspect it’s not always taking place as it should if it is even enabled.

You can vacuum the database using:
(triple-click the command to select all, then copy)
/usr/bin/sqlite3 ~/Library/Group\ Containers/UBF8T346G9.Office/Outlook/Outlook\ 15\ Profiles/Main\ Profile/Data/Outlook.sqlite vacuum

On Answers, Michal Palat provides additional options that can allow you to rebuild the sqlite3 database by exporting the data to an ASCII “dump” and recreating a new one based on the output:
answers.microsoft.co…