July 4, 2011 at 5:30 am
Rhox (7/4/2011)
I was searching for a good index optimalisation script.That reorganised or rebuild the indexes (online) for the database.
I'm trying following script: Example Script to Rebuild / Reorganize Fragmented Indexes
I've modified only the rebuild command line with the extra option 'ONLINE = ON'.
But I'm always getting an error: Incorrect syntax near '('. on Line 38
LINE38 > FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS
I noticed the compatibility mode of our db = 80 (SQL 2000)
Already changed DB_ID() into 'A105_P' (the db name) but then I get this output (scripted error):
DATE : Jul 4 2011 1:01PM - Retrieving indexes with high fragmentation from A105_P database.
DATE : Jul 4 2011 1:01PM - There is some run time exception.
ERROR CODE : 245
ERROR MESSAGE : Conversion failed when converting the varchar value 'A105_P' to data type smallint.
Ok gonna try that script, thanks!
July 4, 2011 at 5:34 am
muthukkumaran Kaliyamoorthy (7/4/2011)
Incorrect syntax near '('. on Line 38
LINE38 > FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS
I noticed the compatibility mode of our db = 80 (SQL 2000)
Already changed DB_ID() into 'A105_P' (the db name) but then I get this output (scripted error):
DATE : Jul 4 2011 1:01PM - Retrieving indexes with high fragmentation from A105_P database.
DATE : Jul 4 2011 1:01PM - There is some run time exception.
ERROR CODE : 245
ERROR MESSAGE : Conversion failed when converting the varchar value 'A105_P' to data type smallint.
You can't pass the db name directly 'A105_P' to the db_id . use like db_id('A105_P')
DMV will not work against the compatibility 80.
Yes they will. They just need some help getting there...
use master
go
select dbo.compatview (@Dbid_level80...)
Maybe it works with db_id('level 80 db name') but I'm not 100% sure on that one.
July 4, 2011 at 5:37 am
muthukkumaran Kaliyamoorthy (7/4/2011)
DMV will not work against the compatibility 80.
It can and will, see Paul Randall's article at the following link
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 4, 2011 at 5:40 am
Perry Whittle (7/4/2011)
muthukkumaran Kaliyamoorthy (7/4/2011)
DMV will not work against the compatibility 80.It can and will, see Paul Randall's article at the following link
Thanks perry. Thats great info from paul.
Thanks to you.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
July 4, 2011 at 6:32 am
You're welcome
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 1, 2013 at 3:46 am
Hi,
I am going to deploly at production database for Mr. Michal rebuild index script,
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
I have doubt as below parameter
@forceRescan 1 - forace scan and 0 = use existing scan,
should I kept first time value 1 and then next time will kept it 0.?
Standard Edition will not be support as below parameter, pl. confirm
@onlineRebuild =0
@sortInTempDB =0
In standard edition let it be value 0 these two parametes.
thanks'
ananda
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply