We can actually ignore the scale truncation errors via 'set arithabort numeric_truncation off' command.
This also gives a better picture as why the errors were thrown and actually shows us that how many scale values we lost when implicit conversions were made ( good debugging )
Also even if the truncation error occurs, the batch sql continues to execute so might be that the job which executed the procedure was not failing but the data where this truncation is happening, was not getting displayed.
Here are some examples and comments:
Case 1 :
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(28,13),1.4556537149915)
2>
Truncation error occurred.
Command has been aborted.
1> set arithabort numeric_truncation off
2>
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(28,13),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.41325534436901151728336040
(1 row affected)
1> set arithabort numeric_truncation on ( this is ON by default thus we get errors in scale losses )
2>
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(28,18),1.4556537149915)
2>
140416.4132553443690115172833604056875 -- for original setting, we were loosing out 5 scale values, hence when we made the scale as 18, no truncation occurred.
Case 2
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.828113962515) / 100) * convert(numeric(35,20),1.45565371499151111111)
2>
Truncation error occurred.
Command has been aborted.
1> set arithabort numeric_truncation off
2>
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.828113962515) / 100) * convert(numeric(35,20),1.45565371499151111111)
2>
--------------------------------------------------------------------------------
140416.41325534766300175115789757
Lets find out how much it goes..
1> select 10177.244681*(947.828113962515/100)*1.45565371499151111111
2>
---------------------------------------------------------------
140416.4132553476630017511578975709032198096365000000000
Its going beyond 38 which is the max scale we can have on numeric and hence truncating…
Case 3 :
In truncation errors being off :
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(35,20),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.41325534436901151728336040
We know that this multiplication goes to 56875 as per Case 1 so adding 5 more to scale would lead to no scale loss
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(35,25),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.4132553443690115172833604056875
1> set arithabort numeric_truncation on
2>
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(35,25),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.4132553443690115172833604056875
We definetely do not need that length of precesions and can safely ignore the truncation errors..
Hope this helps.
Thursday 31 December 2009
Computed Columns / Functional Indexes ASE 15 Practicals
4 tables are created :
All tables contain 3 base columns num1, num2, num3
table_computed extra computed column ( num1 + num2 + num3 ) materialized
table_computed_nmz extra computed column ( num1 + num2 + num3 ) virtual
table_function extra computed column ( num1 + num2 + num3 ) materialized
table_function_nmz extra computed column ( num1 + num2 + num3 ) virtual
1> use sag
1> create nonclustered index table_computed_nci on table_computed(colcomp)
2>
1>
1> create nonclustered index table_computed_nmz_nci on table_computed_nmz(colcomp)
2>
Msg 1777, Level 16, State 1:
Server 'GALLARDO', Line 1:
Cannot create index on a virtual computed column (colcomp). Modify it to materialized using the ALTER TABLE MODIFY commmand first and retry.
1> create table table_function
2> (
1> create nonclustered index table_function_nmz_nci on table_function_nmz(compcol*5/4)
2>
Msg 11062, Level 16, State 1:
Server 'GALLARDO', Line 1:
Column 'compcol' is a computed column. Only regular columns can be referenced in a computed column (or function-based index key) definition.
1> create nonclustered index table_function_nmz_nci on table_function_nmz(num1*compcol*5/4)
2>
Msg 11062, Level 16, State 1:
Server 'GALLARDO', Line 1:
Column 'compcol' is a computed column. Only regular columns can be referenced in a computed column (or function-based index key) definition.
1> create nonclustered index table_function_nci on table_function(num1*compcol*5/4)
2>
Msg 11062, Level 16, State 1:
Server 'GALLARDO', Line 1:
Column 'compcol' is a computed column. Only regular columns can be referenced in a computed column (or function-based index key) definition.
1> create nonclustered index table_function_nci on table_function(num1*num2)
2>
Msg 11052, Level 16, State 1:
Server 'GALLARDO', Line 1:
The 'select into' database option is not enabled for database 'sag'. ALTER TABLE with data copy cannot be done. Set the 'select into' database option and re-run.
1> use master
2>
1> sp_dboption sag,'select', true
2>
Database option 'select into/bulkcopy/pllsort' turned ON for database 'sag'.
Running CHECKPOINT on database 'sag' for option 'select into/bulkcopy/pllsort' to take effect.
(return status = 0)
1> use sag
2> checkpoint
3>
1> create nonclustered index table_function_nci on table_function(num1*num2)
2>
(442 rows affected)
1> create nonclustered index table_function_nci_nmz on table_function_nmz(num1*num2)
2>
(492 rows affected)
1> create clustered index table_function_ci on table_function(num1*num2*num3)
2>
Msg 1948, Level 16, State 1:
Server 'GALLARDO', Line 1:
Clustered function-based index is not supported.
1> create clustered index table_computed_ci on table_computed(colcomp)
2>
Non-clustered index (index id = 2) is being rebuilt.
All tables contain 3 base columns num1, num2, num3
table_computed extra computed column ( num1 + num2 + num3 ) materialized
table_computed_nmz extra computed column ( num1 + num2 + num3 ) virtual
table_function extra computed column ( num1 + num2 + num3 ) materialized
table_function_nmz extra computed column ( num1 + num2 + num3 ) virtual
1> use sag
1> create nonclustered index table_computed_nci on table_computed(colcomp)
2>
1>
1> create nonclustered index table_computed_nmz_nci on table_computed_nmz(colcomp)
2>
Msg 1777, Level 16, State 1:
Server 'GALLARDO', Line 1:
Cannot create index on a virtual computed column (colcomp). Modify it to materialized using the ALTER TABLE MODIFY commmand first and retry.
1> create table table_function
2> (
1> create nonclustered index table_function_nmz_nci on table_function_nmz(compcol*5/4)
2>
Msg 11062, Level 16, State 1:
Server 'GALLARDO', Line 1:
Column 'compcol' is a computed column. Only regular columns can be referenced in a computed column (or function-based index key) definition.
1> create nonclustered index table_function_nmz_nci on table_function_nmz(num1*compcol*5/4)
2>
Msg 11062, Level 16, State 1:
Server 'GALLARDO', Line 1:
Column 'compcol' is a computed column. Only regular columns can be referenced in a computed column (or function-based index key) definition.
1> create nonclustered index table_function_nci on table_function(num1*compcol*5/4)
2>
Msg 11062, Level 16, State 1:
Server 'GALLARDO', Line 1:
Column 'compcol' is a computed column. Only regular columns can be referenced in a computed column (or function-based index key) definition.
1> create nonclustered index table_function_nci on table_function(num1*num2)
2>
Msg 11052, Level 16, State 1:
Server 'GALLARDO', Line 1:
The 'select into' database option is not enabled for database 'sag'. ALTER TABLE with data copy cannot be done. Set the 'select into' database option and re-run.
1> use master
2>
1> sp_dboption sag,'select', true
2>
Database option 'select into/bulkcopy/pllsort' turned ON for database 'sag'.
Running CHECKPOINT on database 'sag' for option 'select into/bulkcopy/pllsort' to take effect.
(return status = 0)
1> use sag
2> checkpoint
3>
1> create nonclustered index table_function_nci on table_function(num1*num2)
2>
(442 rows affected)
1> create nonclustered index table_function_nci_nmz on table_function_nmz(num1*num2)
2>
(492 rows affected)
1> create clustered index table_function_ci on table_function(num1*num2*num3)
2>
Msg 1948, Level 16, State 1:
Server 'GALLARDO', Line 1:
Clustered function-based index is not supported.
1> create clustered index table_computed_ci on table_computed(colcomp)
2>
Non-clustered index (index id = 2) is being rebuilt.
Archive Database Access in ASE 15 example
ARCHIVE DATABASE ACCESS IN ASE1502
1> sp_dboption scratchdb,'scratch database', true
2>
Database option 'scratch database' turned ON for database 'scratchdb'.
Running CHECKPOINT on database 'scratchdb' for option 'scratch database' to take effect.
(return status = 0)
1> use scratchdb
2>
1> checkpoint
2>
1> use master
2>
1>
1> create database scratchdb on v_scratch=5
2>
CREATE DATABASE: allocating 2560 logical pages (5.0 megabytes) on disk 'v_scratch'.
Database 'scratchdb' is now oline.
1> dump database sag to '/OracleDumps/opctlsy2/sybase/data/sag_archive_dump.dmp'
2>
Backup Server session id is: 5. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /OracleDumps/opctlsy2/sybase/data/sag_archive_dump.dmp.
Backup Server: 6.28.1.1: Dumpfile name 'sag082000CAD5 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sybase/data/sag_archive_dump.dmp'
Backup Server: 4.188.1.1: Database sag: 3916 kilobytes (47%) DUMPED.
Backup Server: 4.188.1.1: Database sag: 5214 kilobytes (100%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 4.188.1.1: Database sag: 5218 kilobytes (100%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database sag: 5222 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database sag).
1>
1> use master
2>
1> disk init
2> name=v_archive_db
3> ,physname='/OracleDumps/opctlsy2/sybase/data/archive_db.dat',
4> size="3M"
5>
1> create archive database archive_database on v_archive_db=3 with scratch_database=scratchdb
2>
CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'v_archive_db'.
1> sp_help
1> load database archive_database from '/OracleDumps/opctlsy2/sybase/data/sag_archive_dump.dmp'
2>
Started estimating recovery log boundaries for database 'archive_database'.
Database 'archive_database', checkpoint=(10214, 13), first=(10214, 13), last=(10214, 13).
Completed estimating recovery log boundaries for database 'archive_database'.
Started ANALYSIS pass for database 'archive_database'.
Completed ANALYSIS pass for database 'archive_database'.
Started REDO pass for database 'archive_database'. The total number of log records to process is 1.
Completed REDO pass for database 'archive_database'.
Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.
1>
1> online database archive_database
2>
Started estimating recovery log boundaries for database 'archive_database'.
Database 'archive_database', checkpoint=(10214, 13), first=(10214, 13), last=(10214, 13).
Completed estimating recovery log boundaries for database 'archive_database'.
Started ANALYSIS pass for database 'archive_database'.
Completed ANALYSIS pass for database 'archive_database'.
Recovery of database 'archive_database' will undo incomplete nested top actions.
Database 'archive_database' is now online.
1>
1> use scratchdb
2>
1> select * from sysaltusages
2>
dbid location lstart size vstart vdevno segmap
------ ----------- ----------- ----------- ----------- ----------- -----------
10 4 0 5120 1 25 3
10 4 5120 5120 1 25 4
10 5 0 256 8 26 3
10 5 256 2 265 26 3
10 5 264 2 267 26 3
10 5 272 2 269 26 3
10
5 280 1 271
1> dump database archive_database to '/OracleDumps/opctlsy2/sybase/data/archive.dmp'
2>
Msg 3167, Level 16, State 2:
Server 'GALLARDO', Line 1:
DUMP DATABASE/TRANSACTION is not currently supported on archive databases.
1>
1> sp_dboption scratchdb,'scratch database', true
2>
Database option 'scratch database' turned ON for database 'scratchdb'.
Running CHECKPOINT on database 'scratchdb' for option 'scratch database' to take effect.
(return status = 0)
1> use scratchdb
2>
1> checkpoint
2>
1> use master
2>
1>
1> create database scratchdb on v_scratch=5
2>
CREATE DATABASE: allocating 2560 logical pages (5.0 megabytes) on disk 'v_scratch'.
Database 'scratchdb' is now oline.
1> dump database sag to '/OracleDumps/opctlsy2/sybase/data/sag_archive_dump.dmp'
2>
Backup Server session id is: 5. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /OracleDumps/opctlsy2/sybase/data/sag_archive_dump.dmp.
Backup Server: 6.28.1.1: Dumpfile name 'sag082000CAD5 ' section number 1 mounted on disk file '/OracleDumps/opctlsy2/sybase/data/sag_archive_dump.dmp'
Backup Server: 4.188.1.1: Database sag: 3916 kilobytes (47%) DUMPED.
Backup Server: 4.188.1.1: Database sag: 5214 kilobytes (100%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 4.188.1.1: Database sag: 5218 kilobytes (100%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database sag: 5222 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database sag).
1>
1> use master
2>
1> disk init
2> name=v_archive_db
3> ,physname='/OracleDumps/opctlsy2/sybase/data/archive_db.dat',
4> size="3M"
5>
1> create archive database archive_database on v_archive_db=3 with scratch_database=scratchdb
2>
CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'v_archive_db'.
1> sp_help
1> load database archive_database from '/OracleDumps/opctlsy2/sybase/data/sag_archive_dump.dmp'
2>
Started estimating recovery log boundaries for database 'archive_database'.
Database 'archive_database', checkpoint=(10214, 13), first=(10214, 13), last=(10214, 13).
Completed estimating recovery log boundaries for database 'archive_database'.
Started ANALYSIS pass for database 'archive_database'.
Completed ANALYSIS pass for database 'archive_database'.
Started REDO pass for database 'archive_database'. The total number of log records to process is 1.
Completed REDO pass for database 'archive_database'.
Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.
1>
1> online database archive_database
2>
Started estimating recovery log boundaries for database 'archive_database'.
Database 'archive_database', checkpoint=(10214, 13), first=(10214, 13), last=(10214, 13).
Completed estimating recovery log boundaries for database 'archive_database'.
Started ANALYSIS pass for database 'archive_database'.
Completed ANALYSIS pass for database 'archive_database'.
Recovery of database 'archive_database' will undo incomplete nested top actions.
Database 'archive_database' is now online.
1>
1> use scratchdb
2>
1> select * from sysaltusages
2>
dbid location lstart size vstart vdevno segmap
------ ----------- ----------- ----------- ----------- ----------- -----------
10 4 0 5120 1 25 3
10 4 5120 5120 1 25 4
10 5 0 256 8 26 3
10 5 256 2 265 26 3
10 5 264 2 267 26 3
10 5 272 2 269 26 3
10
5 280 1 271
1> dump database archive_database to '/OracleDumps/opctlsy2/sybase/data/archive.dmp'
2>
Msg 3167, Level 16, State 2:
Server 'GALLARDO', Line 1:
DUMP DATABASE/TRANSACTION is not currently supported on archive databases.
1>
dbcc dbreboot undocumented command
1> dbcc dbreboot(reboot,sag)
2>
00:00000:00013:2009/06/17 13:19:51.15 server ---------- Shutting Down Database 'sag' ----------
00:00000:00013:2009/06/17 13:19:53.17 server ---------- Re-starting Database 'sag' With Recovery ----------
00:00000:00013:2009/06/17 13:19:53.19 server Log contains all committed transactions until 2009/06/17 12:16:11.54 for database sag.
00:00000:00013:2009/06/17 13:19:53.24 server ---------- Operation on Database 'sag' Completed Successfully ----------
---------- Shutting Down Database 'sag' ----------
---------- Re-starting Database 'sag' With Recovery ----------
Recovering database 'sag'.
Started estimating recovery log boundaries for database 'sag'.
Database 'sag', checkpoint=(43329, 21), first=(43329, 21), last=(43329, 21).
Completed estimating recovery log boundaries for database 'sag'.
Started ANALYSIS pass for database 'sag'.
Completed ANALYSIS pass for database 'sag'.
Started REDO pass for database 'sag'. The total number of log records to process is 1.
Completed REDO pass for database 'sag'.
Recovery of database 'sag' will undo incomplete nested top actions.
Started filling free space info for database 'sag'.
Completed filling free space info for database 'sag'.
Started cleaning up the default data cache for database 'sag'.
Completed cleaning up the default data cache for database 'sag'.
Recovery complete.
Database 'sag' is now online.
---------- Operation on Database 'sag' Completed Successfully ----------
1>
2>
00:00000:00013:2009/06/17 13:19:51.15 server ---------- Shutting Down Database 'sag' ----------
00:00000:00013:2009/06/17 13:19:53.17 server ---------- Re-starting Database 'sag' With Recovery ----------
00:00000:00013:2009/06/17 13:19:53.19 server Log contains all committed transactions until 2009/06/17 12:16:11.54 for database sag.
00:00000:00013:2009/06/17 13:19:53.24 server ---------- Operation on Database 'sag' Completed Successfully ----------
---------- Shutting Down Database 'sag' ----------
---------- Re-starting Database 'sag' With Recovery ----------
Recovering database 'sag'.
Started estimating recovery log boundaries for database 'sag'.
Database 'sag', checkpoint=(43329, 21), first=(43329, 21), last=(43329, 21).
Completed estimating recovery log boundaries for database 'sag'.
Started ANALYSIS pass for database 'sag'.
Completed ANALYSIS pass for database 'sag'.
Started REDO pass for database 'sag'. The total number of log records to process is 1.
Completed REDO pass for database 'sag'.
Recovery of database 'sag' will undo incomplete nested top actions.
Started filling free space info for database 'sag'.
Completed filling free space info for database 'sag'.
Started cleaning up the default data cache for database 'sag'.
Completed cleaning up the default data cache for database 'sag'.
Recovery complete.
Database 'sag' is now online.
---------- Operation on Database 'sag' Completed Successfully ----------
1>
dbccdb checkalloc, checkdb, check catalog failures practicals
Fixed checkdb errors:
Keys of index id 2 for table 'sysattributes' in leaf page not in proper order. Drop and recreate the index. (index page 14833)Msg 7928, Level 18, State 1:Line 1:Index ncsysattributes is not consistent; found 2 leaf rows but 20 data rows. Drop and recreate the index.Checking table 'dbcc_types' (object ID 752002679): Logical page size is 2048 bytes.
1> sp_fixindex dbccdb, sysattributes,22>00:00000:00012:2008/08/06 12:52:40.59 server dbcc dbrepair: Non-clustered index restored for dbccdb..sysattributes.There are 20.000000 rows in 1 pages for object 'sysattributes'.Non-clustered index successfully restored for object 'sysattributes' in 'dbccdb' database.DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.(return status = 0)1>
1> dbcc checktable(sysattributes)2>Checking table 'sysattributes' (object ID 21): Logical page size is 2048 bytes.
Checking partition 'sysattributes_21' (partition ID 21) of table 'sysattributes'. The logical page size of this table is 2048 bytes.The total number of data pages in partition 'sysattributes_21' (partition ID 21) is 1.Partition 'sysattributes_21' (partition ID 21) has 20 data rows.The total number of empty pages (with all deleted rows) in patition 'sysattributes_21' (partition ID 21) is 0.The total number of pages in partition 'sysattributes_21' (partition ID 21) which could be garbage collected to free up some space is 0.The total number of deleted rows in partition 'sysattributes_21' (partition ID 21) is 0.The total number of pages in partition 'sysattributes_21' (partition ID 21) with more than 50 percent garbage is 0.The total number of pages in partition 'sysattributes_21' (partition ID 21) with more than 50 percent insert free space is 1.
The total number of data pages in this table is 1.The total number of pages with more than 50 percent insert free space is 1.Table has 20 data rows.DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>
Msg 692, Level 16, State 1:Line 1:Uninitialized logical page '30957' was read while accessing database 'dbccdb' (31515), object 'sysprocedures' (5), index 'sysprocedures' (0), partition 'sysprocedures_5' (5). Please contact Sybase Technical Support.
1> sp_fixindex dbccdb, sysprocedures,22>00:00000:00012:2008/08/06 13:08:14.56 server dbcc dbrepair: Clustered index restored for dbccdb..sysprocedures.There are 1628.000000 rows in 816 pages for object 'sysprocedures'.Clustered index successfully restored for object 'sysprocedures' in 'dbccdb' database.DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.(return status = 0)1> dbcc checktable(sysprocedures)2>Checking table 'sysprocedures' (object ID 5): Logical page size is 2048 bytes.
Checking partition 'sysprocedures_5' (partition ID 5) of table 'sysprocedures'. The logical page size of this table is 2048 bytes.The total number of data pages in partition 'sysprocedures_5' (partition ID 5) is 814.Partition 'sysprocedures_5' (partition ID 5) has 1628 data rows.The total number of empty pages (with all deleted rows) in patition 'sysprocedures_5' (partition ID 5) is 0.The total number of pages in partition 'sysprocedures_5' (partition ID 5) which could be garbage collected to free up some space is 0.The total number of deleted rows in partition 'sysprocedures_5' (partition ID 5) is 0.The total number of pages in partition 'sysprocedures_5' (partition ID 5) with more than 50 percent garbage is 0.The total number of pages in partition 'sysprocedures_5' (partition ID 5) with more than 50 percent insert free space is 0.
The total number of data pages in this table is 814.Table has 1628 data rows.DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>
Fixed checkcatalog errors:
CHECKCATALOG ERRORS:dbcc checkcatalog
Table Corrupt: Object id 1296004617 (object name = sp_dbcc_run_summaryreport) does not match between sysprocedures and sysobjectsMsg 2513, Level 16, State 6:Line 1:Table Corrupt: Object id 1312004674 (object name = sp_dbcc_run_runcheck) does not match between sysprocedures and sysobjectsMsg 2513, Level 16, State 6:Line 1:Table Corrupt: Object id 1328004731 (object name = sp_dbcc_run_statisticsreport) does not match between sysprocedures and sysobjects
dropped and recreated the procedures as the sysprocedures table did not have entry for them
1> dbcc checkcatalog2>Checking current database: Logical pagesize is 2048 bytesThe following segments have been defined for database 31515 (database name dbccdb).virtual device number virtual start addr size (logical pages) segments--------------------- -------------------- ---------------------- --------------------4 0 15360 0 15 0 5120 217 0 10240 23 0 10240 0 1DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>
Fixed checkalloc errors
Can you advise for the following output from dbcc tablealloc for a particular table dbcc_counters as well as others.
Could there have been some other way to fix this
dbcc tablealloc(dbcc_counters, full, fix)2>00:00000:00012:2008/08/05 17:00:27.66 server Error: 1161, Severity: 21, State: 100:00000:00012:2008/08/05 17:00:27.66 server Invalid OAM entry found at slot 9 in OAM page 1025 for table ID 832002964 with index ID 2 in database 'dbccdb' (ID 31515). This entry is either out of order or its allocation page number is wrong.***************************************************************TABLE: dbcc_counters OBJID = 832002964Msg 2523, Level 16, State 3:Line 1:Table Corrupt: Page number 32500 is out of range for this database dbccdb. The maximum page number in this database is 30719PARTITION ID=832002964 FIRST=1027 ROOT=14777 SORT=0Msg 1161, Level 21, State 1:Line 1:Invalid OAM entry found at slot 9 in OAM page 1025 for table ID 832002964 with index ID 2 in database 'dbccdb' (ID 31515). This entry is either out of order or its allocation page number is wrong.TOTAL # of extents = 0
It looks like page number 32500 is getting associated in dbccdb database while the segmap shows that the last page for dbccdb is ( 20480 + 10240 - 1 ) = 30719
1> select * from master..sysusages where dbid = 315152> dbid segmap lstart size vstart pad unreservedpgs crdate vdevno ------ ----------- ----------- ----------- ----------- ------ ------------- -------------------------- ----------- 31515 3 0 15360 0 NULL 1959 Aug 5 2008 4:55PM 4 31515 4 15360 5120 0 NULL 5100 Aug 5 2008 4:55PM 5 31515 4 20480 10240 0 NULL 10200 Aug 5 2008 4:55PM 17
The OAM page 1025 has following information:
1> dbcc page(31515,1025,1)2>
Page found in Cache: default data cache. Cachelet: 1
BUFFER:Buffer header for buffer 0x20a6ff800 page=0x20a6ff000 bdnew=0x210f15cf8 bdold=0x210f15cf8 bhash=0x0 bmass_next=0x210f2f7e0 bmass_prev=0x2115946b8 bdbid=31515 bvirtpg= [ 0x20a6ff8b8 vpgdevno=4 vpvpn=1025 vdisk=0x203c05c30 ] bmass_head=0x210f15cf8 bmass_tail=0x210f15cf8 bcache_desc=0x20a703650 (cache name='default data cache') bpool_desc=0x20a706dc8 bdbtable=0x202a0e180 Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0 bmass_size=2048 (2K pool) bunref_cnt=0 bmass_stat=0x4001010 (0x04000000 (MASS_DONT_DISCARD), 0x00001000 (MASS_HASHED), 0x00000010 (MASS_INWASH)) bbuf_stat=0x1 (0x00000001 (BUF_PG_REF)) Buffer blpageno=1025 bpg_size=2k Mass blpageno=1025 (Buffer slot #: 0) bxls_pin=0x0 bxls_next=0x0 bspid=0 bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x0Latch and the wait queue:Latch (address: 0x20a6ff830) latchmode: 0x0 (FREE_LATCH) latchowner: -1 latchnoofowners: 0 latchwaitq: 0x0 latchwaitqt: 0x0
Latch wait queue:
PAGE HEADER:Page header for page 0x20a6ff000pageno=1025 nextpg=1025 prevpg=1025 ptnid=832002964 timestamp=0000 0003379foampgcount=1 attrcount=10 indid=2 totalentries_lo=12 entrycount=12page status bits: 0x8008 (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB))
DATA:-----------------------------------------------------------------------------Partition id: 832002964 indid: 2 prevpg: 1025 nextpg: 1025OAM pg cnt: 1 Entry cnt: 12Row count information is not maintained for index pages.Used pgs: 619 Unused pgs: 10Attribute entries: 10OAM status bits set: (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB))Cannot retrieve OAM attributes as OAM buffer ptr was not provided.OAM pg # 0: 1025 has the following 12 entries (allocpg:used/unused):
[ 0] 768: 8/ 0 1024: 7/ 0 1280: 8/ 0 2560: 56/ 0[ 4] 3584: 32/ 0 3840: 7/ 0 14592: 11/ 5 14848:119/ 0[ 8] 15104:136/ 0 30720: 64/ 0 30976: 56/ 0 32256:115/ 5
OFFSET TABLE:Offset table is not maintained for OAM pages.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
The 9th slot shows allocation page 30720 for 120 number allocation unit for the database dbccdb
Allocation page 30720 information for object id 832002964 ( dbcc_counters ):
1> dbcc page(31515,30720,1)2>Page 30720 is not in the range of pages for database id 31515DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>
Meaning this is not present at all!! But still the OAM page for the dbcc_counters object has its entry. Thus the OAM page for dbcc_counters table in dbccdb database is corrupt
Lets try adding a device to dbccdb database which will allocate more logical pages to the database as few days back we had run disk reinit and refit...
Did a disk init, altered the database to get the following segmap:
1> select * from master..sysusages where dbid = 315152> dbid segmap lstart size vstart pad unreservedpgs crdate vdevno ------ ----------- ----------- ----------- ----------- ------ ------------- -------------------------- ----------- 31515 3 0 15360 0 NULL 1959 Aug 5 2008 4:55PM 4 31515 4 15360 5120 0 NULL 5100 Aug 5 2008 4:55PM 5 31515 4 20480 10240 0 NULL 10200 Aug 5 2008 4:55PM 17 31515 3 30720 10240 0 NULL 10200 Aug 6 2008 11:35AM 3
1> sp_dboption 'dbccdb', 'single', true2>Database option 'single user' turned ON for database 'dbccdb'.Running CHECKPOINT on database 'dbccdb' for option 'single user' to take effect.(return status = 0)1> use dbccdb
1> checkpoint2>
Following errors were getting reported now after addition of the device:
A. TABLE: sysprocedures OBJID = 5PARTITION ID=5 FIRST=169 ROOT=169 SORT=0Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 30720 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [159, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 30976 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [160, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 31232 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [255, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 31488 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [255, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 31744 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [255, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 32000 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [255, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 32256 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [60, 3].There are 3 OAM entries with zero used/unused page counts. Run DBCC TABLEALLOC FIX on objid 5 indid 0.Msg 7940, Level 18, State 1:Line 1:The counts in the OAM are incorrect. This implies that there are entries missing. Run tablealloc utility with the FIX option on the table with the inaccurate OAM counts.
I corrected sysprocedures by running a full, fix:
1> dbcc tablealloc(sysprocedures,full,fix)2>***************************************************************TABLE: sysprocedures OBJID = 5PARTITION ID=5 FIRST=169 ROOT=169 SORT=010 OAM entries with zero used/unused page counts have been deleted for objid 5 indid 0.The oam counts for objid 5 indid 0 are corrected.Data level: indid 0, partition 5. 816 Data pages allocated and 232 Extents allocated.PARTITION ID=5 FIRST=193 ROOT=192 SORT=1Indid : 2, partition : 5. 56 Index pages allocated and 8 Extents allocated.TOTAL # of extents = 240Alloc page 0 (# of extent=3 used pages=18 ref pages=18)Alloc page 768 (# of extent=3 used pages=16 ref pages=16)Alloc page 1280 (# of extent=5 used pages=31 ref pages=31)Alloc page 1536 (# of extent=18 used pages=133 ref pages=133)Alloc page 1792 (# of extent=11 used pages=74 ref pages=74)Alloc page 2048 (# of extent=14 used pages=107 ref pages=107)Alloc page 2304 (# of extent=15 used pages=112 ref pages=112)Alloc page 2560 (# of extent=10 used pages=74 ref pages=74)Alloc page 2816 (# of extent=10 used pages=73 ref pages=73)Alloc page 3072 (# of extent=1 used pages=7 ref pages=7)Alloc page 3328 (# of extent=6 used pages=40 ref pages=40)Alloc page 3584 (# of extent=3 used pages=17 ref pages=17)Alloc page 3840 (# of extent=10 used pages=66 ref pages=66)Alloc page 14592 (# of extent=2 used pages=15 ref pages=15)Alloc page 14848 (# of extent=8 used pages=64 ref pages=64)Alloc page 15104 (# of extent=5 used pages=37 ref pages=37)Total (# of extent=124 used pages=884 ref pages=884) in this databaseDBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
B.TABLE: dbcc_dev_info OBJID = 784002793PARTITION ID=784002793 FIRST=889 ROOT=889 SORT=0Data level: indid 0, partition 784002793. 1 Data pages allocated and 1 Extents allocated.PARTITION ID=784002793 FIRST=985 ROOT=985 SORT=1Indid : 2, partition : 784002793. 1 Index pages allocated and 1 Extents allocated. Page linkage failed for OBJID 832002964 INDID 0 PARTITION 832002964; Processed 230 pages; Page linkage failed for OBJID 832002964 INDID 2 PARTITION 832002964; Processed 312 pages; Page linkage failed for OBJID 832002964 INDID 2 PARTITION 832002964; Processed 10 pages;TOTAL # of extents = 2
Dropped and re created non clustered index
C.
1> dbcc tablealloc('dbcc_counters',full,fix)2> Page linkage failed for OBJID 832002964 INDID 0 PARTITION 832002964; Processed 230 pages; Page linkage failed for OBJID 832002964 INDID 2 PARTITION 832002964; Processed 312 pages; Page linkage failed for OBJID 832002964 INDID 2 PARTITION 832002964; Processed 10 pages;***************************************************************TABLE: dbcc_counters OBJID = 832002964PARTITION ID=832002964 FIRST=897 ROOT=32500 SORT=1Msg 2529, Level 16, State 7:Line 1:Table Corrupt: Attempted to get page 30760, partition ID 832002964; got page 0, partition ID 0.PARTITION ID=832002964 FIRST=1027 ROOT=14777 SORT=0Msg 2529, Level 16, State 7:Line 1:Table Corrupt: Attempted to get page 30819, partition ID 832002964; got page 0, partition ID 0.Msg 2529, Level 16, State 7:Line 1:Table Corrupt: Attempted to get page 30821, partition ID 832002964; got page 0, partition ID 0.TOTAL # of extents = 0
Truncated the table and ran a full fix:
1> truncate table dbcc_counters
1> dbcc tablealloc(dbcc_counters, full, fix)2>***************************************************************TABLE: dbcc_counters OBJID = 832002964PARTITION ID=832002964 FIRST=897 ROOT=897 SORT=1Data level: indid 0, partition 832002964. 1 Data pages allocated and 1 Extents allocated.PARTITION ID=832002964 FIRST=14777 ROOT=14777 SORT=0Indid : 2, partition : 832002964. 1 Index pages allocated and 2 Extents allocated.TOTAL # of extents = 3Alloc page 768 (# of extent=1 used pages=2 ref pages=2)Alloc page 1024 (# of extent=1 used pages=2 ref pages=2)Alloc page 14592 (# of extent=1 used pages=1 ref pages=1)Total (# of extent=3 used pages=5 ref pages=5) in this databaseDBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Currently the allocation checks seem to be fine:
1> dbcc listoam(31515,dbcc_counters,0)2>-----------------------------------------------------------------------------Partition id: 832002964 indid: 0 prevpg: 896 nextpg: 896OAM pg cnt: 1 Entry cnt: 1Rows: 0 Rows Per pg: 0Used pgs: 2 Unused pgs: 6Attribute entries: 10OAM status bits set: (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB), 0x0004 (PG_OAMSORT))LAST SCANNED OAM PAGE: 0ALLOCATION HINTS : 896 0 0 0 0 0 0 0 0 0 0 0 0 0 0OAM pg # 1: 896 has the following 1 entry (allocpg:used/unused):
[ 0] 768: 2/ 6
---- End of OAM chain for partition 832002964 ----DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>
Keys of index id 2 for table 'sysattributes' in leaf page not in proper order. Drop and recreate the index. (index page 14833)Msg 7928, Level 18, State 1:Line 1:Index ncsysattributes is not consistent; found 2 leaf rows but 20 data rows. Drop and recreate the index.Checking table 'dbcc_types' (object ID 752002679): Logical page size is 2048 bytes.
1> sp_fixindex dbccdb, sysattributes,22>00:00000:00012:2008/08/06 12:52:40.59 server dbcc dbrepair: Non-clustered index restored for dbccdb..sysattributes.There are 20.000000 rows in 1 pages for object 'sysattributes'.Non-clustered index successfully restored for object 'sysattributes' in 'dbccdb' database.DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.(return status = 0)1>
1> dbcc checktable(sysattributes)2>Checking table 'sysattributes' (object ID 21): Logical page size is 2048 bytes.
Checking partition 'sysattributes_21' (partition ID 21) of table 'sysattributes'. The logical page size of this table is 2048 bytes.The total number of data pages in partition 'sysattributes_21' (partition ID 21) is 1.Partition 'sysattributes_21' (partition ID 21) has 20 data rows.The total number of empty pages (with all deleted rows) in patition 'sysattributes_21' (partition ID 21) is 0.The total number of pages in partition 'sysattributes_21' (partition ID 21) which could be garbage collected to free up some space is 0.The total number of deleted rows in partition 'sysattributes_21' (partition ID 21) is 0.The total number of pages in partition 'sysattributes_21' (partition ID 21) with more than 50 percent garbage is 0.The total number of pages in partition 'sysattributes_21' (partition ID 21) with more than 50 percent insert free space is 1.
The total number of data pages in this table is 1.The total number of pages with more than 50 percent insert free space is 1.Table has 20 data rows.DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>
Msg 692, Level 16, State 1:Line 1:Uninitialized logical page '30957' was read while accessing database 'dbccdb' (31515), object 'sysprocedures' (5), index 'sysprocedures' (0), partition 'sysprocedures_5' (5). Please contact Sybase Technical Support.
1> sp_fixindex dbccdb, sysprocedures,22>00:00000:00012:2008/08/06 13:08:14.56 server dbcc dbrepair: Clustered index restored for dbccdb..sysprocedures.There are 1628.000000 rows in 816 pages for object 'sysprocedures'.Clustered index successfully restored for object 'sysprocedures' in 'dbccdb' database.DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.(return status = 0)1> dbcc checktable(sysprocedures)2>Checking table 'sysprocedures' (object ID 5): Logical page size is 2048 bytes.
Checking partition 'sysprocedures_5' (partition ID 5) of table 'sysprocedures'. The logical page size of this table is 2048 bytes.The total number of data pages in partition 'sysprocedures_5' (partition ID 5) is 814.Partition 'sysprocedures_5' (partition ID 5) has 1628 data rows.The total number of empty pages (with all deleted rows) in patition 'sysprocedures_5' (partition ID 5) is 0.The total number of pages in partition 'sysprocedures_5' (partition ID 5) which could be garbage collected to free up some space is 0.The total number of deleted rows in partition 'sysprocedures_5' (partition ID 5) is 0.The total number of pages in partition 'sysprocedures_5' (partition ID 5) with more than 50 percent garbage is 0.The total number of pages in partition 'sysprocedures_5' (partition ID 5) with more than 50 percent insert free space is 0.
The total number of data pages in this table is 814.Table has 1628 data rows.DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>
Fixed checkcatalog errors:
CHECKCATALOG ERRORS:dbcc checkcatalog
Table Corrupt: Object id 1296004617 (object name = sp_dbcc_run_summaryreport) does not match between sysprocedures and sysobjectsMsg 2513, Level 16, State 6:Line 1:Table Corrupt: Object id 1312004674 (object name = sp_dbcc_run_runcheck) does not match between sysprocedures and sysobjectsMsg 2513, Level 16, State 6:Line 1:Table Corrupt: Object id 1328004731 (object name = sp_dbcc_run_statisticsreport) does not match between sysprocedures and sysobjects
dropped and recreated the procedures as the sysprocedures table did not have entry for them
1> dbcc checkcatalog2>Checking current database: Logical pagesize is 2048 bytesThe following segments have been defined for database 31515 (database name dbccdb).virtual device number virtual start addr size (logical pages) segments--------------------- -------------------- ---------------------- --------------------4 0 15360 0 15 0 5120 217 0 10240 23 0 10240 0 1DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>
Fixed checkalloc errors
Can you advise for the following output from dbcc tablealloc for a particular table dbcc_counters as well as others.
Could there have been some other way to fix this
dbcc tablealloc(dbcc_counters, full, fix)2>00:00000:00012:2008/08/05 17:00:27.66 server Error: 1161, Severity: 21, State: 100:00000:00012:2008/08/05 17:00:27.66 server Invalid OAM entry found at slot 9 in OAM page 1025 for table ID 832002964 with index ID 2 in database 'dbccdb' (ID 31515). This entry is either out of order or its allocation page number is wrong.***************************************************************TABLE: dbcc_counters OBJID = 832002964Msg 2523, Level 16, State 3:Line 1:Table Corrupt: Page number 32500 is out of range for this database dbccdb. The maximum page number in this database is 30719PARTITION ID=832002964 FIRST=1027 ROOT=14777 SORT=0Msg 1161, Level 21, State 1:Line 1:Invalid OAM entry found at slot 9 in OAM page 1025 for table ID 832002964 with index ID 2 in database 'dbccdb' (ID 31515). This entry is either out of order or its allocation page number is wrong.TOTAL # of extents = 0
It looks like page number 32500 is getting associated in dbccdb database while the segmap shows that the last page for dbccdb is ( 20480 + 10240 - 1 ) = 30719
1> select * from master..sysusages where dbid = 315152> dbid segmap lstart size vstart pad unreservedpgs crdate vdevno ------ ----------- ----------- ----------- ----------- ------ ------------- -------------------------- ----------- 31515 3 0 15360 0 NULL 1959 Aug 5 2008 4:55PM 4 31515 4 15360 5120 0 NULL 5100 Aug 5 2008 4:55PM 5 31515 4 20480 10240 0 NULL 10200 Aug 5 2008 4:55PM 17
The OAM page 1025 has following information:
1> dbcc page(31515,1025,1)2>
Page found in Cache: default data cache. Cachelet: 1
BUFFER:Buffer header for buffer 0x20a6ff800 page=0x20a6ff000 bdnew=0x210f15cf8 bdold=0x210f15cf8 bhash=0x0 bmass_next=0x210f2f7e0 bmass_prev=0x2115946b8 bdbid=31515 bvirtpg= [ 0x20a6ff8b8 vpgdevno=4 vpvpn=1025 vdisk=0x203c05c30 ] bmass_head=0x210f15cf8 bmass_tail=0x210f15cf8 bcache_desc=0x20a703650 (cache name='default data cache') bpool_desc=0x20a706dc8 bdbtable=0x202a0e180 Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0 bmass_size=2048 (2K pool) bunref_cnt=0 bmass_stat=0x4001010 (0x04000000 (MASS_DONT_DISCARD), 0x00001000 (MASS_HASHED), 0x00000010 (MASS_INWASH)) bbuf_stat=0x1 (0x00000001 (BUF_PG_REF)) Buffer blpageno=1025 bpg_size=2k Mass blpageno=1025 (Buffer slot #: 0) bxls_pin=0x0 bxls_next=0x0 bspid=0 bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x0Latch and the wait queue:Latch (address: 0x20a6ff830) latchmode: 0x0 (FREE_LATCH) latchowner: -1 latchnoofowners: 0 latchwaitq: 0x0 latchwaitqt: 0x0
Latch wait queue:
PAGE HEADER:Page header for page 0x20a6ff000pageno=1025 nextpg=1025 prevpg=1025 ptnid=832002964 timestamp=0000 0003379foampgcount=1 attrcount=10 indid=2 totalentries_lo=12 entrycount=12page status bits: 0x8008 (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB))
DATA:-----------------------------------------------------------------------------Partition id: 832002964 indid: 2 prevpg: 1025 nextpg: 1025OAM pg cnt: 1 Entry cnt: 12Row count information is not maintained for index pages.Used pgs: 619 Unused pgs: 10Attribute entries: 10OAM status bits set: (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB))Cannot retrieve OAM attributes as OAM buffer ptr was not provided.OAM pg # 0: 1025 has the following 12 entries (allocpg:used/unused):
[ 0] 768: 8/ 0 1024: 7/ 0 1280: 8/ 0 2560: 56/ 0[ 4] 3584: 32/ 0 3840: 7/ 0 14592: 11/ 5 14848:119/ 0[ 8] 15104:136/ 0 30720: 64/ 0 30976: 56/ 0 32256:115/ 5
OFFSET TABLE:Offset table is not maintained for OAM pages.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
The 9th slot shows allocation page 30720 for 120 number allocation unit for the database dbccdb
Allocation page 30720 information for object id 832002964 ( dbcc_counters ):
1> dbcc page(31515,30720,1)2>Page 30720 is not in the range of pages for database id 31515DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>
Meaning this is not present at all!! But still the OAM page for the dbcc_counters object has its entry. Thus the OAM page for dbcc_counters table in dbccdb database is corrupt
Lets try adding a device to dbccdb database which will allocate more logical pages to the database as few days back we had run disk reinit and refit...
Did a disk init, altered the database to get the following segmap:
1> select * from master..sysusages where dbid = 315152> dbid segmap lstart size vstart pad unreservedpgs crdate vdevno ------ ----------- ----------- ----------- ----------- ------ ------------- -------------------------- ----------- 31515 3 0 15360 0 NULL 1959 Aug 5 2008 4:55PM 4 31515 4 15360 5120 0 NULL 5100 Aug 5 2008 4:55PM 5 31515 4 20480 10240 0 NULL 10200 Aug 5 2008 4:55PM 17 31515 3 30720 10240 0 NULL 10200 Aug 6 2008 11:35AM 3
1> sp_dboption 'dbccdb', 'single', true2>Database option 'single user' turned ON for database 'dbccdb'.Running CHECKPOINT on database 'dbccdb' for option 'single user' to take effect.(return status = 0)1> use dbccdb
1> checkpoint2>
Following errors were getting reported now after addition of the device:
A. TABLE: sysprocedures OBJID = 5PARTITION ID=5 FIRST=169 ROOT=169 SORT=0Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 30720 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [159, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 30976 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [160, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 31232 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [255, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 31488 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [255, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 31744 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [255, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 32000 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [255, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 32256 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [60, 3].There are 3 OAM entries with zero used/unused page counts. Run DBCC TABLEALLOC FIX on objid 5 indid 0.Msg 7940, Level 18, State 1:Line 1:The counts in the OAM are incorrect. This implies that there are entries missing. Run tablealloc utility with the FIX option on the table with the inaccurate OAM counts.
I corrected sysprocedures by running a full, fix:
1> dbcc tablealloc(sysprocedures,full,fix)2>***************************************************************TABLE: sysprocedures OBJID = 5PARTITION ID=5 FIRST=169 ROOT=169 SORT=010 OAM entries with zero used/unused page counts have been deleted for objid 5 indid 0.The oam counts for objid 5 indid 0 are corrected.Data level: indid 0, partition 5. 816 Data pages allocated and 232 Extents allocated.PARTITION ID=5 FIRST=193 ROOT=192 SORT=1Indid : 2, partition : 5. 56 Index pages allocated and 8 Extents allocated.TOTAL # of extents = 240Alloc page 0 (# of extent=3 used pages=18 ref pages=18)Alloc page 768 (# of extent=3 used pages=16 ref pages=16)Alloc page 1280 (# of extent=5 used pages=31 ref pages=31)Alloc page 1536 (# of extent=18 used pages=133 ref pages=133)Alloc page 1792 (# of extent=11 used pages=74 ref pages=74)Alloc page 2048 (# of extent=14 used pages=107 ref pages=107)Alloc page 2304 (# of extent=15 used pages=112 ref pages=112)Alloc page 2560 (# of extent=10 used pages=74 ref pages=74)Alloc page 2816 (# of extent=10 used pages=73 ref pages=73)Alloc page 3072 (# of extent=1 used pages=7 ref pages=7)Alloc page 3328 (# of extent=6 used pages=40 ref pages=40)Alloc page 3584 (# of extent=3 used pages=17 ref pages=17)Alloc page 3840 (# of extent=10 used pages=66 ref pages=66)Alloc page 14592 (# of extent=2 used pages=15 ref pages=15)Alloc page 14848 (# of extent=8 used pages=64 ref pages=64)Alloc page 15104 (# of extent=5 used pages=37 ref pages=37)Total (# of extent=124 used pages=884 ref pages=884) in this databaseDBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
B.TABLE: dbcc_dev_info OBJID = 784002793PARTITION ID=784002793 FIRST=889 ROOT=889 SORT=0Data level: indid 0, partition 784002793. 1 Data pages allocated and 1 Extents allocated.PARTITION ID=784002793 FIRST=985 ROOT=985 SORT=1Indid : 2, partition : 784002793. 1 Index pages allocated and 1 Extents allocated. Page linkage failed for OBJID 832002964 INDID 0 PARTITION 832002964; Processed 230 pages; Page linkage failed for OBJID 832002964 INDID 2 PARTITION 832002964; Processed 312 pages; Page linkage failed for OBJID 832002964 INDID 2 PARTITION 832002964; Processed 10 pages;TOTAL # of extents = 2
Dropped and re created non clustered index
C.
1> dbcc tablealloc('dbcc_counters',full,fix)2> Page linkage failed for OBJID 832002964 INDID 0 PARTITION 832002964; Processed 230 pages; Page linkage failed for OBJID 832002964 INDID 2 PARTITION 832002964; Processed 312 pages; Page linkage failed for OBJID 832002964 INDID 2 PARTITION 832002964; Processed 10 pages;***************************************************************TABLE: dbcc_counters OBJID = 832002964PARTITION ID=832002964 FIRST=897 ROOT=32500 SORT=1Msg 2529, Level 16, State 7:Line 1:Table Corrupt: Attempted to get page 30760, partition ID 832002964; got page 0, partition ID 0.PARTITION ID=832002964 FIRST=1027 ROOT=14777 SORT=0Msg 2529, Level 16, State 7:Line 1:Table Corrupt: Attempted to get page 30819, partition ID 832002964; got page 0, partition ID 0.Msg 2529, Level 16, State 7:Line 1:Table Corrupt: Attempted to get page 30821, partition ID 832002964; got page 0, partition ID 0.TOTAL # of extents = 0
Truncated the table and ran a full fix:
1> truncate table dbcc_counters
1> dbcc tablealloc(dbcc_counters, full, fix)2>***************************************************************TABLE: dbcc_counters OBJID = 832002964PARTITION ID=832002964 FIRST=897 ROOT=897 SORT=1Data level: indid 0, partition 832002964. 1 Data pages allocated and 1 Extents allocated.PARTITION ID=832002964 FIRST=14777 ROOT=14777 SORT=0Indid : 2, partition : 832002964. 1 Index pages allocated and 2 Extents allocated.TOTAL # of extents = 3Alloc page 768 (# of extent=1 used pages=2 ref pages=2)Alloc page 1024 (# of extent=1 used pages=2 ref pages=2)Alloc page 14592 (# of extent=1 used pages=1 ref pages=1)Total (# of extent=3 used pages=5 ref pages=5) in this databaseDBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Currently the allocation checks seem to be fine:
1> dbcc listoam(31515,dbcc_counters,0)2>-----------------------------------------------------------------------------Partition id: 832002964 indid: 0 prevpg: 896 nextpg: 896OAM pg cnt: 1 Entry cnt: 1Rows: 0 Rows Per pg: 0Used pgs: 2 Unused pgs: 6Attribute entries: 10OAM status bits set: (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB), 0x0004 (PG_OAMSORT))LAST SCANNED OAM PAGE: 0ALLOCATION HINTS : 896 0 0 0 0 0 0 0 0 0 0 0 0 0 0OAM pg # 1: 896 has the following 1 entry (allocpg:used/unused):
[ 0] 768: 2/ 6
---- End of OAM chain for partition 832002964 ----DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>
Wednesday 30 December 2009
DataChange Function to check update statistics in ASE 15
1> update statistics test1.tab1
2>
1> select datachange('test1.tab1',null,'name')
2>
---------------------------
128.571429
(1 row affected)
1> update all statistics test1.tab1
2>
1> select datachange('test1.tab1',null,'name')
2>
---------------------------
0.000000
(1 row affected)
2>
1> select datachange('test1.tab1',null,'name')
2>
---------------------------
128.571429
(1 row affected)
1> update all statistics test1.tab1
2>
1> select datachange('test1.tab1',null,'name')
2>
---------------------------
0.000000
(1 row affected)
Row Level Access Rules example
1> sp_configure "enable row level access", 1
2>
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
enable row level access 0 0 1 1 switch dynamic
(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is dynamic.
Changing the value of 'enable row level access' does not increase the amount of memory Adaptive Server uses.
(return status = 0)
1>
ACCESS RULES:
1> create table tstudent ( name varchar(10), id int )
2>
1> insert into tstudent values ( 'sysadmin', 1234)
2>
….
….
1> select * from tstudent
2>
name id
---------- -----------
sysadmin 1234
sa 3456
test 34567
sa 34566
1> create access rule rule_student as @name = suser_name()
1> sp_bindrule rule_student,"tstudent.name"
2>
Rule bound to table column.
(return status = 0)
1> select suser_name()
2>
------------------------------
sa
(1 row affected)
1> select * from tstudent
2>
name id
---------- -----------
sa 3456
sa 34566
(2 rows affected)
1> sp_unbindrule "tstudent.name",NULL, 'all'
2>
Rule unbound from table column.
(return status = 0)
1> select * from tstudent
2>
name id
---------- -----------
sysadmin 1234
sa 3456
test 34567
sa 34566
(7 rows affected)
1> drop rule rule_student
2>
1>
2>
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
enable row level access 0 0 1 1 switch dynamic
(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is dynamic.
Changing the value of 'enable row level access' does not increase the amount of memory Adaptive Server uses.
(return status = 0)
1>
ACCESS RULES:
1> create table tstudent ( name varchar(10), id int )
2>
1> insert into tstudent values ( 'sysadmin', 1234)
2>
….
….
1> select * from tstudent
2>
name id
---------- -----------
sysadmin 1234
sa 3456
test 34567
sa 34566
1> create access rule rule_student as @name = suser_name()
1> sp_bindrule rule_student,"tstudent.name"
2>
Rule bound to table column.
(return status = 0)
1> select suser_name()
2>
------------------------------
sa
(1 row affected)
1> select * from tstudent
2>
name id
---------- -----------
sa 3456
sa 34566
(2 rows affected)
1> sp_unbindrule "tstudent.name",NULL, 'all'
2>
Rule unbound from table column.
(return status = 0)
1> select * from tstudent
2>
name id
---------- -----------
sysadmin 1234
sa 3456
test 34567
sa 34566
(7 rows affected)
1> drop rule rule_student
2>
1>
DBCC Database Installation and configuration
Installing DBCCDB database
Steps 1
1> sp_plan_dbccdb
2>
Recommended size for dbccdb database is 18MB (data = 16MB, log = 2MB).
Recommended devices for dbccdb are:
Logical Device Name Device Size (KB)
------------------------------ ----------------
systemdbdev 3072
dev_pm_log01 5120
dev_default 20480
dbcc_dev01 30720
dbcc_log_dev01 10240
Recommended values for workspace size, cache size and process count are:
dbname scan ws text ws cache process count
pm 144K 48K 640K 1
master 1104K 288K 640K 1
model 64K 48K 640K 1
tempdb 80K 48K 640K 1
sybsystemdb 64K 48K 640K 1
sybsystemprocs 1488K 384K 640K 1
sag 272K 80K 1280K 2
1> disk init
2> name = 'dbcc_dev01',
3> physname = '/OracleDumps/opctlsy2/sybase/data/dbccdb.dat'
4> , size = '30M'
5>
1> disk init
2> name = 'dbcc_log_dev01',
3> physname = '/OracleDumps/opctlsy2/sybase/data/dbccdb_log.dat'
4> , size = '10M'
5>
1> create database dbccdb on dbcc_dev01=30 log on dbcc_log_dev01=10
2>
CREATE DATABASE: allocating 15360 logical pages (30.0 megabytes) on disk 'dbcc_dev01'.
CREATE DATABASE: allocating 5120 logical pages (10.0 megabytes) on disk 'dbcc_log_dev01'.
1>
1> use dbccdb
2>
1> sp_addsegment scanseg, dbccdb, dbcc_dev01
2>
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Segment created.
(return status = 0)
1> sp_addsegment textseg,dbccdb, dbcc_dev01
2>
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Segment created.
(return status = 0)
1>
Install dbccdb tables:
aloncvc0402:opctlsy2 >pwd
/OracleDumps/opctlsy2/sybase/ASE-15_0/scripts
aloncvc0402:opctlsy2 >isql -U sa -SGALLARDO -iinstalldbccdb
Database option 'select into/bulkcopy/pllsort' turned ON for database 'dbccdb'.
Running CHECKPOINT on database 'dbccdb' for option 'select
into/bulkcopy/pllsort' to take effect.
Creating dbcc_types table
Object name has been changed.
Creating dbcc_config table
Object name has been changed.
Creating dbcc_dev_info table
Object name has been changed.
Creating dbcc_operation_log table
Object name has been changed.
Creating dbcc_operation_results table
Object name has been changed.
Creating dbcc_counters table
Object name has been changed.
Creating dbcc_faults table
Object name has been changed.
Creating dbcc_fault_params table
Object name has been changed.
Creating dbcc_exclusions table
Inserting default exclusions into the dbcc_exclusions table
Object name has been changed.
Creating dbcc_reco table
Object name has been changed.
Creating dbcc_control table
Object name has been changed.
Inserting type codes into dbcc_types table
Inserting recommendation codes into the dbcc_reco table.
Upgrading dbcc_operation_log schema
Upgrading dbcc_operation_results schema
Upgrading dbcc_faults schema
Upgrading dbcc_operation_results schema
Upgrading dbcc_operation_log schema
Upgrading dbcc_counters schema
Upgrading dbcc_faults schema
Upgrading default exclusion list
Creating index on dbcc_types table
Creating index on dbcc_config table
Creating index on dbcc_dev_info table
Creating index on dbcc_operation_log table
Creating index on dbcc_operation_results table
Creating index on dbcc_counters table
Creating index on dbcc_faults table
Creating index on dbcc_fault_params table
Creating index on dbcc_exclusions table
Creating index on dbcc_reco table
Creating default workspaces
Configuring default workspaces
Installing sp_validatedb
Installing sp_getopid
Installing sp_dbcc_run_recommendations
Installing sp_dbcc_run_alterws
Installing sp_dbcc_run_createws
Installing sp_dbcc_run_configreport
Installing sp_dbcc_run_deletehistory
Installing sp_dbcc_run_deletedb
Installing sp_dbcc_run_evaluatedb
Installing sp_dbcc_run_updateconfig
Installing sp_dbcc_run_summaryreport
Installing sp_dbcc_run_runcheck
Installing sp_dbcc_run_statisticsreport
Installing sp_dbcc_run_faultreport
Installing sp_dbcc_run_differentialreport
Installing sp_dbcc_run_fullreport
Installing sp_dbcc_run_patch_finishtime
Installing sp_dbcc_run_exclusions
Installing dbcc stored procedures.
Installing sp_dbcc_recommendations
Installing sp_dbcc_patch_finishtime
Installing sp_dbcc_exclusions
Installing sp_dbcc_alterws
Installing sp_dbcc_createws
Installing sp_dbcc_configreport
Installing sp_dbcc_deletehistory
Installing sp_dbcc_deletedb
Installing sp_dbcc_evaluatedb
Installing sp_dbcc_updateconfig
Installing sp_dbcc_summaryreport
Installing sp_dbcc_runcheck
Installing sp_dbcc_statisticsreport
Installing sp_dbcc_faultreport
Installing sp_dbcc_differentialreport
Installing sp_dbcc_fullreport
Installing sp_dbcc_help_fault
Loading of dbccdb database is complete.
(return status = 0)
Setting up work tables:
1> sp_helpdb
2>
name db_size owner dbid created status
-------------- ------------- ----- ------ ------------------ --------------------------------------------------------------------
dbccdb 40.0 MB sa 31515 Feb 20, 2008 select into/bulkcopy/pllsort
master 89.5 MB sa 1 Feb 19, 2008 mixed log and data
model 3.0 MB sa 3 Feb 19, 2008 mixed log and data
pm 10.0 MB sa 4 Feb 19, 2008 mixed log and data
sag 20.0 MB sa 5 Feb 19, 2008 abort tran on log full
sybsystemdb 3.0 MB sa 31513 Feb 19, 2008 mixed log and data
sybsystemprocs 120.0 MB sa 31514 Feb 19, 2008 trunc log on chkpt, mixed log and data
tempdb 4.0 MB sa 2 Feb 20, 2008 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
(1 row affected)
(return status = 0)
1> use dbccdb
2>
1> sp_plan_dbccdb
2>
Recommended size for dbccdb database is 21MB (data = 19MB, log = 2MB).
dbccdb database already exists with size 40MB.
Recommended values for workspace size, cache size and process count are:
dbname scan ws text ws cache process count
dbccdb 496K 128K 1280K 2
pm 144K 48K 640K 1
master 1104K 288K 640K 1
model 64K 48K 640K 1
tempdb 80K 48K 640K 1
sybsystemdb 64K 48K 640K 1
sybsystemprocs 1488K 384K 640K 1
sag 272K 80K 1280K 2
1> sp_dbcc_createws dbccdb,scanseg,scanws,scan,"1M"
2>
(1 row affected)
Workspace scanws of 1024KB size has been created successfully in dbccdb database.
(return status = 0)
1> sp_dbcc_createws dbccdb,textseg,textws,text,"1M"
2>
(1 row affected)
Workspace textws of 1024KB size has been created successfully in dbccdb database.
(return status = 0)
1>
DBCC Configuration parametrs:
1> sp_dbcc_configreport
2>
Reporting default DBCC configuration information.
Parameter Name Value Size
database name DEFAULT_VALUES
scan workspace def$scan$ws (id = 1088003876) 256K
text workspace def$text$ws (id = 1120003990) 128K
(return status = 0)
1>
sp_cacheconfig "dbcc data cache","4M"
sp_poolconfig "dbcc data cache","3M","16K"
1> use master
2>
1> sp_bindcache "dbcc data cache", dbccdb
1> sp_helpcache
2>
Cache Name Config Size Run Size Overhead
------------------ ----------- ---------- ----------
dbcc data cache 4.00 Mb 4.00 Mb 0.52 Mb
default data cache 0.00 Mb 8.00 Mb 0.94 Mb
(2 rows affected)
Memory Available For Memory Configured
Named Caches To Named Caches
-------------------- ----------------
12.01 Mb 4.00 Mb
------------------ Cache Binding Information: ------------------
Cache Name Entity Name Type Index Name Status
---------- ----------- ---- ---------- ------
dbcc data cache dbccdb database V
use dbccdb
sp_dbcc_updateconfig null,"dbcc named cache","dbcc data cache","4M"
sp_dbcc_updateconfig null,"number of woeker processes" , 4
Steps 1
1> sp_plan_dbccdb
2>
Recommended size for dbccdb database is 18MB (data = 16MB, log = 2MB).
Recommended devices for dbccdb are:
Logical Device Name Device Size (KB)
------------------------------ ----------------
systemdbdev 3072
dev_pm_log01 5120
dev_default 20480
dbcc_dev01 30720
dbcc_log_dev01 10240
Recommended values for workspace size, cache size and process count are:
dbname scan ws text ws cache process count
pm 144K 48K 640K 1
master 1104K 288K 640K 1
model 64K 48K 640K 1
tempdb 80K 48K 640K 1
sybsystemdb 64K 48K 640K 1
sybsystemprocs 1488K 384K 640K 1
sag 272K 80K 1280K 2
1> disk init
2> name = 'dbcc_dev01',
3> physname = '/OracleDumps/opctlsy2/sybase/data/dbccdb.dat'
4> , size = '30M'
5>
1> disk init
2> name = 'dbcc_log_dev01',
3> physname = '/OracleDumps/opctlsy2/sybase/data/dbccdb_log.dat'
4> , size = '10M'
5>
1> create database dbccdb on dbcc_dev01=30 log on dbcc_log_dev01=10
2>
CREATE DATABASE: allocating 15360 logical pages (30.0 megabytes) on disk 'dbcc_dev01'.
CREATE DATABASE: allocating 5120 logical pages (10.0 megabytes) on disk 'dbcc_log_dev01'.
1>
1> use dbccdb
2>
1> sp_addsegment scanseg, dbccdb, dbcc_dev01
2>
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Segment created.
(return status = 0)
1> sp_addsegment textseg,dbccdb, dbcc_dev01
2>
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Segment created.
(return status = 0)
1>
Install dbccdb tables:
aloncvc0402:opctlsy2 >pwd
/OracleDumps/opctlsy2/sybase/ASE-15_0/scripts
aloncvc0402:opctlsy2 >isql -U sa -SGALLARDO -iinstalldbccdb
Database option 'select into/bulkcopy/pllsort' turned ON for database 'dbccdb'.
Running CHECKPOINT on database 'dbccdb' for option 'select
into/bulkcopy/pllsort' to take effect.
Creating dbcc_types table
Object name has been changed.
Creating dbcc_config table
Object name has been changed.
Creating dbcc_dev_info table
Object name has been changed.
Creating dbcc_operation_log table
Object name has been changed.
Creating dbcc_operation_results table
Object name has been changed.
Creating dbcc_counters table
Object name has been changed.
Creating dbcc_faults table
Object name has been changed.
Creating dbcc_fault_params table
Object name has been changed.
Creating dbcc_exclusions table
Inserting default exclusions into the dbcc_exclusions table
Object name has been changed.
Creating dbcc_reco table
Object name has been changed.
Creating dbcc_control table
Object name has been changed.
Inserting type codes into dbcc_types table
Inserting recommendation codes into the dbcc_reco table.
Upgrading dbcc_operation_log schema
Upgrading dbcc_operation_results schema
Upgrading dbcc_faults schema
Upgrading dbcc_operation_results schema
Upgrading dbcc_operation_log schema
Upgrading dbcc_counters schema
Upgrading dbcc_faults schema
Upgrading default exclusion list
Creating index on dbcc_types table
Creating index on dbcc_config table
Creating index on dbcc_dev_info table
Creating index on dbcc_operation_log table
Creating index on dbcc_operation_results table
Creating index on dbcc_counters table
Creating index on dbcc_faults table
Creating index on dbcc_fault_params table
Creating index on dbcc_exclusions table
Creating index on dbcc_reco table
Creating default workspaces
Configuring default workspaces
Installing sp_validatedb
Installing sp_getopid
Installing sp_dbcc_run_recommendations
Installing sp_dbcc_run_alterws
Installing sp_dbcc_run_createws
Installing sp_dbcc_run_configreport
Installing sp_dbcc_run_deletehistory
Installing sp_dbcc_run_deletedb
Installing sp_dbcc_run_evaluatedb
Installing sp_dbcc_run_updateconfig
Installing sp_dbcc_run_summaryreport
Installing sp_dbcc_run_runcheck
Installing sp_dbcc_run_statisticsreport
Installing sp_dbcc_run_faultreport
Installing sp_dbcc_run_differentialreport
Installing sp_dbcc_run_fullreport
Installing sp_dbcc_run_patch_finishtime
Installing sp_dbcc_run_exclusions
Installing dbcc stored procedures.
Installing sp_dbcc_recommendations
Installing sp_dbcc_patch_finishtime
Installing sp_dbcc_exclusions
Installing sp_dbcc_alterws
Installing sp_dbcc_createws
Installing sp_dbcc_configreport
Installing sp_dbcc_deletehistory
Installing sp_dbcc_deletedb
Installing sp_dbcc_evaluatedb
Installing sp_dbcc_updateconfig
Installing sp_dbcc_summaryreport
Installing sp_dbcc_runcheck
Installing sp_dbcc_statisticsreport
Installing sp_dbcc_faultreport
Installing sp_dbcc_differentialreport
Installing sp_dbcc_fullreport
Installing sp_dbcc_help_fault
Loading of dbccdb database is complete.
(return status = 0)
Setting up work tables:
1> sp_helpdb
2>
name db_size owner dbid created status
-------------- ------------- ----- ------ ------------------ --------------------------------------------------------------------
dbccdb 40.0 MB sa 31515 Feb 20, 2008 select into/bulkcopy/pllsort
master 89.5 MB sa 1 Feb 19, 2008 mixed log and data
model 3.0 MB sa 3 Feb 19, 2008 mixed log and data
pm 10.0 MB sa 4 Feb 19, 2008 mixed log and data
sag 20.0 MB sa 5 Feb 19, 2008 abort tran on log full
sybsystemdb 3.0 MB sa 31513 Feb 19, 2008 mixed log and data
sybsystemprocs 120.0 MB sa 31514 Feb 19, 2008 trunc log on chkpt, mixed log and data
tempdb 4.0 MB sa 2 Feb 20, 2008 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
(1 row affected)
(return status = 0)
1> use dbccdb
2>
1> sp_plan_dbccdb
2>
Recommended size for dbccdb database is 21MB (data = 19MB, log = 2MB).
dbccdb database already exists with size 40MB.
Recommended values for workspace size, cache size and process count are:
dbname scan ws text ws cache process count
dbccdb 496K 128K 1280K 2
pm 144K 48K 640K 1
master 1104K 288K 640K 1
model 64K 48K 640K 1
tempdb 80K 48K 640K 1
sybsystemdb 64K 48K 640K 1
sybsystemprocs 1488K 384K 640K 1
sag 272K 80K 1280K 2
1> sp_dbcc_createws dbccdb,scanseg,scanws,scan,"1M"
2>
(1 row affected)
Workspace scanws of 1024KB size has been created successfully in dbccdb database.
(return status = 0)
1> sp_dbcc_createws dbccdb,textseg,textws,text,"1M"
2>
(1 row affected)
Workspace textws of 1024KB size has been created successfully in dbccdb database.
(return status = 0)
1>
DBCC Configuration parametrs:
1> sp_dbcc_configreport
2>
Reporting default DBCC configuration information.
Parameter Name Value Size
database name DEFAULT_VALUES
scan workspace def$scan$ws (id = 1088003876) 256K
text workspace def$text$ws (id = 1120003990) 128K
(return status = 0)
1>
sp_cacheconfig "dbcc data cache","4M"
sp_poolconfig "dbcc data cache","3M","16K"
1> use master
2>
1> sp_bindcache "dbcc data cache", dbccdb
1> sp_helpcache
2>
Cache Name Config Size Run Size Overhead
------------------ ----------- ---------- ----------
dbcc data cache 4.00 Mb 4.00 Mb 0.52 Mb
default data cache 0.00 Mb 8.00 Mb 0.94 Mb
(2 rows affected)
Memory Available For Memory Configured
Named Caches To Named Caches
-------------------- ----------------
12.01 Mb 4.00 Mb
------------------ Cache Binding Information: ------------------
Cache Name Entity Name Type Index Name Status
---------- ----------- ---- ---------- ------
dbcc data cache dbccdb database V
use dbccdb
sp_dbcc_updateconfig null,"dbcc named cache","dbcc data cache","4M"
sp_dbcc_updateconfig null,"number of woeker processes" , 4
SQL to check syslogs record types
1> select op,case op
2> when 17 then 'CHECKPOINT'
3> when 4 then 'INSERT'
4> when 30 then 'ENDXACT-commit'
5> when 0 then 'BEGINXACT-begin tran'
6> when 9 then 'MODIFY-apl update'
when 26 then 'CLR-rollback'
7> 8> when 5 then 'DELETE'
when 77 then 'DROPEXTSMAP-truncate table'
9> 10> when 65 then 'DOL_UPDATE'
11> when 54 then 'LOGDEALLOC'
12> when 25 then 'PGCHGSYSIND'
13> when 14 then 'DBNEXTID'
when 7 then 'IINSERT'
14> 15> when 41 then 'OAMCREATE'
when 39 then 'OAMINSERT'
16> 17> when 13 then 'ALLOC'
18> when 63 then 'DOL_INSERT'
19> when 71 then 'BT_INSERT'
20> when 12 then 'DNOOP'
21> when 8 then 'IDELETE'
22> when 66 then 'DOL_DELETE'
23> when 51 then 'OAMENTRYMOVE'
24> when 52 then 'OAMATPUT'
25> end as operation,
26> numop=count(*) from syslogs group by op
27>
op operation numop
--- -------------------------- -----------
72 NULL 26
4 INSERT 2
17 CHECKPOINT 1
9 MODIFY-apl update 7
71 BT_INSERT 34
14 DBNEXTID 2
65 DOL_UPDATE 13
0 BEGINXACT-begin tran 18
54 LOGDEALLOC 1
30 ENDXACT-commit 18
63 DOL_INSERT 4
2> when 17 then 'CHECKPOINT'
3> when 4 then 'INSERT'
4> when 30 then 'ENDXACT-commit'
5> when 0 then 'BEGINXACT-begin tran'
6> when 9 then 'MODIFY-apl update'
when 26 then 'CLR-rollback'
7> 8> when 5 then 'DELETE'
when 77 then 'DROPEXTSMAP-truncate table'
9> 10> when 65 then 'DOL_UPDATE'
11> when 54 then 'LOGDEALLOC'
12> when 25 then 'PGCHGSYSIND'
13> when 14 then 'DBNEXTID'
when 7 then 'IINSERT'
14> 15> when 41 then 'OAMCREATE'
when 39 then 'OAMINSERT'
16> 17> when 13 then 'ALLOC'
18> when 63 then 'DOL_INSERT'
19> when 71 then 'BT_INSERT'
20> when 12 then 'DNOOP'
21> when 8 then 'IDELETE'
22> when 66 then 'DOL_DELETE'
23> when 51 then 'OAMENTRYMOVE'
24> when 52 then 'OAMATPUT'
25> end as operation,
26> numop=count(*) from syslogs group by op
27>
op operation numop
--- -------------------------- -----------
72 NULL 26
4 INSERT 2
17 CHECKPOINT 1
9 MODIFY-apl update 7
71 BT_INSERT 34
14 DBNEXTID 2
65 DOL_UPDATE 13
0 BEGINXACT-begin tran 18
54 LOGDEALLOC 1
30 ENDXACT-commit 18
63 DOL_INSERT 4
Understanding Sybase Page Architecture
1. The databases storage are divided into allocation units. Allocation units are storages comprising of 256 data pages. Thus meaning that there will be one row of one extent ( 8 pages / row ). Thus giving us a total of 32 rows in an allocation unit. Each row has 8 pages or 1 extent. An object has minimum space allocated as one extent
2. The first page of each allocation unit ( 0, 256 …numbers ) are the allocation pages for the allocation unit which contain information of the objects and respective pages allocated to the objects which are having pages allocated in that allocation unit.
3. The GAM is the global allocation map page which is containing bitmap of the allocation units per database. If an allocation unit is having no free extent then it will show bitmap value 1.
4. The OAM is the object allocation map page which is the first page of the allocated pages for an object and this page basically points to the allocation pages which contain the objects page allocation information. Thus this basically points to the allocation unit's allocation page which has its information about page alllocation.
Thus to conclude 1 allocation unit - 32 rows --8 pages per row -- 1 extent per row. Page(0), Page(256) , Page (512) are the allocation pages. These are referenced by OAM pages of the objects which actually have pages in the allocation units.
Suppose an object is stored in 3 extents. 2 extents in allocation unit 1 and 1 extent in allocation unit 2. Then Allocation page of AU1 and AU2 will contain information of the object id and the extents occupied in the respective allocation units. Thus the OAM of the object will be making references to allocation page of AU1 and AU2.
2. The first page of each allocation unit ( 0, 256 …numbers ) are the allocation pages for the allocation unit which contain information of the objects and respective pages allocated to the objects which are having pages allocated in that allocation unit.
3. The GAM is the global allocation map page which is containing bitmap of the allocation units per database. If an allocation unit is having no free extent then it will show bitmap value 1.
4. The OAM is the object allocation map page which is the first page of the allocated pages for an object and this page basically points to the allocation pages which contain the objects page allocation information. Thus this basically points to the allocation unit's allocation page which has its information about page alllocation.
Thus to conclude 1 allocation unit - 32 rows --8 pages per row -- 1 extent per row. Page(0), Page(256) , Page (512) are the allocation pages. These are referenced by OAM pages of the objects which actually have pages in the allocation units.
Suppose an object is stored in 3 extents. 2 extents in allocation unit 1 and 1 extent in allocation unit 2. Then Allocation page of AU1 and AU2 will contain information of the object id and the extents occupied in the respective allocation units. Thus the OAM of the object will be making references to allocation page of AU1 and AU2.
Sybase Fragmentation Report SQL
From below sqls , we can have a very good idea about the fragmentation going on in Sybase user tables. Based on the reports you can think of reorg, rebuilds, etc.
ASE 12.5 Compatible
select object_name(i.id) AS OBJECT_NAME, i.name AS INDEXNAME,CASE WHEN convert(varchar(8),lockscheme(i.id)) = "allpages" THEN "APL"WHEN convert(varchar(8),lockscheme(i.id)) = "datapages" THEN "DPG"ELSE "DRW" END AS "SCHEME",CASEWHEN i.status2 & 512 = 512 OR i.status & 16 = 16THEN "CLI"WHEN i.indid = 0THEN "TBL"ELSE "NCL"END AS "IDXTYPE","DPCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"dpcr"),0.00)),"DRCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"drcr"),0.00)),"SPUT"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"sput"),0.00)),"LGIO"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"lgio"),0.00)),"IPCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"ipcr"),0.00)),"FROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.forwrowcnt),16),8,2))),"DROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.delrowcnt),16),8,2))),"NROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.rowcnt),16),8,2))),"SPACEKB"=convert(numeric(15, 2), (reserved_pgs(i.id, i.doampg) + reserved_pgs(i.id, i.ioampg))*2)from sysindexes i, sysobjects o,systabstats twhere o.id=i.id and o.type = 'U'and o.id=t.id and i.id=t.id and i.indid=t.indidand object_name(o.id) = 'tPrice'order by lockscheme(i.id)
ASE 15.0 Compatible
select object_name(i.id) AS OBJECTNAME, i.name AS INDEXNAME,CASE WHEN convert(varchar(8),lockscheme(i.id)) = "allpages" THEN "APL"WHEN convert(varchar(8),lockscheme(i.id)) = "datapages" THEN "DPG"ELSE "DRW" END AS "SCHEME",CASEWHEN i.status2 & 512 = 512 OR i.status & 16 = 16THEN "CLI"WHEN i.indid = 0THEN "TBL"ELSE "NCL"END AS "IDXTYPE","DPCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"dpcr"),0.00)),"DRCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"drcr"),0.00)),"SPUT"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"sput"),0.00)),"LGIO"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"lgio"),0.00)),"IPCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"ipcr"),0.00)),"FROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.forwrowcnt),16),8,2))),"DROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.delrowcnt),16),8,2))),"NROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.rowcnt),16),8,2))),"SPACEKB"=reserved=convert(numeric(15, 2), (reserved_pages(db_id(),i.id, i.indid))*2)from sysindexes i, sysobjects o,systabstats twhere o.id=i.id and o.type = 'U'and o.id=t.id and i.id=t.id and i.indid=t.indid
ASE 12.5 Compatible
select object_name(i.id) AS OBJECT_NAME, i.name AS INDEXNAME,CASE WHEN convert(varchar(8),lockscheme(i.id)) = "allpages" THEN "APL"WHEN convert(varchar(8),lockscheme(i.id)) = "datapages" THEN "DPG"ELSE "DRW" END AS "SCHEME",CASEWHEN i.status2 & 512 = 512 OR i.status & 16 = 16THEN "CLI"WHEN i.indid = 0THEN "TBL"ELSE "NCL"END AS "IDXTYPE","DPCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"dpcr"),0.00)),"DRCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"drcr"),0.00)),"SPUT"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"sput"),0.00)),"LGIO"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"lgio"),0.00)),"IPCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"ipcr"),0.00)),"FROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.forwrowcnt),16),8,2))),"DROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.delrowcnt),16),8,2))),"NROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.rowcnt),16),8,2))),"SPACEKB"=convert(numeric(15, 2), (reserved_pgs(i.id, i.doampg) + reserved_pgs(i.id, i.ioampg))*2)from sysindexes i, sysobjects o,systabstats twhere o.id=i.id and o.type = 'U'and o.id=t.id and i.id=t.id and i.indid=t.indidand object_name(o.id) = 'tPrice'order by lockscheme(i.id)
ASE 15.0 Compatible
select object_name(i.id) AS OBJECTNAME, i.name AS INDEXNAME,CASE WHEN convert(varchar(8),lockscheme(i.id)) = "allpages" THEN "APL"WHEN convert(varchar(8),lockscheme(i.id)) = "datapages" THEN "DPG"ELSE "DRW" END AS "SCHEME",CASEWHEN i.status2 & 512 = 512 OR i.status & 16 = 16THEN "CLI"WHEN i.indid = 0THEN "TBL"ELSE "NCL"END AS "IDXTYPE","DPCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"dpcr"),0.00)),"DRCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"drcr"),0.00)),"SPUT"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"sput"),0.00)),"LGIO"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"lgio"),0.00)),"IPCR"=convert(decimal(6,2),isnull(derived_stat(i.id,i.indid,"ipcr"),0.00)),"FROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.forwrowcnt),16),8,2))),"DROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.delrowcnt),16),8,2))),"NROW"=ltrim(convert(varchar(8),str(round(convert(double precision,t.rowcnt),16),8,2))),"SPACEKB"=reserved=convert(numeric(15, 2), (reserved_pages(db_id(),i.id, i.indid))*2)from sysindexes i, sysobjects o,systabstats twhere o.id=i.id and o.type = 'U'and o.id=t.id and i.id=t.id and i.indid=t.indid
How to change the physical device path in ASE
1> select db_name(a.dbid) as database_name , a.segmap, b.name as dev_name, b.phyname from sysusages a, sysdevices b where b.phyname like '%test%' and a.vstart=b.low
2>
database_name segmap dev_name phyname
---------------- ----------- --------------------- -------------------------------------------------------------------------------------------------------------------------------
mydb 3 mydb_test_dev01 /SybaseDevices/sybase/ASESERVER.mydb_test.dev01.dat
mydb 4 mydb_test_dev02 /SybaseDevices/sybase/ASESERVER.mydb_test.dev02.dat
mydb 3 mydb_test_dev03 /SybaseDevices/sybase/ASESERVER.FwpPLonSpt.dev03.dat
mydb 3 mydb_test_dev04 /SybaseDevices/sybase/ASESERVER.mydb_test.dev04.dat
mydb 3 mydb_test_dev05 /SybaseDevices/sybase/ASESERVER.mydb_test.dev05.dat
mydb 3 mydb_test_dev06 /SybaseDevices/sybase/ASESERVER.mydb_test_dev06.dat
mydb 4 mydb_test_dev07 /SybaseDevices/sybase/ASESERVER.mydb_test_dev09.dat
mydb 3 mydb_test_dev08 /SybaseDevices/sybase/ASESERVER.mydb_test_dev10.dat
Please check this :
Mirror the device which you want to change:
1
1> sp_configure 'disable disk mirroring'
2>
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------
disable disk mirroring 1 0 1 1 switch static
(1 row affected)
(return status = 0)
1>
1> sp_configure 'disable disk mirroring',0
2>
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------
disable disk mirroring 1 0 0 1 switch static
(1 row affected)
Configuration option changed. Since the option is static, Adaptive Server must be rebooted in order for the change to take effect.
Changing the value of 'disable disk mirroring' does not increase the amount of memory Adaptive Server uses.
(return status = 0)
RECYCLED THE SERVER
1> disk mirror
2> name='mydb_test_dev07',
3> mirror='/SybaseDevices/sybase/ASESERVER.mydb_test_dev09.dat'
4>
Creating the physical file for the mirror...
Starting Dynamic Mirroring of 179200 pages for logical device 'mydb_test_dev07'.
512 pages mirrored...
1024 pages mirrored...
1536 pages mirrored...
2048 pages mirrored...
2560 pages mirrored...
3072 pages mirrored...
3584 pages mirrored...
Remove the primary side of mirror and retain the secondary :
disk mirror
name='mydb_test_dev08',
mirror='/SybaseDevices/sybase/ASESERVER.mydb_test_dev10.dat'
1> disk mirror
2> name='mydb_test_dev08',
3> mirror='/SybaseDevices/sybase/ASESERVER.mydb_test_dev10.dat'
4>
Creating the physical file for the mirror...
Starting Dynamic Mirroring of 256000 pages for logical device 'mydb_test_dev08'.
512 pages mirrored...
1024 pages mirrored...
1536 pages mirrored...
2048 pages mirrored...
2560 pages mirrored...
3072 pages mirrored...
3584 pages mirrored...
4096 pages mirrored...
4608 pages mirrored...
1> select name,phyname,mirrorname from sysdevices where phyname like '%Spt%'
2>
name phyname mirrorname
------------------------------ --------------------------------------------------------------- ----------------------------------------------------------------
mydb_test_dev01 /SybaseDevices/sybase/ASESERVER.mydb_test.dev01.dat NULL
mydb_test_dev02 /SybaseDevices/sybase/ASESERVER.mydb_test.dev02.dat NULL
mydb_test_dev03 /SybaseDevices/sybase/ASESERVER.FwpPLonSpt.dev03.dat NULL
mydb_test_dev04 /SybaseDevices/sybase/ASESERVER.mydb_test.dev04.dat NULL
mydb_test_dev05 /SybaseDevices/sybase/ASESERVER.mydb_test.dev05.dat NULL
mydb_test_dev06 /SybaseDevices/sybase/ASESERVER.mydb_test_dev06.dat NULL
mydb_test_dev07 /SybaseDumps/sybase/ASESERVER.mydb_test_dev07.dat /SybaseDevices/sybase/ASESERVER.mydb_test_dev09.dat
mydb_test_dev08 /SybaseDumps/sybase/ASESERVER.mydb_test_dev08.dat /SybaseDevices/sybase/ASESERVER.mydb_test_dev10.dat
1> disk unmirror
2> name='mydb_test_dev08',
3> side="primary",
4> mode=remove
5>
1>
select name,phyname,mirrorname from sysdevices where phyname like '%test%'
2>
name phyname mirrorname
------------------------------ -------------------------------------------------------------- ---------------
mydb_test_dev01 /SybaseDevices/sybase/ASESERVER.mydb_test.dev01.dat NULL
mydb_test_dev02 /SybaseDevices/sybase/ASESERVER.mydb_test.dev02.dat NULL
mydb_test_dev03 /SybaseDevices/sybase/ASESERVER.mydb_test.dev03.dat NULL
mydb_test_dev04 /SybaseDevices/sybase/ASESERVER.mydb_test.dev04.dat NULL
mydb_test_dev05 /SybaseDevices/sybase/ASESERVER.mydb_test.dev05.dat NULL
mydb_test_dev06 /SybaseDevices/sybase/ASESERVER.mydb_test_dev06.dat NULL
mydb_test_dev07 /SybaseDevices/sybase/ASESERVER.mydb_test_dev09.dat NULL
mydb_test_dev08 /SybaseDevices/sybase/ASESERVER.mydb_test_dev10.dat NULL
Fixing a corrupt index on system table example
1> sp_logiosize 'default'
2>
Msg 644, Level 21, State 5:
Procedure 'sp_logiosize', Line 313:
Index row entry for data row id (817, 15) is missing from index page 1009 of index id 2 of table 'sysattributes' in database 'sag'. Xactid is (10215,24). Drop and re-create the index.
1> sp_dboption sag, 'single', true
2>
Database option 'single user' turned ON for database 'sag'.
Running CHECKPOINT on database 'sag' for option 'single user' to take effect.
(return status = 0)
1>
1> use sag
2>
1> checkpoint
1> use sag
2>
1> sp_fixindex sag,sysattributes,2
2>
There are 22.000000 rows in 1 pages for object 'sysattributes'.
Non-clustered index successfully restored for object 'sysattributes' in 'sag' database.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
(return status = 0)
1> sp_logiosize 'default'
2>
The transaction log for database 'sag' will use I/O size of 4 Kbytes.
(return status = 0)
1>
2>
Msg 644, Level 21, State 5:
Procedure 'sp_logiosize', Line 313:
Index row entry for data row id (817, 15) is missing from index page 1009 of index id 2 of table 'sysattributes' in database 'sag'. Xactid is (10215,24). Drop and re-create the index.
1> sp_dboption sag, 'single', true
2>
Database option 'single user' turned ON for database 'sag'.
Running CHECKPOINT on database 'sag' for option 'single user' to take effect.
(return status = 0)
1>
1> use sag
2>
1> checkpoint
1> use sag
2>
1> sp_fixindex sag,sysattributes,2
2>
There are 22.000000 rows in 1 pages for object 'sysattributes'.
Non-clustered index successfully restored for object 'sysattributes' in 'sag' database.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
(return status = 0)
1> sp_logiosize 'default'
2>
The transaction log for database 'sag' will use I/O size of 4 Kbytes.
(return status = 0)
1>
Subscribe to:
Posts (Atom)