|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DBCC CHECKIDENT call never returnsI am calling DBCC CHECKIDENT from a VB App through ADO. The call is not
returning, even after a day. If I do the same call through Query Analyser it takes about a minute to run. Can anyone think of anything that might affect the use of DBCC CHECKIDENT. I cant check for deadlocks, because the Current Activity tab in Enterprise Manager is locked up, presumably because of a deadlock of some kind... Thanks in advance, Fred Forsyth. Development Manager The reason EM is "locked up" is certainly not due to a deadlock. SQL Server
automatically resolves deadlocks in a few seconds. It might be due to blocking though. What permissions does the vb app have? To run DBCC CHECKIDENT you must be sa or dbo. -- Show quoteAndrew J. Kelly SQL MVP "Fred Forsyth" <fred.forsyth.nospam@nospam.rcp.co.uk> wrote in message news:%23nkjGb6BGHA.4076@TK2MSFTNGP14.phx.gbl... >I am calling DBCC CHECKIDENT from a VB App through ADO. The call is not >returning, even after a day. If I do the same call through Query Analyser >it takes about a minute to run. > > Can anyone think of anything that might affect the use of DBCC CHECKIDENT. > I cant check for deadlocks, because the Current Activity tab in Enterprise > Manager is locked up, presumably because of a deadlock of some kind... > > Thanks in advance, > Fred Forsyth. > Development Manager > Fred Forsyth (fred.forsyth.nospam@nospam.rcp.co.uk) writes:
> I am calling DBCC CHECKIDENT from a VB App through ADO. The call is not Current Activity gets block if some process has create tables within a> returning, even after a day. If I do the same call through Query > Analyser it takes about a minute to run. > > Can anyone think of anything that might affect the use of DBCC > CHECKIDENT. I cant check for deadlocks, because the Current Activity tab > in Enterprise Manager is locked up, presumably because of a deadlock of > some kind... transaction that still is open. Use sp_who or sp_who2 to check for blocking. Check the Blk or BlkBy column. If this column has a non-zero value, the spid on this row, is blocked by spid in the Blk column. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Some further investigation has shown that the VB process is blocked
waiting on a checkpoint to complete, and that there is a system process that is attempting to checkpoint the database but is blocked by the VB process. (I can this through select * from sysprocesses). I suspect that the reason for the need to checkpoint is that the I removed the log files from the databases, and then run the dbcc command on them, and that this is what is causing the problems. I think my test case is at fault, because in the real world the original log files would be there (they are very big, hence the reason I removed them). The log files are pretty important and it is never a good idea to remove
them. -- Show quoteAndrew J. Kelly SQL MVP <fredfors***@gmail.com> wrote in message news:1135870204.499678.310830@f14g2000cwb.googlegroups.com... > Some further investigation has shown that the VB process is blocked > waiting on a checkpoint to complete, and that there is a system process > that is attempting to checkpoint the database but is blocked by the VB > process. (I can this through select * from sysprocesses). > > I suspect that the reason for the need to checkpoint is that the I > removed the log files from the databases, and then run the dbcc command > on them, and that this is what is causing the problems. I think my test > case is at fault, because in the real world the original log files > would be there (they are very big, hence the reason I removed them). > (fredfors***@gmail.com) writes:
> Some further investigation has shown that the VB process is blocked You did what???? Never, never do that again! If you remove the log file, > waiting on a checkpoint to complete, and that there is a system process > that is attempting to checkpoint the database but is blocked by the VB > process. (I can this through select * from sysprocesses). > > I suspect that the reason for the need to checkpoint is that the I > removed the log files from the databases, you are gambling with your database. There is a fair chance that you will have to remove the data file as well, because it will not be accessibe. > I think my test case is at fault, because in the real world the original You can shrink a log file with DBCC SHRINKFILE, but don't do this with a> log files would be there (they are very big, hence the reason I removed > them). production database, unless the log file has grown because of some one-off operation. For a production database, you should also backup the transaction log regulary. If you don't care about up-to-the-point recovery, you can set the database in simple recovery mode. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics