|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Server 2000 production problem: can't drop/index tableI have a problem with a SQL Server 2000 Standard database. One specific table in the database demonstrates the following behaviour: 1) it cannot be dropped (the operation 'hangs' - nothing happens) 2) I cannot add an index (again, the operation fails to complete successfully or return an error) 3) The snapshot agent on this database (it's used for merge replication) times out on the table in question. Despite all of that, the table can be read via Enterprise Manager and Query Analyser without issue. I haven't tried amending the data as I don't want to risk making the problem any worse than it already is. Finally, DBCC CHECKDB and DBCC CHECKTABLE both complete successfully and indicate no errors. I'm reluctant to try restoring from a backup, because the problem has quite possibly existed for some time (data in this table is changed infrequently) and I'm worried about the consequences of restoring a database that has a replication publication. If all else fails I'll have to give it a shot. Any thoughts regarding the possible causes of the problem, or how I might go about fixing it, would be greatly appreciated. Thanks Alex Hi
Do you try to add an index via EM? <WombatDe***@gmail.com> wrote in message Show quote news:8cd62a4e-e996-4716-b3d6-57fdbe646191@g21g2000hsh.googlegroups.com... > Hi, > > I have a problem with a SQL Server 2000 Standard database. One > specific table in the database demonstrates the following behaviour: > > 1) it cannot be dropped (the operation 'hangs' - nothing happens) > > 2) I cannot add an index (again, the operation fails to complete > successfully or return an error) > > 3) The snapshot agent on this database (it's used for merge > replication) times out on the table in question. > > Despite all of that, the table can be read via Enterprise Manager and > Query Analyser without issue. I haven't tried amending the data as I > don't want to risk making the problem any worse than it already is. > > Finally, DBCC CHECKDB and DBCC CHECKTABLE both complete successfully > and indicate no errors. > > I'm reluctant to try restoring from a backup, because the problem has > quite possibly existed for some time (data in this table is changed > infrequently) and I'm worried about the consequences of restoring a > database that has a replication publication. If all else fails I'll > have to give it a shot. > > Any thoughts regarding the possible causes of the problem, or how I > might go about fixing it, would be greatly appreciated. > > Thanks > Alex Hi,
Thank you for your response - yes, the attempt to add an index was via EM. Cheers Alex Show quote On Nov 27, 6:51 am, "Uri Dimant" <u***@iscar.co.il> wrote: > Hi > Do you try to add an index via EM? > > <WombatDe***@gmail.com> wrote in message > > news:8cd62a4e-e996-4716-b3d6-57fdbe646191@g21g2000hsh.googlegroups.com... > > > Hi, > > > I have a problem with a SQL Server 2000 Standard database. One > > specific table in the database demonstrates the following behaviour: > > > 1) it cannot be dropped (the operation 'hangs' - nothing happens) > > > 2) I cannot add an index (again, the operation fails to complete > > successfully or return an error) > > > 3) The snapshot agent on this database (it's used for merge > > replication) times out on the table in question. > > > Despite all of that, the table can be read via Enterprise Manager and > > Query Analyser without issue. I haven't tried amending the data as I > > don't want to risk making the problem any worse than it already is. > > > Finally, DBCC CHECKDB and DBCC CHECKTABLE both complete successfully > > and indicate no errors. > > > I'm reluctant to try restoring from a backup, because the problem has > > quite possibly existed for some time (data in this table is changed > > infrequently) and I'm worried about the consequences of restoring a > > database that has a replication publication. If all else fails I'll > > have to give it a shot. > > > Any thoughts regarding the possible causes of the problem, or how I > > might go about fixing it, would be greatly appreciated. > > > Thanks > > Alex Ok, do not do that as EM does behind the scenes DROP table and CREATE table
( you can observe it by yourself by running SQL Server Profilier) . Use Query Analyzer to add/alter columns as well as create indexes ALTER TABLE tbl ADD newcol INT <WombatDe***@gmail.com> wrote in message Show quote news:34f674e0-bed0-4288-9cff-99fd5095cb8a@d27g2000prf.googlegroups.com... > Hi, > > Thank you for your response - yes, the attempt to add an index was via > EM. > > Cheers > Alex > > On Nov 27, 6:51 am, "Uri Dimant" <u***@iscar.co.il> wrote: >> Hi >> Do you try to add an index via EM? >> >> <WombatDe***@gmail.com> wrote in message >> >> news:8cd62a4e-e996-4716-b3d6-57fdbe646191@g21g2000hsh.googlegroups.com... >> >> > Hi, >> >> > I have a problem with a SQL Server 2000 Standard database. One >> > specific table in the database demonstrates the following behaviour: >> >> > 1) it cannot be dropped (the operation 'hangs' - nothing happens) >> >> > 2) I cannot add an index (again, the operation fails to complete >> > successfully or return an error) >> >> > 3) The snapshot agent on this database (it's used for merge >> > replication) times out on the table in question. >> >> > Despite all of that, the table can be read via Enterprise Manager and >> > Query Analyser without issue. I haven't tried amending the data as I >> > don't want to risk making the problem any worse than it already is. >> >> > Finally, DBCC CHECKDB and DBCC CHECKTABLE both complete successfully >> > and indicate no errors. >> >> > I'm reluctant to try restoring from a backup, because the problem has >> > quite possibly existed for some time (data in this table is changed >> > infrequently) and I'm worried about the consequences of restoring a >> > database that has a replication publication. If all else fails I'll >> > have to give it a shot. >> >> > Any thoughts regarding the possible causes of the problem, or how I >> > might go about fixing it, would be greatly appreciated. >> >> > Thanks >> > Alex > Arghh.
This was actually not a database problem at all - it transpires that a developer had written a stored procedure that took ages to perform a batch of updates, and scheduled it to run so frequently that it was permanently executing. The table in question was therefore permanently locked and the behaviour I described earlier was presumably due to that. Anyway, all is now resolved - many thanks for your help! Cheers Alex On Nov 26, 9:55 pm, WombatDe***@gmail.com wrote: Show quote > Hi, > > I have a problem with a SQL Server 2000 Standard database. One > specific table in the database demonstrates the following behaviour: > > 1) it cannot be dropped (the operation 'hangs' - nothing happens) > > 2) I cannot add an index (again, the operation fails to complete > successfully or return an error) > > 3) The snapshot agent on this database (it's used for merge > replication) times out on the table in question. > > Despite all of that, the table can be read via Enterprise Manager and > Query Analyser without issue. I haven't tried amending the data as I > don't want to risk making the problem any worse than it already is. > > Finally, DBCC CHECKDB and DBCC CHECKTABLE both complete successfully > and indicate no errors. > > I'm reluctant to try restoring from a backup, because the problem has > quite possibly existed for some time (data in this table is changed > infrequently) and I'm worried about the consequences of restoring a > database that has a replication publication. If all else fails I'll > have to give it a shot. > > Any thoughts regarding the possible causes of the problem, or how I > might go about fixing it, would be greatly appreciated. > > Thanks > Alex  |
|||||||||||||||||||||||
Other interesting topics