Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1873
Members Online:
2
Guests Online:
68

Total Post History
Posts:
81445
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
BitogClogs

Other

FAQ
All times are UTC [ DST ]

Google Ads

Can't drop a clustered index because a foreign key exists--

Please wait...

Can't drop a clustered index because a foreign key exists--

Postby izDBA on Wed Jan 09, 2013 8:21 pm

I'm trying to drop a clustered index but receive a message like the one below:

Msg 3725, Level 16, State 0, Line 3
The constraint 'entity_pk' is being referenced by table 'local_zipcodes_table', foreign key constraint 'local_zip_fk'.
Msg 3727, Level 16, State 0, Line 3
Could not drop constraint. See previous errors.

Seems simple, except that table doesn't have a foreign key pointing back to my original table.

hlep hlep.
izDBA
Apprentice
Apprentice
 
Posts: 7
Joined: Wed Jan 09, 2013 8:16 pm
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby chrissie1 on Wed Jan 09, 2013 8:37 pm

did you try drop index local_zip_fk ?
pink fuzzy slippers
User avatar
chrissie1
Senior Guru
Senior Guru
LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135
LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135
LTD Gold - Rating: 2135LTD Gold - Rating: 2135
 
Posts: 9475
Joined: Wed Oct 10, 2007 7:18 pm
Location: Belgium
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby chrissie1 on Wed Jan 09, 2013 8:43 pm

oops that should have been

ALTER TABLE [dbo.local_zipcodes_table] DROP CONSTRAINT [local_zip_fk]
pink fuzzy slippers
User avatar
chrissie1
Senior Guru
Senior Guru
LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135
LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135LTD Gold - Rating: 2135
LTD Gold - Rating: 2135LTD Gold - Rating: 2135
 
Posts: 9475
Joined: Wed Oct 10, 2007 7:18 pm
Location: Belgium
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby izDBA on Wed Jan 09, 2013 9:04 pm

Yeah.... and that's where it got interesting. I just ran it from the command line (recall in SSMS it didn't show up) and it said it ran successfully. Then tried the drop index again and, wonder of wonders, got the same message about a different index. Ran through this a couple times dropping indexes that didn't exist and finally it worked.

But the new question is why... did sys.sysreferences have some junk data in it that wasn't being discovered in normal database maintenances?
izDBA
Apprentice
Apprentice
 
Posts: 7
Joined: Wed Jan 09, 2013 8:16 pm
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby SQLDenis on Wed Jan 09, 2013 9:29 pm

This will give the table name if searching by constraint name


  1. select  name as ConstraintName ,
  2. OBJECT_NAME(parent_object_id) as TableName
  3. from sys.foreign_keys
  4. where name = 'local_zip_fk'


or if you want to use sys.sysobjects

  1. select s.name as ConstraintName ,
  2. OBJECT_NAME(parent_obj) as TableName,
  3. c.name as ColumnName
  4. from sys.sysobjects s
  5. join sys.syscolumns c on s.parent_obj = c.id
  6. where s.xtype = 'F'
  7. and  s.name = 'local_zip_fk'
  8.  



don't use sys.sysreferences anymore, it has been replaced by sys.foreign_keys
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby izDBA on Wed Jan 09, 2013 10:59 pm

Thanks for the comments -- but I'm still stumped why I can't see the index in SQL Server management studio. Is there an inconsistency there?

What started all this was I ran a report from SSMS that shows about 30% of the database has unused space. I checked sp_spaceused and it's telling me only about 10% of my database isn't being used. I tracked it down to this one table but now I'm kinda stumped. I've dropped all the indexes an created a primary key with fill_factor =100 but I still have the same thing going on. This table DOES have LOB data in it. Is that really the issue?
izDBA
Apprentice
Apprentice
 
Posts: 7
Joined: Wed Jan 09, 2013 8:16 pm
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby SQLDenis on Wed Jan 09, 2013 11:17 pm

fill factor of 100% doesn't mean 100% will be used. A page is 8060 bytes. if for example you have a row that is 5000 bytes you can only store 1 row per page in this case you will never fill up more than 60% of a page. It would be very hard to fill up over 90% of a page in most cases since that last right might just be to long to fit

what is your average row size?
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby SQLDenis on Wed Jan 09, 2013 11:22 pm

See also here: Topic17980: Identity Column / Clustered Index

Basically, fragmentation occurs when you have data pages than you really need. SQL Server stores data in 8kb pages. When you insert data, SQL server will use the clustered index to determine which page the data should be stored. If this page is in the middle of the table, and the page is too full to accommodate another row, SQL Server will split the page, putting 1/2 of the data on one page and the other 1/2 on another page. This effectively leaves you with 2 pages 1/2 full, which leads to less efficient queries because SQL server will now need to search through more data pages.

You can alleviate the fragmentation issue somewhat by allowing a certain amount of free space on each data page. 50% free space is usually way too much, but you can set it to 10%. If you do not cluster on an identity column, it is a good idea to track your fragmentation to see if it getting high. To correct the fragmentation problem, you can rebuild the index.
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby izDBA on Thu Jan 10, 2013 2:49 pm

I'm adding an attachment so you can see what I'm looking at. The unused space is accounting for 1/3 of the database and is more than 30GB of space. Most of that is one table. That's why I focused on that one trying to reduce the amount of space wasted. After the reindex I still see a collosal amount of unused space.
You do not have the required permissions to view the files attached to this post.
izDBA
Apprentice
Apprentice
 
Posts: 7
Joined: Wed Jan 09, 2013 8:16 pm
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby SQLDenis on Thu Jan 10, 2013 3:06 pm

That is just freed up space after you reindexed then. Nothing wrong with that, you actually want free space, this way SQL Server doesn't have to grow the file everytime you do a couple of inserts. See also this post Sizing database files that shows that there is a performance benefit if you have free space in the data files

can you run this, change TableName to the real table name and post results here

  1. DBCC SHOWCONTIG ('TableName')
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby izDBA on Thu Jan 10, 2013 3:11 pm

Hi, I'm talking about the unused space, which I understand to be allocated to something but not used. Thus, if table 1 has it but it's needed by table 2 then table 2 can't use it. Further, I need to shrink files and I can't shrink this.

I must shrink this down.
izDBA
Apprentice
Apprentice
 
Posts: 7
Joined: Wed Jan 09, 2013 8:16 pm
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby gmmastros on Thu Jan 10, 2013 3:12 pm

Can you run this query and post the results?

  1. Select  Sum(C.max_length)
  2. From    sys.Columns As C
  3. Where   object_id = Object_ID('YourTableNameHere')
  4.  


This query will show you the "width" of your table, in bytes. It's a little misleading because text, ntext, varchar(max), and nvarchar(max) will only use 16 bytes if the data doesn't fit within 8060 bytes, but it will give us a good estimate.

By the way... this query will run extremely fast and will not impact the performance on your server.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby SQLDenis on Thu Jan 10, 2013 3:16 pm

You would have to shrink the file in order to get space back. In general this is not recommended since it causes massive fragmentation. First read this post http://www.sqlskills.com/blogs/paul/why ... ata-files/


then take a look at DBCC SHRINKFILE http://msdn.microsoft.com/en-us/library/ms189493.aspx
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby izDBA on Thu Jan 10, 2013 3:21 pm

I can't shrink the file and gain the unused space (from the picture). The unallocated space, yes, but not the unused space.
izDBA
Apprentice
Apprentice
 
Posts: 7
Joined: Wed Jan 09, 2013 8:16 pm
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby onpnt on Thu Jan 10, 2013 3:22 pm

Trying to ignore the concept of dropping indexes and such because of a notice of unallocated space...

Questions that need to be answered before I'll offer the best practice and, right thing to do here.

Is this production
Are you in need of reclaiming space due to a critical reason
Do you have a DBA on hand or are you the DBA
Do you care about what happens to the data or performance
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby SQLDenis on Thu Jan 10, 2013 3:24 pm

Did you drop varchar or text columns from this table? If so try running DBCC CLEANTABLE
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Can't drop a clustered index because a foreign key exists--

Postby izDBA on Thu Jan 10, 2013 3:35 pm

I don't know the history of the table; I did try that just in case but to no avail. The table has several fields holding varbinary and image datatypes.
izDBA
Apprentice
Apprentice
 
Posts: 7
Joined: Wed Jan 09, 2013 8:16 pm
Unrated