InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 22:41:52 GMT20http://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspx[quote][b]Brian Seib (6/10/2013)[/b][hr]Very strange - so I am was looking at one of the servers that this is running on and failing - and after looking through the history is turns out that somedays it works and other days it does not. So that pretty much takes any syntax issues out of the running. So this apparently is requesting information that is hanging some days?. I do not fully understand what the query is doing step by step, but it apparenly relies on the information on the database as it i works one hay and not the other, without my naking any changes to the database. Any ideas?[/quote]I suggest you to do an error handling (Just insert an error into another table) and find out what it's.Tue, 18 Jun 2013 06:55:27 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxVery strange - so I am was looking at one of the servers that this is running on and failing - and after looking through the history is turns out that somedays it works and other days it does not. So that pretty much takes any syntax issues out of the running. So this apparently is requesting information that is hanging some days?. I do not fully understand what the query is doing step by step, but it apparenly relies on the information on the database as it i works one hay and not the other, without my naking any changes to the database. Any ideas?Mon, 10 Jun 2013 14:36:12 GMTBrian Seibhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspx[quote][b]Brian Seib (5/30/2013)[/b][hr]Looking at another server that is runnig the same script and the error it is getting is "COUNT field incorrect or syntax error [SQLSTATE 07002] (Error 0). The step failed". Keep in mind these were working a few months ago, just started getting errors. Very strange.[/quote]Error: 07002 Number of columns does not match number of host variables.http://msdn.microsoft.com/en-us/library/aa176407%28v=sql.80%29.aspxHave a look how many columns you selected for an insert.Fri, 31 May 2013 03:43:42 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxLooking at another server that is runnig the same script and the error it is getting is "COUNT field incorrect or syntax error [SQLSTATE 07002] (Error 0). The step failed". Keep in mind these were working a few months ago, just started getting errors. Very strange.Thu, 30 May 2013 10:54:06 GMTBrian Seibhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxSo here is an odd update. It fails when it is running at night (normal time), but just for kicks I ran it this morning and it acually ran fine..? Very strange behavior - could it be that it is butting heads with a nightly process? I do not know this script that well, so I am not too sure.Thanks,BrianThu, 30 May 2013 10:47:24 GMTBrian Seibhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxIf that's case you can add additional condition within the script. Run a select statement one by one and check which is causing an issue.Wed, 29 May 2013 07:19:08 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxI've had errors like this and have always found that the original code I've worked from overlooked something causing the error. I would check the database is online, you are correctly handling schemas, names that need enclosing in [ ] etc.Fri, 24 May 2013 16:18:19 GMTBalancehttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxIs someone changing the compatibility level/mode of the database?Fri, 24 May 2013 16:01:20 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxGood afternoon -- I have an odd question - I have been using this scipt on a few of my servers for a while now, and just recentyl I have been getting an error when the job runs some days and not other days. The error is as follows: Incorrect syntax near '('. [SQLSTATE 42000] (Error 102). The step failed.I would say it is just a syntax error, but it runs one day and not another - so I do not really understand what parameter is changing on my database some days to make this error out.. Any ideas??Thanks,BrianFri, 24 May 2013 15:42:53 GMTBrian Seibhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspx[quote][b]Paul Randal (9/26/2012)[/b][hr]Your script as such will suffer false positives from fragmented LOB_DATA and ROW_OVERFLOW_DATA allocation units - a common oversight. You need to filter on alloc_unit_type_desc = IN_ROW_DATA.[/quote]I have no idea on that.Thanks Paul.I will update it asap alloc_unit_type_desc = IN_ROW_DATA.Wed, 26 Sep 2012 10:50:20 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxYour script as such will suffer false positives from fragmented LOB_DATA and ROW_OVERFLOW_DATA allocation units - a common oversight. You need to filter on alloc_unit_type_desc = IN_ROW_DATA.Wed, 26 Sep 2012 10:36:15 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspx[quote][b]henrik staun poulsen (9/26/2012)[/b][hr]Hi Muthukkumaran, Did you look at http://ola.hallengren.com/ IMHO it works better on larger databases.Best regards,Henrik Staun PoulsenStovi Software[/quote]Henrik,Indeed! That's good for larger databases.Wed, 26 Sep 2012 01:50:18 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxHi Muthukkumaran, Did you look at http://ola.hallengren.com/ IMHO it works better on larger databases.Best regards,Henrik Staun PoulsenStovi SoftwareWed, 26 Sep 2012 00:44:47 GMThenrik staun poulsenhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxThanks Siebe. Will change it.Wed, 20 Jun 2012 03:16:22 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxHi Muthukkumaran, The phrases '@frag BETWEEN 15 AND 29' and '@frag>=30' should be replaced by '@frag BETWEEN 15 AND 30' and '@frag>30'.With the current script you are missing the values of @frag greater than 29 and smaller than 30.(also the comments should be adjusted likewise)Regards,SiebeTue, 19 Jun 2012 08:35:33 GMTsboerdehttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspx[quote][b]pavankvnv (4/17/2012)[/b][hr]HI Muthu,I ran the script and it created those two tables in master and msdb and executed successfully.But fragmentation is not reduced. Please let me know how to put this script in a job that should execute weekly and should run for all databases.Thanks,Pavan KVN[/quote]It may be a small table.You can do that using script. Write a SP loop the sys.databases.Wed, 18 Apr 2012 06:02:13 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxHI Muthu,I ran the script and it created those two tables in master and msdb and executed successfully.But fragmentation is not reduced. Please let me know how to put this script in a job that should execute weekly and should run for all databases.Thanks,Pavan KVNTue, 17 Apr 2012 01:46:03 GMTpavankvnvhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspx[quote][b]vanceprice (3/26/2012)[/b][hr]If we are running SQL 2008 and have "Auto Update Statistics" enabled, Do we need to update statistics in condition 2 or 3 of this script? Are there any down sides to doing both?[/quote]It will not create and problem. if you don't want then you may remove it.Tue, 27 Mar 2012 06:15:57 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxIf we are running SQL 2008 and have "Auto Update Statistics" enabled, Do we need to update statistics in condition 2 or 3 of this script? Are there any down sides to doing both?Mon, 26 Mar 2012 15:16:57 GMTvancepricehttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxWhat error are you getting?Fri, 09 Mar 2012 22:18:21 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxThanks , however this script from your link for sql 2000 is exactly what I used... and it does not run successfully.Fri, 09 Mar 2012 12:12:13 GMTnatasha-501269http://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspx[quote][b]natasha-501269 (3/8/2012)[/b][hr]I've got the following error when used your script in sql 2000. Any idea?ThanksServer: Msg 1038, Level 15, State 3, Line 1Cannot use empty object or column names. Use a single space if necessary.Server: Msg 1038, Level 15, State 1, Line 1Cannot use empty object or column names. Use a single space if necessary.Server: Msg 1038, Level 15, State 1, Line 1Cannot use empty object or column names. Use a single space if necessary.(1 row(s) affected)[/quote]It will not work for SQL 2000.Use this for SQL 2000[url=http://www.sqlserverblogforum.com/2011/03/index-defragmentation-script-for-sql-server-2000][b][u]Rebuild Index script for SQL 2000[/u] [/b][/url]Fri, 09 Mar 2012 09:17:20 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxI've got the following error when used your script in sql 2000. Any idea?ThanksServer: Msg 1038, Level 15, State 3, Line 1Cannot use empty object or column names. Use a single space if necessary.Server: Msg 1038, Level 15, State 1, Line 1Cannot use empty object or column names. Use a single space if necessary.Server: Msg 1038, Level 15, State 1, Line 1Cannot use empty object or column names. Use a single space if necessary.(1 row(s) affected)Thu, 08 Mar 2012 12:02:46 GMTnatasha-501269http://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspx[quote][b]vanceprice (2/23/2012)[/b][hr]I am new to TSQL.What would be the best way to run this sp for all user databases on a SQL 2008 server?[/quote]Welcome to T-SQL language.Loop the sys.databases and use this SProc inside the loop.Take an example of my Index defragmentation script and Try yourself using sys.databases. If you face any problem open a new thread and ask your quires.We will help you.Thu, 23 Feb 2012 21:36:04 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxI am new to TSQL.What would be the best way to run this sp for all user databases on a SQL 2008 server?Thu, 23 Feb 2012 14:58:00 GMTvancepricehttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxThanks Greg Shinder.I'll look into this.I'm glad you liked it.Thu, 02 Feb 2012 22:29:02 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxVery nice and useful script. Thank you.Had problems with similar table names under different schemes as well.However, there is no need for joins to sys.tables and sys.schemas. [code="sql"]INSERT INTO #TEMPFRAGSELECT OBJECT_NAME(F.OBJECT_ID), I.NAME IND, F.AVG_FRAGMENTATION_IN_PERCENT, F.PAGE_COUNT, s.name [b]-- NOTE CHANGED FROM TABLE_SCHEMA[/b]--, i.allow_page_locks -- I use this to better handle allow_page_locks true / falseFROM SYS.DM_DB_INDEX_PHYSICAL_STATS ('+cast(@DB_ID as varchar)+',NULL,NULL,NULL,NULL) FJOIN SYS.INDEXES I ON(F.OBJECT_ID=I.OBJECT_ID) AND I.INDEX_ID=F.INDEX_ID and i.is_disabled = 0 -- Note I'm skipping disabled indexesjoin sys.tables t on f.object_id = t.object_id -- [b]NOTE NEW LINE TO REPLACE USE OF INFORMATION_SCHEMA[/b]join sys.schemas s on s.schema_id = t.schema_id -- [b]NOTE NEW LINE TO COMPLETE JOIN[/b]--WHERE INDEX_ID<> 0 AND F.DATABASE_ID=DB_ID() AND OBJECTPROPERTY(I.OBJECT_ID,''ISSYSTEMTABLE'')=0[/code]You can use OBJECT_SCHEMA_NAME function instead:[code]INSERT INTO #tempfrag (schema_name,table_name,index_name,frag,pages)SELECT OBJECT_SCHEMA_NAME(v.object_id) AS SchemaName, OBJECT_NAME(v.object_id) AS TableName, i.name AS IndexName, v.avg_fragmentation_in_percent AS Fragmentation, v.page_count AS PageCountFROM SYS.DM_DB_INDEX_PHYSICAL_STATS (11,NULL,NULL,NULL,NULL) v INNER JOIN SYS.INDEXES i ON v.object_id = i.object_id AND v.index_id = i.index_id AND i.is_disabled = 0[/code]Thu, 02 Feb 2012 10:52:50 GMTGreg Shinderhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxif your machine is case sensitive I would check the code carefully.We also used some of this code as the basis for our current routine - for a production install you might want to consider your local preferences on minimum pages, fragmentation levels (esp whether you ever want to reorganize) and also the other index options that rebuild should consider.Thu, 01 Dec 2011 02:22:49 GMTBalancehttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxNice script.For performance purposes, have you considered dynamically specifying the FILLFACTOR on your index rebuilds to help with page splits and IO performance?Not sure what your database default is for FILLFACTOR, but your script is going to apply the default setting to all your index rebuilds when this is not specified. Since you are already checking for the percent fragmentation in your script, you could specify a lower FILLFACTOR for more fragmented non-clustered indexes and possibly even a zero FILLFACTOR on clustered indexes that currently have a near zero percent fragmentation since they are obviously designed to add all new rows to the end of the table.This approach allows your clustered indexes to have a higher page density (improving overall IO performance) and allows your non-clustered indexes to handle some number of new records before a page split is needed, but not too much to waste a bunch of space on the drives and in memory. Having too low of a page density can also adversely affect performance by requiring the buffer pool to perform more IOs to get the same amount of data off the disk or out of memory.The FILLFACTOR should be set dynamically as the insert and update patterns on your tables will most likely vary from table to table - figuring out that algorithm is the tricky part. I guess you could even create a table of all your indexes where you specify your best FILLFACTOR based on your trials and refer to that table during your index rebuilds and only use the default on all new indexes that have not been added to your table yet.-EricWed, 30 Nov 2011 19:44:51 GMTEric Niemiechttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspx[quote][b]bobw1776 (11/30/2011)[/b][hr]I haven't read through the above replies to check, but has anyone received the below error ONLY when the SP is executed against a CS collated DB? e.g. "SQL_Latin1_General_CP1_CS_AS" "Msg 208, Level 16, State 1, Line 2 Invalid object name 'SYS.DM_DB_INDEX_PHYSICAL_STATS'. " It runs fine on any other DB. It might be the solution to my failed indexing scripts, won't know until I get it running for a little bit. Thanks, Bobw[/quote]Bobw,Hi,Ask a question in general forum ---> http://www.sqlservercentral.com/Forums/you got 'Invalid object name' error the DMV available on 2005 onwards.Wed, 30 Nov 2011 11:38:59 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxI haven't read through the above replies to check, but has anyone received the below error ONLY when the SP is executed against a CS collated DB? e.g. "SQL_Latin1_General_CP1_CS_AS" "Msg 208, Level 16, State 1, Line 2 Invalid object name 'SYS.DM_DB_INDEX_PHYSICAL_STATS'. " It runs fine on any other DB. It might be the solution to my failed indexing scripts, won't know until I get it running for a little bit. Thanks, BobwWed, 30 Nov 2011 09:28:19 GMTbobw1776http://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspx[url=http://www.sqlserverblogforum.com/2011/03/index-defragmentation-script-for-sql-server-2000/]Click here:INDEX DE-FRAGMENTATION SCRIPT for SQL server 2000 [/url]Edit: to Fix the linkSun, 13 Mar 2011 00:36:12 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspx[quote][b]Balance (1/22/2010)[/b][hr]Can I ask how you came to the decision to only do if @Page>1000?Thanks[/quote]Its MS rule of thumb.Mon, 03 May 2010 01:26:57 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspx[quote][b]macherla.harish (2/12/2010)[/b][hr]HI Muthu ,Good script given.HI muthu, i have a Dought in Sqlserver2000 , IN sqlserver 2005 Copy_only option is there But In sql server2000 not there if hava log shiping or Replication iam using iam taken backup lsn chain will breakup.In my production sqlserver2000 is there how to take a backup without lSN BreakupThank YouHarishkumar.M[/quote]Please start a new thread :-)Sat, 13 Feb 2010 03:38:20 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxHI Muthu ,Good script given.HI muthu, i have a Dought in Sqlserver2000 , IN sqlserver 2005 Copy_only option is there But In sql server2000 not there if hava log shiping or Replication iam using iam taken backup lsn chain will breakup.In my production sqlserver2000 is there how to take a backup without lSN BreakupThank YouHarishkumar.MFri, 12 Feb 2010 03:21:41 GMTmacherla.harishhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxHI Muthu ,Good script given.HI muthu, i have a Dought in Sqlserver2000 , IN sqlserver 2005 Copy_only option is there But In sql server2000 not there if hava log shiping or Replication iam using iam taken backup lsn chain will breakup.In my production sqlserver2000 is there how to take a backup without lSN BreakupThank YouHarishkumar.MFri, 12 Feb 2010 03:19:31 GMTmacherla.harishhttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxThanks Mutthu...this goes to my briefcase.:satisfied:Thu, 11 Feb 2010 05:38:39 GMTjshailendrahttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxHaving used this as the basis for my own version though I best update again - there is an error in the code when you have multiple schemas in a database where tablenames are repeated (so schemaA.Table1 and schemaB.Table1).Solution is to make the code schema aware, so changing this code section :[code]INSERT INTO #TEMPFRAGSELECT OBJECT_NAME(F.OBJECT_ID) OBJ,I.NAME IND,F.AVG_FRAGMENTATION_IN_PERCENT,F.PAGE_COUNT,TABLE_SCHEMAFROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL) FJOIN SYS.INDEXES ION(F.OBJECT_ID=I.OBJECT_ID)AND I.INDEX_ID=F.INDEX_IDJOIN INFORMATION_SCHEMA.TABLES SON (S.TABLE_NAME=OBJECT_NAME(F.OBJECT_ID))--WHERE INDEX_ID<> 0AND F.DATABASE_ID=DB_ID()AND OBJECTPROPERTY(I.OBJECT_ID,''ISSYSTEMTABLE'')=0'[/code]with this[code]INSERT INTO #TEMPFRAGSELECT OBJECT_NAME(F.OBJECT_ID), I.NAME IND, F.AVG_FRAGMENTATION_IN_PERCENT, F.PAGE_COUNT, s.name [b]-- NOTE CHANGED FROM TABLE_SCHEMA[/b]--, i.allow_page_locks -- I use this to better handle allow_page_locks true / falseFROM SYS.DM_DB_INDEX_PHYSICAL_STATS ('+cast(@DB_ID as varchar)+',NULL,NULL,NULL,NULL) FJOIN SYS.INDEXES I ON(F.OBJECT_ID=I.OBJECT_ID) AND I.INDEX_ID=F.INDEX_ID and i.is_disabled = 0 -- Note I'm skipping disabled indexesjoin sys.tables t on f.object_id = t.object_id -- [b]NOTE NEW LINE TO REPLACE USE OF INFORMATION_SCHEMA[/b]join sys.schemas s on s.schema_id = t.schema_id -- [b]NOTE NEW LINE TO COMPLETE JOIN[/b]--WHERE INDEX_ID<> 0 AND F.DATABASE_ID=DB_ID() AND OBJECTPROPERTY(I.OBJECT_ID,''ISSYSTEMTABLE'')=0[/code]The only other change you want to make for the above to work is to add at the start of your code :[code]declare @DB_ID intset @DB_ID= DB_ID([i]'Your DB Name Here'[/i])[/code]Because that gets around a failure in the original source if you want to run this for any database from any database.Thu, 11 Feb 2010 05:23:10 GMTBalancehttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspxI know this comment is rather late in the day but I'm just revisiting your script - I've used it as the basis of something that suits what I want so thought I'd mention a simple performance improvement & that is to make the cursor a fast_forward cursor. Makes access to a "step through 1 row after another" type of cursor significantly faster.Thu, 04 Feb 2010 02:03:28 GMTBalancehttp://www.sqlservercentral.com/Forums/Topic821729-1692-1.aspx[quote][b]D Rose (1/22/2010)[/b][hr]I see in the SQL that this is for SQL 2005 +.Is this possible on SQL 2000?Thanks![/quote]Defragmentation script only works for sql server 2005/2008.If u want u can search here for 2000.Sun, 24 Jan 2010 04:19:14 GMTmuthukkumaran Kaliyamoorthy