February 11, 2009 at 11:27 am
Hi,
We have 2 sql servers. ServerA(Production Sql server 2000 Standard edition) and ServerB(Sql server 2005 Developer edition, used as a query database).
From ServerA, we backup 2 databases(Mydb and Mydb2) every day and copy them to serverB and restore in it. It is automated using jobs.
Every time Iam seeing the below messages in serverB when the databases were restored regarding inconsistency warning messages.
SQL Server has encountered 10 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 5 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 5 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Starting up database 'MyDB'.
The database 'MyDB' is marked RESTORING and is in a state that does not allow recovery to be run. SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Starting up database 'MyDB'.
Recovery is writing a checkpoint in database 'MyDB' (7). This is an informational message only. No user action is required.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_stdAQSKey_78415083" (index_id = 3) on object ID 2017546371 in database "MyDB". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Database was restored: Backup Database: MyDB, creation date(time): 2007/09/04(15:16:03), first LSN: 118431:11698:1, last LSN: 118431:11748:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\abcDatabaseRestore\MyDB.bak'}). Informational message. No user action required.
Starting up database 'MyDB2'.
The database 'MyDB2' is marked RESTORING and is in a state that does not allow recovery to be run.
Starting up database 'MyDB2'.
Recovery is writing a checkpoint in database 'MyDB2' (8). This is an informational message only. No user action is required.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_abc_523643 " (index_id = 4) on object ID 1523643 in database "MyDB2". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_stg_cms_32g8578" (index_id = 5) on object ID 152364 in database "MyDB2". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Plz advice me what have to do to get rid of these errors.
Thanks
February 11, 2009 at 1:12 pm
Have you done as the error said and run a checktable (or better yet, a full checkDB)? Please post the errors it returned.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2009 at 1:34 pm
First of all you should drop that stat (in case it was "upgraded" from 2000)
Then run an update of the stats with fullscan and also update usage
* Noel
February 11, 2009 at 3:13 pm
Thank you
In serverB, sql server 2005:
DBCC CHECKDB(Mydb) with no_infomsgs has no errors.
DBCC CHECKDB(Mydb2) with no_infomsgs has below errors
Result:
Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object "ABC", index ID 0, partition ID 2302345250764, alloc unit ID 96295478820864 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'BAC' (object ID 2468762309).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'Mydb2'.
I also ran the dbcc updateusage(Mydb2) with no_infomsgs, I did not get any errors...
But in Production server(ServerA, sql server 2000) I did not get any errors while running DBCC CHECKDB for the same database Mydb2.
Plz advice me
February 11, 2009 at 10:18 pm
Did you run checkdb again after running the checkusage? It's normal for checkusage not to give errors. It fixes problems.
Those won't generate errors on SQL 2000. CheckDB on SQL 2000 did not consider that kind of metadata inconsistency an error.
The restores that give errors, what's the source server and what's the destination?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 11, 2009 at 11:41 pm
Thanks Gail,
Here is the scenario: Source server is a production SQL Server 2000 Standard edition and Destination is SQL server 2005 developer edition. from the production we are taking the backups of Mydb and Mydb2, copying to Destination SQL Server 2005 and restoring them.
I just took the backup from production SQL Server 2000 and restored in SQL Server 2005 and again Iam seeing the same warnings in the error log.
Now, I ran the DBCC CHECKDB(Mydb2) with no_infomsgs and I got the same error.Next I ran dbcc updateusage and then dbcc checkdb(as you suggested) and now there are no errors.
So my question is how can resolve this issue, because every day I will take the backups from sql 2000 and restore in sql 2005. Every time it restored in the SQL 2005, Iam getting this errors and warnings
Do I need to create one job in destination SQL 2000 whcih will do dbcc checkdb and dbcc updateusage and then dbcc checkdb?
or is there any problem with the production databases?
Thanks for your help
February 12, 2009 at 8:22 am
Drop all the statistics referenced in the error messages. SQL will recreate them if it needs.
Maybe run a checkcatalog on the sql 2000 database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2009 at 10:25 am
Hi Gail,
Could you please tell me how to delete statistics referenced in the error messages and this should be done in production SQL 2000 instance or SQL 2005?
Thanks
February 12, 2009 at 10:33 am
DROP STATISTICS statisticsname
you can list more than one just separate them with a comma
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 12, 2009 at 11:33 am
Do We need run the DROP STATISTICS statisticsname on the production SQL server 2000?
February 12, 2009 at 1:45 pm
i would drop the statistics in the source (sql2000) database. As Gail said they will be re created if needed (assuming you have auto create statistics enabled on the database).
you could drop them on the sql2005 database but if they're corrupted at the source each time you restore to 2005 you'll get the same errors. Dont forget to run dbcc chceckcatalog too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2009 at 3:52 am
while you are at it ...
I always run the check for data purity after a restore from a lower version of sqlserver.
/*
* To be executed after a db-restore from a lower version than sqlserver 2005 !
*
*DATA_PURITY
* Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range.
* For example, DBCC CHECKDB detects columns with date and time values that are larger than or less
* than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns
* with scale or precision values that are not valid.
*
* For databases created in SQL Server 2005, column-value integrity checks are enabled by default and
* do not require the DATA_PURITY option.
* For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default
* until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database.
* After this, DBCC CHECKDB checks column-value integrity by default.
*
* For more information about how CHECKDB might be affected by upgrading database from earlier versions of SQL Server, see the Remarks section later in this topic.
*
* If PHYSICAL_ONLY is specified, column-integrity checks are not performed.
*
* Validation errors reported by this option cannot be fixed by using DBCC repair options.
* For information about manually correcting these errors, see Knowledge Base article 923247: Troubleshooting DBCC error 2570 in SQL Server 2005.
* http://support.microsoft.com/kb/923247
*
*/
-- checkdb with content-re-eveluation
DBCC CHECKDB (0) WITH ALL_ERRORMSGS, DATA_PURITY;
go
print '' ;
print '*********************************'
print '**'
print '** If DBCC CheckDB returned DATA_PURITY errors, you must view http://support.microsoft.com/kb/923247 !!! and repair the issues !!!'
print '*********************************'
print '**'
print '' ;
print '--' ;
Print '-- Now execute SQL: Gen en Execute Rebuild tables and indexes.sql';
go
print '--' ;
print '' ;
print '--' ;
-- Check DbLevel
if not exists ( select 1
from master.sys.databases
where [name] = db_name()
and compatibility_level = 90 )
Print '-- If possible update dblevel : Exec sp_dbcmptlevel @dbname = '''+ db_name() +''' , @new_cmptlevel = 90 ;'
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 17, 2009 at 11:54 pm
After upgrading from Sql server 2000 to 2005 what are the DBCC commands do we need to run inorder to make sure all databases are in correct state.
Thank you
February 18, 2009 at 12:13 am
We use this sequence:
1) restore the db to SQL2005
2) DBCC for DATA_PURITY
3) switch to dblevel 90 (if possible)
4) rebuild all indexes (individually)
5) dbcc updateusage with count_rows
6) sp_updatestats
7) synchronize users
8) create a new backup
9) perform test by the application team(s)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 18, 2009 at 2:46 am
ALZDBA (2/18/2009)
We use this sequence:1) restore the db to SQL2005
2) DBCC for DATA_PURITY
3) switch to dblevel 90 (if possible)
4) rebuild all indexes (individually)
5) dbcc updateusage with count_rows
6) sp_updatestats
7) synchronize users
8) create a new backup
9) perform test by the application team(s)
i always use the following
1) restore the db to SQL2005 (DBCC CHECKDB is part of this)
2) dbcc updateusage
3) switch to dblevel 90 (if possible)
4) sp_updatestats
5) rebuild all indexes (individually)
6) DBCC for DATA_PURITY
7) synchronize users
8) create a new backup
9) perform test by the application team(s)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply