Could not find database

[WebSuburbFTV]

SQLindexManager_error.txt The app finds my Azure SQL database ID 5

[sergiisyrovatchenko]

Hi @WebSuburbFTV . Please provide a steps to reproduce this issue

Also please run this query from SSMS to ensure that everything fine with your rights:

SELECT DatabaseName  = [name]
     , DataSize      = CAST(NULL AS BIGINT)
     , DataUsedSize  = CAST(NULL AS BIGINT)
     , LogSize       = CAST(NULL AS BIGINT)
     , LogUsedSize   = CAST(NULL AS BIGINT)
     , RecoveryModel = [recovery_model_desc]
     , LogReuseWait  = [log_reuse_wait_desc]
     , CreateDate    = DATEADD(MINUTE, -DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), [create_date])
FROM sys.databases WITH(NOLOCK)
WHERE [state] = 0
    AND ISNULL(HAS_DBACCESS([name]), 1) = 1

SELECT @@VERSION

If possible please provide result of this query.

Thanks!

[WebSuburbFTV]

Steps to reproduce

1. Unzip the SQLindexManager
2. Run SQLIndexManager.exe (as administrator)
3. Enter credentials for SQL Server
4. Select the FxTradeResults database

Query result master 1 NULL NULL NULL NULL FULL NOTHING 2015-08-17 17:22:30.103 FxTradeResults 5 NULL NULL NULL NULL FULL NOTHING 2015-08-17 17:22:39.503

From: Sergii Syrovatchenko @.> Sent: Thursday, April 29, 2021 8:39 AM To: sergiisyrovatchenko/SQLIndexManager @.> Cc: WebSuburbFTV @.>; Mention @.> Subject: Re: [sergiisyrovatchenko/SQLIndexManager] Could not find database (#27)

Hi @WebSuburbFTV https://github.com/WebSuburbFTV . Please provide a steps to reproduce this issue

Also please run this query from SSMS to ensure that everything fine with your rights:

SELECT DatabaseName = [name] , DataSize = CAST(NULL AS BIGINT) , DataUsedSize = CAST(NULL AS BIGINT) , LogSize = CAST(NULL AS BIGINT) , LogUsedSize = CAST(NULL AS BIGINT) , RecoveryModel = [recovery_model_desc] , LogReuseWait = [log_reuse_wait_desc] , CreateDate = DATEADD(MINUTE, -DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), [create_date]) FROM sys.databases WITH(NOLOCK) WHERE [state] = 0 AND ISNULL(HAS_DBACCESS([name]), 1) = 1

Thanks!

— You are receiving this because you were mentioned.

[sergiisyrovatchenko]

Thanks for your answer!

• SELECT @@VERSION?
• This database in AG?
• SELECT * FROM sys.databases WHERE database_id = 5?
• Also please provide a log file

[WebSuburbFTV]

Microsoft SQL Azure (RTM) - 12.0.2000.8 Apr 19 2021 17:05:18 Copyright (C) 2019 Microsoft Corporation

name FxTradeResults

database_id 5

source_database_id NULL

owner_sid 0x010600000000016400000000000000008BAC8A0F3AD1C94D8450B1AE3CAE2828

create_date 22:39.5

compatibility_level 120

collation_name SQL_Latin1_General_CP1_CI_AS

user_access 0

user_access_desc MULTI_USER

is_read_only 0

is_auto_close_on 0

is_auto_shrink_on 0

state 0

state_desc ONLINE

— You are receiving this because you were mentioned.

[sergiisyrovatchenko]

Please share information from log file. Thanks!

[WebSuburbFTV]

OK, now this is getting beyond my limited SQL knowledge.

In Azure portal it seems I have all log activity switched off. I am the only user of this database so I don’t use any tracking.

Which diagnostic setting should I enable in order to generate a log?

— You are receiving this because you were mentioned.

[sergiisyrovatchenko]

I mean this file D:\GIT\SQLIndexManager\Bin\SQLIndexManager.log, because need to know when this error was appear (before open connection or after). Also please check previous build v1.0.0.65 if possible.

Thanks!

[WebSuburbFTV]

I’ll check previous build in the meanwhile

— You are receiving this because you were mentioned.

[sergiisyrovatchenko]

Hi @WebSuburbFTV

Tried to reproduce at Microsoft SQL Azure (RTM) - 12.0.2000.8 version, but without any luck. Please provide a bit more details... first of all SQLIndexManager.log

Thanks!

[WebSuburbFTV]

Hi Sergi, apologies for the delay in responding. I got stuck with trying to download the previous version on GitHub, it’s not a platform that I use regularly.

I’ll try again, but unfortunately for the next day or two I have to complete my monthly administration. To pay my rent 😊

— You are receiving this because you were mentioned.

[sergiisyrovatchenko]

Hi @WebSuburbFTV. Any updates?

[WebSuburbFTV]

Hi,

Sorry for the delay. Admin for the month end now finished.

I downloaded version 65. Log file attached, the error message was the same.

From: Sergii Syrovatchenko @.> Sent: Wednesday, May 5, 2021 10:23 AM To: sergiisyrovatchenko/SQLIndexManager @.> Cc: WebSuburbFTV @.>; Mention @.> Subject: Re: [sergiisyrovatchenko/SQLIndexManager] Could not find database (#27)

Hi @WebSuburbFTV https://github.com/WebSuburbFTV . Any updates?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/sergiisyrovatchenko/SQLIndexManager/issues/27#issuecomment-832506980 , or unsubscribe https://github.com/notifications/unsubscribe-auth/ANAG5OKZHZBRDQLYEF7L2JLTMD565ANCNFSM43ZEU6VQ . https://github.com/notifications/beacon/ANAG5OKH2DTIOOVANRC2VL3TMD565A5CNFSM43ZEU6V2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOGGPQYZA.gif

[sergiisyrovatchenko]

Hi @WebSuburbFTV. Log file is missed at your last answer.

[WebSuburbFTV]

From: Sergii Syrovatchenko @.> Sent: Thursday, May 6, 2021 3:12 PM To: sergiisyrovatchenko/SQLIndexManager @.> Cc: WebSuburbFTV @.>; Mention @.> Subject: Re: [sergiisyrovatchenko/SQLIndexManager] Could not find database (#27)

Hi @WebSuburbFTV https://github.com/WebSuburbFTV . Log file is missed at your last answer.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/sergiisyrovatchenko/SQLIndexManager/issues/27#issuecomment-833511195 , or unsubscribe https://github.com/notifications/unsubscribe-auth/ANAG5ONWH3Z5FQDUJCZ4OLLTMKISRANCNFSM43ZEU6VQ . https://github.com/notifications/beacon/ANAG5OOVXB3QRUMJWZNC3GTTMKISRA5CNFSM43ZEU6V2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOGGXF6GY.gif

[sergiisyrovatchenko]

Hi @WebSuburbFTV. Please use a web version of GitHub. Seems all attachments are ignored.

[WebSuburbFTV]

I’m sorry, I don’t understand this at all. I don’t ever use github. Where do I find a web version of release 65?

1. I have downloaded the zip file from this address https://github.com/sergiisyrovatchenko/SQLIndexManager/releases/tag/1.0.0.65
2. I have extracted the folder from the zip file
3. Then I have run the file C:\Users\micro\Downloads\SQLIndexManager.exe

a. There are a number of dlls in the folder. I don’t understand what you mean by the attachments are ignored.

4. I have entered the SQL Server credentials

5. I select the database

6. And then this error is returned.

7. The log file is attached.

From: Sergii Syrovatchenko @.> Sent: Thursday, May 6, 2021 3:56 PM To: sergiisyrovatchenko/SQLIndexManager @.> Cc: WebSuburbFTV @.>; Mention @.> Subject: Re: [sergiisyrovatchenko/SQLIndexManager] Could not find database (#27)

Hi @WebSuburbFTV https://github.com/WebSuburbFTV . Please use a web version of GitHub. Seems all attachments are ignored.

https://user-images.githubusercontent.com/44335685/117310116-ad882500-ae8b-11eb-9f93-a9448f58e323.png

— You are receiving this because you were mentioned. Reply to this email directly, https://github.com/sergiisyrovatchenko/SQLIndexManager/issues/27#issuecomment-833543428 view it on GitHub, or https://github.com/notifications/unsubscribe-auth/ANAG5ONL3UEXX5YZJRFTV33TMKNWJANCNFSM43ZEU6VQ unsubscribe. https://github.com/notifications/beacon/ANAG5OK4SWNIAOBUJB37J3LTMKNWJA5CNFSM43ZEU6V2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOGGXN2BA.gif

[sergiisyrovatchenko]

Hi @WebSuburbFTV

Sorry for disturbing, but I don't see any attachment. Please provide C:\Users\micro\Downloads\SQLIndexManager.log via simple text here.

Thanks!

[sergiisyrovatchenko]

Hi @WebSuburbFTV. Any updates?

[WebSuburbFTV]

I don’t know why the log file attachment is getting through to you.

Is this the content you are looking for?

16:39:31.145 - Log folder: C:\Users\micro\Downloads\SQLIndexManager

16:40:29.690 - Host: j00828xdok.database.windows.net,1433

16:40:29.690 - Server: SQL Server 2014 RTM (12.0.2000.8) SQL Azure

16:40:30.117 - Refresh databases...

16:40:30.597 - Elapsed time: 00:00:00:450. Found 2 databases

16:40:37.557 - Describe: FxTradeResults

16:40:37.934 - Error: .Net SqlClient Data Provider

Could not find database ID 5. The database ID either does not exist, or the database was dropped before a statement tried to use it. Verify if the database ID exists by querying the sys.databases catalog view.

The statement has been terminated.

16:40:41.943 - Processed: 0. Fragmented: 0. No indexes found. Try searching again or change settings...

16:43:08.440 - Refresh databases...

16:43:08.503 - Elapsed time: 00:00:00:053. Found 2 databases

16:43:11.825 - Describe: FxTradeResults

16:43:11.970 - Error: .Net SqlClient Data Provider

Could not find database ID 5. The database ID either does not exist, or the database was dropped before a statement tried to use it. Verify if the database ID exists by querying the sys.databases catalog view.

The statement has been terminated.

16:49:01.557 - Processed: 0. Fragmented: 0. No indexes found. Try searching again or change settings...

16:49:06.106 - Refresh databases...

16:49:06.407 - Elapsed time: 00:00:00:302. Found 2 databases

16:49:34.183 - Describe: FxTradeResults

16:49:34.256 - Error: .Net SqlClient Data Provider

Could not find database ID 5. The database ID either does not exist, or the database was dropped before a statement tried to use it. Verify if the database ID exists by querying the sys.databases catalog view.

The statement has been terminated.

16:50:30.657 - Grid layout saved: C:\Users\micro\Downloads\SQLIndexManager\SQLIndexManager.layout

From: Sergii Syrovatchenko @.> Sent: Friday, May 14, 2021 8:32 AM To: sergiisyrovatchenko/SQLIndexManager @.> Cc: WebSuburbFTV @.>; Mention @.> Subject: Re: [sergiisyrovatchenko/SQLIndexManager] Could not find database (#27)

Hi @WebSuburbFTV https://github.com/WebSuburbFTV . Any updates?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/sergiisyrovatchenko/SQLIndexManager/issues/27#issuecomment-841042052 , or unsubscribe https://github.com/notifications/unsubscribe-auth/ANAG5OOME2LDJP6YCX2IEMTTNS7XBANCNFSM43ZEU6VQ . https://github.com/notifications/beacon/ANAG5OM4LNXCRCXAWVID2GTTNS7XBA5CNFSM43ZEU6V2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOGIQURBA.gif

[sergiisyrovatchenko]

Hi @WebSuburbFTV

I can't reproduce your issue. Please run this query for FxTradeResults database:

SET NOCOUNT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF

IF OBJECT_ID('tempdb.dbo.#ExcludeList') IS NOT NULL
    DROP TABLE #ExcludeList

CREATE TABLE #ExcludeList (ID INT PRIMARY KEY)

INSERT INTO #ExcludeList
SELECT [object_id]
FROM sys.objects WITH(NOLOCK)
WHERE [type] IN ('V', 'U')
    AND ( [is_ms_shipped] = 1 )

IF OBJECT_ID('tempdb.dbo.#Partitions') IS NOT NULL
    DROP TABLE #Partitions

SELECT [object_id]
     , [index_id]
     , [partition_id]
     , [partition_number]
     , [rows]
     , [data_compression]
INTO #Partitions
FROM sys.partitions WITH(NOLOCK)
WHERE [object_id] > 255
    AND [rows] > 0
    AND [object_id] NOT IN (SELECT * FROM #ExcludeList)

IF OBJECT_ID('tempdb.dbo.#Indexes') IS NOT NULL
    DROP TABLE #Indexes

CREATE TABLE #Indexes (
      ObjectID         INT NOT NULL
    , IndexID          INT NOT NULL
    , PagesCount       BIGINT NOT NULL
    , UnusedPagesCount BIGINT NOT NULL
    , PartitionNumber  INT NOT NULL
    , RowsCount        BIGINT NOT NULL
    , DataCompression  TINYINT NOT NULL
    , IndexName        SYSNAME NULL
    , IndexType        TINYINT NOT NULL
    , IsAllowPageLocks BIT NOT NULL
    , DataSpaceID      INT NOT NULL
    , IsUnique         BIT NOT NULL
    , IsPK             BIT NOT NULL
    , FillFactorValue  INT NOT NULL
    , IsFiltered       BIT NOT NULL
    , PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)

INSERT INTO #Indexes
SELECT p.ObjectID
     , p.IndexID
     , p.PagesCount
     , p.UnusedPagesCount
     , p.PartitionNumber
     , p.RowsCount
     , p.DataCompression
     , IndexName        = i.[name]
     , IndexType        = i.[type]
     , IsAllowPageLocks = i.[allow_page_locks]
     , DataSpaceID      = i.[data_space_id]
     , IsUnique         = i.[is_unique]
     , IsPK             = i.[is_primary_key]
     , FillFactorValue  = i.[fill_factor]
     , IsFiltered       = i.[has_filter]
FROM (
    SELECT ObjectID         = p.[object_id]
         , IndexID          = p.[index_id]
         , PartitionNumber  = p.[partition_number]
         , DataCompression  = MAX(p.[data_compression])
         , RowsCount        = ISNULL(SUM(p.[rows]), 0)
         , PagesCount       = SUM(a.[total_pages])
         , UnusedPagesCount = SUM(CASE WHEN ABS(a.[total_pages] - a.[used_pages]) > 32 THEN a.[total_pages] - a.[used_pages] ELSE 0 END)
    FROM #Partitions p
    JOIN (
        SELECT [container_id]
             , [total_pages] = SUM([total_pages])
             , [used_pages]  = SUM([used_pages])
        FROM sys.allocation_units WITH(NOLOCK)
        WHERE [total_pages] > 0
        GROUP BY [container_id]
    ) a ON a.[container_id] = p.[partition_id]
    GROUP BY p.[object_id]
           , p.[index_id]
           , p.[partition_number]
) p
JOIN sys.indexes i WITH(NOLOCK) ON i.[object_id] = p.ObjectID AND i.[index_id] = p.IndexID  
WHERE i.[type] IN (0, 1, 2)
    AND i.[object_id] > 255

DECLARE @files TABLE (ID INT PRIMARY KEY)
INSERT INTO @files
SELECT DISTINCT [data_space_id]
FROM sys.database_files WITH(NOLOCK)
WHERE [state] != 0
    AND [type] = 0

IF @@ROWCOUNT > 0 BEGIN

    DELETE FROM i
    FROM #Indexes i
    LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] AND i.PartitionNumber = dds.[destination_id]
    WHERE ISNULL(dds.[data_space_id], i.DataSpaceID) IN (SELECT * FROM @files)

END

DECLARE @DBID   INT
      , @DBNAME SYSNAME

SET @DBNAME = DB_NAME()
SELECT @DBID = [database_id]
FROM sys.databases WITH(NOLOCK)
WHERE [name] = @DBNAME

IF OBJECT_ID('tempdb.dbo.#Fragmentation') IS NOT NULL
    DROP TABLE #Fragmentation

CREATE TABLE #Fragmentation (
      ObjectID         INT NOT NULL
    , IndexID          INT NOT NULL
    , PartitionNumber  INT NOT NULL
    , Fragmentation    FLOAT NOT NULL
    , PageSpaceUsed    FLOAT NULL
    , PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)

INSERT INTO #Fragmentation (ObjectID, IndexID, PartitionNumber, Fragmentation, PageSpaceUsed)
SELECT i.ObjectID
     , i.IndexID
     , i.PartitionNumber
     , r.[avg_fragmentation_in_percent]
     , r.[avg_page_space_used_in_percent]
FROM #Indexes i
CROSS APPLY sys.dm_db_index_physical_stats(@DBID, i.ObjectID, i.IndexID, i.PartitionNumber, 'LIMITED') r
WHERE r.[index_level] = 0
    AND r.[alloc_unit_type_desc] = 'IN_ROW_DATA'
    AND i.IndexType IN (0, 1, 2)

IF OBJECT_ID('tempdb.dbo.#Columns') IS NOT NULL
    DROP TABLE #Columns

CREATE TABLE #Columns (
      ObjectID     INT NOT NULL
    , ColumnID     INT NOT NULL
    , ColumnName   SYSNAME NULL
    , SystemTypeID TINYINT NULL
    , IsSparse     BIT
    , IsColumnSet  BIT
    , MaxLen       INT
    , PRIMARY KEY (ObjectID, ColumnID)
)

INSERT INTO #Columns
SELECT ObjectID     = [object_id]
     , ColumnID     = [column_id]
     , ColumnName   = [name]
     , SystemTypeID = [system_type_id]
     , IsSparse     = [is_sparse]
     , IsColumnSet  = [is_column_set]
     , MaxLen       = [max_length]
FROM sys.columns WITH(NOLOCK)
WHERE [object_id] IN (SELECT DISTINCT i.ObjectID FROM #Indexes i)

IF OBJECT_ID('tempdb.dbo.#IndexColumns') IS NOT NULL
    DROP TABLE #IndexColumns

CREATE TABLE #IndexColumns (
      ObjectID   INT NOT NULL
    , IndexID    INT NOT NULL
    , OrderID    INT NOT NULL
    , ColumnID   INT NOT NULL
    , IsIncluded BIT NOT NULL
    , PRIMARY KEY (ObjectID, IndexID, ColumnID)
)

INSERT INTO #IndexColumns
SELECT ObjectID   = [object_id]
     , IndexID    = [index_id]
     , OrderID    = CASE WHEN [is_included_column] = 0 THEN [key_ordinal] ELSE [index_column_id] END
     , ColumnID   = [column_id]
     , IsIncluded = ISNULL([is_included_column], 0)
FROM sys.index_columns ic WITH(NOLOCK)
WHERE EXISTS(
        SELECT *
        FROM #Indexes i
        WHERE i.ObjectID = ic.[object_id]
            AND i.IndexID = ic.[index_id]
            AND i.IndexType IN (1, 2)
    )

IF OBJECT_ID('tempdb.dbo.#Lob') IS NOT NULL
    DROP TABLE #Lob

CREATE TABLE #Lob (
      ObjectID    INT NOT NULL
    , IndexID     INT NOT NULL
    , IsLobLegacy BIT
    , IsLob       BIT
    , PRIMARY KEY (ObjectID, IndexID)
)

