From: Barb on
Environment:

SQL Server 2005 Enterprise Edition SP2

Windows 2003 R2 x64 Enterprise

80+ Databases, all upgraded to compatibility 90



Since upgrading to SP2 for SQL Server 2005, my scheduled jobs based on
Maintenance Plans which include Statistics Updates, Index Rebuilding and
Index Reorganization tasks have reported failure. The failed job history
indicates the following: “The Execute method on the task returned error code
0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.).”
Although the job fails, I'm not convinced that it's not also completing its
tasks. A trace on the activity of the SQL Agent while the jobs are running
indicates that these jobs are properly executing their Alter Table statements
without incident and the duration of execution seems to be roughly the same
as before.



The above seemed like an unsatisfactorily vague error, so I decided to run a
bit more extensive trace to see exactly what errors were being raised while
the job was run. Doing so revealed the following error: Event Class: User
Error Message; ApplicationName: Microsoft SQL Server Management Studio;
Error: 3701; TextData: “Cannot drop the table
'#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do
not have permission.”



This error did not occur prior to the upgrade to SP2, and I've noticed
similar errors being trapped in traces run on my other SQL Servers post-SP2
when trying to do multi-database maintenance plans. I have expanded my trace
to trap all of the RPC and SQL textdata created by the SQL Agent when running
my jobs, looking in particular for the text
'#tmp_sp_db_vardecimal_storage_format'. I found on several occasions (at
least 4 times for each database altered within the job) that a “drop table
#tmp_sp_db_vardecimal_storage_format” was being called within the maintenance
plan without any corresponding create table statement.



Though I don't doubt that my maintenance plans are succeeding at some level,
I'm bothered by all of these unnecessary 3701 errors raised by the “drop
table #tmp_sp_db_vardecimal_storage_format” statements. I believe that this
is a bug in the post-SP2 Management Studio code and wonder if there's some
kind of workaround to avoid this, or some patch on the near horizon.


Thanks,
Barb

From: John Bell on
Hi Barb

"Barb" wrote:

> Environment:
>
> SQL Server 2005 Enterprise Edition SP2
>
> Windows 2003 R2 x64 Enterprise
>
> 80+ Databases, all upgraded to compatibility 90
>
>
>
> Since upgrading to SP2 for SQL Server 2005, my scheduled jobs based on
> Maintenance Plans which include Statistics Updates, Index Rebuilding and
> Index Reorganization tasks have reported failure. The failed job history
> indicates the following: “The Execute method on the task returned error code
> 0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.).”
> Although the job fails, I'm not convinced that it's not also completing its
> tasks. A trace on the activity of the SQL Agent while the jobs are running
> indicates that these jobs are properly executing their Alter Table statements
> without incident and the duration of execution seems to be roughly the same
> as before.
>
>
>
> The above seemed like an unsatisfactorily vague error, so I decided to run a
> bit more extensive trace to see exactly what errors were being raised while
> the job was run. Doing so revealed the following error: Event Class: User
> Error Message; ApplicationName: Microsoft SQL Server Management Studio;
> Error: 3701; TextData: “Cannot drop the table
> '#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do
> not have permission.”
>
>
>
> This error did not occur prior to the upgrade to SP2, and I've noticed
> similar errors being trapped in traces run on my other SQL Servers post-SP2
> when trying to do multi-database maintenance plans. I have expanded my trace
> to trap all of the RPC and SQL textdata created by the SQL Agent when running
> my jobs, looking in particular for the text
> '#tmp_sp_db_vardecimal_storage_format'. I found on several occasions (at
> least 4 times for each database altered within the job) that a “drop table
> #tmp_sp_db_vardecimal_storage_format” was being called within the maintenance
> plan without any corresponding create table statement.
>
>
>
> Though I don't doubt that my maintenance plans are succeeding at some level,
> I'm bothered by all of these unnecessary 3701 errors raised by the “drop
> table #tmp_sp_db_vardecimal_storage_format” statements. I believe that this
> is a bug in the post-SP2 Management Studio code and wonder if there's some
> kind of workaround to avoid this, or some patch on the near horizon.
>
>
> Thanks,
> Barb

vardecimal is a new datatype introduced in SP2. Have you tried to run
sp_db_vardecimal_storage_format
http://msdn2.microsoft.com/en-us/library/bb326653.aspx

John
From: delashmutt on
On Feb 27, 11:58 pm, John Bell <jbellnewspo...(a)hotmail.com> wrote:
> Hi Barb
>
>
>
>
>
> "Barb" wrote:
> > Environment:
>
> > SQL Server 2005 Enterprise EditionSP2
>
> > Windows 2003 R2 x64 Enterprise
>
> > 80+ Databases, all upgraded to compatibility 90
>
> > Since upgrading toSP2for SQL Server 2005, my scheduled jobs based on
> > Maintenance Plans which include Statistics Updates, Index Rebuilding and
> > Index Reorganization tasks have reported failure. The failed job history
> > indicates the following: "The Execute method on the task returned error code
> > 0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.)."
> > Although the job fails, I'm not convinced that it's not also completing its
> > tasks. A trace on the activity of the SQL Agent while the jobs are running
> > indicates that these jobs are properly executing their Alter Table statements
> > without incident and the duration of execution seems to be roughly the same
> > as before.
>
> > The above seemed like an unsatisfactorily vague error, so I decided to run a
> > bit more extensive trace to see exactly what errors were being raised while
> > the job was run. Doing so revealed the following error: Event Class: User
> > Error Message; ApplicationName: Microsoft SQL Server Management Studio;
> > Error: 3701; TextData: "Cannot drop the table
> > '#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do
> > not have permission."
>
> > This error did not occur prior to the upgrade toSP2, and I've noticed
> > similar errors being trapped in traces run on my other SQL Servers post-SP2
> > when trying to do multi-database maintenance plans. I have expanded my trace
> > to trap all of the RPC and SQL textdata created by the SQL Agent when running
> > my jobs, looking in particular for the text
> > '#tmp_sp_db_vardecimal_storage_format'. I found on several occasions (at
> > least 4 times for each database altered within the job) that a "drop table
> > #tmp_sp_db_vardecimal_storage_format" was being called within the maintenance
> > plan without any corresponding create table statement.
>
> > Though I don't doubt that my maintenance plans are succeeding at some level,
> > I'm bothered by all of these unnecessary 3701 errors raised by the "drop
> > table #tmp_sp_db_vardecimal_storage_format" statements. I believe that this
> > is a bug in the post-SP2Management Studio code and wonder if there's some
> > kind of workaround to avoid this, or some patch on the near horizon.
>
> > Thanks,
> > Barb
>
> vardecimal is a new datatype introduced inSP2. Have you tried to run
> sp_db_vardecimal_storage_formathttp://msdn2.microsoft.com/en-us/library/bb326653.aspx
>
> John- Hide quoted text -
>
> - Show quoted text -

If you run a User Error Trace and look for 3701 errors, they pop up
not only when the packages are being run, but also when you're trying
to create Maint. jobs in SQL Server Management Studio. Whether
sp_db_vardecimal_storage_format is enabled or disabled for a database
makes no difference.