Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Maitenance plan generated T-sql for nonexisting indexes and table

Posted on 2013-11-11
4
Medium Priority
?
280 Views
Last Modified: 2013-11-21
I have a weird problem which our weekly maintenance plan for reorganize was keep failing  with a following errors


Executing the query "ALTER INDEX [Samba_2_Indexes_CL2] ON [dbo].[Samba_2] REORGANIZE WITH ( LOB_COMPACTION = ON )
" failed with the following error: "Cannot find the object "dbo.Samba_2" because it does not exist or you do not have permissions.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have verified a table samba2 is no longer exist in the db but again when I generate the T-sql from reorganize maintenance task, The wizard still generated T-sql for that table.
0
Comment
Question by:motioneye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 39642580
Do you have an integrity check running as part of your maintenance plan?
Does DBCC CHECKDB come back clean?
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 1000 total points
ID: 39642919
Hi,

I use Ola Hallengren's free maintenance script.
http://ola.hallengren.com/

The beauty about this script and the jobs it creates is: user_databases is evaluated at run-time, so selects all the user databases. I presume from the little of the code I've looked at that it does the same for the tables and indexes to reorganise.

HTH
  David
0
 
LVL 25

Expert Comment

by:jogos
ID: 39643208
Guess it's hard-coded that your that this index is created, check the process that is creating your T-sql.
0
 
LVL 30

Accepted Solution

by:
Rich Weissler earned 1000 total points
ID: 39644490
Could be hard coded in the maintenance plan.
I'd assumed motioneye was using the maintenance plans built in with the wizard from MS SQL.  In that case, I question whether there might be an error in the catalog which DBCC would correct.  If that is the case, unfortunately Ola Hallengren's script will also give a (likely different) error.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question