Why 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] -
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]The answer is literally in the error-message. From MSDN[^]
Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
The answer is literally in the error-message. From MSDN[^]
Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
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?
-
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?
-
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]According to this KB article[^], SQL 2012 should be able to rebuild the index online. Try installing SP3[^], possibly followed by the latest cumulative update[^]. Or, if you've got SP2, install CU4[^] or one of the later cumulative updates[^].
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
