June 20, 2016 at 10:21 am
I had a job setup to run the index maintenance using the SQLFool script.
The system has multiple databases with on VLDB at over 1TB but the rest are maybe 100GB together
I had 2 jobs setup, 1 for the VLDB and one for everything else.
2 weeks ago, everything ran fine with the settings I had in place and it took about 30 minutes to complete the everything else.
Last week and this week the job was still running after 11 hours.
When I try and run the script as it is in the job it hits the VLDB
The script I have is basically
Execute dbo.dba_indexDefrag_sp
@database = 'smallDB'
Anyone know why it is hitting the VLDB and not the individual "smallDB" that is specified? my googlefu seems to be week today as I have looked for something even remotely similar and come up with 0 in the last few hours.
June 21, 2016 at 3:53 am
You'll have to debug the sp you call.
The version of the sp I found takes a @database parameter which is VARCHAR(128) and it defaults to NULL . When it's NULL, it tries to do all databases. So check that.
You could also run this to see what's returned .
SELECT database_id
, name
, 0 -- not scanned yet for fragmentation
FROM sys.databases AS d
JOIN dbo.dba_parseString_udf(@database, ',') AS x
ON d.name = x.stringValue
WHERE [name] NOT IN ('master', 'tempdb')-- exclude system databases
AND [state] = 0 -- state must be ONLINE
AND is_read_only = 0; -- cannot be read_only
You can also run the sp and pass @debugmode = 1. That gives lots of info.
And @executeSQL = 0. That allows you to see what it will run without it processing
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply