Database level lock: tables

Showing posts with label tables. Show all posts

Thursday, March 29, 2012

Database in recovery

Hi,
I'm not that familiar with SQL Server, just mainly use it for
development. Anyway I was modifying some keys in some tables last
night. Everything went fine and I continued to use the database.
When i woke this morning and turned on the computer, my database
was in recovery. I am unable to do anything to get it back.
I have tried a few things to alter the database, (ie. set offline, set
single user) but I get an error saying
"Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'trace05e' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."
when I do an sp_who I get
spid ecid status loginame
hostname
blk dbname
cmd request_id
-- -- --
------
------
------
-- --
1 0 background sa
0 NULL
RESOURCE MONITOR 0
2 0 suspended sa
0 NULL
LOG WRITER 0
3 0 background sa
0 NULL
LAZY WRITER 0
4 0 background sa
0 master
UNKNOWN TOKEN 0
5 0 background sa
0 NULL
LOCK MONITOR 0
6 0 sleeping sa
0 master
TASK MANAGER 0
7 0 background sa
0 master
TRACE QUEUE TASK 0
8 0 sleeping sa
0 NULL
UNKNOWN TOKEN 0
9 0 background sa
0 master
BRKR TASK 0
10 0 background sa
0 master
TASK MANAGER 0
11 0 suspended sa
0 master
CHECKPOINT 0
17 0 background sa
0 NULL
UNKNOWN TOKEN 0
19 0 background sa
0 master
BRKR EVENT HNDLR 0
20 0 background sa
0 master
BRKR TASK 0
51 0 sleeping NT AUTHORITY\SYSTEM
computer
0 ReportServer$sqlexpress
AWAITING COMMAND 0
53 0 sleeping computer\User
computer
0 master
AWAITING
COMMAND 0
54 0 runnable computer\User
computer
0
master
SELECT
0
56 0 sleeping computer\User
I can't find any commands to kill the last three processes.
I need to know two things.
1) How can I find out why my database went into recovery mode?
2) How can I get my database back into normal mode?
Thanks a million in advance
JD
I managed to kill some processes and now I have two left, the one I am
using and one more, which is needed as a transport.
i still cannot execute this statment
RESTORE DATABASE trace05e
WITH RECOVERY
I get
Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
|||Hi
I can only guess. When you start SQL Server it does perfom the opeartion (in
every databases on the server) 'recovering' by using CHECKPOINT command that
means that all transactions are resided in the cache should be REDO/UNDO
depends on its status.
<the_kiddie98@.yahoo.com> wrote in message
news:1141293232.548857.223880@.t39g2000cwt.googlegr oups.com...
> Hi,
> I'm not that familiar with SQL Server, just mainly use it for
> development. Anyway I was modifying some keys in some tables last
> night. Everything went fine and I continued to use the database.
> When i woke this morning and turned on the computer, my database
> was in recovery. I am unable to do anything to get it back.
> I have tried a few things to alter the database, (ie. set offline, set
> single user) but I get an error saying
> "Msg 5011, Level 14, State 7, Line 1
> User does not have permission to alter database 'trace05e' or the
> database does not exist.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed."
> when I do an sp_who I get
> spid ecid status loginame
> hostname
> blk dbname
> cmd request_id
> -- -- --
> ------
> ------
> --
> ------
> -- --
> 1 0 background sa
>
> 0 NULL
> RESOURCE MONITOR 0
> 2 0 suspended sa
>
> 0 NULL
> LOG WRITER 0
> 3 0 background sa
>
> 0 NULL
> LAZY WRITER 0
> 4 0 background sa
>
> 0 master
> UNKNOWN TOKEN 0
> 5 0 background sa
>
> 0 NULL
> LOCK MONITOR 0
> 6 0 sleeping sa
>
> 0 master
> TASK MANAGER 0
> 7 0 background sa
>
> 0 master
> TRACE QUEUE TASK 0
> 8 0 sleeping sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 9 0 background sa
>
> 0 master
> BRKR TASK 0
> 10 0 background sa
>
> 0 master
> TASK MANAGER 0
> 11 0 suspended sa
>
> 0 master
> CHECKPOINT 0
> 17 0 background sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 19 0 background sa
>
> 0 master
> BRKR EVENT HNDLR 0
> 20 0 background sa
>
> 0 master
> BRKR TASK 0
> 51 0 sleeping NT AUTHORITY\SYSTEM
> computer
> 0 ReportServer$sqlexpress
> AWAITING COMMAND 0
> 53 0 sleeping computer\User
> computer
> 0 master
> AWAITING
> COMMAND 0
> 54 0 runnable computer\User
> computer
> 0
> master
> SELECT
> 0
> 56 0 sleeping computer\User
> I can't find any commands to kill the last three processes.
> I need to know two things.
> 1) How can I find out why my database went into recovery mode?
> 2) How can I get my database back into normal mode?
> Thanks a million in advance
> JD
>
|||SQL Server shouldn't let anyone in to the database if it is in recovery state. Perhaps it is only a
refresh problem in EM/SSMS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<the_kiddie98@.yahoo.com> wrote in message
news:1141293565.559941.65460@.v46g2000cwv.googlegro ups.com...
>I managed to kill some processes and now I have two left, the one I am
> using and one more, which is needed as a transport.
> i still cannot execute this statment
> RESTORE DATABASE trace05e
> WITH RECOVERY
> I get
> Msg 3101, Level 16, State 1, Line 3
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
>
|||Hi Uri,
Sorry for my ignorance but what does that mean. Is there a way I
can rollback the logs to a certain point in time?
Is there any way outside of sql server that I can gain access to
the stored procedures inside in the mdf file?
Thanks
JD
Uri Dimant wrote:
> Hi
> I can only guess. When you start SQL Server it does perfom the opeartion (in
> every databases on the server) 'recovering' by using CHECKPOINT command that
> means that all transactions are resided in the cache should be REDO/UNDO
> depends on its status.
>
|||nope it isn't a refresh error as I have rebooted a few times and I am
still unable to get the database to work.
I have now resorted to removing the instance and re-installing it and
trying to get back an older database. I can't run any commands like
dbcc checkdb even on master, there seems to be invisible connections
all over the place.

Database in recovery

Hi,
I'm not that familiar with SQL Server, just mainly use it for
development. Anyway I was modifying some keys in some tables last
night. Everything went fine and I continued to use the database.
When i woke this morning and turned on the computer, my database
was in recovery. I am unable to do anything to get it back.
I have tried a few things to alter the database, (ie. set offline, set
single user) but I get an error saying
"Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'trace05e' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."
when I do an sp_who I get
spid ecid status loginame
hostname
blk dbname
cmd request_id
-- -- --
------
------
--
------
-- --
1 0 background sa
0 NULL
RESOURCE MONITOR 0
2 0 suspended sa
0 NULL
LOG WRITER 0
3 0 background sa
0 NULL
LAZY WRITER 0
4 0 background sa
0 master
UNKNOWN TOKEN 0
5 0 background sa
0 NULL
LOCK MONITOR 0
6 0 sleeping sa
0 master
TASK MANAGER 0
7 0 background sa
0 master
TRACE QUEUE TASK 0
8 0 sleeping sa
0 NULL
UNKNOWN TOKEN 0
9 0 background sa
0 master
BRKR TASK 0
10 0 background sa
0 master
TASK MANAGER 0
11 0 suspended sa
0 master
CHECKPOINT 0
17 0 background sa
0 NULL
UNKNOWN TOKEN 0
19 0 background sa
0 master
BRKR EVENT HNDLR 0
20 0 background sa
0 master
BRKR TASK 0
51 0 sleeping NT AUTHORITY\SYSTEM
computer
0 ReportServer$sqlexpress
AWAITING COMMAND 0
53 0 sleeping computer\User
computer
0 master
AWAITING
COMMAND 0
54 0 runnable computer\User
computer
0
master
SELECT
0
56 0 sleeping computer\User
I can't find any commands to kill the last three processes.
I need to know two things.
1) How can I find out why my database went into recovery mode?
2) How can I get my database back into normal mode?
Thanks a million in advance
JDI managed to kill some processes and now I have two left, the one I am
using and one more, which is needed as a transport.
i still cannot execute this statment
RESTORE DATABASE trace05e
WITH RECOVERY
I get
Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.|||Hi
I can only guess. When you start SQL Server it does perfom the opeartion (in
every databases on the server) 'recovering' by using CHECKPOINT command that
means that all transactions are resided in the cache should be REDO/UNDO
depends on its status.
<the_kiddie98@.yahoo.com> wrote in message
news:1141293232.548857.223880@.t39g2000cwt.googlegroups.com...
> Hi,
> I'm not that familiar with SQL Server, just mainly use it for
> development. Anyway I was modifying some keys in some tables last
> night. Everything went fine and I continued to use the database.
> When i woke this morning and turned on the computer, my database
> was in recovery. I am unable to do anything to get it back.
> I have tried a few things to alter the database, (ie. set offline, set
> single user) but I get an error saying
> "Msg 5011, Level 14, State 7, Line 1
> User does not have permission to alter database 'trace05e' or the
> database does not exist.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed."
> when I do an sp_who I get
> spid ecid status loginame
> hostname
> blk dbname
> cmd request_id
> -- -- --
> ------
> ------
> --
> ------
> -- --
> 1 0 background sa
>
> 0 NULL
> RESOURCE MONITOR 0
> 2 0 suspended sa
>
> 0 NULL
> LOG WRITER 0
> 3 0 background sa
>
> 0 NULL
> LAZY WRITER 0
> 4 0 background sa
>
> 0 master
> UNKNOWN TOKEN 0
> 5 0 background sa
>
> 0 NULL
> LOCK MONITOR 0
> 6 0 sleeping sa
>
> 0 master
> TASK MANAGER 0
> 7 0 background sa
>
> 0 master
> TRACE QUEUE TASK 0
> 8 0 sleeping sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 9 0 background sa
>
> 0 master
> BRKR TASK 0
> 10 0 background sa
>
> 0 master
> TASK MANAGER 0
> 11 0 suspended sa
>
> 0 master
> CHECKPOINT 0
> 17 0 background sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 19 0 background sa
>
> 0 master
> BRKR EVENT HNDLR 0
> 20 0 background sa
>
> 0 master
> BRKR TASK 0
> 51 0 sleeping NT AUTHORITY\SYSTEM
> computer
> 0 ReportServer$sqlexpress
> AWAITING COMMAND 0
> 53 0 sleeping computer\User
> computer
> 0 master
> AWAITING
> COMMAND 0
> 54 0 runnable computer\User
> computer
> 0
> master
> SELECT
> 0
> 56 0 sleeping computer\User
> I can't find any commands to kill the last three processes.
> I need to know two things.
> 1) How can I find out why my database went into recovery mode?
> 2) How can I get my database back into normal mode?
> Thanks a million in advance
> JD
>|||SQL Server shouldn't let anyone in to the database if it is in recovery state. Perhaps it is only a
refresh problem in EM/SSMS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<the_kiddie98@.yahoo.com> wrote in message
news:1141293565.559941.65460@.v46g2000cwv.googlegroups.com...
>I managed to kill some processes and now I have two left, the one I am
> using and one more, which is needed as a transport.
> i still cannot execute this statment
> RESTORE DATABASE trace05e
> WITH RECOVERY
> I get
> Msg 3101, Level 16, State 1, Line 3
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
>|||Hi Uri,
Sorry for my ignorance but what does that mean. Is there a way I
can rollback the logs to a certain point in time?
Is there any way outside of sql server that I can gain access to
the stored procedures inside in the mdf file?
Thanks
JD
Uri Dimant wrote:
> Hi
> I can only guess. When you start SQL Server it does perfom the opeartion (in
> every databases on the server) 'recovering' by using CHECKPOINT command that
> means that all transactions are resided in the cache should be REDO/UNDO
> depends on its status.
>|||nope it isn't a refresh error as I have rebooted a few times and I am
still unable to get the database to work.
I have now resorted to removing the instance and re-installing it and
trying to get back an older database. I can't run any commands like
dbcc checkdb even on master, there seems to be invisible connections
all over the place.

Database in recovery

Hi,
I'm not that familiar with SQL Server, just mainly use it for
development. Anyway I was modifying some keys in some tables last
night. Everything went fine and I continued to use the database.
When i woke this morning and turned on the computer, my database
was in recovery. I am unable to do anything to get it back.
I have tried a few things to alter the database, (ie. set offline, set
single user) but I get an error saying
"Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'trace05e' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."
when I do an sp_who I get
spid ecid status loginame
hostname
blk dbname
cmd request_id
-- -- --
----
----
----
----
--
----
----
-- --
1 0 background sa
0 NULL
RESOURCE MONITOR 0
2 0 suspended sa
0 NULL
LOG WRITER 0
3 0 background sa
0 NULL
LAZY WRITER 0
4 0 background sa
0 master
UNKNOWN TOKEN 0
5 0 background sa
0 NULL
LOCK MONITOR 0
6 0 sleeping sa
0 master
TASK MANAGER 0
7 0 background sa
0 master
TRACE QUEUE TASK 0
8 0 sleeping sa
0 NULL
UNKNOWN TOKEN 0
9 0 background sa
0 master
BRKR TASK 0
10 0 background sa
0 master
TASK MANAGER 0
11 0 suspended sa
0 master
CHECKPOINT 0
17 0 background sa
0 NULL
UNKNOWN TOKEN 0
19 0 background sa
0 master
BRKR EVENT HNDLR 0
20 0 background sa
0 master
BRKR TASK 0
51 0 sleeping NT AUTHORITY\SYSTEM
computer
0 ReportServer$sqlexpress
AWAITING COMMAND 0
53 0 sleeping computer\User
computer
0 master
AWAITING
COMMAND 0
54 0 runnable computer\User
computer
0
master
SELECT
0
56 0 sleeping computer\User
I can't find any commands to kill the last three processes.
I need to know two things.
1) How can I find out why my database went into recovery mode?
2) How can I get my database back into normal mode?
Thanks a million in advance
JDI managed to kill some processes and now I have two left, the one I am
using and one more, which is needed as a transport.
i still cannot execute this statment
RESTORE DATABASE trace05e
WITH RECOVERY
I get
Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.|||Hi
I can only guess. When you start SQL Server it does perfom the opeartion (in
every databases on the server) 'recovering' by using CHECKPOINT command that
means that all transactions are resided in the cache should be REDO/UNDO
depends on its status.
<the_kiddie98@.yahoo.com> wrote in message
news:1141293232.548857.223880@.t39g2000cwt.googlegroups.com...
> Hi,
> I'm not that familiar with SQL Server, just mainly use it for
> development. Anyway I was modifying some keys in some tables last
> night. Everything went fine and I continued to use the database.
> When i woke this morning and turned on the computer, my database
> was in recovery. I am unable to do anything to get it back.
> I have tried a few things to alter the database, (ie. set offline, set
> single user) but I get an error saying
> "Msg 5011, Level 14, State 7, Line 1
> User does not have permission to alter database 'trace05e' or the
> database does not exist.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed."
> when I do an sp_who I get
> spid ecid status loginame
> hostname
> blk dbname
> cmd request_id
> -- -- --
> ----
---
> ----
---
> --
> ----
---
> -- --
> 1 0 background sa
>
> 0 NULL
> RESOURCE MONITOR 0
> 2 0 suspended sa
>
> 0 NULL
> LOG WRITER 0
> 3 0 background sa
>
> 0 NULL
> LAZY WRITER 0
> 4 0 background sa
>
> 0 master
> UNKNOWN TOKEN 0
> 5 0 background sa
>
> 0 NULL
> LOCK MONITOR 0
> 6 0 sleeping sa
>
> 0 master
> TASK MANAGER 0
> 7 0 background sa
>
> 0 master
> TRACE QUEUE TASK 0
> 8 0 sleeping sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 9 0 background sa
>
> 0 master
> BRKR TASK 0
> 10 0 background sa
>
> 0 master
> TASK MANAGER 0
> 11 0 suspended sa
>
> 0 master
> CHECKPOINT 0
> 17 0 background sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 19 0 background sa
>
> 0 master
> BRKR EVENT HNDLR 0
> 20 0 background sa
>
> 0 master
> BRKR TASK 0
> 51 0 sleeping NT AUTHORITY\SYSTEM
> computer
> 0 ReportServer$sqlexpress
> AWAITING COMMAND 0
> 53 0 sleeping computer\User
> computer
> 0 master
> AWAITING
> COMMAND 0
> 54 0 runnable computer\User
> computer
> 0
> master
> SELECT
> 0
> 56 0 sleeping computer\User
> I can't find any commands to kill the last three processes.
> I need to know two things.
> 1) How can I find out why my database went into recovery mode?
> 2) How can I get my database back into normal mode?
> Thanks a million in advance
> JD
>|||SQL Server shouldn't let anyone in to the database if it is in recovery stat
e. Perhaps it is only a
refresh problem in EM/SSMS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<the_kiddie98@.yahoo.com> wrote in message
news:1141293565.559941.65460@.v46g2000cwv.googlegroups.com...
>I managed to kill some processes and now I have two left, the one I am
> using and one more, which is needed as a transport.
> i still cannot execute this statment
> RESTORE DATABASE trace05e
> WITH RECOVERY
> I get
> Msg 3101, Level 16, State 1, Line 3
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
>|||Hi Uri,
Sorry for my ignorance but what does that mean. Is there a way I
can rollback the logs to a certain point in time?
Is there any way outside of sql server that I can gain access to
the stored procedures inside in the mdf file?
Thanks
JD
Uri Dimant wrote:
> Hi
> I can only guess. When you start SQL Server it does perfom the opeartion (
in
> every databases on the server) 'recovering' by using CHECKPOINT command th
at
> means that all transactions are resided in the cache should be REDO/UNDO
> depends on its status.
>|||nope it isn't a refresh error as I have rebooted a few times and I am
still unable to get the database to work.
I have now resorted to removing the instance and re-installing it and
trying to get back an older database. I can't run any commands like
dbcc checkdb even on master, there seems to be invisible connections
all over the place.sql

Database Import/Export Question

I tried to export a database which includes tables and stored procedures from development environment to a production environment. For unknown reasons only the tables were copied whereas stored procedures were not copied and no error message shown what happened. Does anybody have a clue? I did the same export to an XP machine and it went through properly.How did you export the data? Did you use DTS?
Maybe you will find my previous post listing the steps I use to export objects to be helpful:http://forums.asp.net/1037418/showpost.aspx
|||Thanks. I got it. Don't know why the import/export said 100% complete but nothing showed up even I refreshed the stroed procedures in Enterprise Manager. And after a while (actually I went to pull someone to see the situation) the SP shown. Weird?|||

vljw8202 wrote:
Thanks. I got it. Don't know why theimport/export said 100% complete but nothing showed up even I refreshedthe stroed procedures in Enterprise Manager. And after a while(actually I went to pull someone to see the situation) the SPshown. Weird?

Enterprise Manager is often annoyingly unaware of schema changes. What I typically do is right-click on the server name and chooseRefresh, do the same for the Database node, the specific database, andthen the Tables node and any other node which should berefreshed. The new objects will then show up.
|||I see. Thanks for your advice.

Tuesday, March 27, 2012

Database Help

Hi,

I have a question about setting up a couple of database tables.

I'll try to explain using an example:

We have a customer (stored in a customer table)

CustomerCode: JDOE (Primary Key)

CustomerName: John Doe

Address: 123 Cherry Lane

City: Dubbo

State: NSW

PostalCode: 2830

Who was sent an invoice for an order he placed. That invoice was sent to his current address at the time (123 Cherry Lane).

Let’s say he moved to a new address. We would update his customer record accordingly.

CustomerCode: JDOE (Primary Key)

CustomerName: John Doe

Address: 987 Apple Road

City: Dubbo

State: NSW

PostalCode: 2830

Now, let’s say that we want to be able to look back at JDOE’s invoices and see where they were shipped too. This means we have to store that address somewhere.

Now if we said just store it on the invoice table like:

InvoiceNo: 0001 (Primary Key)

CustomerCode: JDOE (Foreign Key)

Address: 123 Cherry Lane

City: Dubbo

State: NSW

PostalCode: 2830

Amount: $300.00

That would break 3^rd normal form as the address doesn’t depend on the primary key (InvoiceNo [0001]).

We want to be able to see the address from the invoice table via a relationship/lookup/computational column/whatever.

How would we go about doing this in SQL Server version 8?

Cheers,

Pete

I think it is pretty common to have a set up like that. So you might have have a table(s) that lists default values for customers and an actual invoice table that includes actual ship address. You can divide it up for storage and performance reasons as necessary but most companies need to have that historical info available.

My two cents :-)

|||

Hi,

You can have an additional column say address2 for the current address. I know this sounds naive, but sometimes this is the best way to get things done. Now you may ask, what if I have more than 2 addresses, then boy ur going to have a tough time coming up with a schema, which stores historical information.

Good Luck and let me know, how u went about it.

|||

G'day,

Not be blunt, but the above suggested example is very poor design. One particular (and there are several) solutions to this is to first separate the address details from the customer details:

Customer Table

CustomerCode, CustomerName,

Address Table

CustomerCode, AddressID, Addresstype, line1, Line2, PostCode,State

