SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


script to run dbcc indexdefrag for all databases in a server.


script to run dbcc indexdefrag for all databases in a server.

Author
Message
mig28mx
mig28mx
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 175
Hi all,
I'm trying to create a script to run dbcc indexdefrag not only for one database, but for all of my databases in my server.
I try the following:

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
DECLARE @name VARCHAR(50) -- database name
declare @SQL varchar(1000)
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb', 'MFB_Forms', 'MFB_Objects','ODDevContent','KMASTER')
AND name NOT LIKE 'D%'
AND name NOT LIKE '%_Forms'
and name not like '%_Objects'



OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
set @SQL = 'USE ' + @name + ';'
print @SQL
exec (@SQL)



-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
AND TABLE_NAME NOT IN('ConnectionInformation','SessionContextNames','DefineVariables')
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

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

GO


When I run this code on SSMS it runs perfect, but when I try to schedule on a job, it fails and only runs in the current database.
Any advice on this?

Thank you in advance.
Sue_H
Sue_H
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62725 Visits: 13322


Are you sure this is the exact same thing you ran in SSMS? It looks like it would run in just master if that's where it started.
In this part:
set @SQL = 'USE ' + @name + ';'
print @SQL
exec (@SQL)


Once you execute with the exec(@SQL), the change in database context with the USE statement is no longer in effect. Check the example in the documentation and the last bullet in for the section on Self-Contained batches.
Using sp_executesql

You can see this in your script - just add select db_name() after that execute. You could pull out the other code in your cursor and just use:
set @SQL = 'USE ' + @name + ';'
print @SQL
exec (@SQL)
print DB_NAME ()


It will just print print out master after each of the USE YourDatabase statements

Sue



Sue_H
Sue_H
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62725 Visits: 13322
Sorry, forgot to post these. Just for reference and if you want to get an idea of how others handled this or if you want to try their scripts, check these links. The last one is an older version but should cover you on SQL Server 2008. It still gives you an idea nonetheless:
Minionware Reindex
Ola Hallengren Index and Statistics Maintenance
SQLFool Index Defrag

Sue



mig28mx
mig28mx
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 175
Hi Sue,
Thank for your reply. Yes you are right. This procedure only runs in the master db. So the, objective is not covered with this script.
I will take a look to Ola Hallegeren Index to see if I can implement on my db server.
From the application point of view (I use Infor's SYTELINE ERP), the recommended procedure is to use dbcc INDEXDEFRAG for each table.

Thank you.
Sue_H
Sue_H
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62725 Visits: 13322
mig28mx - Friday, September 1, 2017 8:46 AM
Hi Sue,
Thank for your reply. Yes you are right. This procedure only runs in the master db. So the, objective is not covered with this script.
I will take a look to Ola Hallegeren Index to see if I can implement on my db server.
From the application point of view (I use Infor's SYTELINE ERP), the recommended procedure is to use dbcc INDEXDEFRAG for each table.

Thank you.


Correct since the use statement is no longer applicable after the exec. I think most of the others have a table for databases and table for indexes that would need to be defragemented. You could use temp tables or static tables that you truncate before processing. Most of how it is set up is in the beginning of those other scripts.
If the vendor recommends something, it's usually best to follow their recommendations.

Sue



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search