Query for Rebuild all Indexes with update statistics of Database at Single Click

Question

Hi All,

Can you please help me schedule (task scheduler ) below script on Azure Pass databases.

DECLARE @TableName VARCHAR(255)

DECLARE @sql NVARCHAR(500)

DECLARE @fillfactor INT

SET @fillfactor = 80

DECLARE TableCursor CURSOR FOR

SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName

FROM sys.tables

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

PRINT (@sql)

EXEC (@sql)

 

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

Answer 1

Hi TS

Welcome to Q&A Forum, this is a great place to get support, answers and tips.

Thank you for posting your query, I'll be more than glad to help you out.

As I understood, you want to run that script on a regular basis to rebuild all indexes of a given database, and you want to use the Windows Task Scheduler for that...

Then you have to store that script as a *.sql file, write a PowerShell script which connects to your database and executes that sql-script (for example with commands from the module "dbatools"), then you create a task in task scheduler to trigger/run that PowerShell script every week (eg).

https://docs.dbatools.io/Invoke-DbaQuery.html

https://voiceofthedba.com/2015/12/09/running-powershell-with-task-scheduler/

Or a different approach would be using Azure Automation to run that *.sql script as explained in this blog post:

https://geeks.ms/davidjrh/2015/10/08/rebuilding-sql-database-indexes-using-azure-automation/

but this post is already some kind of old and there where a lot of changes in the portal, so maybe service names had been changed and the design of the portal will look different. but the blog post and the explanation should lead you into the right direction.

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 2

You can put that maintenance script on a stored procedure, and then follow the steps outlined on this Microsoft article to schedule its execution on Azure Automation.

Answer 3

Thanks for your question.

You can also automate Azure SQL DB index and statistics maintenance using Azure Data Factory

Here is a blog and detailed steps for reference.

https://techcommunity.microsoft.com/t5/azure-database-support-blog/automating-azure-sql-db-index-and-statistics-maintenance-using/ba-p/3057821

Please let us know if this helps!

Thank you.