This will then allow a customer to first of all have more then 1 address recorded (physical, postal, work, home etc) as well as almost conforming to 3NF (the State column would in fact need to become a "StateID" column lookup into a "State' table...).

Or, if you really didn't want to store multiple addresses, just keep your same design and create a trigger on the customer table to record any address details that change into a seperate table (example only):

CREATE TRIGGER CustomerAddress_upd

on CustomerTable AFTER UPDATE

BEGIN

IF ( UPDATE(Line1) OR UPDATE(PostCode) ) --list all required cols here

BEGIN

INSERT INTO AddressAudit (CustomerCode, Line1, PostCode, DateChanged)

SELECT CustomerCode, Line1, PostCode, getDate()

FROM CustomerTable c

JOIN Inserted i ON c.CustomerCode = i.CustomerCode

END

Then, if required, just query the audit table.

Cheers,

Rob

sql

Database Growth & Space Recovery Problem

I have done this experiment on one of the tables. There is table called
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
SP_SPACEUSED BUILD Results
name rows reserved data index_size unused
1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K
4. After Executing DBCC DBReindex (build,'',70)
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.
Regards,
Harcharan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
One thing is that you should be suspicious of the numbers , as you are..
SP_SPaceused reports information from Sysindexes, but the sysindexes info is
NOT updated with everyinsert update or delete...So the numbers may not
reflect the actual size..
For each sp_spaceused in your test , execute like this
exec sp_spaceused 'build', true
The second parameter tells sql to read through the table and update the
estimates, it runs longer but you will get tru results...If the numbers
still look funny, we'll try something else...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harcharan Jassal" <hjjassal@.yahoo.com> wrote in message
news:uCtYWSBhEHA.3540@.TK2MSFTNGP10.phx.gbl...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED 'BUILD' Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Database Growth & Space Recovery Problem

I have done this experiment on one of the tables. There is table called
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
SP_SPACEUSED BUILD Results
name rows reserved data index_size unused
1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K
4. After Executing DBCC DBReindex (build,'',70)
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.
Regards,
Harcharan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!One thing is that you should be suspicious of the numbers , as you are..
SP_SPaceused reports information from Sysindexes, but the sysindexes info is
NOT updated with everyinsert update or delete...So the numbers may not
reflect the actual size..
For each sp_spaceused in your test , execute like this
exec sp_spaceused 'build', true
The second parameter tells sql to read through the table and update the
estimates, it runs longer but you will get tru results...If the numbers
still look funny, we'll try something else...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harcharan Jassal" <hjjassal@.yahoo.com> wrote in message
news:uCtYWSBhEHA.3540@.TK2MSFTNGP10.phx.gbl...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED 'BUILD' Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!sql

database grows abnormally

there are 350 tables in database.in each hour 60 records appended to each
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
Message posted via http://www.sqlmonster.com
sepideh iranpour via SQLMonster.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
David Gugick
Imceda Software
www.imceda.com
sql

database grows abnormally

there are 350 tables in database.in each hour 60 records appended to each
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
--
Message posted via http://www.sqlmonster.comsepideh iranpour via SQLMonster.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
--
David Gugick
Imceda Software
www.imceda.com

database grows abnormally

there are 350 tables in database.in each hour 60 records appended to each
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
Message posted via http://www.droptable.comsepideh iranpour via droptable.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
David Gugick
Imceda Software
www.imceda.com

Sunday, March 25, 2012

Database fragmentation causing problems

I have really high fragmentation on the tables in my database even after running DBCC DBREINDEX and I was curious as to why this would happen. Could someone help me with this?

Hi Evidica. Could you provide some additional information on what type of fragmentation you are seeing and/or output from a sample dbcc showcontig on the given tables/indexes? Are you seeing extent scan fragmentation, logical scan fragmentation, what types of indexes do you have on the tables, etc.?

For example, if you are seeing extent scan fragmentation but have multiple data files in the given database, this is an expected result given the way pages/extents are allocated amoung files in a given database...

|||

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

http://www.microsoft.com/technet/abouttn/flash/tips/tips_083104.mspx

|||Sorry about leaving this dead. I did some research and found out that the person that created the database didnt know what they were doing. Turns out he had not put any primary keys on any of the tables.

Database fragmentation causing problems

I have really high fragmentation on the tables in my database even after running DBCC DBREINDEX and I was curious as to why this would happen. Could someone help me with this?

|||

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

http://www.microsoft.com/technet/abouttn/flash/tips/tips_083104.mspx

Monday, March 19, 2012

Database Encryption

Hello All,
Is there a built-in way that one can encrypt and decrypt data in SQL
Server tables. My client wants all data in the database encrypted so that
even the system admins doing backups cannot view the sensitive data.
Any suggestions or solutions will be greatly appreciated.
Thanks a lot,
Imran.What version of SQL Server, 2000 or 2005..?
--
HTH. Ryan
"Imran Aziz" <imran@.tb2.net> wrote in message
news:Omk%23c3gjGHA.1552@.TK2MSFTNGP03.phx.gbl...
> Hello All,
> Is there a built-in way that one can encrypt and decrypt data in SQL
> Server tables. My client wants all data in the database encrypted so that
> even the system admins doing backups cannot view the sensitive data.
> Any suggestions or solutions will be greatly appreciated.
> Thanks a lot,
> Imran.
>
>|||Hello Ryan thanks for your response. I will be using SQL Server 2005,
googling around I can see that SQL Server 2005 has this option , but I
cannot seem to see how to work with it in our install of SQL Server 2005.
In addition the current database is in SQL Server 2000 which I will
transfer to SQL Server 2005 and then would like to encrypt all existing data
in the database.
Of some info that I have found till now, it seems that one has to set the
encryption up for a database table on creation (although could not find it
in my SQL server 2005)
but will SQL Server 2005 encrypt existing data in tables ?
Thanks a lot,
Imran.
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eZmEVchjGHA.1600@.TK2MSFTNGP04.phx.gbl...
> What version of SQL Server, 2000 or 2005..?
> --
> HTH. Ryan
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:Omk%23c3gjGHA.1552@.TK2MSFTNGP03.phx.gbl...
>> Hello All,
>> Is there a built-in way that one can encrypt and decrypt data in SQL
>> Server tables. My client wants all data in the database encrypted so that
>> even the system admins doing backups cannot view the sensitive data.
>> Any suggestions or solutions will be greatly appreciated.
>> Thanks a lot,
>> Imran.
>>
>

Database Documentation

I would be interested in hearing what tools people have used to document the
business uses of tables in a database? I have looked at the meta data
services that comes with SQL Server, and while it looks like a good tool, I
do not think that it fits particularly well with what I am looking to
accomplish. I would like to have some documentation that outlines the
business uses of/business rules for each table in the database. Almost an
ERD, but something that discusses/shows information about the business model
that the database/system(s) were constructed to support.
Any advice is appreciated!
TimHello Tim,
This might not be what you are looking for but maybe it can be. I've started
a project which is intended to help people with their network documentation.
The output is to Microsoft word.
The project has two files, one for documenting Servers in general and one
for documenting SQL-Servers. I've just started with the SQL version so there
are a lot of things that can be added.
If you are interested in the project you can find information here:
http://sydi.sourceforge.net
If you want to add feature requests you can do so here:
http://sourceforge.net/tracker/?group_id=116471&atid=674897
Best regards
Patrick
"TimS" <timstspry@.msn.com(donotspam)> wrote in message
news:77AD57C8-C893-41D7-B200-38435DB2D11A@.microsoft.com...
> I would be interested in hearing what tools people have used to document
the
> business uses of tables in a database? I have looked at the meta data
> services that comes with SQL Server, and while it looks like a good tool,
I
> do not think that it fits particularly well with what I am looking to
> accomplish. I would like to have some documentation that outlines the
> business uses of/business rules for each table in the database. Almost an
> ERD, but something that discusses/shows information about the business
model
> that the database/system(s) were constructed to support.
> Any advice is appreciated!
> Tim|||Hi Tim,
Here is what we have done. In our database we have two "extra tables" One
that describes the tables and one that describes individual fields
(columns). The fields table has a foreign key back to the tables table so
that fields can be associated with their proper table. In those tables we
have columns that are used to describe the individual tables and fields.
Things like business rules, uses etc. We then have a set of web pages that
connect to an empty version of the database that has only this meta data
information in it. Everyone can use them to view and update this
information. Each time a new version of the DB comes out we have an
automated process that creates a new empty DB, copies the two tables of meta
data from the existing DB and then updates the metadata tables with the
updates to the schema (updated tables & colums). The existing DB then gets
replaced with the updated DB and the web pages now use this.
Wayne
"TimS" <timstspry@.msn.com(donotspam)> wrote in message
news:77AD57C8-C893-41D7-B200-38435DB2D11A@.microsoft.com...
>I would be interested in hearing what tools people have used to document
>the
> business uses of tables in a database? I have looked at the meta data
> services that comes with SQL Server, and while it looks like a good tool,
> I
> do not think that it fits particularly well with what I am looking to
> accomplish. I would like to have some documentation that outlines the
> business uses of/business rules for each table in the database. Almost an
> ERD, but something that discusses/shows information about the business
> model
> that the database/system(s) were constructed to support.
> Any advice is appreciated!
> Tim|||SchemaToDoc (http://www.schematodoc.com) lets you annotate the tables and
fields in your database. It then lets you export the structure of your
database (tables, fields, indexes, check constraints, foreign key
constraints, and triggers) as well as your annotations to a Word document.
"Patrick Ogenstad" <patrick.ogenstad@.netsafe.se> wrote in message
news:uoeqjdgoEHA.1272@.TK2MSFTNGP09.phx.gbl...
> Hello Tim,
>
> This might not be what you are looking for but maybe it can be. I've
started
> a project which is intended to help people with their network
documentation.
> The output is to Microsoft word.
>
> The project has two files, one for documenting Servers in general and one
> for documenting SQL-Servers. I've just started with the SQL version so
there
> are a lot of things that can be added.
>
> If you are interested in the project you can find information here:
> http://sydi.sourceforge.net
>
> If you want to add feature requests you can do so here:
> http://sourceforge.net/tracker/?group_id=116471&atid=674897
>
> Best regards
> Patrick
>
> "TimS" <timstspry@.msn.com(donotspam)> wrote in message
> news:77AD57C8-C893-41D7-B200-38435DB2D11A@.microsoft.com...
> > I would be interested in hearing what tools people have used to document
> the
> > business uses of tables in a database? I have looked at the meta data
> > services that comes with SQL Server, and while it looks like a good
tool,
> I
> > do not think that it fits particularly well with what I am looking to
> > accomplish. I would like to have some documentation that outlines the
> > business uses of/business rules for each table in the database. Almost
an
> > ERD, but something that discusses/shows information about the business
> model
> > that the database/system(s) were constructed to support.
> >
> > Any advice is appreciated!
> >
> > Tim
>

Database Documentation

I would be interested in hearing what tools people have used to document the
business uses of tables in a database? I have looked at the meta data
services that comes with SQL Server, and while it looks like a good tool, I
do not think that it fits particularly well with what I am looking to
accomplish. I would like to have some documentation that outlines the
business uses of/business rules for each table in the database. Almost an
ERD, but something that discusses/shows information about the business model
that the database/system(s) were constructed to support.
Any advice is appreciated!
Tim
Hello Tim,
This might not be what you are looking for but maybe it can be. I've started
a project which is intended to help people with their network documentation.
The output is to Microsoft word.
The project has two files, one for documenting Servers in general and one
for documenting SQL-Servers. I've just started with the SQL version so there
are a lot of things that can be added.
If you are interested in the project you can find information here:
http://sydi.sourceforge.net
If you want to add feature requests you can do so here:
http://sourceforge.net/tracker/?grou...71&atid=674897
Best regards
Patrick
"TimS" <timstspry@.msn.com(donotspam)> wrote in message
news:77AD57C8-C893-41D7-B200-38435DB2D11A@.microsoft.com...
> I would be interested in hearing what tools people have used to document
the
> business uses of tables in a database? I have looked at the meta data
> services that comes with SQL Server, and while it looks like a good tool,
I
> do not think that it fits particularly well with what I am looking to
> accomplish. I would like to have some documentation that outlines the
> business uses of/business rules for each table in the database. Almost an
> ERD, but something that discusses/shows information about the business
model
> that the database/system(s) were constructed to support.
> Any advice is appreciated!
> Tim
|||Hi Tim,
Here is what we have done. In our database we have two "extra tables" One
that describes the tables and one that describes individual fields
(columns). The fields table has a foreign key back to the tables table so
that fields can be associated with their proper table. In those tables we
have columns that are used to describe the individual tables and fields.
Things like business rules, uses etc. We then have a set of web pages that
connect to an empty version of the database that has only this meta data
information in it. Everyone can use them to view and update this
information. Each time a new version of the DB comes out we have an
automated process that creates a new empty DB, copies the two tables of meta
data from the existing DB and then updates the metadata tables with the
updates to the schema (updated tables & colums). The existing DB then gets
replaced with the updated DB and the web pages now use this.
Wayne
"TimS" <timstspry@.msn.com(donotspam)> wrote in message
news:77AD57C8-C893-41D7-B200-38435DB2D11A@.microsoft.com...
>I would be interested in hearing what tools people have used to document
>the
> business uses of tables in a database? I have looked at the meta data
> services that comes with SQL Server, and while it looks like a good tool,
> I
> do not think that it fits particularly well with what I am looking to
> accomplish. I would like to have some documentation that outlines the
> business uses of/business rules for each table in the database. Almost an
> ERD, but something that discusses/shows information about the business
> model
> that the database/system(s) were constructed to support.
> Any advice is appreciated!
> Tim
|||SchemaToDoc (http://www.schematodoc.com) lets you annotate the tables and
fields in your database. It then lets you export the structure of your
database (tables, fields, indexes, check constraints, foreign key
constraints, and triggers) as well as your annotations to a Word document.
"Patrick Ogenstad" <patrick.ogenstad@.netsafe.se> wrote in message
news:uoeqjdgoEHA.1272@.TK2MSFTNGP09.phx.gbl...
> Hello Tim,
>
> This might not be what you are looking for but maybe it can be. I've
started
> a project which is intended to help people with their network
documentation.
> The output is to Microsoft word.
>
> The project has two files, one for documenting Servers in general and one
> for documenting SQL-Servers. I've just started with the SQL version so
there[vbcol=seagreen]
> are a lot of things that can be added.
>
> If you are interested in the project you can find information here:
> http://sydi.sourceforge.net
>
> If you want to add feature requests you can do so here:
> http://sourceforge.net/tracker/?grou...71&atid=674897
>
> Best regards
> Patrick
>
> "TimS" <timstspry@.msn.com(donotspam)> wrote in message
> news:77AD57C8-C893-41D7-B200-38435DB2D11A@.microsoft.com...
> the
tool,[vbcol=seagreen]
> I
an
> model
>

Sunday, March 11, 2012

Database Diagrams: FOREIGN KEY constraint "fell off"; can't drop/recreate it...

Hello,

I have two tables Person & Location where Location has a primary key LocationId and Person has a foreign key LocationId.

Sometime ago I used the Database Diagrams visual tool of SQL Server Management Studio Express to create the foreign key relationship between the two tables - i.e. "visually" (drawing a line between the PK & FK LocationId elements of both tables).

Time has passed and I recently noticed that, upon retrieving my saved diagram, the foreign key relationship had "fallen off" (i.e. the many-to-one line was no longer showing in the diagram).

After recreating the relationship (redrawing the line) I find that I get an error message when I try to save the diagram:

Post-Save Notifications
[!] Errors were encountered during the save process. Some database objects were not saved.

'Location' table saved successfully
'Person' table
- Unable to create relationship 'FK_Person_Location'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person_Location". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

When I go back to the object explorer and view the dependencies for the two tables, there is no dependency (between these two tables) revealed. When I try to create the foreign key constraint manually (T-SQL) it again says can't add the constraint. It comes up with an error as follows:

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Person__LocationId__793DFFAF". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

(Note: Each time I do this, the 8 hexadecimal character suffix changes.)

When I try to drop the foreign key:

alter table Person
drop constraint FK__Person__LocationId

it comes back with the error:

Msg 3728, Level 16, State 1, Line 2
'FK__Person__LocationId' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.

So it seems that there's some kind of goof up here. Can anybody shed light on this / tell me how to fix it?

Best guess, you have data that will not pass the Foreign key constraint. As for why it "dropped off" that is a totally different question that can;t be answered here (do your developers have dbo access and possibly dropped the constraint so they could insert invalid data to make the UI work? (no offense to any developer who wouldn't do that :) )

create table person
(
personId int constraint PKperson primary key,
locationId int
)
insert into person
values (1,1)
insert into person
values (2,2)

create table location
(
locationId int constraint PKlocation primary key
)

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__person__location__4B0D20AB". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

Better to name the constraint:
ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKPerson_References_Location". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

insert into location
values (1)
insert into location
values (2)

ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

to see the constraints and the columns you can use this query:

select *
from information_schema.constraint_column_usage as ccu
join information_schema.referential_constraints as rc
on rc.constraint_schema = ccu.table_schema
and ccu.constraint_name = rc.constraint_name
where ccu.table_name = 'person'

Also, if you want to ignore the invalid data there is a NOCHECK clause when creating constraints, but it is highly advised to not use it.

|||

Thanks Louis for the useful info. It helped in examining the problem more closely.

In the end I decided just to save off my data from the Location table, and to drop / recreate it. Everything is working fine again. I really believe that an inconsistency got in the system tables somewhere - that was my main concern. By dropping / recreating the problem table I believe I eliminated the inconsistency.

|||I get the same error. I wish I could find the source of it in the system tables (or wherever the cause is located) and fix it - I do not have the luxury of merely trying to drop and recreata the tables.|||

Hi all,

I have the same error. But only on my table that I migrate from SQL 2000 to SQL 2005.

And I can't drop and recreate table.

How can I do ?

|||

The ALTER TABLE that you were using does not have a constraint name specified so SQL Server will generate a name automatically and since constraint names have to be unique SQL Server uses combination of table / column names / unique number to make it unique. But it looks like you never had the FK constraint defined so the tables would have contained invalid data. And when you tried to create the constraint from the diagram designer it failed and the constraint creation gets rolled back. You could have checked the data in the table like below to find the offending rows:

select *

from Person as p

where not exists(

select * from Location as l

where l.LocationId = p.LocationId

)

But you shouldn't use diagram designer as the modelling tool. It is not designed for that. It does lot of things behind the scenes which is unnecessary in some cases (like dropping & recreating tables to make certain schema changes). You could make simple schema changes but script out the schema definitions and store it in files.