OpenXML Identity Insert: integrity

Showing posts with label integrity. Show all posts

Wednesday, March 21, 2012

Optimizations & Integrity Check Jobs failing?

Thanks in advance for any info.
What would cause Optimization & Integrity Check jobs to fail? The agent info
shows SQL State = 42000, which, according to BOL is "Syntax error,
permission violation, or other nonspecific error. " Hmm... where to go from
here?
Thanks,
MorganI think I have found out what it is...
After further investigation, the server is running SQL Server personal
edition and was exceeding the 5 user limit, which I am guessing is causing
the jobs to not run and reporting back a generic error message.
"Morgan" <abcd@.senditon.com> wrote in message
news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Thanks in advance for any info.
> What would cause Optimization & Integrity Check jobs to fail? The agent
info
> shows SQL State = 42000, which, according to BOL is "Syntax error,
> permission violation, or other nonspecific error. " Hmm... where to go
from
> here?
>
> Thanks,
> Morgan
>
>|||The "5 user limit" shouldn't cause this. That is only a throttling mechanism
(very misunderstood, though). Are you using the maint wizard? If so, specify
a report file in the maint plan and check for the real error message in that
report file.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Morgan" <abcd@.senditon.com> wrote in message
news:OPPPEXM7DHA.2300@.TK2MSFTNGP10.phx.gbl...
> I think I have found out what it is...
> After further investigation, the server is running SQL Server personal
> edition and was exceeding the 5 user limit, which I am guessing is causing
> the jobs to not run and reporting back a generic error message.
> "Morgan" <abcd@.senditon.com> wrote in message
> news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > Thanks in advance for any info.
> >
> > What would cause Optimization & Integrity Check jobs to fail? The agent
> info
> > shows SQL State = 42000, which, according to BOL is "Syntax error,
> > permission violation, or other nonspecific error. " Hmm... where to go
> from
> > here?
> >
> >
> > Thanks,
> >
> > Morgan
> >
> >
> >
>|||Thanks, Tibor. Unfortunately we had to burn the SQL Server install and
install EE on the box. If it starts happening again, we'll hit the logs for
more detail.
Thanks again.
Morgan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfAd4CO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> The "5 user limit" shouldn't cause this. That is only a throttling
mechanism
> (very misunderstood, though). Are you using the maint wizard? If so,
specify
> a report file in the maint plan and check for the real error message in
that
> report file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Morgan" <abcd@.senditon.com> wrote in message
> news:OPPPEXM7DHA.2300@.TK2MSFTNGP10.phx.gbl...
> > I think I have found out what it is...
> > After further investigation, the server is running SQL Server personal
> > edition and was exceeding the 5 user limit, which I am guessing is
causing
> > the jobs to not run and reporting back a generic error message.
> >
> > "Morgan" <abcd@.senditon.com> wrote in message
> > news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > > Thanks in advance for any info.
> > >
> > > What would cause Optimization & Integrity Check jobs to fail? The
agent
> > info
> > > shows SQL State = 42000, which, according to BOL is "Syntax error,
> > > permission violation, or other nonspecific error. " Hmm... where to go
> > from
> > > here?
> > >
> > >
> > > Thanks,
> > >
> > > Morgan
> > >
> > >
> > >
> >
> >
>

Optimizations & Integrity Check Jobs failing?

Tuesday, March 20, 2012

Optimization and Integrity question

Hi,
I have set up a database maintenance plan for all the databases on a server
that takes care of the Optimization and Integrity every week on the weekends.
However, it's been failing consistently for the past couple of weeks. This is
how it's been set up:
FOR OPTIMIZATIONS
Reorganize data and index pages
Reorganize pages with the original amount of free space
and for INTEGRITY CHECKS
Check Database Integrity
Include Indexes
This is the message in the error log file for optimizations:
(null)
(null)
[16] Database TestMS: Index Rebuild (leaving 100%% free space)...
(null)
(null)
(null)
Rebuilding indexes for table 'tblData'
(null)
(null)
** Execution Time: 0 hrs, 0 mins, 1 secs **
(null)
(null)
End of maintenance plan 'DB Maintenance Plan1' on 4/23/2007 9:48:31 PM
(null)
SQLMAINT.EXE Process Exit Code: 1 (Failed)
(null)
Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]
and this is the message in the error log file for Integrity:
(null)
(null)
[16] Database TestMS: Check Data and Index Linkage...
(null)
(null)
** Execution Time: 0 hrs, 0 mins, 1 secs **
(null)
(null)
End of maintenance plan 'DB Maintenance Plan1' on 4/23/2007 9:56:25 PM
(null)
SQLMAINT.EXE Process Exit Code: 1 (Failed)
(null)
Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]
Any help will be greatly appreciated. Thanks.These are just some ideas:
1)Check to see none of the dbs upon which you are attempting this
maintenance plan are either : corrupt or off line
2)Check the compatibility levels are to low
3)Hard disk full
--
Jack Vamvas
___________________________________
Need an IT job? <a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">uk it jobs</a>
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:C9D6FB45-77F3-418F-8508-0439665AFFE4@.microsoft.com...
> Hi,
> I have set up a database maintenance plan for all the databases on a
> server
> that takes care of the Optimization and Integrity every week on the
> weekends.
> However, it's been failing consistently for the past couple of weeks. This
> is
> how it's been set up:
> FOR OPTIMIZATIONS
> Reorganize data and index pages
> Reorganize pages with the original amount of free space
> and for INTEGRITY CHECKS
> Check Database Integrity
> Include Indexes
> This is the message in the error log file for optimizations:
>
> (null)
> (null)
> [16] Database TestMS: Index Rebuild (leaving 100%% free space)...
>
> (null)
> (null)
> (null)
> Rebuilding indexes for table 'tblData'
> (null)
> (null)
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>
> (null)
> (null)
> End of maintenance plan 'DB Maintenance Plan1' on 4/23/2007 9:48:31 PM
>
> (null)
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
> (null)
> Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]
>
> and this is the message in the error log file for Integrity:
>
> (null)
> (null)
> [16] Database TestMS: Check Data and Index Linkage...
>
> (null)
> (null)
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>
> (null)
> (null)
> End of maintenance plan 'DB Maintenance Plan1' on 4/23/2007 9:56:25 PM
>
> (null)
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
> (null)
> Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]
> Any help will be greatly appreciated. Thanks.
>

Optimization and Integrity question

Hi,
I have set up a database maintenance plan for all the databases on a server
that takes care of the Optimization and Integrity every week on the weekends
.
However, it's been failing consistently for the past couple of weeks. This i
s
how it's been set up:
FOR OPTIMIZATIONS
Reorganize data and index pages
Reorganize pages with the original amount of free space
and for INTEGRITY CHECKS
Check Database Integrity
Include Indexes
This is the message in the error log file for optimizations:
(null)
(null)
[16] Database TestMS: Index Rebuild (leaving 100%% free space)...
(null)
(null)
(null)
Rebuilding indexes for table 'tblData'
(null)
(null)
** Execution Time: 0 hrs, 0 mins, 1 secs **
(null)
(null)
End of maintenance plan 'DB Maintenance Plan1' on 4/23/2007 9:48:31 PM
(null)
SQLMAINT.EXE Process Exit Code: 1 (Failed)
(null)
Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]
and this is the message in the error log file for Integrity:
(null)
(null)
[16] Database TestMS: Check Data and Index Linkage...
(null)
(null)
** Execution Time: 0 hrs, 0 mins, 1 secs **
(null)
(null)
End of maintenance plan 'DB Maintenance Plan1' on 4/23/2007 9:56:25 PM
(null)
SQLMAINT.EXE Process Exit Code: 1 (Failed)
(null)
Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]
Any help will be greatly appreciated. Thanks.These are just some ideas:
1)Check to see none of the dbs upon which you are attempting this
maintenance plan are either : corrupt or off line
2)Check the compatibility levels are to low
3)Hard disk full
Jack Vamvas
___________________________________
Need an IT job? <a href="http://links.10026.com/?link=http://www.itjobfeed.com">uk it jobs</a>
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:C9D6FB45-77F3-418F-8508-0439665AFFE4@.microsoft.com...
> Hi,
> I have set up a database maintenance plan for all the databases on a
> server
> that takes care of the Optimization and Integrity every week on the
> weekends.
> However, it's been failing consistently for the past couple of weeks. This
> is
> how it's been set up:
> FOR OPTIMIZATIONS
> Reorganize data and index pages
> Reorganize pages with the original amount of free space
> and for INTEGRITY CHECKS
> Check Database Integrity
> Include Indexes
> This is the message in the error log file for optimizations:
>
> (null)
> (null)
> [16] Database TestMS: Index Rebuild (leaving 100%% free space)...
>
> (null)
> (null)
> (null)
> Rebuilding indexes for table 'tblData'
> (null)
> (null)
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>
> (null)
> (null)
> End of maintenance plan 'DB Maintenance Plan1' on 4/23/2007 9:48:31 PM
>
> (null)
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
> (null)
> Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]
>
> and this is the message in the error log file for Integrity:
>
> (null)
> (null)
> [16] Database TestMS: Check Data and Index Linkage...
>
> (null)
> (null)
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>
> (null)
> (null)
> End of maintenance plan 'DB Maintenance Plan1' on 4/23/2007 9:56:25 PM
>
> (null)
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
> (null)
> Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]
> Any help will be greatly appreciated. Thanks.
>

Optimization and Integrity Error

Is there any way to run an optimization and integrity check against the
master and msdb databases? Below are the log files. Thanks!
- Gabe
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
Starting maintenance plan 'System Databases - Optimizations and Integrity'
on 6/2/2007 2:00:01 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC SQL
Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database
'MASTER'.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database master: 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 **
[2] Database model: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[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 'msdb'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[3] Database msdb: 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 **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'System Databases - Optimizations and Integrity' on
6/2/2007 2:00:01 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Gabe,
Sure, but you need to turn off the "repair minor problems" option.
http://support.microsoft.com/kb/290622
RLF
"Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message
news:uTngsk6pHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Is there any way to run an optimization and integrity check against the
> master and msdb databases? Below are the log files. Thanks!
> - Gabe
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
> Starting maintenance plan 'System Databases - Optimizations and Integrity'
> on 6/2/2007 2:00:01 AM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in
> database 'MASTER'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [1] Database master: 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 **
> [2] Database model: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [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 'msdb'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [3] Database msdb: 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 **
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'System Databases - Optimizations and Integrity'
> on 6/2/2007 2:00:01 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>|||Thanks!!
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23qevkq6pHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Gabe,
> Sure, but you need to turn off the "repair minor problems" option.
> http://support.microsoft.com/kb/290622
> RLF
> "Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message
> news:uTngsk6pHHA.3892@.TK2MSFTNGP05.phx.gbl...
>> Is there any way to run an optimization and integrity check against the
>> master and msdb databases? Below are the log files. Thanks!
>> - Gabe
>> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
>> 'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
>> Starting maintenance plan 'System Databases - Optimizations and
>> Integrity' on 6/2/2007 2:00:01 AM
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in
>> database 'MASTER'.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
>> failed.
>> [1] Database master: 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 **
>> [2] Database model: Check Data and Index Linkage...
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [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 'msdb'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
>> failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
>> failed.
>> [3] Database msdb: 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 **
>> Deleting old text reports... 0 file(s) deleted.
>> End of maintenance plan 'System Databases - Optimizations and Integrity'
>> on 6/2/2007 2:00:01 AM
>> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>>
>

Optimization and integrity check errors

At an installation of SharePointPortal Server 2003 were created automatically
3 databases.
The jobs for optimizations and integrity check for one of these databases
produce always errors:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver] [SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'
I changed repeatedly QUOTED_IDENTIFIER, but the errors appeared again.
I used directly the DBCC statements for all tables of the database and I
became no errors.
What can I do?
Thank you in advance for your help
Andreas Marner
Sounds like you have a computed column or an Indexed view in the database.
There is a bug with the MP that does not allow it to handle these very well.
I recommend you create your own scheduled job to issue the backups and
reindexing for at least those db's.
Andrew J. Kelly SQL MVP
"Marner" <marner@.discussions.microsoft.com> wrote in message
news:6F859E50-AE75-4587-95F8-F4697BF4FD6F@.microsoft.com...
> At an installation of SharePointPortal Server 2003 were created
automatically
> 3 databases.
> The jobs for optimizations and integrity check for one of these databases
> produce always errors:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL
> Server Driver] [SQL Server]DBCC failed because the following SET options
have
> incorrect settings: 'QUOTED_IDENTIFIER'
> I changed repeatedly QUOTED_IDENTIFIER, but the errors appeared again.
> I used directly the DBCC statements for all tables of the database and I
> became no errors.
> What can I do?
> Thank you in advance for your help
> Andreas Marner

Optimization and integrity check errors

At an installation of SharePointPortal Server 2003 were created automatically
3 databases.
The jobs for optimizations and integrity check for one of these databases
produce always errors:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver] [SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'
I changed repeatedly QUOTED_IDENTIFIER, but the errors appeared again.
I used directly the DBCC statements for all tables of the database and I
became no errors.
What can I do?
Thank you in advance for your help
Andreas MarnerSounds like you have a computed column or an Indexed view in the database.
There is a bug with the MP that does not allow it to handle these very well.
I recommend you create your own scheduled job to issue the backups and
reindexing for at least those db's.
--
Andrew J. Kelly SQL MVP
"Marner" <marner@.discussions.microsoft.com> wrote in message
news:6F859E50-AE75-4587-95F8-F4697BF4FD6F@.microsoft.com...
> At an installation of SharePointPortal Server 2003 were created
automatically
> 3 databases.
> The jobs for optimizations and integrity check for one of these databases
> produce always errors:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL
> Server Driver] [SQL Server]DBCC failed because the following SET options
have
> incorrect settings: 'QUOTED_IDENTIFIER'
> I changed repeatedly QUOTED_IDENTIFIER, but the errors appeared again.
> I used directly the DBCC statements for all tables of the database and I
> became no errors.
> What can I do?
> Thank you in advance for your help
> Andreas Marner

Wednesday, March 21, 2012

Optimizations & Integrity Check Jobs failing?

Optimizations & Integrity Check Jobs failing?

Tuesday, March 20, 2012

Optimization and Integrity question

Optimization and Integrity question

Optimization and Integrity Error

Optimization and Integrity Error

Optimization and Integrity Error

Optimization and Integrity Error

Optimization and integrity check errors

Optimization and integrity check errors

OpenXML Identity Insert

Blog Archive

About Me