February 19, 2009 at 1:27 am
hi Gail,
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?
I did not understand what were the statistics from the error messages. Could you plz identify me the statistics in the error messages.
and I need to drop them in source server(sql 2000(production box)right?
is it safe to drop statistics from a production box?
Thanks for your help
February 19, 2009 at 1:46 am
2009-02-10 22:16:05.31 spid53 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.
2009-02-10 22:16:49.46 spid53 Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctlRenewal_636F8578" (index_id = 3) on object ID 1668253048 in database "MyDB2". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
2009-02-10 22:16:49.48 spid53 Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctl_mstID_636F8578" (index_id = 4) on object ID 1668253048 in database "MyDB2". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
2009-02-10 22:16:49.48 spid53 Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctl_staNumber_636F8578" (index_id = 5) on object ID 1668253048 in database "MyDB2". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
2009-02-10 22:16:49.48 spid53 Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctlEffDate_636F8578" (index_id = 6) on object ID 1668253048 in database "MyDB2". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
2009-02-10 22:16:49.48 spid53 Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctlEnabled_636F8578" (index_id = 8) on object ID 1668253048 in database "MyDB2". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
2009-02-10 22:16:49.48 spid53 Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctl_typID_636F8578" (index_id = 9) on object ID 1668253048 in database "MyDB2". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
2009-02-10 22:16:49.48 spid53 Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctl_rlvID_636F8578" (index_id = 10) on object ID 1668253048 in database "MyDB2". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
2009-02-10 22:16:49.48 spid53 Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctlDescription_636F8578" (index_id = 11) on object ID 1668253048 in database "MyDB2". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
You need to drop them on the 2000 box. It is always safe to drop automatically created statistics (which these are) as the system can just generate them again if it feels it needs to.
Once you're dropped them, run a checktable on whatever table this returns
SELECT OBJECT_NAME(1668253048)
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 19, 2009 at 1:58 am
thank you.
To drop the statistics:
1.I have to go database Mydb and execute drop statistics statstics name right?
2.how do we know these statistics are automatically created?
and I came to know about these inconsistency errors as Iam restoring these 2 databases to sql server 2005. So how can I know for the rest of databases in sql server 2000 have these problem or not?
February 19, 2009 at 5:57 am
madhu.arda (2/19/2009)
1.I have to go database Mydb and execute drop statistics _WA_Sys_stdAQSKey_78415083 right?
Yup, for each of the statistics listed in the errors
2.how do we know these statistics are automatically created?
From the name. Stats with that form of name (_WA_Sys_*) are system-created statistics. Yes, someone could probably go and create a statistic manually with exactly the same form, but is that really likely?
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 19, 2009 at 11:58 am
thank you.
Yes, someone could probably go and create a statistic manually with exactly the same form, but is that really likely?
Iam not sure that somebody manually created these statistics with exacltly the same form(Excep sqlserver admin) nobody has access though).If some body created them how can we know?
and where can i see these ststistics in the database Mydb?
and aslo please clarify me this:
I
came to know about these inconsistency errors as Iam restoring these 2 databases to sql server 2005. So how can I know for the rest of databases in sql server 2000 have these problem or not
?
February 19, 2009 at 12:26 pm
madhu.arda (2/19/2009)
Iam not sure that somebody manually created these statistics with exacltly the same form(Excep sqlserver admin) nobody has access though).If some body created them how can we know?
Use INDEXPROPERY with the IsAutoStatistics property It'll return 1 if the stats were automatically created, 0 otherwise.
Is it really likely that your sysadmin managed to completely duplicate the system naming standard for statistics? Would he really create things with a name like "_WA_Sys_stdAQSKey_78415083"?
and where can i see these ststistics in the database Mydb?
SELECT * FROM sysindexes WHERE INDEXPROPERTY(id, name,'IsStatistics') = 1
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 19, 2009 at 1:19 pm
thank you..
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_key2354" (index_id = 3) on object ID 2235454631 in database "MyDB". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
To know whether this auto created or not I did the following
Use Mydb
go
INDEXPROPERTY ( 2017546371 , _WA_Sys_23543 ,IsAutoStatistics )
But Iam getting an error :
like server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '2017546371'.
I took the object_id as 2017546371 from 'A column nullability inconsistency was detected in the metadata of index "_WA_Sys_2354" (index_id = 3) on object ID 235446371 in database "MyDB". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.'
where Iam going wrong? from the above query if we get result as 1, then we can go head drop the statistics right?
I never come across these issues..so Iam asking many questions
Thanks for prompt respone
February 19, 2009 at 1:47 pm
madhu.arda (2/19/2009)
INDEXPROPERTY ( 2017546371 , _WA_Sys_stdAQSKey_78415083 ,IsAutoStatistics )But Iam getting an error :
Did you see how I used INDEXPROPERTY above? Did you look at the examples of its use in Books Online?
It's a function, it needs to be called within a SELECT or an assignment (SET) and string constants have to be in quotes.
SELECT INDEXPROPERTY ( 2017546371 , '_WA_Sys_stdAQSKey_78415083','IsAutoStatistics')
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 19, 2009 at 3:38 pm
Thanks Gail,
As Noil said 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
In next week we are upgrading the sql 2000 to 2005 using side by side method.
So after restoring the databases in Newly installed sql server 2005, I will drop the statistics the one giving the warning and errors while restoring and then I will run
DBCC CHECKDB
DBCC UPDATESTATS
DBCC UPDATEUSAGE.
Is this approch is fine or I MUST need to drop these statistics from production SQL 2000 before taking backups and restore in sql 2005?
Please give me your suggestion
Thank You
Madhu
February 19, 2009 at 4:41 pm
drop the auto created stats from the sql2000 prod database, otherwise each time you restore that db the corrupted object will still be there,
does this make sense
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 19, 2009 at 5:09 pm
drop the auto created stats from the sql2000 prod database, otherwise each time you restore that db the corrupted object will still be there,
does this make sense
I think the above method will be good for the scenario 'Taking backups from Server A(Production sql 2000), copying the backups to Server B(Sql 2005) and restore. '(Daily process)
In the scenario, upgrade from sql 2000 to 2005, droping the statistics in SQL 2005 and run all the dbcc commands to make sure these in no corruption might be good(Correct me if Iam wrong)
And why these Auto created statistics were corrupted? how to diagnose the reason for corruption?
Thanks for your inputs
Madhu
February 19, 2009 at 9:52 pm
madhu.arda (2/19/2009)
In the scenario, upgrade from sql 2000 to 2005, droping the statistics in SQL 2005 and run all the dbcc commands to make sure these in no corruption might be good
If you do that, you'll have to do it on every single restore. The source of the problem is the SQL 2000 database. Fix it there. It makes no sense at all to fix it in a copy that's overwritten every day.
And why these Auto created statistics were corrupted? how to diagnose the reason for corruption?
Corruption's typically an IO problem. If you don't know when it started, the chances of finding the root cause are slim. Check windows event logs, see if there are any hardware-related events
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 19, 2009 at 11:48 pm
Thank you..
Finally, I deleted the statistics from production sql 2000 in Mydb2 and restored in sql 2005.Now I did not see any warnings/errors.
I have one more question in the below results of restoring the mydb2 in sql 2005. What is the meaning of the quoted statements below
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 4776 pages for database 'Mydb2', file 'MYdb2_dat' on file 1.
Processed 1 pages for database 'Mydb2', file 'Mydb2_log' on file 1.
Converting database 'Mydb2' from version 539 to the current version 611.
Database 'Mydb2' running the upgrade step from version 539 to version 551.
Database 'Mydb2' running the upgrade step from version 551 to version 552.
Database 'Mydb2' running the upgrade step from version 552 to version 553.
Database 'Mydb2' running the upgrade step from version 553 to version 554.
Database 'Mydb2' running the upgrade step from version 554 to version 589.
Database 'Mydb2' running the upgrade step from version 589 to version 590.
Database 'Mydb2' running the upgrade step from version 590 to version 593.
Database 'Mydb2' running the upgrade step from version 593 to version 597.
Database 'Mydb2' running the upgrade step from version 597 to version 604.
Database 'Mydb2' running the upgrade step from version 604 to version 605.
Database 'Mydb2' running the upgrade step from version 605 to version 606.
Database 'Mydb2' running the upgrade step from version 606 to version 607.
Database 'Mydb2' running the upgrade step from version 607 to version 608.
Database 'Mydb2' running the upgrade step from version 608 to version 609.
Database 'Mydb2' running the upgrade step from version 609 to version 610.
Database 'Mydb2' running the upgrade step from version 610 to version 611.
RESTORE DATABASE successfully processed 4777 pages in 4.757 seconds (8.224 MB/sec).
Thank You
February 20, 2009 at 12:06 am
That's just SQLServer performing the internal db upgrade at restore time if your backup originates from a lower version.
That also occurs if you attach a db from a lower version.
And that's the reason why you cannot go back to a lower version of the engine.
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 20, 2009 at 12:53 am
hi,
When I restored the backups from sql 2000 in sql 2005, I did find the below messages in the error log.
What is the meaning of the quoted lines below.
2009-02-19 23:31:00.31 spid60 SQL Server has encountered 12 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations
.
2009-02-19 23:31:00.32 spid60 SQL Server has encountered 12 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-02-19 23:31:00.32 spid60 SQL Server has encountered 12 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-02-19 23:43:36.89 spid60 Starting up database 'Mydb'.
2009-02-19 23:43:36.91 spid60
The database 'Mydb' is marked RESTORING and is in a state that does not allow recovery to be run.
2009-02-19 23:43:43.02 spid60 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.
2009-02-19 23:43:43.02 spid60 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.
2009-02-19 23:43:43.02 spid60 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.
2009-02-19 23:43:43.06 spid60 Starting up database 'Mydb'.
2009-02-19 23:43:43.36 spid60 Recovery is writing a checkpoint in database 'Mydb' (7). This is an informational message only. No user action is required.
2009-02-19 23:44:55.76 Backup Database was restored: Database: Mydb, creation date(time): 2007/09/04(15:16:03), first LSN: 118718:211:1, last LSN: 118718:321:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\Restore\Mydb.bak'}). Informational message. No user action required.
2009-02-19 23:45:00.13 spid60 Starting up database 'Mydb2'.
2009-02-19 23:45:00.13 spid60 The database 'Mydb2' is marked RESTORING and is in a state that does not allow recovery to be run.
2009-02-19 23:45:00.37 spid60 Starting up database 'Mydb2'.
2009-02-19 23:45:00.42 spid60 Recovery is writing a checkpoint in database 'Mydb2' (8). This is an informational message only. No user action is required.
2009-02-19 23:45:04.96 Backup Database was restored: Database: Mydb2, creation date(time): 2009/02/10(17:56:15), first LSN: 13921:5832:1, last LSN: 13921:5834:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\Restore\Mydb2.bak'}). Informational message. No user action required.
If the quoted line are any issue, please tell me how to resolve it.
Thank you
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply