Useful NearPoint SQL Queries

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)