G’day,

I’m probably not the only one that has noticed that as the volume of data gets bigger in out databases, the time taken to preform maintenance increases.

This seems to be a simple fact of life for the DBA. But as our maintenance time increases, we need to be aware of if we are still able to meet out SLA’s.

For example, a database that too 20 minutes to restore 6 months ago may now be so full of data that it now takes 45 minutes to restore. If our SLA’s say 30 minutes then it was fine 6 months ago, but now we’ll definitely be breaking that contract. This probably means that your boss will not be too happy should you need to restore that database now.

Another thing that happens is that as data in tables gets bigger, so too do the indexes, and consequently the time to rebuild / reorganize will increase. In fact the time to rebuild / reorganize may be so long that doing ONLINE rebuilds may be your only option to maintain your SLA’s. For example, if you rebuild an index OFFLINE then any data associated with the index will be inaccessible for the period of the rebuild, possibly taking your downtime past any acceptable metric defined in your SLA’s.

To start with, you’ll either need the enterprise or datacenter version of SQL SERVER.

Assuming you’re got that, you’re going to have to be aware of certain criteria that exist around rebuilding indexes on line.

The table can’t contain a lob type, if it does then you’ll recieve the following error message.

USE master
GO
-- Drop the database if it already exists
IF  EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'TestDB'
)
BEGIN
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestDB;
END
GO
CREATE DATABASE TestDB
GO
USE TestDB;
GO
CREATE TABLE TestLOB
(
PK_ID INT IDENTITY(1,1) NOT NULL,
AGE INT NOT NULL,
FULLNAME VARCHAR(MAX) NOT NULL
CONSTRAINT PK_TESTLOB PRIMARY KEY CLUSTERED (PK_ID ASC)
);
INSERT INTO TestLOB(AGE, FULLNAME) VALUES(41,'Martin');
INSERT INTO TestLOB(AGE, FULLNAME) VALUES(39,'Suzanne');
INSERT INTO TestLOB(AGE, FULLNAME) VALUES(2,'Callum');
GO
ALTER INDEX PK_TESTLOB
ON TestLOB
REBUILD
WITH ( ONLINE = ON )
GO