sp_IndexCleanup
Closed this issue · 5 comments
Version of the script
@Version = '-2147483648',
@version_date = '17530101';
What is the current behavior?
Executed with default parameters it completes but with the following error:
Error occurred: Cannot insert the value NULL into column 'database_id', table 'tempdb.dbo.#index_analysis_____________________________________________________________________________________________________000000A80594'; column does not allow nulls. INSERT fails.
If the current behavior is a bug, please provide the steps to reproduce.
EXEC sp_IndexCleanup
What is the expected behavior?
I don't know, it's the first run.
Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)
HAHAHA, that doesn't happen for me locally, but I can totally see why that would happen somewhere else. This should be fixed now.
With this version
@version = '-2147483648',
@version_date = '17530101';
I get a different error:
Warning: The join order has been enforced because a local join hint is used.
Error occurred: Violation of PRIMARY KEY constraint 'PK__#index_a__0E32247C47215F50'. Cannot insert duplicate key in object 'dbo.#index_analysis'. The duplicate key value is (Allergen, IX_Allergen_1).
With the current version, after about 5 minutes I get this:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
database_id = @database_id,
x.object_id,
x.index_id,
x.schema_name,
x.table_name,
x.index_name,
x.partition_id,
x.partition_number,
x.total_rows,
x.total_space_mb,
x.reserved_lob_mb,
x.reserved_row_overflow_mb,
x.data_compression_desc,
built_on =
ISNULL
(
psfg.partition_scheme_name,
psfg.filegroup_name
),
psfg.partition_function_name,
pc.partition_columns
FROM
(
SELECT
ps.object_id,
ps.index_id,
schema_name = s.name,
table_name = t.name,
index_name = i.name,
ps.partition_id,
p.partition_number,
total_rows = SUM(ps.row_count),
total_space_mb = SUM(a.total_pages) * 8 / 1024.0,
reserved_lob_mb = SUM(ps.lob_reserved_page_count) * 8. / 1024.,
reserved_row_overflow_mb = SUM(ps.row_overflow_reserved_page_count) * 8. / 1024.,
p.data_compression_desc,
i.data_space_id
FROM [MY_DB_NAME].sys.tables AS t
JOIN [MY_DB_NAME].sys.indexes AS i
ON t.object_id = i.object_id
JOIN [MY_DB_NAME].sys.schemas AS s
ON t.schema_id = s.schema_id
JOIN [MY_DB_NAME].sys.partitions AS p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
JOIN [MY_DB_NAME].sys.allocation_units AS a
ON p.partition_id = a.container_id
LEFT HASH JOIN [MY_DB_NAME].sys.dm_db_partition_stats AS ps
ON p.partition_id = ps.partition_id
WHERE t.type <> 'TF'
AND i.type = 2
GROUP BY
t.name,
i.name,
i.data_space_id,
s.name,
p.partition_number,
p.data_compression_desc,
ps.object_id,
ps.index_id,
ps.partition_id
) AS x
OUTER APPLY
(
SELECT
filegroup_name =
fg.name,
partition_scheme_name =
ps.name,
partition_function_name =
pf.name
FROM [MY_DB_NAME].sys.filegroups AS fg
FULL JOIN [MY_DB_NAME].sys.partition_schemes AS ps
ON ps.data_space_id = fg.data_space_id
LEFT JOIN [MY_DB_NAME].sys.partition_functions AS pf
ON pf.function_id = ps.function_id
WHERE x.data_space_id = fg.data_space_id
OR x.data_space_id = ps.data_space_id
) AS psfg
OUTER APPLY
(
SELECT
partition_columns =
STUFF
(
(
SELECT
N', ' +
c.name
FROM [MY_DB_NAME].sys.index_columns AS ic
JOIN [MY_DB_NAME].sys.columns AS c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
WHERE ic.object_id = x.object_id
AND ic.index_id = x.index_id
AND ic.partition_ordinal > 0
ORDER BY
ic.partition_ordinal
FOR XML
PATH(''),
TYPE
).value('.', 'nvarchar(MAX)'),
1,
2,
''
)
) AS pc
OPTION(RECOMPILE);
Warning: The join order has been enforced because a local join hint is used.
Error occurred: Invalid length parameter passed to the LEFT or SUBSTRING function.
Completion time: 2024-10-10T08:59:19.6642976+02:00
@leonardolombardo okay, great, this is where open source fun kicks in: I don't have access to your server, so I can't troubleshoot this part for you.
What I can tell you is that there are no LEFT or SUBSTRING functions in the query you posted up there, so this isn't where the error happened.
If you want to do the work to figure the error out, cool. You can either submit a pull request or tell me which query the problem is in. Just open a new issue for that and provide as much detail as possible.
Thanks!