The following SQL queries apply to NearPoint version 4.x:

General Mailbox Information

To determine the number of folders in a user’s mailbox archive:

select * from MessageFolder (nolock)
where MailboxID = 16

or if you just want the count

select COUNT(*) from MessageFolder (nolock)
where MailboxID = 16

To find information on a given mailbox by name:

select * from MimosaContext..Mailbox (nolock)
where MailboxName like '%clare%'

To show the number items awaiting processing, use the following query (Note that I’m not really sure about this.)

select mailboxid, COUNT(*)  from mimosaexchangeitem_1..messagequeryprep (nolock)
group by mailboxid

Mailbox Indices

Number of folders in a user’s mailbox:

use MimosaExchangeItem_1
go;
select * from MessageFolder (nolock)
where MailboxID = 16

Location of the Index chunk(s) for a given mailbox, e.g., MailboxId = ‘34’

use MimosaContext
go
select MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as Location, IL.IndexPath 
from storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
where MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
      AND LV.LogicalVolumeID=IL.LogicalVolumeID
      AND MB.MailboxId = '34'

Mailboxes queued for re-indexing:

use MimosaContext
go;
Select * from IndexTaskQueue
/* TaskType=23:  Queue for re-index; this task removes the indices from the original location */
/* TaskType=25:  Rebuilds index in new location */
Where TaskType = '23'

Mailboxes being re-indexed:

use MimosaContext
go;
Select * from IndexTaskQueue
/* TaskType=23:  Queue for re-index; this task removes the indices from the original location */
/* TaskType=25:  Rebuilds index in new location */
Where TaskType = '25'

Mailbox indices by location, mailbox id, store, etc.

use MimosaContext
go;
select MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as Location, IL.IndexPath  from storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)  where MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
      AND LV.LogicalVolumeID=IL.LogicalVolumeID
      /*AND MailboxStoreName LIKE '%MAILNO04%'*/
      /*AND MailboxName like 'HR%'*/
      /*AND MB.MailboxId = '1122'*/
      /*AND IL.IndexRelativePath LIKE '%INDEX2%'*/
      /*AND LV.SharePath LIKE '\MIMNVT02IORINDEX-02%'*/
        AND LV.SharePath LIKE '\MIMNVT02INDEX%'

Queue mailboxes for re-indexing based on location, mailbox id, etc.

use MimosaContext
go
 -- Declare variables
DECLARE @MailboxID VARCHAR(150)
DECLARE @MailboxDB TABLE(MailboxID NVARCHAR(150), StorageGroupName NVARCHAR(100), MailboxStoreName NVARCHAR(100), MailboxName NVARCHAR(100), Location NVARCHAR(100))
INSERT INTO @MailboxDB (MailboxID, StorageGroupName, MailboxStoreName, MailboxName, Location)
SELECT TOP 38 MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as Location 
from storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
where MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
      AND LV.LogicalVolumeID=IL.LogicalVolumeID
      /*AND MailboxStoreName LIKE '%MAILNO04%'*/
      /*AND MailboxName like 'HR%'*/
      /*AND IL.IndexRelativePath LIKE '%INDEX2%'*/
      /*AND LV.SharePath LIKE '\MIMNVT02IORINDEX-02%'*/
      AND LV.SharePath LIKE '\MIMNVT02INDEX%'

-- Create a cursor for the databases on each ServerInstance (Inner Loop)
DECLARE dbCursor CURSOR FOR
SELECT MailboxID FROM @MailboxDB
OPEN dbCursor

FETCH NEXT FROM dbCursor
INTO @MailboxID

WHILE (@@FETCH_STATUS=0)
BEGIN
      SELECT @MailboxID

      exec msisp_AddIndexingtask @MailboxID,0,'','','CONSOLE',0,0,DEFAULT,DEFAULT,23,0

      -- Fetch Next Record
      FETCH NEXT FROM dbCursor
      INTO @MailboxID
END

To determine the index locations for a given mailbox (e.g., ‘AssetID=16’) use the following queries:

select * from MimosaContext..AssetIndexMap (nolock)
where AssetId = 16
 select * from MimosaContext..Indexlocation (nolock)
where MailboxId = 16

To identify mailbox indices which have been split into multiple chunks:

use MimosaContext
go
select MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as Location, IL.IndexPath  from storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
where MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
      AND LV.LogicalVolumeID=IL.LogicalVolumeID
      AND IL.IndexRelativePath LIKE '%INDEX2%'

Re-index mailboxes consisting of more than one index chunk using the following script:

use MimosaContext
go

-- Declare variables
DECLARE @MailboxID VARCHAR(150)
DECLARE @MailboxDB TABLE(MailboxID NVARCHAR(150), StorageGroupName NVARCHAR(100), MailboxStoreName NVARCHAR(100), MailboxName NVARCHAR(100), Location NVARCHAR(100))

INSERT INTO @MailboxDB (MailboxID, StorageGroupName, MailboxStoreName, MailboxName, Location)
SELECT TOP 100 MB.MailboxID, SG.StorageGroupName, SG.MailboxStoreName, MB.MailboxName, LV.SharePath+IL.IndexRelativePath as Location 
from storageGroup SG (NOLOCK), Mailbox MB (NOLOCK), IndexLocation IL (NOLOCK), NPStorageLogicalVolume LV (NOLOCK)
where MB.StorageGroupID = SG.StorageGroupID AND MB.MailboxID = IL.MailboxID
      AND LV.LogicalVolumeID=IL.LogicalVolumeID
      AND IL.IndexRelativePath LIKE '%INDEX2%'

-- Create a cursor for the databases on each ServerInstance (Inner Loop)
DECLARE dbCursor CURSOR FOR
SELECT MailboxID FROM @MailboxDB
OPEN dbCursor

FETCH NEXT FROM dbCursor
INTO @MailboxID

WHILE (@@FETCH_STATUS=0)
BEGIN
      SELECT @MailboxID

      exec msisp_AddIndexingtask @MailboxID,0,'','','CONSOLE',0,0,DEFAULT,DEFAULT,23,0

      -- Fetch Next Record
      FETCH NEXT FROM dbCursor
      INTO @MailboxID
END

General

To extract XML code from a database field:

select cast(NPConfigDetails as XML) from MimosaContext..npconfiguration (nolock)

Fix for Mulitple Folders in a User’s Archived Mailbox

Confirm that there were multiple empty folders named “Inbox” in a single user’s archive.

–This is most likely due to archiving a mailbox with corruption. The folders were archived multiple times but the items were not.

Ran the following to clear the archived mailbox of empty folders:

--found mailbox ID of archived mailbox:
Select * from MimosaContext..Mailbox (nolock) Where MailboxName like '%Parriott%'

--mailboxid = 11

--changed databases to MimosaExchangeItem_1 and ran the following Stored Procedure:
exec msisp_DeleteEmptyFolders 802, 1
exec msisp_DeleteEmptyFolders MailboxID, 1
exec MimosaExchangeItem_1.dbo.msisp_DeleteEmptyFolders MailboxID, 1
exec msisp_DeleteEmptyFolders 11, 1

--"11" is the mailboxid and "1" is the item type "folder". Changes to archived mailbox is immediate.