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

job for index defrag Expand / Collapse
Author
Message
Posted Wednesday, December 26, 2007 11:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 7:59 AM
Points: 49, Visits: 397
i have a job which is failing

my lead wants me to avoid the cursors in that job and modify it
any suggestion would be of great help please

--

DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT database_name FROM WHERE status='y'
AND database_name NOT IN ('master','model','msdb','tempdb')
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




SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor


thanks
Post #436453
Posted Thursday, December 27, 2007 7:14 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 26, 2009 3:02 PM
Points: 515, Visits: 655
All that I have in my script library is a cursor-based script, which believe came from the SQL Server 2000 Books Online (BOL).

--

--Begin Script--

/*Perform a 'USE ' to select the database in which to run the script.*/
-- Declare variables
USE CG
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 5.0

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO

--End Script--

Happy T-SQLing


"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Post #436647
Posted Thursday, December 27, 2007 8:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 7:59 AM
Points: 49, Visits: 397
Hey Damon

Thanks for your reply and i appreciate your time and i did liked your code anyways my lead wants me to remove all the cursors use something else instead of cursor's so i dont want any cursor's


Thanks


if you can provide a solution without any cursor would be a great help
Post #436690
Posted Thursday, December 27, 2007 9:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:00 PM
Points: 33,204, Visits: 15,354
You could do something like:

create table #tables (table_name varchar(80), completed datetime)

insert #tables
SELECT TABLE_NAME, null
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

while exists (select table_name from #tables where completed is null)
begin
select top 1 @table = table_name from #tables where completed is null
... exec code
update #tables
set completed = getdate()
where table_name = @table

end

Use this to drive your system. Not sure it's much better than a cursor, but if you change #tables to some permanent table, you can track when things are happening, even restart if the job fails with a new table.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #436712
Posted Friday, December 28, 2007 1:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:26 AM
Points: 7,005, Visits: 8,452
We use acursor driven proc but just altering it to be #temptbl oriented, you might handle it like this:
DECLARE @SQLStatement nvarchar(4000)
--DECLARE TableList CURSOR LOCAL FAST_FORWARD READ_ONLY FOR

Declare @tmpReindexList table (SeqNo int identity(1,1) not null, SQLStatement nvarchar(4000) not null)

insert into @tmpReindexList (SQLStatement)
SELECT

N'DBCC DBREINDEX (''['+ db_name() + '].' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
+ ''')' as SQLStatement

FROM
INFORMATION_SCHEMA.TABLES
WHERE EXISTS
(
SELECT *
FROM sysindexes
WHERE id =
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME))
AND
indid IN(0,1)
)
ORDER BY 1

-- OPEN TableList
declare @SeqNo int
WHILE 1 = 1
BEGIN
--FETCH NEXT FROM TableList INTO @TableName
Select top 1 @SeqNo = SeqNo, @SQLStatement=SQLStatement
from @tmpReindexList
if @@rowcount = 0 break

print @SQLStatement

EXEC(@SQLStatement)

delete from @tmpReindexList where SeqNo = @SeqNo

END
-- CLOSE TableList
-- DEALLOCATE TableList

exec sp_updatestats

dbcc updateusage(0) with count_rows

btw I've kept the commented lines in the code so you can easily see the difference


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #436926
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse