Solved

Maitenance plan generated T-sql for nonexisting indexes and table

Posted on 2013-11-11
4
262 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
  • 2
4 Comments
 
LVL 29

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 250 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 29

Accepted Solution

by:
Rich Weissler earned 250 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now