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.