INSERT INTO #Lob (ObjectID, IndexID, IsLobLegacy, IsLob)
SELECT c.ObjectID
     , IndexID     = ISNULL(i.IndexID, 0)
     , IsLobLegacy = MAX(CASE WHEN c.SystemTypeID IN (34, 35, 99) THEN 1 END)
     , IsLob       = 0
FROM #Columns c
LEFT JOIN #IndexColumns i ON c.ObjectID = i.ObjectID AND c.ColumnID = i.ColumnID
WHERE c.SystemTypeID IN (34, 35, 99)
GROUP BY c.ObjectID
       , i.IndexID

IF OBJECT_ID('tempdb.dbo.#Sparse') IS NOT NULL
    DROP TABLE #Sparse

CREATE TABLE #Sparse (ObjectID INT PRIMARY KEY)
INSERT INTO #Sparse
SELECT DISTINCT ObjectID
FROM #Columns
WHERE IsSparse = 1
    OR IsColumnSet = 1

IF OBJECT_ID('tempdb.dbo.#AggColumns') IS NOT NULL
    DROP TABLE #AggColumns

CREATE TABLE #AggColumns (
      ObjectID        INT NOT NULL
    , IndexID         INT NOT NULL
    , IndexColumns    NVARCHAR(MAX)
    , IncludedColumns NVARCHAR(MAX)
    , PRIMARY KEY (ObjectID, IndexID)
)

INSERT INTO #AggColumns
SELECT t.ObjectID
     , t.IndexID
     , IndexColumns = STUFF((
            SELECT ', [' + c.ColumnName + ']'
            FROM #IndexColumns i
            JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID
            WHERE i.ObjectID = t.ObjectID
                AND i.IndexID = t.IndexID
                AND i.IsIncluded = 0
            ORDER BY i.OrderID
        FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '')
     , IncludedColumns = STUFF((
            SELECT ', [' + c.ColumnName + ']'
            FROM #IndexColumns i
            JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID
            WHERE i.ObjectID = t.ObjectID
                AND i.IndexID = t.IndexID
                AND i.IsIncluded = 1
            ORDER BY i.OrderID
        FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '')
FROM (
    SELECT DISTINCT ObjectID, IndexID
    FROM #Indexes
    WHERE IndexType IN (1, 2)
) t

IF OBJECT_ID('tempdb.dbo.#Stats') IS NOT NULL
    DROP TABLE #Stats

CREATE TABLE #Stats (
      ObjectID      INT NOT NULL
    , IndexID       INT NOT NULL
    , IsNoRecompute BIT
    , StatsSampled  FLOAT
    , RowsSampled   BIGINT
    , PRIMARY KEY (ObjectID, IndexID)
)

INSERT INTO #Stats (ObjectID, IndexID, IsNoRecompute, StatsSampled, RowsSampled)
SELECT s.[object_id]
     , s.[stats_id]
     , s.[no_recompute]
     , p.[rows_sampled] * 100. / NULLIF(p.[rows], 0)
     , p.[rows_sampled]
FROM (
    SELECT DISTINCT s.[object_id]
                  , s.[stats_id]
                  , s.[no_recompute]
    FROM sys.stats s WITH(NOLOCK)
    WHERE EXISTS(
            SELECT *
            FROM #Indexes i
            WHERE s.[object_id] = i.ObjectID
                AND s.[stats_id] = i.IndexID
                AND i.IndexType IN (1, 2)
        )
) s
CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.[stats_id]) p

DECLARE @MINUTE INT
SET @MINUTE = DATEDIFF(MINUTE, GETUTCDATE(), GETDATE())

SELECT i.ObjectID
     , i.IndexID
     , i.IndexName
     , ObjectName       = o.[name]
     , SchemaName       = s.[name]
     , i.PagesCount
     , i.UnusedPagesCount
     , i.PartitionNumber
     , i.RowsCount
     , i.IndexType
     , i.IsAllowPageLocks
     , u.TotalUpdates
     , u.TotalSeeks
     , u.TotalScans
     , u.TotalLookups
     , u.LastWrite
     , u.LastRead
     , i.DataCompression
     , f.Fragmentation
     , f.PageSpaceUsed
     , IndexStats       = DATEADD(MINUTE, -@MINUTE, STATS_DATE(i.ObjectID, i.IndexID))
     , IsLobLegacy      = ISNULL(lob.IsLobLegacy, 0)
     , IsLob            = ISNULL(lob.IsLob, 0)
     , IsSparse         = CAST(CASE WHEN p.ObjectID IS NULL THEN 0 ELSE 1 END AS BIT)
     , IsPartitioned    = CAST(CASE WHEN dds.[data_space_id] IS NOT NULL THEN 1 ELSE 0 END AS BIT)
     , FileGroupName    = fg.[name]
     , CreateDate       = DATEADD(MINUTE, -@MINUTE, o.[create_date])
     , ModifyDate       = DATEADD(MINUTE, -@MINUTE, o.[modify_date])
     , i.IsUnique
     , i.IsPK
     , i.FillFactorValue
     , i.IsFiltered
     , a.IndexColumns
     , a.IncludedColumns
     , ss.IsNoRecompute
     , ss.StatsSampled
     , ss.RowsSampled
FROM #Indexes i
JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = i.ObjectID
JOIN sys.schemas s WITH(NOLOCK) ON s.[schema_id] = o.[schema_id]
LEFT JOIN #Stats ss ON ss.ObjectID = i.ObjectID AND ss.IndexID = i.IndexID
LEFT JOIN #AggColumns a ON a.ObjectID = i.ObjectID AND a.IndexID = i.IndexID
LEFT JOIN #Sparse p ON p.ObjectID = i.ObjectID
LEFT JOIN #Fragmentation f ON f.ObjectID = i.ObjectID AND f.IndexID = i.IndexID AND f.PartitionNumber = i.PartitionNumber
LEFT JOIN (
    SELECT ObjectID     = [object_id]
         , IndexID      = [index_id]
         , TotalUpdates = NULLIF([user_updates], 0)
         , TotalSeeks   = NULLIF([user_seeks], 0)
         , TotalScans   = NULLIF([user_scans], 0)
         , TotalLookups = NULLIF([user_lookups], 0)
         , LastWrite    = DATEADD(MINUTE, -@MINUTE, [last_user_update])
         , LastRead     = DATEADD(MINUTE, -@MINUTE, (
                               SELECT MAX(dt)
                               FROM (
                                   VALUES ([last_user_seek])
                                        , ([last_user_scan])
                                        , ([last_user_lookup])
                               ) t(dt)
                          ))
    FROM sys.dm_db_index_usage_stats WITH(NOLOCK)
    WHERE [database_id] = @DBID
) u ON i.ObjectID = u.ObjectID AND i.IndexID = u.IndexID
LEFT JOIN #Lob lob ON lob.ObjectID = i.ObjectID AND (lob.IndexID = i.IndexID OR (i.IndexID IN (0, 1) AND lob.IndexID = 0))
LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] AND i.PartitionNumber = dds.[destination_id]
JOIN sys.filegroups fg WITH(NOLOCK) ON ISNULL(dds.[data_space_id], i.DataSpaceID) = fg.[data_space_id] 
WHERE o.[type] IN ('V', 'U')

Any errors during execution?

Possible you don't have enough rights to run dm_db_index_physical_stats.

Thanks for your answer!

[sergiisyrovatchenko]

Hi @WebSuburbFTV

Please try to use v1.0.0.68 release.

Thanks!

[WebSuburbFTV]

Excellent, this works now.

Thanks!

I’ll try it out

From: Sergii Syrovatchenko @.> Sent: Monday, June 28, 2021 6:40 AM To: sergiisyrovatchenko/SQLIndexManager @.> Cc: WebSuburbFTV @.>; Mention @.> Subject: Re: [sergiisyrovatchenko/SQLIndexManager] Could not find database (#27)

Hi @WebSuburbFTV https://github.com/WebSuburbFTV

Please try to use v1.0.0.68 release.

Thanks!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/sergiisyrovatchenko/SQLIndexManager/issues/27#issuecomment-869348079 , or unsubscribe https://github.com/notifications/unsubscribe-auth/ANAG5OJ74UND2P7ZVIEEX4DTU74INANCNFSM43ZEU6VQ . https://github.com/notifications/beacon/ANAG5OJRDM63J47KHFY3CHDTU74INA5CNFSM43ZEU6V2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOGPITF3Y.gif