Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Intelligent Index Reorganize and Rebuild Script - v1.0 Expand / Collapse
Author
Message
Posted Sunday, November 25, 2012 1:59 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 26, 2013 12:38 PM
Points: 338, Visits: 449
Comments posted to this topic are about the item Intelligent Index Reorganize and Rebuild Script - v1.0

Post #1388376
Posted Monday, November 26, 2012 8:43 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 94, Visits: 218
I'm running this but I'm getting

(0 row(s) affected)
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ')'.

I'm trying to track it down but it's a little complicated, and it's still running so I don't want to mess with it until it's finished. Any ideas on what could cause this?
Post #1388662
Posted Monday, November 26, 2012 8:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 7:46 AM
Points: 20, Visits: 41
Is there a way to run this against only a single database within the SQL Server? When I ran it, it ran against all 12 databases being hosted by the instance.

Thank you,
Wes Crockett
Post #1388668
Posted Monday, November 26, 2012 10:02 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 94, Visits: 218
I pulled the following code below from the script so I could try to debug my issue. You could use it to build the statements and then just run the ones you want.

Use YOURDATABASE
GO

SELECT avg_fragmentation_in_percent,
CASE WHEN avg_fragmentation_in_percent BETWEEN 5 AND 30
THEN 'ALTER INDEX [' + name + '] ON ' + (SELECT TOP 1 TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = OBJECT_NAME(b.[OBJECT_ID])
AND TABLE_TYPE = 'BASE TABLE')
+ '.[' + OBJECT_NAME(b.[OBJECT_ID]) + '] REORGANIZE ;'
WHEN avg_fragmentation_in_percent > 30
THEN 'ALTER INDEX [' + name + '] ON ' + (SELECT TOP 1 TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = OBJECT_NAME(b.[OBJECT_ID])
AND TABLE_TYPE = 'BASE TABLE')
+ '.[' + OBJECT_NAME(b.[OBJECT_ID]) + '] REBUILD WITH (FILLFACTOR = 90) ;'
END AS Index_Statement
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent > 5
AND index_type_desc <> 'HEAP'
AND page_count > 640
ORDER BY avg_fragmentation_in_percent DESC

Post #1388698
Posted Tuesday, November 27, 2012 2:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 61, Visits: 154
Tim,

Executing the Stored Proc on 2008R2 gives this error....

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '('.

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)
Post #1389407
Posted Wednesday, November 28, 2012 8:37 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 26, 2013 12:38 PM
Points: 338, Visits: 449
Hi everyone,

Take a look at the attached file, which has the SQL script in known good formatted state. I'm guessing there may have been an issue with the code formatting in the article versus what I actually have in my SQL script.

Please try the attached script and let me know if this resolves the issue.

The attached script name is ReorgRebuildIndex.txt. Of course you will need to change the extension back to a .SQL for it to run in SSMS.

Tim P.




  Post Attachments 
ReorgRebuildIndex.txt (38 views, 12.82 KB)
Post #1389846
Posted Thursday, December 20, 2012 2:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:06 PM
Points: 37, Visits: 176
Hi all,

I borrowed the guts of what Timothy Parker did and created a version that works on one database only, so there is no outer cursor loop. I also added two output parameters, a try/catch, and renamed some object to suit my conventions.

Thanks, Timothy

Jeff Roughgarden





  Post Attachments 
usp_ReorgRebuildIndexOneDB.txt (19 views, 6.85 KB)
Post #1399112
Posted Friday, May 9, 2014 8:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:27 AM
Points: 4, Visits: 91
Hi Tim,

I added a check to your code to skip off-line databases. Here is the original section of code:

--Begin our maintenance tasks.
DECLARE curDatabase CURSOR FOR
--This statement filters out system databases and databases with open cursors.
SELECT NAME
FROM sys.sysdatabases
WHERE DBID > 4 AND dbid NOT IN (
SELECT DISTINCT PRO.dbid
FROM sys.dm_exec_cursors(0) CURS INNER JOIN sys.sysprocesses PRO
ON CURS.session_ID = PRO.spid
WHERE is_open =1
)
AND dbid NOT IN (
SELECT DISTINCT SP.[dbid]
FROM sys.sysprocesses SP CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE [TEXT] LIKE 'FETCH%'
)
ORDER BY NAME
OPEN curDatabase

And here is the code with the added check.

--Begin our maintenance tasks.
DECLARE curDatabase CURSOR FOR
--This statement filters out system databases and databases with open cursors.
-- 2014-04-08 Tom Uellner: Added check for sys.databases [state] > 0 (database must be online)
SELECT NAME
FROM sys.sysdatabases
WHERE DBID > 4 AND dbid NOT IN (
SELECT DISTINCT PRO.dbid
FROM sys.dm_exec_cursors(0) CURS INNER JOIN sys.sysprocesses PRO
ON CURS.session_ID = PRO.spid
WHERE is_open =1
)
AND dbid NOT IN (
SELECT DISTINCT SP.[dbid]
FROM sys.sysprocesses SP CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE [TEXT] LIKE 'FETCH%'
)
AND dbid NOT IN (
SELECT database_id
FROM sys.databases
WHERE [state] > 0
)
ORDER BY NAME
OPEN curDatabase

-Tom
Post #1569306
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse