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 '\\MIMNVT02\IORINDEX-02%'*/
AND LV.SharePath LIKE '\\MIMNVT02\INDEX%'
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 '\\MIMNVT02\IORINDEX-02%'*/
AND LV.SharePath LIKE '\\MIMNVT02\INDEX%'
-- Create a cursor for the databases on each Server\Instance (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 Server\Instance (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)
