question

cornpoppy avatar image
cornpoppy asked

SQL server maintenance plan Cannot rebuild clustered index offline?

This is my table structure and I select 'for index types that do not support online index rebuild rebuild indexes offline' option in rebuild maintenance plan(use SQL maintenance plan-SQL Server 2012) but still getting error failed with the following error: "An online operation cannot be performed for index 'PK_Table1_1' because the index contains column 'FileContent' of data type text, ntext Table structure : CREATE TABLE [dbo].[Table1]( [ColumnID] [INT] NOT NULL, [ColumnName] [NVARCHAR](250) NOT NULL, [FileContent] [VARBINARY](MAX) FILESTREAM NOT NULL, [ColumnDate] [VARCHAR](50) NOT NULL, [UserID] [VARCHAR](50) NOT NULL, [DefaultColumn] [BIT] NOT NULL, [ID] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL CONSTRAINT [DF__TblCheque__ID__398D8EEE] DEFAULT (NEWID()), [ReadOnly] [BIT] NOT NULL, CONSTRAINT [PK_Table1_1] PRIMARY KEY CLUSTERED ( [ColumnID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [FileStreamG], CONSTRAINT [UQ__Table1__3214EC26C89CBB09] UNIQUE NONCLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY] ) ON [PRIMARY] FILESTREAM_ON [FileStreamG] select au.* from sys.system_internals_allocation_units au join sys.system_internals_partitions p on au.container_id = p.partition_id where p.object_id = object_id('dbo.Table1'); ![alt text][1] [1]: /storage/temp/3753-paint.png
sql-server-2012dbamaintenance-plansdba-developer
paint.png (10.4 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
David Wimbush avatar image
David Wimbush answered
I reckon that's a slightly out of date error message. I think it's the varbinary(max) column that's blocking the online rebuild. You'd have to rebuild it offline or reorganise it. See Books Online, specifically the details of the OFFLINE = ON clause: https://msdn.microsoft.com/en-us/library/ms188388.aspx
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

cornpoppy avatar image cornpoppy commented ·
Yes i know that ,The table has varbinary(max) column can not online rebuild ,But i check this option : 'for index types that do not support online index rebuild, rebuild indexes offline' in SQL maintenance plan (SQL Server 2012). So this plan does rebuild it offline but still does online why?
0 Likes 0 ·
David Wimbush avatar image David Wimbush commented ·
Sorry, I misread your question. I know it doesn't answer your question but have you considered using Ola Hallengren's solution instead? Many people prefer that to maintenance plans. You can pick which parts you want to use. I've never tried maintenance plans but I use Ola's index maintenance job and it works well for me.
0 Likes 0 ·
cornpoppy avatar image cornpoppy commented ·
Thank you so much, But i am not allowed to use another maintenance job.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.