Hi,
We have a SQL Azure DB which is about 5 GB & whenever we try to execute an query which takes little more time to process it gives following error. Retrying also gives the same error. (Timeout occurs after executing query for about 5-10 mins )
- "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
Sample queries that gives the error (First one deletes old records & other for fragmentation):
-------------------------------------------------------------------------------------------------
DELETE [Table]
where [Date] < DATEADD(d, -30, getdate())
and
Environment in ('DEV','QA1','QA2', 'QA4','STG','')
GO
-------------------------------------------------------------------------------------------------
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
(
SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName]
FROM INFORMATION_SCHEMA.TABLES IST
WHERE IST.TABLE_TYPE = 'BASE TABLE'
)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT('Rebuilding Indexes on ' + @TableName)
Begin Try
EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
End Try
Begin Catch
PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down for douing rebuild')
EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
End Catch
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
-------------------------------------------------------------------------------------------------
Is there anything that i can do to avoid this?
- Edited by Threedor Wednesday, July 16, 2014 6:11 AM
Other recent topics
