When I try to run an integrity check on my project Server
2002 database, I get the following error:
[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 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: 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.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
..
You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [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 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: 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.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>
Showing posts with label sql-dmo. Show all posts
Showing posts with label sql-dmo. Show all posts
Wednesday, March 7, 2012
integrity check error and single user mode on Project Server database
When I try to run an integrity check on my project Server
2002 database, I get the following error:
[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 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: 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.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
.You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
--
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [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 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: 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.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>|||Thanks! I changed my integrity check to not attempt to repair any minor problems, and it doesn't require single user mode to do the check. The integrity check came back fine, so I assume it didn't need to do any repairing anyway.
Regards,
Mike Walraven
"Andrew J. Kelly" wrote:
> You can use sp_who2 to see who is in a particular database but you should
> not be running this in Single User mode anyway. Single User is only
> required to fix issues not to do the initial checks.
> --
> Andrew J. Kelly SQL MVP
>
> "Mike Walraven" <mwalraven@.syncroness.com> wrote in message
> news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> > When I try to run an integrity check on my project Server
> > 2002 database, I get the following error:
> >
> > [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 'ProjectServer'
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> > DATABASE statement failed.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > sp_dboption command failed.
> > [31] Database ProjectServer: 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.
> >
> > This same error comes up if I try to manually change the
> > database to single user mode.
> >
> > Is there any way to see which users are using the
> > database, close their sessions and then do the integrity
> > check.
> >
> > This is the only database on my SQL server that I have
> > any issues with.
> >
> > Any help would be appreciated.
> >
> > Regards,
> > Mike Walraven
> > .
> >
> >
>
>|||That is usually the case. If there is an error you can then decide the best
approach to handle it instead of letting the wizard make those decisions.
--
Andrew J. Kelly SQL MVP
"Mike Walraven" <Mike Walraven@.discussions.microsoft.com> wrote in message
news:23F9018D-52F4-4BA4-8A23-05AD4A83D080@.microsoft.com...
> Thanks! I changed my integrity check to not attempt to repair any minor
problems, and it doesn't require single user mode to do the check. The
integrity check came back fine, so I assume it didn't need to do any
repairing anyway.
> Regards,
> Mike Walraven
> "Andrew J. Kelly" wrote:
> > You can use sp_who2 to see who is in a particular database but you
should
> > not be running this in Single User mode anyway. Single User is only
> > required to fix issues not to do the initial checks.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Mike Walraven" <mwalraven@.syncroness.com> wrote in message
> > news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> > > When I try to run an integrity check on my project Server
> > > 2002 database, I get the following error:
> > >
> > > [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 'ProjectServer'
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> > > DATABASE statement failed.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > > sp_dboption command failed.
> > > [31] Database ProjectServer: 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.
> > >
> > > This same error comes up if I try to manually change the
> > > database to single user mode.
> > >
> > > Is there any way to see which users are using the
> > > database, close their sessions and then do the integrity
> > > check.
> > >
> > > This is the only database on my SQL server that I have
> > > any issues with.
> > >
> > > Any help would be appreciated.
> > >
> > > Regards,
> > > Mike Walraven
> > > .
> > >
> > >
> >
> >
> >
2002 database, I get the following error:
[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 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: 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.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
.You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
--
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [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 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: 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.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>|||Thanks! I changed my integrity check to not attempt to repair any minor problems, and it doesn't require single user mode to do the check. The integrity check came back fine, so I assume it didn't need to do any repairing anyway.
Regards,
Mike Walraven
"Andrew J. Kelly" wrote:
> You can use sp_who2 to see who is in a particular database but you should
> not be running this in Single User mode anyway. Single User is only
> required to fix issues not to do the initial checks.
> --
> Andrew J. Kelly SQL MVP
>
> "Mike Walraven" <mwalraven@.syncroness.com> wrote in message
> news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> > When I try to run an integrity check on my project Server
> > 2002 database, I get the following error:
> >
> > [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 'ProjectServer'
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> > DATABASE statement failed.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > sp_dboption command failed.
> > [31] Database ProjectServer: 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.
> >
> > This same error comes up if I try to manually change the
> > database to single user mode.
> >
> > Is there any way to see which users are using the
> > database, close their sessions and then do the integrity
> > check.
> >
> > This is the only database on my SQL server that I have
> > any issues with.
> >
> > Any help would be appreciated.
> >
> > Regards,
> > Mike Walraven
> > .
> >
> >
>
>|||That is usually the case. If there is an error you can then decide the best
approach to handle it instead of letting the wizard make those decisions.
--
Andrew J. Kelly SQL MVP
"Mike Walraven" <Mike Walraven@.discussions.microsoft.com> wrote in message
news:23F9018D-52F4-4BA4-8A23-05AD4A83D080@.microsoft.com...
> Thanks! I changed my integrity check to not attempt to repair any minor
problems, and it doesn't require single user mode to do the check. The
integrity check came back fine, so I assume it didn't need to do any
repairing anyway.
> Regards,
> Mike Walraven
> "Andrew J. Kelly" wrote:
> > You can use sp_who2 to see who is in a particular database but you
should
> > not be running this in Single User mode anyway. Single User is only
> > required to fix issues not to do the initial checks.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Mike Walraven" <mwalraven@.syncroness.com> wrote in message
> > news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> > > When I try to run an integrity check on my project Server
> > > 2002 database, I get the following error:
> > >
> > > [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 'ProjectServer'
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> > > DATABASE statement failed.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > > sp_dboption command failed.
> > > [31] Database ProjectServer: 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.
> > >
> > > This same error comes up if I try to manually change the
> > > database to single user mode.
> > >
> > > Is there any way to see which users are using the
> > > database, close their sessions and then do the integrity
> > > check.
> > >
> > > This is the only database on my SQL server that I have
> > > any issues with.
> > >
> > > Any help would be appreciated.
> > >
> > > Regards,
> > > Mike Walraven
> > > .
> > >
> > >
> >
> >
> >
integrity check error and single user mode on Project Server database
When I try to run an integrity check on my project Server
2002 database, I get the following error:
[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 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: 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.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
.You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx
.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [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 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: 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.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>
2002 database, I get the following error:
[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 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: 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.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
.You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx
.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [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 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: 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.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>
Integrity check and optimization job failed
I found the following error in my maintenance plan please help me on this
Under maintenace plan history i found this error:-
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
and jobs failed with following error
i found this error in job history of optimization & integrity check jobs:-
Executed as user: XXXX\admin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Please help me find out the solution
Thanks in advance
Regards,
Sumitdo not use maintenance plans. setup your own jobs for this stuff. easier to troubleshoot and you have more granular control.|||This error isn't caused by the maintenance plan, but rather occurs within the job the maintenance plan creates. The error is generated by UPDATE STATISTICS, and in my experience is related to attempts to update the stats on a table that is used to record or maintain table statistics (such as a table within the msdb database). Unfortunately, I have not been able to reliably reproduce the problem in order to debug it. When I run UPDATE STATS on the offending table a second time, it always completes succesfully. So far, it has not bothered my enough to make me want to delve into it any deeper.
Under maintenace plan history i found this error:-
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
and jobs failed with following error
i found this error in job history of optimization & integrity check jobs:-
Executed as user: XXXX\admin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Please help me find out the solution
Thanks in advance
Regards,
Sumitdo not use maintenance plans. setup your own jobs for this stuff. easier to troubleshoot and you have more granular control.|||This error isn't caused by the maintenance plan, but rather occurs within the job the maintenance plan creates. The error is generated by UPDATE STATISTICS, and in my experience is related to attempts to update the stats on a table that is used to record or maintain table statistics (such as a table within the msdb database). Unfortunately, I have not been able to reliably reproduce the problem in order to debug it. When I run UPDATE STATS on the offending table a second time, it always completes succesfully. So far, it has not bothered my enough to make me want to delve into it any deeper.
Subscribe to:
Posts (Atom)