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 123»»»

BLOCKING CAUSED BY REORGANIZE OF INDEX JOB IN SQL SERVER 2005 Expand / Collapse
Author
Message
Posted Monday, September 30, 2013 2:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 123, Visits: 453
hi all,
need your help on this.

i have a job which is reorganizing the index and with that i am having a issue of blocking when ever the job is running the blocking is coming up.AS per my knowledge reorganize of index is online and it should not create long holding blocking or blocking due to any update or select.

guide me if i am wrong and suggest for a solution and why the blocking happening ?



thanks
Ivan
Post #1499887
Posted Monday, September 30, 2013 5:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Two points ...

1) are you running this maintenance job in peak hours... it should be run in nights (OFF - peak hours)
2) have you selectiong the advanced option in reindxing/rebuilding" like 'Sort in Tempdb ' and "Keep index online while .."


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1499928
Posted Monday, September 30, 2013 5:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 5,308, Visits: 9,700
Rebuilding indexes will take locks; reorganising won't. Please will you post your code?

John
Post #1499930
Posted Monday, September 30, 2013 5:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 123, Visits: 453
Reorganize of index is always online by-Default and we run it off hours less transaction.
Post #1499932
Posted Monday, September 30, 2013 5:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 123, Visits: 453
below is the script that i use to reorganize the index

DECLARE @Database VARCHAR(255)


DECLARE @Table VARCHAR(255)

DECLARE @cmd NVARCHAR(500)

DECLARE @fillfactor INT

DECLARE @Statement NVARCHAR(300)



SET @fillfactor = 90



DECLARE DatabaseCursor CURSOR FOR

SELECT name

FROM sys.databases

WHERE name NOT IN (''master'',''msdb'',''tempdb'',''model'')

AND DATABASEPROPERTYEX(name, ''Status'')=''ONLINE'' AND is_read_only<>1

ORDER BY 1







OPEN DatabaseCursor



FETCH NEXT FROM DatabaseCursor INTO @Database

WHILE @@FETCH_STATUS = 0

BEGIN



SET @cmd = ''DECLARE TableCursor CURSOR FOR SELECT ''''['''' + table_catalog + ''''].['''' + table_schema + ''''].['''' +

table_name + '''']'''' as tableName FROM '' + @Database + ''.INFORMATION_SCHEMA.TABLES

WHERE table_type = ''''BASE TABLE''''''



-- create table cursor

EXEC (@cmd)



OPEN TableCursor



FETCH NEXT FROM TableCursor INTO @Table

WHILE @@FETCH_STATUS = 0

BEGIN



IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)

BEGIN

-- SQL 2005 or higher command

SET @cmd = ''ALTER INDEX ALL ON '' + @Table + '' REORGANIZE ''

EXEC (@cmd)

--PRINT (@cmd)

END

ELSE

BEGIN

-- SQL 2000 command

DBCC DBREINDEX(@Table,'' '',@fillfactor)



END



--PRINT N''UPDATING STATISTICS '' + @Table

SET @Statement = ''UPDATE STATISTICS '' + @Table + '' WITH FULLSCAN''

EXEC sp_executesql @Statement

--PRINT @Statement



FETCH NEXT FROM TableCursor INTO @Table

END



CLOSE TableCursor

DEALLOCATE TableCursor



FETCH NEXT FROM DatabaseCursor INTO @Database

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor
Post #1499935
Posted Monday, September 30, 2013 5:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 5,308, Visits: 9,700
I don't know why that would hold a lock for a long period of time. Have you tried to use sys.dm_os_waiting_tasks to see which session is doing the blocking?

John
Post #1499940
Posted Monday, September 30, 2013 5:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 123, Visits: 453
yes that is what i am asking i am getting blocking email when ever this job is running so asked you all to help me to find a solution .
Post #1499943
Posted Monday, September 30, 2013 6:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Do we need to update statistics AFTER index rebuild/reorgainze ??

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1499951
Posted Monday, September 30, 2013 6:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 123, Visits: 453
Well that is not the issue over here plz read my question throughly and then reply.
Post #1499958
Posted Monday, September 30, 2013 6:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 5,308, Visits: 9,700
What is a blocking email and under what circumstances do you get one? Have you managed to ascertain which particular statement is causing the blocking?

John
Post #1499963
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse