Indexing Tables in Azure Using Automation

If you’re developing in Azure it won’t be long before you need to do some indexing and not long after that some index maintenance. Microsoft has created an automation framework to enable you to schedule maintenance tasks and has provided some sample scripts that work pretty well. If you are using schemas other than dbo (and you should) then the sample runbook in the gallery named “Indexes Tables in An Azure Database If They Have A High Fragmentation” will give you an error when executing the defragmentation command.

azure_automation_2

The error looks something like this:

2/13/2015 6:26:05 PM, Error: Table CustomerExt could not be indexed. Investigate indexing each index instead of the complete table Exception calling

"Fill" with "1" argument(s): "Cannot find the object "Customer" because it does not exist or you do not have permissions."
At Update-SQLIndexRunbook:180 char:180
+
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException

The problem is actually around line 120 or so.

 

This block of code is the issue

# Get the list of tables with their object ids
$SQLCommandString = @"
SELECT t.name AS TableName, t.OBJECT_ID FROM sys.tables t
"@

Change it to this and you should be in good shape.

# Get the list of tables with their object ids
$SQLCommandString = @"
SELECT '['+s.name +'].[' + t.name + ']' AS TableName, t.OBJECT_ID
FROM sys.tables t
join sys.schemas s on t.schema_id = s.schema_id
"@

As you can see, you need to add the schema along with the table name. This is the second time this week I’ve seen this kind of error in published maintenance scripts. Rule of thumb … don’t assume dbo.

 

 

 

 

twitterrssby feather
twitterredditpinterestlinkedinmailby feather

Leave a Reply

Your email address will not be published. Required fields are marked *