August 13, 2012 at 3:03 pm
I am starting to try to use http://ola.hallengren.com/ site maintenance script.
I would like to exclude some database that start with Z
For example we have some archived database for previous years and we make the name something like Z11-12Transportation, Z10-11Transportation.
How can I exclude database for reindex? I want o exclude anydatabses with a first letter start capital Z.
I tried this, but it seems not working. It is quickly done without doing anything.
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBA -Q "EXECUTE [dbo].[IndexOptimize] @databases = 'USER_DATABASES -Z%', @LogToTable = 'Y'" -b
August 13, 2012 at 3:39 pm
I believe you change this
@Databases = 'USER_DATABASES -Z%', @LogToTable = 'Y'" -b
To this
@Databases = 'USER_DATABASES, -Z%', @LogToTable = 'Y'" -b
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 13, 2012 at 5:23 pm
Thanks, I think you are right.
I changed it, and now I would like to test ola.hallengren.com script for just one database.
Now when I run the index optimizing job, it runs so quick.
So I wonder if it does something, I do a query like below to see if fragmenation changed or not,
SELECT DB_NAME(PS.database_id) AS dbName,
S.name AS SchemaName,
O.name AS TableName,
b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
INNER JOIN sys.objects O ON PS.object_id = O.object_id
INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
WHERE ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20
AND PS.index_type_desc IN ('CLUSTERED INDEX','NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes
AND b.is_hypothetical = 0 -- Only real indexes
AND O.type_desc = 'USER_TABLE' -- Restrict to user tables
AND PS.page_count > 8 --- ignore tables less tha 64K
ORDER BY ps.avg_fragmentation_in_percent DESC
And find out there are still a lot of avg_fragmenation_in_percent is bigger than 30 percent.
So I guess ola.hallengren.com script didn't do anything for me.
What I am missing here?
Thanks
August 14, 2012 at 12:21 am
The index procedure in Olas script outputs to a table have checked this table for any results?
It may be possible that no indexes are found for defragging in this particular database based on the thresholds you are supplying.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 14, 2012 at 3:53 am
I think that Ola's index maintenance have a default of PageCountLevel=1000.
In your scripts it's = 8 and that 's why you get so many records.
Franco
August 14, 2012 at 9:21 am
franco (8/14/2012)
I think that Ola's index maintenance have a default of PageCountLevel=1000.In your scripts it's = 8 and that 's why you get so many records.
yes, I figured out that too. Thanks.
So do you think pageCoutLevel default should set up to 1000 or 8, they seem big difference.
Thanks
August 14, 2012 at 9:27 am
It's always depends:
PageCountLevel:Set a size, in pages.
Indexes with fewer pages are skipped for index maintenance. The default is 1000 pages. This is based on Microsoft’s recommendation...
You are free to change it.
Franco
August 14, 2012 at 9:28 am
Your main issue was the syntax error in the calling script.
It won't hurt too much to use page count 8 in your script as your using the results to view frag levels. Just be aware you'll see more results than Ola's scripts will
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply