How can I update system catalogs or sys.indexes on SQL2012

Question

How can I update system catalogs or sys.indexes on SQL2012

sql-lover

SSCoach

Points: 18530
More actions
April 9, 2014 at 9:50 am

#306529

1st, please do not tell me this is not recommended, I know this already. I am testing a backup/restore of FileGroups backups and this is the only way to re-establish missing Indexes from an absent FG.
Having said that, I've done this previously on MS-SQL 2005, with SQL server on single user mode and connecting via DAC. But for some reason, it does not work on SQL 2012 anymore. It may be it was actually and finally deprecated, or something changed?
I enabled 'allow updates" via sp_configure. I am also connecting via DAC and added "-m" parameter on SQL service.
Here's the statement I am trying to run:
USE Adventureworks2012;
DELETE
FROM sys.indexes
WHERE name IS NOT NULL
AND index_id = 3
AND object_id = object_id(N'person.person',N'U')
It is generating following error
View or function 'sys.indexes' is not updatable because the modification affects multiple base tables

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First, sys.indexes is not a table but a system view. As the error message indicates it is made up of multiple tables and you can't update multiple tables at the same time through a view.

Also, as you said, it is not a recommended practice and MS has deprecated the ability to do so.

You may need to explore different options to accomplish the task at hand.

sql-lover SSCoach Points: 18530 More actions · Answer 2

Lynn Pettis (4/9/2014)
First, sys.indexes is not a table but a system view. As the error message indicates it is made up of multiple tables and you can't update multiple tables at the same time through a view.
Also, as you said, it is not a recommended practice and MS has deprecated the ability to do so.
You may need to explore different options to accomplish the task at hand.

Ok, let me put it this way...

I need to know how to update sys.indexes view or system catalog directly on SQL2012. Or, would like to confirm from someone else that has done this before, if it's no longer possible on SQL2012.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

sql-lover (4/9/2014)
I need to know how to update sys.indexes view or system catalog directly on SQL2012.

You can't. sys.indexes is not a table, it's a view.

Please, don't try the 'partial restore and delete my indexes from the system tables' trick unless you're willing to recreate the DB at a later point.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql-lover SSCoach Points: 18530 More actions · Answer 4

GilaMonster (4/9/2014)
sql-lover (4/9/2014)
I need to know how to update sys.indexes view or system catalog directly on SQL2012.
You can't. sys.indexes is not a table, it's a view.
Please, don't try the 'partial restore and delete my indexes from the system tables' trick unless you're willing to recreate the DB at a later point.

Please, stop repeating is a view 🙁 ...I know that!!!

But I can, at least on previous versions. It seems it's no longer working on SQL2012.

This is the most cost effective way for me to drastically reduce backup time and space for TB size databases. I've succesfully test and accomplished this before on 2005 and 2008 without any problems at all. Putting NCI on its own FG, and skip that FG from the backup. Then restore from backup file and recreate the missing Index. Because the Index is not the Clustered one, the table is fully readable, some queries are slow because the missing NCI, but it works. Editing the system catalog, dropping the absent Index allows me to recreate it while keeping the database up and running. This saves hours, probably a day, on a potential restore scenario. This is not something you should do for any database but especial cases and huge clients. It was doable before.

Gosh! ... sometimes is frustrating to me getting specific answers to specific questions without getting a lecture.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sql-lover (4/9/2014)
GilaMonster (4/9/2014)
sql-lover (4/9/2014)
I need to know how to update sys.indexes view or system catalog directly on SQL2012.
You can't. sys.indexes is not a table, it's a view.
Please, don't try the 'partial restore and delete my indexes from the system tables' trick unless you're willing to recreate the DB at a later point.
Please, stop repeating is a view 🙁 ...I know that!!!

So why are you trying to update it if you know that it's a view? Views, as I assume you know, can't be updated if the update would affect multiple tables, which is exactly what the error you initially posted told you. If you want to break things, update the tables, not a view, and if you want to fiddle with stuff that's undocumented, don't complain when it changes from one version to another.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

sql-lover (4/9/2014)
GilaMonster (4/9/2014)
sql-lover (4/9/2014)
I need to know how to update sys.indexes view or system catalog directly on SQL2012.
You can't. sys.indexes is not a table, it's a view.
Please, don't try the 'partial restore and delete my indexes from the system tables' trick unless you're willing to recreate the DB at a later point.
Please, stop repeating is a view 🙁 ...I know that!!!
But I can, at least on previous versions. It seems it's no longer working on SQL2012.
This is the most cost effective way for me to drastically reduce backup time and space for TB size databases. I've succesfully test and accomplished this before on 2005 and 2008 without any problems at all. Putting NCI on its own FG, and skip that FG from the backup. Then restore from backup file and recreate the missing Index. Because the Index is not the Clustered one, the table is fully readable, some queries are slow because the missing NCI, but it works. Editing the system catalog, dropping the absent Index allows me to recreate it while keeping the database up and running. This saves hours, probably a day, on a potential restore scenario. This is not something you should do for any database but especial cases and huge clients. It was doable before.
Gosh! ... sometimes is frustrating to me getting specific answers to specific questions without getting a lecture.

Well, running SELECT * FROM sys.indexes and having that return the actual execution plan, it looks like the underlying tables for this view are:

sysidxstats

syssingleobjrefs

syspalvalues

These tables most likely reside in the resources database. If you want to figure out how to access and update this database and tables, go right ahead. I just hope you don't trash your TB+ database while doing it.

sql-lover SSCoach Points: 18530 More actions · Answer 7

My goal is, be able to recreate the missing non clustered Index with same name and drop the old one. Because the database was restored without a FG that contains that NCI, that's not possible. I totally understand that. But again, that's the main goal here, skip the big NCI from the weekly backup.

On 2005 and 2008, I was able to accomplish this without any issue, but it's no longer working.

"Playing around" with resource database may be the solution, but I don't know, and that's a more drastically approach.

My guts are telling me editing SQL2012 system catalog via DAC and while running SQL on single user is no longer possible. If that's the case, then the right answer would be: it was possible on previous versions via hack, but not anymore on SQL2012.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

GilaMonster (4/9/2014)
sql-lover (4/9/2014)
GilaMonster (4/9/2014)
sql-lover (4/9/2014)
I need to know how to update sys.indexes view or system catalog directly on SQL2012.
You can't. sys.indexes is not a table, it's a view.
Please, don't try the 'partial restore and delete my indexes from the system tables' trick unless you're willing to recreate the DB at a later point.
Please, stop repeating is a view 🙁 ...I know that!!!
So why are you trying to update it if you know that it's a view? Views, as I assume you know, can't be updated if the update would affect multiple tables, which is exactly what the error you initially posted told you. If you want to break things, update the tables, not a view, and if you want to fiddle with stuff that's undocumented, don't complain when it changes from one version to another.

Same thing I told him.

Interesting that I see more of your post when quoted than directly reading the post in the forum.

sql-lover SSCoach Points: 18530 More actions · Answer 9

GilaMonster (4/9/2014)

If you want to break things, update the tables, not a view, and if you want to fiddle with stuff that's undocumented, don't complain when it changes from one version to another.

There are tons of undocumented DBCC commands and SQL workarounds out there even more dangerous than this. But Totally understand. Nobody is complaining or will complain and that's a risk you assume when dealing with undocumented stuff.