Wednesday, December 11, 2013

Fix: Reorganize Index Task fails in Maintenance Plan

If you are using the Maintenance Plan creator in SSMS, you may have observed that your Reorganize Index tasks are failing. The error is difficult to read unless you log the result to a file.

This is due to one or more indexes not allowing PAGE LOCKs which are required for executing ALTER INDEX REORGANIZE. ALLOW_PAGE_LOCKS = ON has to be set for each index you wish to reorganize.

I found this code on SQL Server Central which will ALTER all indexes to allow PAGE LOCKs. Thank you Ranjith:

Use this query to determine if you have any indexes that disallow page locks:

SELECT A.Name AS InName,ob.Name AS DBName
FROM sys.indexes A
LEFT OUTER JOIN sys.objects ob ON ob.object_id=A.Object_id
WHERE ALLOW_PAGE_LOCKS=0 and ob.type='U'


And here is the full code block to allow page locks:

SET NOCOUNT ON
DECLARE @DBName nvarchar(50), @INName nvarchar(50)
DECLARE @ODBName nvarchar(50), @OINName nvarchar(50)
Declare @execstr nvarchar(200)
DECLARE Index_cursor CURSOR FOR
SELECT A.Name AS InName,ob.Name AS DBName
FROM sys.indexes A
LEFT OUTER JOIN sys.objects ob ON ob.object_id=A.Object_id
WHERE ALLOW_PAGE_LOCKS=0 and ob.type='U'
-- Select only allow_page_locks 0 and User Tables
OPEN Index_cursor
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DBName +' ' + @INName
SET @ODBName = ltrim(rtrim(@DBName))
SET @OINName = ltrim(rtrim(@INName))
SELECT @execstr = 'ALTER INDEX '+@OINName+ ' ON '+
@ODBName+' SET (ALLOW_PAGE_LOCKS = ON)';
EXEC (@execstr);
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
END
CLOSE Index_cursor
DEALLOCATE Index_cursor


Thursday, September 12, 2013

Fix: “To run a ssis package outside of sql server data tools you must install send successful email of Integration Services or higher.”

This error was seen with SQL Server 2012 version component (SSIS, DB Engine, Agent):

Error: “To run a ssis package outside of sql server data tools you must install send successful email of Integration Services or higher.”

Scenario:

Host 1 - SQL Server 2012 DB Engine and Agent.
Host 2 - SSIS 2012

Package would execute successfully if executed from Host 2. If executed from Host 1 (where the Agent resides, with the ultimate intention of running as a job), it produces the error. Send Mail Task was the focus of the error.

To fix, SSIS 2012 components had to be installed on Host 1.

*This is not a desired solution, as the intention was to keep SSIS components on Host 2 only. If anyone has a solution that does not require SSIS to be installed on Host 1, please post and I'll update the info.

Please see this post for additional info.

Fix: SSIS Warning of Truncation From Database to Data Flow Column

The error appears as follows, even after verifying matching source --> destination length.

Validation warning. [PackageName] {GUID}: Truncation may occur due to retrieving data from database column "[SourceColumnName]" with a length of (x) to data flow column "DataFlowColumnName]" with a length of (y).

To fix, open the data flow task. Select columns, then deselect the columns, and re-select all needed columns.

This was seen with SSIS 2012.


Monday, March 25, 2013

Fix: Unable to shrink file

You've cleared DB connections, CHECKPOINTed, took a backup, but still can't shrink a file.

Grow the file slightly, even just 1 MB.

Then try the shrink again.

Here's a script you can use: http://www.sqlservercentral.com/Forums/Topic647662-5-1.aspx#bm648809