show_missing_stats shows details of useful statistics missing from SARG / Join columns.
It is important to note there are several situations where show_missing_stats will not print.
If you run a “stored-procedure” and there is a current plan in the master..sysqueryplans”, the “show_missing_statistics” will not print anything. You can do one of 2 things.
Run the stored-procedure with [...]
When trying to extract data from a text or image field, it is not always clear, how many bytes of data can be expected. Here’s a way how to determine this:
1> SET textsize 1669842
2> go
1> SELECT Xml FROM TradeXml WHERE AsOfDate = ‘20091019′ AND TradeId = ‘xxxxxxx’
2> go
In order to determine the value that needs [...]
Log on to the server
$ devinfo -i /dev/md/rdsk/d301
/dev/md/rdsk/d301 0 0 16384 512 1
It is possible to drop DBSPACEs on an IQ Server, if there is enough space to move all data to other DBSPACEs.
1. Check DBSPACEs
The sp_iqdbspace command should be used to list all DBSPACEs and their status.
(DBA)> sp_iqdbspace
Execution time: 0.053 seconds
Name Path [...]
Corrupt transaction log; Build a new ASE transaction log with dbcc rebuild_log
In some cases it is necessary to rebuild the ASE transaction log.
Sybase Technical Support states that this is extremely dangerous. It may cause potential database corruption if the system fails while the timestamp rolls over.
dump the database first. If “dbcc rebuild_log” corrupts the database [...]
ASE stores important information on databases as part of the backup procedure. This information can be retrieved starting with ASE v12.5.4 using the “with headeronly” feature of “load database”.
This will not load the database, so it’s safe to use.
1> LOAD DATABASE db_one FROM "/tmp/test_ce_0.bak.gz"
2> stripe ON "/tmp/test_ce_1.bak.gz"
3> WITH headeronly
4> go
Backup Server session id is: [...]
Quick and dirty approach to generate an arbitrary number of rows of test data in two-column table, where one column should not be unique:
1> CREATE TABLE
<table_name> (id int,whatever varchar(10))
2> go
1> INSERT
<table_name> VALUES (rand() * 1000000, ‘Datarow’)
2> go 2560
This will insert 2560 rows into the table “table_name” with unique values for the first column.
If it happens that the ’sa’-password for your ASE is lost, it can reset following the below steps:
1. As you cannot log in to your server as sa anymore, you will have to shut it down forcefully, using an approbriate operating system command, like ‘kill’ on Unix Solaris.
2. When it is shut down, modify the [...]
run dbcc checkdb and dbcc checkalloc
put database in single-user mode
execute sp_flushstats
… wait some time (approx. 1 min)
execute checkpoint
dump database
load databases on target db
execute sp_post_xpload - it ensures, that all indexes are correct
execute sp_indsuspect - findout corrupted indexes
Check for minimally logged operations in a dataserver:
The dbinfo memory structure, which each database has, holds information on whether the sequence of the transaction log entries were written consecutively, or if the sequence was broken by minimally logged transactions. The information is stored in sysindexes for object id 8, which is the syslogs table, i.e. [...]