May 24, 2012 at 10:31 pm
Comments posted to this topic are about the item Defrag All Indexes On a Database
Hakim Ali
www.sqlzen.com
May 25, 2012 at 7:19 am
When I try to run this script, I get the errors below. Note! These same 'Incorrect syntax near '' ' happen at times on other scripts I get from SSC and try to run.
It may be an issue with my SSMS setup. Any one have some ideas on this? Thanks in advance!
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '?'.
May 25, 2012 at 7:42 am
dbajunior (5/25/2012)
When I try to run this script, I get the errors below. Note! These same 'Incorrect syntax near '' ' happen at times on other scripts I get from SSC and try to run.It may be an issue with my SSMS setup. Any one have some ideas on this? Thanks in advance!
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '?'.
May be copy and paste issues. I've had this problem and have seen others post the same issue and solution.
The problem seems to be that special/unwanted characters get copied from the web page and cause errors if pasted directly into SSMS.
The solution that has worked for me and others is to paste the code into Notepad first then copy from Notepad to SSMS. The process will remove the special/unwanted characters and hopefully the errors in SSMS.
Good luck!
May 25, 2012 at 7:50 am
Thanks for the tip! In Notepad++, the code (a sample of it) displayed like this:
declare @Database_Name nvarchar(100);????????????????????set @DATABASE_NAME = 'AdventureWorks2012'
declare @Rebuild_Threshold_Percent nvarchar(10);????????set @REBUILD_THRESHOLD_PERCENT = 30
declare @Reorganize_Threshold_Percent nvarchar(10);????????set @REORGANIZE_THRESHOLD_PERCENT = 30
declare @Execute_Rebuild bit;????????????????????????????set @EXECUTE_REBUILD = 0????????-- set to 1 to rebuild
declare @Execute_Reorganize bit;????????????????????????set @EXECUTE_REORGANIZE = 0????????-- set to 1 to reorganize. If rebuilding, no need to do this.
After replacing '?' with a space, and pasting the code back to SSMS, it worked!
May 25, 2012 at 7:54 am
Yes, it looks like the website replaces spaces with some other character that is not ASCII 32. You don't even have to go via Notepad, just in SSMS highlight one of these non-spaces, CTRL+H to replace all instances of these with a space, and it works.
Anybody have a better workaround and explanation?
Hakim Ali
www.sqlzen.com
May 25, 2012 at 10:15 am
hakim.ali (5/25/2012)
Yes, it looks like the website replaces spaces with some other character that is not ASCII 32. You don't even have to go via Notepad, just in SSMS highlight one of these non-spaces, CTRL+H to replace all instances of these with a space, and it works.Anybody have a better workaround and explanation?
Unfortunately not a better workaround but what has already been stated. The trouble I think stems from Unicode encoding used in web pages that is invalid in SSMS and the difference between American US English expectations and the world being what it is (as in not really all American US English).
Congratulations to a very nice script - I have seen bits across the web and was working on a similiar version for our databases - your script just saved me about 12 hours work, Hakim. Thanks a lot! :w00t:
May 25, 2012 at 12:34 pm
Knut Boehnert (5/25/2012)
Congratulations to a very nice script - I have seen bits across the web and was working on a similiar version for our databases - your script just saved me about 12 hours work, Hakim. Thanks a lot! :w00t:
Thank you, very kind.
Hakim Ali
www.sqlzen.com
July 10, 2012 at 4:39 am
Thanks for the lovely script.
I customised it with rebuild for more than 30% and reorg for more than 50% and only for those where page count is > 50. All these changes are marked as bold in original script.
/****************************************************************************************************
Hakim Ali (hakim.ali@sqlzen.com) 2012/05/04
****************************************************************************************************/
-----------------------------------------------------------------------------------------------------
------------------------------------------------------- SET DESIRED VALUES HERE ---------------------
-----------------------------------------------------------------------------------------------------
declare @Database_Name nvarchar(100); set @DATABASE_NAME = 'AdventureWorks2012'
declare @Rebuild_Threshold_Percent nvarchar(10); set @REBUILD_THRESHOLD_PERCENT = 30
declare @Reorganize_Threshold_Percent nvarchar(10); set @REORGANIZE_THRESHOLD_PERCENT = 50
declare @Execute_Rebuild bit; set @EXECUTE_REBUILD = 0 -- set to 1 to rebuild
declare @Execute_Reorganize bit; set @EXECUTE_REORGANIZE = 0 -- set to 1 to reorganize. If rebuilding, no need to do this.
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
----------------------------------
-- Initial checking
----------------------------------
declare @Error nvarchar(500)
declare @SQL nvarchar(max)
declare @Online nvarchar(50)
set @Database_Name = ltrim(rtrim(isnull(@Database_Name,'')))
set @Error = 'Database name required.'
if (@Database_Name = '')
begin
raiserror(@Error,16,1)
goto the_end
end
set @Error = 'Database "' + @Database_Name + '" does not exist.'
if not exists (select name from sys.databases where name = @Database_Name)
begin
raiserror(@Error,16,1)
goto the_end
end
set @Online = ''
if (@@version like '%enterprise edition%')
begin
set @Online = ' with (online = on)'
end
set @SQL = '
----------------------------------
-- Create table to hold results
----------------------------------
declare @fragmented_indexes table
( ID int identity(1,1)
,Database_Name nvarchar(1000)
,[Schema_Name] nvarchar(1000)
,Table_Name nvarchar(1000)
,Index_Name nvarchar(1000)
,Fragmentation_Percent money
,Num_Rows int
,Page_Count int
,Index_Type nvarchar(1000)
,Reorganize_SQL nvarchar(4000)
,Rebuild_SQL nvarchar(4000)
)
----------------------------------
-- Populate table
----------------------------------
insert into @fragmented_indexes
( Database_Name
,[Schema_Name]
,Table_Name
,Index_Name
,Fragmentation_Percent
,Num_Rows
,Page_Count
,Index_Type
)
select distinct
Database_Name
= db_name(database_id)
,[Schema_Name]
= sch.name
,Table_Name
= parent.Name
,Index_Name
= indx.name
,Fragmentation_Percent
= left(isnull(phys_stats.avg_fragmentation_in_percent,0),5)
,Num_Rows
= x.rowcnt
,Page_Count
= phys_stats.page_count
,Index_Type
= phys_stats.index_type_desc
from sys.dm_db_index_physical_stats(
db_id('''+@Database_Name+'''),
default,
default,
default,
''detailed''
) phys_stats
inner join ['+@Database_Name+'].sys.indexes indx
on indx.object_id = phys_stats.object_id
and indx.index_id = phys_stats.index_id
inner join ['+@Database_Name+'].sys.objects parent
on parent.object_id = phys_stats.object_id
inner join ['+@Database_Name+'].dbo.sysindexes x
on x.id = indx.object_id
inner join ['+@Database_Name+'].sys.schemas sch
on sch.schema_id = parent.schema_id
where 1 = 1
and isnull(indx.name,'''') <> ''''
and x.rowcnt > 0
andphys_stats.page_count > 50
and (phys_stats.avg_fragmentation_in_percent >= '+@Rebuild_Threshold_Percent+'
or
phys_stats.avg_fragmentation_in_percent >= '+@Reorganize_Threshold_Percent+')
update @fragmented_indexes
set Reorganize_SQL =
''alter index [''+Index_Name+''] on [''+Database_Name+''].[''+[Schema_Name]+''].[''+Table_Name+''] reorganize''
,Rebuild_SQL =
''alter index [''+Index_Name+''] on [''+Database_Name+''].[''+[Schema_Name]+''].[''+Table_Name+''] rebuild'+@Online+'''
----------------------------------
-- View results
----------------------------------
select ID
,Database_Name
,[Schema_Name]
,Table_Name
,Index_Name
,Fragmentation_Percent
,Num_Rows
,Page_Count
,Index_Type
from @fragmented_indexes
order by Database_Name
,convert(money,Fragmentation_Percent) desc
,[Schema_Name]
,Table_Name
'
----------------------------------
-- If rebuild/reorganize option set...
----------------------------------
if (@Execute_Rebuild = 1)
begin
set @SQL = @SQL + '
declare @current_sql nvarchar(max)
while exists (select top 1 Rebuild_SQL from @fragmented_indexes)
begin
set @current_sql = (select top 1 Rebuild_SQL from @fragmented_indexes)
execute sp_executesql @current_sql
--select @current_sql
delete @fragmented_indexes where Rebuild_SQL = @current_sql
end
'
end
else if (@Execute_Reorganize = 1)
begin
set @SQL = @SQL + '
declare @current_sql nvarchar(max)
while exists (select top 1 Reorganize_SQL from @fragmented_indexes)
begin
set @current_sql = (select top 1 Reorganize_SQL from @fragmented_indexes)
execute sp_executesql @current_sql
--select @current_sql
delete @fragmented_indexes where Reorganize_SQL = @current_sql
end
'
end
----------------------------------
-- Go!
----------------------------------
execute sp_executesql @SQL
the_end:
----------
Ashish
May 10, 2016 at 1:47 pm
Thanks for the script.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply