Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

Hi I'm not a DBA so forgive my ignorance. I don't have access to administrator tools etc. However, I do know that we have a live server which is approaching capacity ie 80%-90% full perhaps.

What is happening is that our web-application has started not writing certain records and it appears as though SQLServer is attempting to write a duplicate primary key in certain tables. I don't understand this and as this is an auto-generated column it is completely out of our control.

Is this a possible symptom of the server getting too full? Surely a DBMS should manage this kind of thing safely?

share|improve this question
1  
Can you provide details of the error message(s) you are seeing? (it's unlikely to be a symptom of the server getting 'full'.) – Andrew Bickerton Feb 4 '11 at 14:32

4 Answers

up vote 4 down vote

I think this could happen only if manual intervention was done to that primary key. It's possible to manually insert data in the identity column only if option "set identity_insert table_name on" was used, that way you can generate PK errors. But better check exactly if the PK is only that identity column, and, if yes, if this option is used in any of the stored procedures or scripts that you use.

SQL Server alone would not have any reason to not treat correctly these columns.

share|improve this answer
I agree with all the above - also check you have no other indexes or constraints that are causing an issue. – Miles D Feb 5 '11 at 18:52
up vote 1 down vote

There could be probably two reasons,

  1. The data type specified for the auto_increment column has reached its limit, for example if you have a TINYINT UNSIGNED AUTO_INCREMENT column, this column would support a maximum value of 255, say you have already reached the value of 255, then inserting any further row would generate a value of 256 which will be truncated to 255 and then because that value is already present in the table, MySQL will throw a duplicate key error.

  2. You are manually trying to insert a value in the AUTO_INCREMENT column that is already present in there,.

share|improve this answer
up vote 0 down vote

It could be quite possible that you are using GUID's, which are not guaranteed to be unique. Since you mention that you are getting 'full', I am assuming that you have inserted many GUIDs so far and that every now and then the system happens to generate a GUID that is already in use.

If you have a specific error message to share, that would be helpful.

For more information check out Kimberly Tripp's blog posts at http://sqlskills.com/BLOGS/KIMBERLY/category/Indexes.aspx

share|improve this answer
I'm not using GUIDs I'm just BIGINT with identity(1,1). The DB is getting full but I'm no where near the max of BIGINT. On a sidenote surely GUIDs would give me even more possible records than BIGINT? – user811 Feb 4 '11 at 14:18
What is the error message you are getting? – SQLRockstar Feb 4 '11 at 14:33
1  
@Rockstar, have you ever had a case of new GUID not being unique? I know one can't guarantee uniqueness in the universe, but still, there's a too small possibility of generating non unique guids, actually not once, but more than once, he mentioned that it happens from time to time, in different tables. I mean, I'd go 99.999% on human error and only the other part on testing the GUID uniqueness :-). – Marian Feb 4 '11 at 15:02
@Marian, personally no, but based upon the question talking about 'fullness' and errors with inserts (and not providing an error message) i was offering one possible suggestion. i also missed the part in the question regarding 'certain tables', so yeah it would appear that there is something else amiss. – SQLRockstar Feb 4 '11 at 15:39
up vote 0 down vote

Explain what you mean by the database getting full. I'm not clear on what that is supposed to mean in this context. Are you running out of disk space?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.