August 31, 2017 at 10:35 am
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.
August 31, 2017 at 4:33 pm
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
August 31, 2017 at 4:47 pm
Minionware Reindex
Ola Hallengren Index and Statistics Maintenance
SQLFool Index Defrag
Sue
September 1, 2017 at 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.
September 1, 2017 at 10:18 am
mig28mx - Friday, September 1, 2017 8:46 AMHi 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply