Posts Tagged ‘Maintenance Plans’

Online Index Rebuild can Cause Fragmentation

June 9, 2014 Leave a comment

As Microsoft SQL Server PFE, I am always playing with SQL Server.  I generally spend most of my time on the engine side of world; so I am somewhat of a newbie when it comes to BI.  So I understand when I see something in BI world and am a bit lost.  However when I ran into this, rebuilding index online caused fragmentation.  I was some what baffled. You can try it if you wish, I tested it on SQL 2008, SQL 2008 R2, and SQL 2012.

-- Create table for testing.
CREATE TABLE Test (Col1 INT IDENTITY(1,1), Col2 CHAR(50), Col3 CHAR(50), Col4 Char(50)

-- Insert test data.
INSERT INTO dbo.Test (Col2, Col3, Col4)
FROM sys.all_objects O1
CROSS JOIN sys.all_objects O2

-- Check fragmentation.
SELECT * FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null)

-- Rebuild Index online using MAXDOP
WITH (Online=ON, MAXDOP=8)

-- Check fragmentation.
SELECT * FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null)

Some of you might noticed an interesting property.  ALLOW_PAGE_LOCK.

So turns out this is by design.  If ALLOW_PAGE_LOCK is turned off, we are building a index using parallelism, and building it online; it can cause fragmentation (Ref 1 & Ref 2).

Well lesson learned for future and no, it will not be last one.


"No description found" Error when modifying Maintenance Plans

February 19, 2009 Leave a comment

When you are trying to modify a maintenance plan; sometimes you might get “No Description Found” error and you cannot do any work in SSIS when that happens.

Everything was working properly for me until I had to install Microsoft Visio 2003 and I couldn’t update my maintenance plans any more.

In the reference link it explains because of MSXML DLL issue in Visio 2003 RTM edition it causes the issue with SSIS packages. Installing the newest Service Pack for Visio resolves the issue after reboot.

So this is not an issue with SQL Server Egine or SSIS Engine but just the Sql Server Management Studio (SSMS).

I also read an post on SQL Server Central where someone stated they got it to work just after running following commands also:

Regsvr32 “C:\WINDOWS\system32\msxml.dll”
Regsvr32 “C:\WINDOWS\system32\msxml3.dll”

You still have to reboot computer; I wanted to get the updates for Visio anyhow, so I didn’t get chance to try that.

Ref Link:

%d bloggers like this: