Integrity check failing
-
On my user databases I'm using the database maintenance plan to run integrity checks. For these I have select the attempt to repair any minor problem. That said I'm getting the following errors on several of my databases.
Downtime on my databases is not desirable. What exactly is checkdb trying to alter. Any suggestions
[5] Database OPCLoad: Check Data and Index Linkage...
NULL
NULL
** Execution Time: 0 hrs, 0 mins, 1 secs **
NULL
NULL
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'Object'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
NULL
John Zacharkan
John Zacharkan -
Don't choose repair minor problem, that needs database in single user only. If you really needs to repair any errors run dbcc repair database.
Robert
-
How can that be an option in the database maint plan then?
quote:
Don't choose repair minor problem, that needs database in single user only. If you really needs to repair any errors run dbcc repair database.Robert
John Zacharkan
John Zacharkan -
Do you have to use maintenance plan to fix database minor problem? You can run dbcc checkdb with repair options and schedule a job to run it.
-
quote:
Do you have to use maintenance plan to fix database minor problem? You can run dbcc checkdb with repair options and schedule a job to run it.No, but tht doesn't answer the question either.
This problem has never come in SQL 7, and I don't think it was luck. My actual problem, putting the database in single user mode to fix some renegade indexes is not an option.
Thank you for the syntax, I actually know how to do this manually, I just prefer to use the maintenance plan. See Andy Warren's article http://www.sqlservercentral.com/columnists/awarren/sqlmaintenanceplans.asp
I happen to be on the one half side that agrees with him.
John Zacharkan
John Zacharkan -
Did you say you can do it in SQL Server 7.0? In order to fix the database problem with 'repair' options either by dbcc checkdb or maintenance plan, the database has to be in single user mode. I believe the error message you received was caused by maintenance plan which tried to set the database to single user mode.
As you already konw, run dbcc checkdb without repir options to find out the database problems. If it indicates the problems that can be fixed by rebuild indexes, then you may not have to set database to single user mode. For the rest of errors such as allocation errors, structural row or page errors etc, I don't see how that can be done without setting database in single user mode.
-
quote:
Did you say you can do it in SQL Server 7.0?On SQL 7.0 I run the integrity checks across 37 databases that support our website every day with the attempt to repair any minor problem flag checked. Setting these databases to single user mode would be impossible.
John Zacharkan
John Zacharkan -
You are right on SQL Server 7.0 but I did get same error message as you got in 2000. It seems Microsoft made changes in 2000.
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'TSTSQL01\TST' as '' (trusted)
Starting maintenance plan 'Test' on 4/23/03 3:36:41 PM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'Northwind'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database Northwind: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
End of maintenance plan 'Test' on 4/23/03 3:36:41 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Edited by - Allen_Cui on 04/23/2003 1:37:07 PM
-
Knock 'em off!! Then do an alter database dbname set single_user with termination!
/* This procedure will kill all current system process id's (spids)
Within the database named.
To execute this procedure type:
EXEC dbo.dba_kill_all_spid ''dbname''
*/
CREATE PROCEDURE dbo.dba_kill_all_spid
(
@p_dbname VARCHAR(32)
)
AS
BEGIN
DECLARE @m_dbid INT
DECLARE @m_spid INT
DECLARE @m_sql NVARCHAR(255)
SELECT @m_dbid = dbid
FROM master..sysdatabases
WHERE name = @p_dbname
DECLARE curKillSet INSENSITIVE CURSOR
FOR SELECT spid
FROM master..sysprocesses
WHERE dbid = @m_dbid
OPEN curKillSet
FETCH NEXT
FROM curKillSet
INTO @m_spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @m_sql ="KILL " + CAST(@m_spid AS VARCHAR)
SELECT @m_sql
EXEC sp_executesql @m_sql
FETCH NEXT
FROM curKillSet
INTO @m_spid
END
CLOSE curKillSet
DEALLOCATE curKillSet
END
GO
-
gfahrlander your mean!!! You kill without warning. I'm sure you'd make my internet community real happy. John Zacharkan
John Zacharkan -
I'm not suggesting it, but if you did want to go down this route, you could more easily run "Alter database single_user with rollback immediate".
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
gfahrlander your mean!!! You kill without warning. I'm sure you'd make my internet community real happy. 
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply