InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 03:34:19 GMT20http://www.sqlservercentral.com/Forums/Topic504420-146-1.aspxWe raised the 'CA' error with MS and they seem to have acknowledged that this is a bug on SQL Server. Very strange that no one has raised it before.Mon, 02 Nov 2009 16:26:13 GMTmaribalahttp://www.sqlservercentral.com/Forums/Topic504420-146-1.aspxMy memory is a little foggy and we have upgraded to Sql 2008. But from what I remember I had to break the MP up into individual Databases and that solved our issue. Originally I had set the MP to do all our User databases. Everything worked OK and then I had a database that was removed from production so I went back into the MP and removed the database from there. But every time the MP ran it continued to look for the deleted DB. I decided it was easier to do a MP for each individual DB and from that point on I no longer saw the errors I had encountered before. Sorry I can't be of more help but as I said we upgraded our servers and I have not had any problems since I changed the Maintenance Plan...Fri, 23 Oct 2009 14:37:28 GMTReg_Mayfieldhttp://www.sqlservercentral.com/Forums/Topic504420-146-1.aspxDid you figure what caused the 'CA' error? I see that this thread changes track a bit and the original 'CA' error issue is not discussed further. I have the same error reported for our product. When my process is run in parallel with index rebuild online, then I get the 'CA' error. I am eager to know if you had overcome the problem.If so, please let us know how.It will be of immense help. Thanks againFri, 23 Oct 2009 13:50:28 GMTmaribalahttp://www.sqlservercentral.com/Forums/Topic504420-146-1.aspx[quote]1) Are you sure that sp_updatestats only updates stats that need it? a) if autoupdate is on this would be pointless since they would already be done presumably and b) I looked at the code for sp_updatestats and it seems to do everything. There was use of stats_ver_current(@table_id, @ind_id), but I can't find code for that anywhere. Perhaps that is what you were referring to?NOTE: per BOL here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b86a88ba-4f7c-4e19-9fbd-2f8bcd3be14a.htm, "If you prefer not to update all statistics by running sp_updatestats". That implies that sp_updatestats does all stats.and yet here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/919158f2-38d0-4f68-82ab-e1633bd0d308.htm, " This updates statistics only when they are required. " Not very clear!! :)2) I am sure there is an algorythm somewhere that details out the logic on what percentage of rows/data is sampled for update stats call, but I can't find it at the moment.Oh, while checking around for some clarification for this topic I stumbled across this: http://blogs.msdn.com/queryoptteam/archive/2006/07/21/674350.aspx. Quite amazing that you can 'falsify' the stats stuff and thus force the optimizer into a particular type of plan!! Gosh can I think of some uses for that. :w00t:[/quote]Yep, I am with you - it is a bit confusing. When I reviewed 'sp_updatestats' I found that it checks whether or not there have been rows modified (rowmodctr). If so, it updates the statistics for that table, if not - it gets skipped. I tested this and it does indeed skip tables that have not had any activity.Now - from everything I have been able to find, the only way to insure that statistics are rebuilt using a full scan is to issue an UPDATE STATISTICS WITH FULLSCAN (or rebuild the index) and turn off auto update stats. If auto update stats kicks in - it rebuilds the statistics using the default sampling rate (which I still don't know what that value is).But, if you have not hit an auto update stats situation for a table - and you run sp_updatestats with the RESAMPLE option or UPDATE STATISTICS WITH RESAMPLE, then those statistics will be rebuilt with the previous sampling rate. That means that statistics built when an index was created/rebuilt will be updated with a full scan - and any statistics that were created manually with a full sampling rate would also be built with a full scan.Basically - it does not look like you can insure that stats are updated with a full scan all the time.JeffFri, 23 May 2008 18:14:29 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic504420-146-1.aspx[quote][b]Jeffrey Williams (5/23/2008)[/b][hr]Unless you are using 'sp_updatestats' or something similar that only updates statistics on those objects that need to be updated. Running that right after rebuilding indexes will skip your just rebuilt indexes because they don't need to be updated.BTW - I am still trying to find out where the default sampling value is kept. When you run UPDATE STATISTICS with no qualifier (i.e. FULLSCAN, SAMPLE, etc...) - the statistics are supposed to be updated using the default sampling. I was under the impression that this would be what the statistics were built with the last time - but may not be?From BOL - statistics on indexes are created using the FULLSCAN option. But if we update the statistics later it does not unless we specifically include the FULLSCAN option? That is really the question...Jeff[/quote][quote][b]Jeffrey Williams (5/23/2008)[/b][hr]Unless you are using 'sp_updatestats' or something similar that only updates statistics on those objects that need to be updated. Running that right after rebuilding indexes will skip your just rebuilt indexes because they don't need to be updated.BTW - I am still trying to find out where the default sampling value is kept. When you run UPDATE STATISTICS with no qualifier (i.e. FULLSCAN, SAMPLE, etc...) - the statistics are supposed to be updated using the default sampling. I was under the impression that this would be what the statistics were built with the last time - but may not be?From BOL - statistics on indexes are created using the FULLSCAN option. But if we update the statistics later it does not unless we specifically include the FULLSCAN option? That is really the question...Jeff[/quote]1) Are you sure that sp_updatestats only updates stats that need it? a) if autoupdate is on this would be pointless since they would already be done presumably and b) I looked at the code for sp_updatestats and it seems to do everything. There was use of stats_ver_current(@table_id, @ind_id), but I can't find code for that anywhere. Perhaps that is what you were referring to?NOTE: per BOL here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b86a88ba-4f7c-4e19-9fbd-2f8bcd3be14a.htm, "If you prefer not to update all statistics by running sp_updatestats". That implies that sp_updatestats does all stats.and yet here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/919158f2-38d0-4f68-82ab-e1633bd0d308.htm, " This updates statistics only when they are required. " Not very clear!! :)2) I am sure there is an algorythm somewhere that details out the logic on what percentage of rows/data is sampled for update stats call, but I can't find it at the moment.Oh, while checking around for some clarification for this topic I stumbled across this: http://blogs.msdn.com/queryoptteam/archive/2006/07/21/674350.aspx. Quite amazing that you can 'falsify' the stats stuff and thus force the optimizer into a particular type of plan!! Gosh can I think of some uses for that. :w00t:Fri, 23 May 2008 16:03:23 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic504420-146-1.aspxUnless you are using 'sp_updatestats' or something similar that only updates statistics on those objects that need to be updated. Running that right after rebuilding indexes will skip your just rebuilt indexes because they don't need to be updated.BTW - I am still trying to find out where the default sampling value is kept. When you run UPDATE STATISTICS with no qualifier (i.e. FULLSCAN, SAMPLE, etc...) - the statistics are supposed to be updated using the default sampling. I was under the impression that this would be what the statistics were built with the last time - but may not be?From BOL - statistics on indexes are created using the FULLSCAN option. But if we update the statistics later it does not unless we specifically include the FULLSCAN option? That is really the question...JeffFri, 23 May 2008 12:42:54 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic504420-146-1.aspx[quote][b]Reg_Mayfield (5/23/2008)[/b][hr]I have created a MP that runs on the weekend, I have two procedures that run, one to Rebuild Indexes and the second is Update Statistics...[/quote]This is a VERY common Worst Practice I see at the majority of my new clients. A REBUILD of the index is forced to read 100% of the pages, obviously, in order to completely REBUILD the index. Thus the statistics that it builds after index creation have a 100%-page-read set of stats. Then when you do the update stats action next - it reads only a PORTION of the pages in the just-REBUILT index . . . and you wind up with LESS EFFECTIVE statistics because of that. More work and 'worse' data. Double hit!Everyone who reads this - write it down in your document of what NOT to do. :DFri, 23 May 2008 12:21:11 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic504420-146-1.aspxI have created a MP that runs on the weekend, I have two procedures that run, one to Rebuild Indexes and the second is Update Statistics...Each Monday morning we have the same Stored proccedures failing as stated above. The three Sp have a Try/Catch block at the begining of the procedure. When we recompile these SP everything works fine till we run the MP on the weekend. I am going to turn off the MP this weekend and see what happens??? Will update next Tuesday (Monday is Memorial day)...Fri, 23 May 2008 10:38:52 GMTReg_Mayfieldhttp://www.sqlservercentral.com/Forums/Topic504420-146-1.aspxAre you reBUILDING the indexes, or reORGing them? a rebuild will redo the stats and that WILL force all associated sprocs to compile their first execution. IIRC a reorg doesn't automatically redo the stats.Could be wrong about that.Fri, 23 May 2008 08:45:39 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic504420-146-1.aspxif thats was the problem then the procedure will generate an error even before the built index.Fri, 23 May 2008 01:56:37 GMTmobashahttp://www.sqlservercentral.com/Forums/Topic504420-146-1.aspxI was looking at the stored procedure and was wondering based on the error I got "Message : Incorrect syntax near 'CA'."Could it be that the TRY/CATCH is not meant for any error but only errors involving a transaction. The code will never cause an event like an update/insert or delete...BEGIN TRY SET @intApplicationKey = CAST(ISNULL(@strApplicationId,0) AS INTEGER)END TRYBEGIN CATCH SET @intApplicationKey = 0 IF @strApplicationId <> '%' SELECT @intApplicationKey = applicationKey FROM [Application] WHERE applicationId = @strApplicationIdEND CATCHSo my thought was the Try/Catch block may not be interpreted correctly and therefore an error is thrown...Any thoughts????Thu, 22 May 2008 15:08:58 GMTReg_Mayfieldhttp://www.sqlservercentral.com/Forums/Topic504420-146-1.aspxYou know what... I completely misread your original problem. I thought you were saying that you were unable to reindex the DB until you recompiled this SP, which was just impossible.OK, I'm on track now... the SP fails after you reindex and won't run again until you recompile, right... ok, I get it now.It's not a big SP, why don't you just save it with the recompile option and recompile it every time.Wed, 21 May 2008 12:48:31 GMTKenpoDBAhttp://www.sqlservercentral.com/Forums/Topic504420-146-1.aspxAdditional Information...Here is the actual error from the Web Application..Inner Exception --------------- Type : System.Data.SqlClient.SqlException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 Message : Incorrect syntax near 'CA'. Source : .Net SqlClient Data Provider Help link : Errors : System.Data.SqlClient.SqlErrorCollection Class : 15 LineNumber : 29 Number : 102 Procedure : uspDALC_mrCourtListByApplicationHere is the Code for the SP where it appears the error is occurring...Again I only see this after I reindex and the error goes away when I recompile the SP.../****** Object: StoredProcedure [dbo].[uspDALC_mrCourtListByApplication] Script Date: 05/21/2008 09:16:43 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGO/*uspDALC_mrCourtListByApplication 'BKECF'uspDALC_mrCourtListByApplication '4'uspDALC_mrCourtListByApplication '%','tracking.exists'exec uspDALC_mrCourtListByApplication @strApplicationId=N'__SELECT_ONE__',@strOption=N'tracking.exists'*/-- =============================================-- Author: -- Create date: -- Description: -- =============================================ALTER PROCEDURE [dbo].[uspDALC_mrCourtListByApplication] @strApplicationId VARCHAR(50) = '%', @strOption VARCHAR(20) = NULLASBEGIN SET NOCOUNT ON; DECLARE @intApplicationKey INTEGER BEGIN TRY SET @intApplicationKey = CAST(ISNULL(@strApplicationId,0) AS INTEGER) END TRY BEGIN CATCH SET @intApplicationKey = 0 IF @strApplicationId <> '%' SELECT @intApplicationKey = applicationKey FROM [Application] WHERE applicationId = @strApplicationId END CATCH SELECT DISTINCT Court.courtKey, courtId, courtName, courtId + ' ' + courtName as courtDisplayName FROM RequestDetail BASE JOIN ReportedBy rby ON BASE.requestKey = rby.requestKey AND rby.reportedByKey = (SELECT MAX(t.reportedByKey) FROM ReportedBy t WHERE t.requestKey = BASE.requestKey) JOIN Contact ON rby.contactKey = Contact.contactKey JOIN Court ON Contact.courtKey = Court.courtKey --ON BASE.courtKeyReportedIn = Court.courtKey AND (applicationKeyAssignedTo = @intApplicationKey OR @intApplicationKey = 0) AND ( (@strOption IS NULL) OR (@strOption = 'tracking.exists' AND EXISTS (SELECT * FROM requesttracking RT WHERE RT.requestKey = BASE.requestKey)) OR (@strOption = 'mr.exists' AND EXISTS (SELECT * FROM MR mr WHERE mr.requestKey = BASE.requestKey)) ) ORDER BY courtId RETURN @@ROWCOUNTENDWed, 21 May 2008 08:33:55 GMTReg_Mayfieldhttp://www.sqlservercentral.com/Forums/Topic504420-146-1.aspxJust ran one and there were no errors...CHECKDB found 0 allocation errors and 0 consistency errors in database 'MRv4_Reg'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Wed, 21 May 2008 07:39:35 GMTReg_Mayfieldhttp://www.sqlservercentral.com/Forums/Topic504420-146-1.aspxNo, you're right... recompiling and reindexing are completely autonomous ops and shouldn't have anything to do with each other. Have you done a checkDB on it?Wed, 21 May 2008 07:31:13 GMTKenpoDBAhttp://www.sqlservercentral.com/Forums/Topic504420-146-1.aspxI have a database that was recently created to interface with an in house web application. The developer has written a number of Stored Procedures to handle data manipulation. After a few weeks the queries started to run rather slow. I suggested we setup a maintenance plan to reindex all the tables during the weekend when there is no one using the application. After reindexing the Web application failed ("500 internal server error"). In order to get things working I had to recompile all of his Stored Procedures. The reindexing did improve performance but this is the first time I have run across this issue. I reindex all my other databases with no problems and no recompiles. Is this something that I should do as a normal course of business or is there something I can do to find out why a recompile is required for SP in this particular Database.I am running Sql Server 2005 Enterprise Edition, SP2, compatibility level 90. Thanks in advance...Wed, 21 May 2008 06:58:21 GMTReg_Mayfield