I've got a webjob triggered by my asp.net server that executes several stored procedures as part of a maintenance cycle (triggered by C#), however the user I'm accessing the database as doesn't appear able to run the AzureSQLMaintenance sproc. The command (exec AzureSQLMaintenance 'all') errors out with the following message:

The specified schema name "154e669f-8721-4e64-863b-e6cc9148f10f@93a5c70e-b223-4d0e-a0cf-4b358d5ba4a0" either does not exist or you do not have permission to use it.
-----------------------
set operation = all
set mode = smart
set ResumableIndexRebuild = 0
set RebuildHeaps = 0
set LogToTable = 0
-----------------------

I'm using an Azure security group (which has the relevant website added as a member), which has permissions enabled on the database as below:

CREATE USER [SQLAccess] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [SQLAccess];
ALTER ROLE db_datawriter ADD MEMBER [SQLAccess];
ALTER ROLE db_ddladmin ADD MEMBER [SQLAccess];

CREATE ROLE db_executer
GRANT EXECUTE to db_executer
ALTER ROLE db_executer ADD MEMBER [SQLAccess];
GO

I have also tried adding the user as db_owner, but that doesn't seem to have any effect on running the procedure. I can successfully run the procedure via SSMS, logged in as myself (I'm a member of a group that is set as the Azure Active Directory admin for the server) and the server is set to accept Azure Active Directory authentication only. I can also trigger the job successfully while running the webserver on my local machine, which uses my azure credentials (the ones that fall into the AAD admin role).

Any clue whether adding extra permissions will fix the issue for the SQLAccess user, or am I looking in the wrong place?

Hi,

I'm running this maintanence procedure for quite a while already and i've noticed that it doesn't rebuild/reorganize columnstore indexes although they are very fragmanted.

Is it out of scope for this script?

Thanks!

Hi Yochanan,

Thank you for these scripts 👍
We had a database that grew over time to > 250GB; basically forcing us to upgrade the service tier from S0 to S3. We have been able to 'prune' the database back into a 'normal' size of ~80GB, but the Allocated space is still ~280 GB. so I'm attempting to run your script to reduce the size. But I keep getting the below exception. I had ran it before, and then got a timeout, and than ran it again. I have now left the database alone for several days until running it again, but still:

Current File Size: 286432MB
Actual used Size: 80096MB
Desired File Size: 100000MB
Interation shrink size: 50MB
Desired Size check - OK
Jan 25 2019  2:28PM - Iteration starting
File ID 1 of database ID 6 cannot be shrunk as it is either being shrunk by another process or is empty.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Jan 25 2019  2:30PM - Iteration completed. current size is: 286432
Done

ps: I found the scripts via https://social.msdn.microsoft.com/Forums/en-US/57a67dde-59d9-4977-9ebe-9074b31582d6/how-to-shrink-logs-on-azure-sql
Since that topic is already very old I did not want to revive that.

Kind regards,-

Help text is switched between the RebuildHeaps and LogToTable options

as per comment from Ian on the blog post

https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-maintain-azure-sql-indexes-and-statistics/bc-p/3029677#M583

ianstirk Occasional Visitor
‎Jan 07 2022 11:33 AM
Hi,
thanks for creating this utility, it's very useful.
Looking at the code, I can see it says "Remove statistics if it is handled by index rebuild / reorginize...", please note that an index reorg does not update the statistics e.g. https://dba.stackexchange.com/questions/48991/does-a-re-index-update-statistics
thanks
Ian

this is confirmed, and we should not skip updating stats when their corresponding index goes for reorganize

I am running procedure with the smart option, and the log table on and I can see that process is doing an alter index rebuild on an index that fragmentation is 0% with 0 pages.
Why is it doing that? Can I disable this somewhere in proc?

Any tips for how to make this process less DTU-intensive?

ALTER TABLE [vf].[v_matter] REBUILD ;
FAILED : 46518The feature 'ALTER TABLE' is not supported with external tables.

It bashes on past this, but obviously the script then ends in the error state so other errors will be concealed. I'll see if I can find time to put together a PR.

I am working in a DB where some system generated index names (below) are causing syntax issues. I realize the syntax is totally incorrect, but since this is coming from a product system generating the indexes, I think the script here can probably be changed to accommodate odd naming

The constraint name I'm running up against is named 'EPK[dbo].[testprofile2_role]' (you can't make this up, I suspect it was initially a bug on creating the default constraints).

I'm thinking to escape it correctly the query to get the index names can be altered slightly to add a "replace(idxs.name, ']',']]')" - such as:

select 
	i.[object_id]
	,ObjectSchema = OBJECT_SCHEMA_NAME(i.object_id)
	,ObjectName = object_name(i.object_id) 
	,IndexName = replace(idxs.name, ']',']]')
	,i.avg_fragmentation_in_percent
	,i.page_count
	,i.index_id
	,i.partition_number
	,i.index_type_desc
	,i.avg_page_space_used_in_percent
	,i.record_count
	,i.ghost_record_count
	,i.forwarded_record_count
	,null as OnlineOpIsNotSupported

Procedure fails when it tries to do rebuild on Clustered Column Store Index:

ALTER INDEX [TableName] REBUILD WITH(ONLINE=ON,MAXDOP=1, RESUMABLE=ON);

Error:
Msg 35397, Level 16, State 1, Line 1 alter statement failed because the operation cannot be performed resumably on a table with a columnstore index. Perform the operation without specifying the RESUMABLE option or drop (or disable) the columnstore index before performing the operation using the RESUMABLE option.

Get index information per tables instead of once for all tables
This will allow us more flexibility with per table maintenance.
for big databases it is taking time to get the index information before we start to process the maintenance.

Hi Yochanan,
Question: How does this compare to Automatic Tuning now available for Azure SQL? Can it be used along side? Anything to be careful about if used with geo-replicas and read-only secondaries?

https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=azuresqldb-current

Our nightly run of AzureSQLMaintenance tends to plateau our DTUs at 100% for about 2 hours. We've narrowed down the culprit to an UPDATE STATISTICS call on a single, particularly huge stat. This made me wonder what the effect would be of dropping WITH FULLSCAN and allowing the default sampling to be used. I tested this manually and the difference was huge - the update completed in just a few minutes without FULLSCAN.

From the docs:

For most workloads, a full scan isn't required, and default sampling is adequate. However, certain workloads that are sensitive to widely varying data distributions may require an increased sample size, or even a full scan. While estimates may become more accurate with a full scan than a sampled scan, complex plans may not substantially benefit.

My question is, would be it appropriate to default to leaving WITH FULLSCAN out of the generated script (here), and perhaps adding adding a new parameter to opt-in? Or is this considered a best practice in this case? (i.e. would a nightly maintenance script not fall under the category of "most workloads"?)

Thanks in advance!

Based on blog comment by: Paul N – ExakTime

We’ve been using your script for sometime. Thank you! We’ve discovered a bug (see error message below). It’s trying to rebuild a clustered index using online tag for a table that has a column of data type TEXT. According to Microsoft this is a limitation ( https://docs.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-2017 ). The rebuild for a clustered index for a table which has a column of data type image, text or ntext needs to be done offline.

Is this something you could fix in your script? Thank you for the consideration!

FAILED : 2725An online operation cannot be performed for index ‘IX_tb_Employee_EmployeeID’ because the index contains column ‘Notes’ of data type text, ntext, image or FILESTREAM. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

As I see there is only a handling for indexes but no
alter table TABLE_NAME rebuild with (ONLINE = ON);

Is there any reason why this is missing?

I like the idea posted in the blog post, seeking for suggestion to limit the time for the maintenance.
here is the comment:

Matt Darnell Occasional Visitor
‎Jan 14 2022 07:01 PM
Hi Yochanan,

This works well so thank you for the great blog. However, we find some of the databases are massive and we wanted to see if you had any suggestions to how we could add a time limit to the maintenance.. IE we only have a 100 Min window when its not being used, so we cant run full maintenance all the time, this works as it always takes the highest fragmented indexes first - then periodically we have a longer window where we can upscale the Database and run full maintenance, then down scale back to production cores.

This way we do as much maintenance in a small window as possible and we find if we do this nightly we actually do catch up with the normal fragmentation. anything that spills over gets done in the monthly longer window.

But currently, we have to interrupt the automation manually. we were hoping for some sleep and have the automation stop the query after X time.

regards
Matt

there are two options, we can set time limit for starting new commands.
however, is we start long running command just a second before time limit, we might go beyond the time.
for resumable index operation we can just stop the command and resume after.

Accept a new options for @operation called "versioncheck" or similar
Perform no maintenance and just return a @Version value. That way in our automated jobs we can detect whether we have the latest version deployed.

On a SQL17 instance, Developer Edition (64-bit), we have a table with FILESTREAM columns. When AzureSQLMaintenance (current version) decides that its PK be rebuilt we get:

ALTER INDEX [PK_Medium] ON [dbo].[Medium] REBUILD WITH(ONLINE=ON,MAXDOP=1);
FAILED : 2725An online operation cannot be performed for index 'PK_Medium' because the index contains column 'ReducedImage' of data type text, ntext, image or FILESTREAM. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

It seems that determining OnlineOpIsNotSupported doesn't take FILESTREAM into account. I think that the line

where t.name in ('text','ntext','image')

should be

where t.name in ('text','ntext','image') OR c.is_filestream = 1

Our table:

CREATE TABLE [dbo].[Medium](
	[MediumId] [int] IDENTITY(1,1) NOT NULL,
	[MediumType] [nvarchar](50) NOT NULL,
	[MediumContent] [varbinary](max) FILESTREAM  NULL,
	[ReducedImage] [varbinary](max) FILESTREAM  NOT NULL,
	[InsertDateTime] [datetime2](7) NOT NULL,
	[UpdateDateTime] [datetime2](7) NOT NULL,
	[RowGuid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[FileName] [nvarchar](260) NULL,
	[CreateTime] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Medium] PRIMARY KEY CLUSTERED 
(
	[MediumId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] FILESTREAM_ON [ebrida_filestream],
 CONSTRAINT [UQ_Medium_RowGuid] UNIQUE NONCLUSTERED 
(
	[RowGuid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] FILESTREAM_ON [ebrida_filestream]
GO

ALTER TABLE [dbo].[Medium] ADD  CONSTRAINT [DF_Medium_MediumContent]  DEFAULT (0x) FOR [MediumContent]
GO

ALTER TABLE [dbo].[Medium] ADD  CONSTRAINT [DF_Medium_ReducedImage]  DEFAULT (0x) FOR [ReducedImage]
GO

ALTER TABLE [dbo].[Medium] ADD  CONSTRAINT [DF_Medium_InsertDateTime]  DEFAULT (sysutcdatetime()) FOR [InsertDateTime]
GO

ALTER TABLE [dbo].[Medium] ADD  CONSTRAINT [DF_Medium_UpdateDateTime]  DEFAULT (sysutcdatetime()) FOR [UpdateDateTime]
GO

ALTER TABLE [dbo].[Medium] ADD  CONSTRAINT [DF_Medium_RowGuid]  DEFAULT (newid()) FOR [RowGuid]
GO

ALTER TABLE [dbo].[Medium] ADD  CONSTRAINT [DF_Medium_CreateTime]  DEFAULT (sysutcdatetime()) FOR [CreateTime]
GO

Of course, the stored procedure is named Azure SQLMaintenance for a reason, and there's no filestream on Azure. Yet, we use this procedure in on-premise SQL instances with great success, so I guess this modification could be helpful to others.

The procedure attempts to rebuild indexes on table-valued function. These should be ignored or skipped.

Attempting to run:
ALTER INDEX [PK_indexName] ON [dbo].[functionName] REBUILD WITH(ONLINE=ON,MAXDOP=1);

Error:
FAILED : 1914 Index cannot be created on object 'dbo.stfGetOrgUnitIdsBySchedGroupOrOrgUnit' because the object is not a user table or view.

while counting the indexes there is an extra filter for index_id 1 or 2 which eliminate counting of indexes if there are more than two indexes on a table.
this does not impact any functionality, and it's just reporting issue.

Feedback from Conrad Buck
Assing a "print only" switch so commands will be just displayed and not executed.
a flavor of "WhatIF" functionality.

Statistics aren't being updated if they belong to an index that wasn't updated. In the section with the comment Remove statistics if it is handled by index rebuild / reorganize the script removes stats that exist in #idxBefore but that includes all indexes, not just the ones that are being rebuilt. It should probably include the same where clause and/or delete records from #idxBefore that aren't being updated.

This was the change we made locally to fix the issue:

Thank you to Nag Reddipalle
for reporting the issue in Tech Community page.

https://techcommunity.microsoft.com/t5/azure-database-support-blog/automating-azure-sql-db-index-and-statistics-maintenance-using/bc-p/2024597/highlight/true#M429

for scenario where we have large tables and we do not want to go through all the data for the stats update it would help to have some control for per table settings that will set the percentage of the scan while updating stats.

Love the script, been working so well but we hit an issue today with heaps and forwarded records:

https://www.brentozar.com/archive/2016/07/fix-forwarded-records/

There's some discussion in there about this: olahallengren/sql-server-maintenance-solution#48

and it looks like you're similarly ignoring heaps in your script as well.

The AzureSQLMaintenance script can take some time to execute. Because of that, I would like to be able to interrupt the execution. In SQL Management Studio you can easily do this with the 'Cancel Executing Query` button.

From code I tried it with calling .Cancel() (of the SqlCommand) after 5 seconds but this didn't work. After some investigation I found at that it was because in the script the with nowait is used, which causes that a .Cancel() does not work (which is also commented in comment https://stackoverflow.com/a/24834029)

When I remove all the with nowait from the script, the .Cancel() works perfectly.

Is it safe to remove all the with nowait?

Hi!

Thank you for script "AzureSQLMaintenance.txt". It´s very nice.
But... it looks like that it doesn´t handle situation when there is horizontal partitioning on table. So it reports many indices and rebuild itself will try to rebuild one index and all it´s partitions multiple times. It would be great if there was only one rebuild. Rebuild by partition is not so important, I think...

Best regards!