Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

trace flag 990

9 posts in General Discussion Last posting was on 2011-07-25 16:41:47.0Z
Raj Posted on 2011-07-04 09:10:35.0Z
Sender: 1885.4e117dea.1804289383@sybase.com
From: Raj
Newsgroups: sybase.public.ase.general
Subject: trace flag 990
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e11838b.19de.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Jul 2011 02:10:35 -0700
X-Trace: forums-1-dub 1309770635 10.22.241.41 (4 Jul 2011 02:10:35 -0700)
X-Original-Trace: 4 Jul 2011 02:10:35 -0700, 10.22.241.41
Lines: 7
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30301
Article PK: 72477

Hi All,I am facing databse upgrade issue while dump/load
from 12.5.4 to 15.0.3 esd#1/2/3/4. Upgrade being terminated
while upgrading item 245....as work around to delete
duplicate or insert desired rows from offline database trace
990 use to work some time and some time does not work...why
this odd nature (using sa account to on the trace 990).
Please help on this.


Manish Negandhi [TeamSybase] Posted on 2011-07-04 15:29:15.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4e11838b.19de.1681692777@sybase.com>
Subject: Re: trace flag 990
Lines: 17
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e11dc4b@forums-1-dub>
Date: 4 Jul 2011 08:29:15 -0700
X-Trace: forums-1-dub 1309793355 10.22.241.152 (4 Jul 2011 08:29:15 -0700)
X-Original-Trace: 4 Jul 2011 08:29:15 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30310
Article PK: 72486


<Raj> wrote in message news:4e11838b.19de.1681692777@sybase.com...
> Hi All,I am facing databse upgrade issue while dump/load
> from 12.5.4 to 15.0.3 esd#1/2/3/4. Upgrade being terminated
> while upgrading item 245....as work around to delete
> duplicate or insert desired rows from offline database trace
> 990 use to work some time and some time does not work...why
> this odd nature (using sa account to on the trace 990).
> Please help on this.

traceflag 990 works well, can you post the exact error mesage ?

-HTH
Manish Negandhi
[TeamSybase]


Raj Posted on 2011-07-05 05:49:08.0Z
Sender: 5ac2.4e129a19.1804289383@sybase.com
From: Raj
Newsgroups: sybase.public.ase.general
Subject: Re: trace flag 990
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e12a5d4.5dad.1681692777@sybase.com>
References: <4e11dc4b@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Jul 2011 22:49:08 -0700
X-Trace: forums-1-dub 1309844948 10.22.241.41 (4 Jul 2011 22:49:08 -0700)
X-Original-Trace: 4 Jul 2011 22:49:08 -0700, 10.22.241.41
Lines: 215
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30312
Article PK: 72491

Thanks Manish for your response.

Please find the scenario :

1> load database test from
"compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp"
2> go
Backup Server session id is: 44. Use this value when
executing the 'sp_volchanged' system stored procedure after
fulfilling any
volume change request from the Backup Server.
Backup Server: 4.132.1.1: Attempting to open byte stream
device:
'compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp::00'
Backup Server: 6.28.1.1: Dumpfile name 'test1118605DB2 '
section number 1 mounted on byte stream
'compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp::00'
Backup Server: 4.188.1.1: Database test: 39942 kilobytes
(13%) LOADED.
Backup Server: 4.188.1.1: Database test: 82954 kilobytes
(27%) LOADED.
Backup Server: 4.188.1.1: Database test: 126474 kilobytes
(41%) LOADED.
Backup Server: 4.188.1.1: Database test: 169486 kilobytes
(55%) LOADED.
Backup Server: 4.188.1.1: Database test: 213006 kilobytes
(69%) LOADED.
Backup Server: 4.188.1.1: Database test: 256018 kilobytes
(83%) LOADED.
Backup Server: 4.188.1.1: Database test: 299538 kilobytes
(97%) LOADED.
Backup Server: 4.188.1.1: Database test: 307220 kilobytes
(100%) LOADED.
Backup Server: 4.188.1.1: Database test: 307228 kilobytes
(100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database test).
Caution: You have set up this database to include space on
disk 17 for both data and the transaction log. This can
make recovery
impossible if that disk fails.
Started estimating recovery log boundaries for database
'test'.
Database 'test', checkpoint=(153523, 18), first=(153523,
18), last=(153523, 21).
Completed estimating recovery log boundaries for database
'test'.
Started ANALYSIS pass for database 'test'.
Completed ANALYSIS pass for database 'test'.
Started REDO pass for database 'test'. The total number of
log records to process is 4.
Redo pass of recovery has processed 1 committed and 0
aborted transactions.
Completed REDO pass for database 'test'.
Use the ONLINE DATABASE command to bring this database
online; ASE will not bring it online automatically.
1> online database test
2> go
Started estimating recovery log boundaries for database
'test'.
Database 'test', checkpoint=(153523, 18), first=(153523,
18), last=(153523, 21).
Completed estimating recovery log boundaries for database
'test'.
Started ANALYSIS pass for database 'test'.
Completed ANALYSIS pass for database 'test'.
Recovery of database 'test' will undo incomplete nested top
actions.
Database 'test' appears to be at an older revision than the
present installation; ASE will assess it, and upgrade it as
required.
Database 'test': beginning upgrade step [ID 247]:
verifying sysstatistics row format
Database 'test': beginning upgrade step [ID 2]: validate
basic system type data
Database 'test': beginning upgrade step [ID 3]: alter
table (table sysindexes)
(60 rows affected)
Database 'test': beginning upgrade step [ID 4]: creating
table (table sysanchors)
Database 'test': beginning upgrade step [ID 5]: changing
name of system table (table syspartitions, new name:
sysslices)
(1 row affected)
(1 row affected)
Database 'test': beginning upgrade step [ID 6]: creating
table (table syspartitions)
Database 'test': beginning upgrade step [ID 7]: insert
rows into syspartitions for each system table and index
Database 'test': beginning upgrade step [ID 8]: alter
table (table systypes)
Non-clustered index (index id = 2) is being rebuilt.
Database 'test': beginning upgrade step [ID 9]: alter
table (table sysobjects)
Non-clustered index (index id = 2) is being rebuilt.
Database 'test': beginning upgrade step [ID 997]:
executing SQL statement ("if exists (select 1 from
syscolumns...")
Database 'test': beginning upgrade step [ID 998]:
executing SQL statement ("update syscolumns set colid =
colid...")
(5 rows affected)
Database 'test': beginning upgrade step [ID 10]: alter
table (table syscolumns)
(538 rows affected)
Database 'test': beginning upgrade step [ID 11]: insert
basic data into bootstrap system tables (table sysobjects)
Database 'test': beginning upgrade step [ID 69]:
executing SQL statement ("if 2 = (select count(name) from
sys...")
Database 'test': beginning upgrade step [ID 12]: alter
table (table systabstats)
(65 rows affected)
(63 rows affected)
Database 'test': beginning upgrade step [ID 26]: alter
table (table sysprocedures)
Database 'test': beginning upgrade step [ID 27]: alter
table (table syscomments)
(0 rows affected)
Database 'test': beginning upgrade step [ID 28]: alter
table (table syssegments)
Database 'test': beginning upgrade step [ID 29]: alter
table (table sysprotects)
Database 'test': beginning upgrade step [ID 30]: alter
table (table sysusers)
Non-clustered index (index id = 2) is being rebuilt.
Non-clustered index (index id = 3) is being rebuilt.
Database 'test': beginning upgrade step [ID 31]: alter
table (table sysalternates)
Database 'test': beginning upgrade step [ID 32]: alter
table (table sysdepends)
Database 'test': beginning upgrade step [ID 33]: alter
table (table syskeys)
Database 'test': beginning upgrade step [ID 47]: creating
table (table syspartitionkeys)
Database 'test': beginning upgrade step [ID 51]: alter
table (table sysreferences)
Non-clustered index (index id = 2) is being rebuilt.
Non-clustered index (index id = 3) is being rebuilt.
Database 'test': beginning upgrade step [ID 52]: alter
table (table sysconstraints)
Non-clustered index (index id = 2) is being rebuilt.
Database 'test': beginning upgrade step [ID 54]: alter
table (table systhresholds)
Database 'test': beginning upgrade step [ID 55]: alter
table (table sysroles)
Database 'test': beginning upgrade step [ID 60]: alter
table (table sysstatistics)
(0 rows affected)
(1 row affected)
(0 rows affected)
Database 'test': beginning upgrade step [ID 62]: alter
table (table sysjars)
Database 'test': beginning upgrade step [ID 65]: alter
table (table sysqueryplans)
Database 'test': beginning upgrade step [ID 68]: creating
table (table sysencryptkeys)
Database 'test': beginning upgrade step [ID 80]: alter
table (table sysattributes)
Non-clustered index (index id = 2) is being rebuilt.
Database 'test': beginning upgrade step [ID 91]:
executing SQL statement ("update systypes set
length=@@maxpag...")
(0 rows affected)
Database 'test': beginning upgrade step [ID 245]: Update
system roles in system tables.
(0 rows affected)
Initialization of sysusers in database test found that the
row for system role 'sa_role' is missing. Adding the
required row to
sysusers.
ASE is terminating this process.
1> exit


[sybase_dev]sybase@devbox $ isql -Usa -w2000
Password:
1> dbcc traceon (990)
2> go
DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.
1> use test
2> go
1> select * from sysusers
2> go
Msg 950, Level 14, State 1:
Server 'lon_oms_sql_dev':
Database 'test' is currently offline. Please wait and try
your command again later.
1> insert into sysusers values ( -2, 16384, 16384,
"sa_role", NULL)
2> go
Msg 950, Level 14, State 1:
Server 'lon_oms_sql_dev':
Database 'test' is currently offline. Please wait and try
your command again later.
1>

> <Raj> wrote in message
> > news:4e11838b.19de.1681692777@sybase.com... Hi All,I am
> > facing databse upgrade issue while dump/load from 12.5.4
> > to 15.0.3 esd#1/2/3/4. Upgrade being terminated while
> > upgrading item 245....as work around to delete duplicate
> > or insert desired rows from offline database trace 990
> > use to work some time and some time does not work...why
> > this odd nature (using sa account to on the trace 990).
> Please help on this.
>
> traceflag 990 works well, can you post the exact error
> mesage ?
>
> -HTH
> Manish Negandhi
> [TeamSybase]
>
>


Manish Negandhi [TeamSybase] Posted on 2011-07-06 03:56:00.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4e11dc4b@forums-1-dub> <4e12a5d4.5dad.1681692777@sybase.com>
Subject: Re: trace flag 990
Lines: 207
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e13dcd0@forums-1-dub>
Date: 5 Jul 2011 20:56:00 -0700
X-Trace: forums-1-dub 1309924560 10.22.241.152 (5 Jul 2011 20:56:00 -0700)
X-Original-Trace: 5 Jul 2011 20:56:00 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30315
Article PK: 72494


<Raj> wrote in message news:4e12a5d4.5dad.1681692777@sybase.com...
> Thanks Manish for your response.
>
> Please find the scenario :
>
> 1> load database test from
> "compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp"
> 2> go
> Backup Server session id is: 44. Use this value when
> executing the 'sp_volchanged' system stored procedure after
> fulfilling any
> volume change request from the Backup Server.
> Backup Server: 4.132.1.1: Attempting to open byte stream
> device:
> 'compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp::00'
> Backup Server: 6.28.1.1: Dumpfile name 'test1118605DB2 '
> section number 1 mounted on byte stream
> 'compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp::00'
> Backup Server: 4.188.1.1: Database test: 39942 kilobytes
> (13%) LOADED.
> Backup Server: 4.188.1.1: Database test: 82954 kilobytes
> (27%) LOADED.
> Backup Server: 4.188.1.1: Database test: 126474 kilobytes
> (41%) LOADED.
> Backup Server: 4.188.1.1: Database test: 169486 kilobytes
> (55%) LOADED.
> Backup Server: 4.188.1.1: Database test: 213006 kilobytes
> (69%) LOADED.
> Backup Server: 4.188.1.1: Database test: 256018 kilobytes
> (83%) LOADED.
> Backup Server: 4.188.1.1: Database test: 299538 kilobytes
> (97%) LOADED.
> Backup Server: 4.188.1.1: Database test: 307220 kilobytes
> (100%) LOADED.
> Backup Server: 4.188.1.1: Database test: 307228 kilobytes
> (100%) LOADED.
> Backup Server: 3.42.1.1: LOAD is complete (database test).
> Caution: You have set up this database to include space on
> disk 17 for both data and the transaction log. This can
> make recovery
> impossible if that disk fails.
> Started estimating recovery log boundaries for database
> 'test'.
> Database 'test', checkpoint=(153523, 18), first=(153523,
> 18), last=(153523, 21).
> Completed estimating recovery log boundaries for database
> 'test'.
> Started ANALYSIS pass for database 'test'.
> Completed ANALYSIS pass for database 'test'.
> Started REDO pass for database 'test'. The total number of
> log records to process is 4.
> Redo pass of recovery has processed 1 committed and 0
> aborted transactions.
> Completed REDO pass for database 'test'.
> Use the ONLINE DATABASE command to bring this database
> online; ASE will not bring it online automatically.
> 1> online database test
> 2> go
> Started estimating recovery log boundaries for database
> 'test'.
> Database 'test', checkpoint=(153523, 18), first=(153523,
> 18), last=(153523, 21).
> Completed estimating recovery log boundaries for database
> 'test'.
> Started ANALYSIS pass for database 'test'.
> Completed ANALYSIS pass for database 'test'.
> Recovery of database 'test' will undo incomplete nested top
> actions.
> Database 'test' appears to be at an older revision than the
> present installation; ASE will assess it, and upgrade it as
> required.
> Database 'test': beginning upgrade step [ID 247]:
> verifying sysstatistics row format
> Database 'test': beginning upgrade step [ID 2]: validate
> basic system type data
> Database 'test': beginning upgrade step [ID 3]: alter
> table (table sysindexes)
> (60 rows affected)
> Database 'test': beginning upgrade step [ID 4]: creating
> table (table sysanchors)
> Database 'test': beginning upgrade step [ID 5]: changing
> name of system table (table syspartitions, new name:
> sysslices)
> (1 row affected)
> (1 row affected)
> Database 'test': beginning upgrade step [ID 6]: creating
> table (table syspartitions)
> Database 'test': beginning upgrade step [ID 7]: insert
> rows into syspartitions for each system table and index
> Database 'test': beginning upgrade step [ID 8]: alter
> table (table systypes)
> Non-clustered index (index id = 2) is being rebuilt.
> Database 'test': beginning upgrade step [ID 9]: alter
> table (table sysobjects)
> Non-clustered index (index id = 2) is being rebuilt.
> Database 'test': beginning upgrade step [ID 997]:
> executing SQL statement ("if exists (select 1 from
> syscolumns...")
> Database 'test': beginning upgrade step [ID 998]:
> executing SQL statement ("update syscolumns set colid =
> colid...")
> (5 rows affected)
> Database 'test': beginning upgrade step [ID 10]: alter
> table (table syscolumns)
> (538 rows affected)
> Database 'test': beginning upgrade step [ID 11]: insert
> basic data into bootstrap system tables (table sysobjects)
> Database 'test': beginning upgrade step [ID 69]:
> executing SQL statement ("if 2 = (select count(name) from
> sys...")
> Database 'test': beginning upgrade step [ID 12]: alter
> table (table systabstats)
> (65 rows affected)
> (63 rows affected)
> Database 'test': beginning upgrade step [ID 26]: alter
> table (table sysprocedures)
> Database 'test': beginning upgrade step [ID 27]: alter
> table (table syscomments)
> (0 rows affected)
> Database 'test': beginning upgrade step [ID 28]: alter
> table (table syssegments)
> Database 'test': beginning upgrade step [ID 29]: alter
> table (table sysprotects)
> Database 'test': beginning upgrade step [ID 30]: alter
> table (table sysusers)
> Non-clustered index (index id = 2) is being rebuilt.
> Non-clustered index (index id = 3) is being rebuilt.
> Database 'test': beginning upgrade step [ID 31]: alter
> table (table sysalternates)
> Database 'test': beginning upgrade step [ID 32]: alter
> table (table sysdepends)
> Database 'test': beginning upgrade step [ID 33]: alter
> table (table syskeys)
> Database 'test': beginning upgrade step [ID 47]: creating
> table (table syspartitionkeys)
> Database 'test': beginning upgrade step [ID 51]: alter
> table (table sysreferences)
> Non-clustered index (index id = 2) is being rebuilt.
> Non-clustered index (index id = 3) is being rebuilt.
> Database 'test': beginning upgrade step [ID 52]: alter
> table (table sysconstraints)
> Non-clustered index (index id = 2) is being rebuilt.
> Database 'test': beginning upgrade step [ID 54]: alter
> table (table systhresholds)
> Database 'test': beginning upgrade step [ID 55]: alter
> table (table sysroles)
> Database 'test': beginning upgrade step [ID 60]: alter
> table (table sysstatistics)
> (0 rows affected)
> (1 row affected)
> (0 rows affected)
> Database 'test': beginning upgrade step [ID 62]: alter
> table (table sysjars)
> Database 'test': beginning upgrade step [ID 65]: alter
> table (table sysqueryplans)
> Database 'test': beginning upgrade step [ID 68]: creating
> table (table sysencryptkeys)
> Database 'test': beginning upgrade step [ID 80]: alter
> table (table sysattributes)
> Non-clustered index (index id = 2) is being rebuilt.
> Database 'test': beginning upgrade step [ID 91]:
> executing SQL statement ("update systypes set
> length=@@maxpag...")
> (0 rows affected)
> Database 'test': beginning upgrade step [ID 245]: Update
> system roles in system tables.
> (0 rows affected)
> Initialization of sysusers in database test found that the
> row for system role 'sa_role' is missing. Adding the
> required row to
> sysusers.
> ASE is terminating this process.
> 1> exit
>
>
> [sybase_dev]sybase@devbox $ isql -Usa -w2000
> Password:
> 1> dbcc traceon (990)
> 2> go
> DBCC execution completed. If DBCC printed error messages,
> contact a user with System Administrator (SA) role.
> 1> use test
> 2> go
> 1> select * from sysusers
> 2> go
> Msg 950, Level 14, State 1:
> Server 'lon_oms_sql_dev':
> Database 'test' is currently offline. Please wait and try
> your command again later.
> 1> insert into sysusers values ( -2, 16384, 16384,
> "sa_role", NULL)
> 2> go
> Msg 950, Level 14, State 1:
> Server 'lon_oms_sql_dev':
> Database 'test' is currently offline. Please wait and try
> your command again later.
> 1>
>

Is "statement cache" config parameter is enabled in your server ? , if
yes, disable it and try again

-HTH
Manish Negandhi
[TeamSybase]


Raj Posted on 2011-07-13 04:08:55.0Z
Sender: d37.4e1d19ec.1804289383@sybase.com
From: Raj
Newsgroups: sybase.public.ase.general
Subject: Re: trace flag 990
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e1d1a57.d41.1681692777@sybase.com>
References: <4e13dcd0@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 12 Jul 2011 21:08:55 -0700
X-Trace: forums-1-dub 1310530135 10.22.241.41 (12 Jul 2011 21:08:55 -0700)
X-Original-Trace: 12 Jul 2011 21:08:55 -0700, 10.22.241.41
Lines: 213
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30340
Article PK: 72518

Thanks Manish.

Yes, its worked.

> <Raj> wrote in message
> > news:4e12a5d4.5dad.1681692777@sybase.com... Thanks
> Manish for your response. >
> > Please find the scenario :
> >
> > 1> load database test from
> > "compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp"
> > 2> go
> > Backup Server session id is: 44. Use this value when
> > executing the 'sp_volchanged' system stored procedure
> > after fulfilling any
> > volume change request from the Backup Server.
> > Backup Server: 4.132.1.1: Attempting to open byte stream
> > device:
> >
> >
> 'compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp::00'
> > Backup Server: 6.28.1.1: Dumpfile name 'test1118605DB2
> > ' section number 1 mounted on byte stream
> >
> 'compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp::00'
> > Backup Server: 4.188.1.1: Database test: 39942 kilobytes
> > (13%) LOADED. Backup Server: 4.188.1.1: Database test:
> > 82954 kilobytes (27%) LOADED.
> > Backup Server: 4.188.1.1: Database test: 126474
> > kilobytes (41%) LOADED.
> > Backup Server: 4.188.1.1: Database test: 169486
> > kilobytes (55%) LOADED.
> > Backup Server: 4.188.1.1: Database test: 213006
> > kilobytes (69%) LOADED.
> > Backup Server: 4.188.1.1: Database test: 256018
> > kilobytes (83%) LOADED.
> > Backup Server: 4.188.1.1: Database test: 299538
> > kilobytes (97%) LOADED.
> > Backup Server: 4.188.1.1: Database test: 307220
> > kilobytes (100%) LOADED.
> > Backup Server: 4.188.1.1: Database test: 307228
> > kilobytes (100%) LOADED.
> > Backup Server: 3.42.1.1: LOAD is complete (database
> > test). Caution: You have set up this database to
> > include space on disk 17 for both data and the
> > transaction log. This can make recovery
> > impossible if that disk fails.
> > Started estimating recovery log boundaries for database
> > 'test'.
> > Database 'test', checkpoint=(153523, 18), first=(153523,
> > 18), last=(153523, 21).
> > Completed estimating recovery log boundaries for
> > database 'test'.
> > Started ANALYSIS pass for database 'test'.
> > Completed ANALYSIS pass for database 'test'.
> > Started REDO pass for database 'test'. The total number
> > of log records to process is 4.
> > Redo pass of recovery has processed 1 committed and 0
> > aborted transactions.
> > Completed REDO pass for database 'test'.
> > Use the ONLINE DATABASE command to bring this database
> > online; ASE will not bring it online automatically.
> > 1> online database test
> > 2> go
> > Started estimating recovery log boundaries for database
> > 'test'.
> > Database 'test', checkpoint=(153523, 18), first=(153523,
> > 18), last=(153523, 21).
> > Completed estimating recovery log boundaries for
> > database 'test'.
> > Started ANALYSIS pass for database 'test'.
> > Completed ANALYSIS pass for database 'test'.
> > Recovery of database 'test' will undo incomplete nested
> > top actions.
> > Database 'test' appears to be at an older revision than
> > the present installation; ASE will assess it, and
> > upgrade it as required.
> > Database 'test': beginning upgrade step [ID 247]:
> > verifying sysstatistics row format
> > Database 'test': beginning upgrade step [ID 2]:
> > validate basic system type data
> > Database 'test': beginning upgrade step [ID 3]:
> > alter table (table sysindexes)
> > (60 rows affected)
> > Database 'test': beginning upgrade step [ID 4]:
> > creating table (table sysanchors)
> > Database 'test': beginning upgrade step [ID 5]:
> > changing name of system table (table syspartitions, new
> > name: sysslices)
> > (1 row affected)
> > (1 row affected)
> > Database 'test': beginning upgrade step [ID 6]:
> > creating table (table syspartitions)
> > Database 'test': beginning upgrade step [ID 7]:
> > insert rows into syspartitions for each system table and
> > index Database 'test': beginning upgrade step [ID
> > 8]: alter table (table systypes)
> > Non-clustered index (index id = 2) is being rebuilt.
> > Database 'test': beginning upgrade step [ID 9]:
> > alter table (table sysobjects)
> > Non-clustered index (index id = 2) is being rebuilt.
> > Database 'test': beginning upgrade step [ID 997]:
> > executing SQL statement ("if exists (select 1 from
> > syscolumns...")
> > Database 'test': beginning upgrade step [ID 998]:
> > executing SQL statement ("update syscolumns set colid =
> > colid...")
> > (5 rows affected)
> > Database 'test': beginning upgrade step [ID 10]:
> > alter table (table syscolumns)
> > (538 rows affected)
> > Database 'test': beginning upgrade step [ID 11]:
> > insert basic data into bootstrap system tables (table
> > sysobjects) Database 'test': beginning upgrade step [ID
> > 69]: executing SQL statement ("if 2 = (select
> > count(name) from sys...")
> > Database 'test': beginning upgrade step [ID 12]:
> > alter table (table systabstats)
> > (65 rows affected)
> > (63 rows affected)
> > Database 'test': beginning upgrade step [ID 26]:
> > alter table (table sysprocedures)
> > Database 'test': beginning upgrade step [ID 27]:
> > alter table (table syscomments)
> > (0 rows affected)
> > Database 'test': beginning upgrade step [ID 28]:
> > alter table (table syssegments)
> > Database 'test': beginning upgrade step [ID 29]:
> > alter table (table sysprotects)
> > Database 'test': beginning upgrade step [ID 30]:
> > alter table (table sysusers)
> > Non-clustered index (index id = 2) is being rebuilt.
> > Non-clustered index (index id = 3) is being rebuilt.
> > Database 'test': beginning upgrade step [ID 31]:
> > alter table (table sysalternates)
> > Database 'test': beginning upgrade step [ID 32]:
> > alter table (table sysdepends)
> > Database 'test': beginning upgrade step [ID 33]:
> > alter table (table syskeys)
> > Database 'test': beginning upgrade step [ID 47]:
> > creating table (table syspartitionkeys)
> > Database 'test': beginning upgrade step [ID 51]:
> > alter table (table sysreferences)
> > Non-clustered index (index id = 2) is being rebuilt.
> > Non-clustered index (index id = 3) is being rebuilt.
> > Database 'test': beginning upgrade step [ID 52]:
> > alter table (table sysconstraints)
> > Non-clustered index (index id = 2) is being rebuilt.
> > Database 'test': beginning upgrade step [ID 54]:
> > alter table (table systhresholds)
> > Database 'test': beginning upgrade step [ID 55]:
> > alter table (table sysroles)
> > Database 'test': beginning upgrade step [ID 60]:
> > alter table (table sysstatistics)
> > (0 rows affected)
> > (1 row affected)
> > (0 rows affected)
> > Database 'test': beginning upgrade step [ID 62]:
> > alter table (table sysjars)
> > Database 'test': beginning upgrade step [ID 65]:
> > alter table (table sysqueryplans)
> > Database 'test': beginning upgrade step [ID 68]:
> > creating table (table sysencryptkeys)
> > Database 'test': beginning upgrade step [ID 80]:
> > alter table (table sysattributes)
> > Non-clustered index (index id = 2) is being rebuilt.
> > Database 'test': beginning upgrade step [ID 91]:
> > executing SQL statement ("update systypes set
> > length=@@maxpag...")
> > (0 rows affected)
> > Database 'test': beginning upgrade step [ID 245]:
> > Update system roles in system tables.
> > (0 rows affected)
> > Initialization of sysusers in database test found that
> > the row for system role 'sa_role' is missing. Adding the
> > required row to
> > sysusers.
> > ASE is terminating this process.
> > 1> exit
> >
> >
> > [sybase_dev]sybase@devbox $ isql -Usa -w2000
> > Password:
> > 1> dbcc traceon (990)
> > 2> go
> > DBCC execution completed. If DBCC printed error messages
> > , contact a user with System Administrator (SA) role.
> > 1> use test
> > 2> go
> > 1> select * from sysusers
> > 2> go
> > Msg 950, Level 14, State 1:
> > Server 'lon_oms_sql_dev':
> > Database 'test' is currently offline. Please wait and
> > try your command again later.
> > 1> insert into sysusers values ( -2, 16384, 16384,
> > "sa_role", NULL)
> > 2> go
> > Msg 950, Level 14, State 1:
> > Server 'lon_oms_sql_dev':
> > Database 'test' is currently offline. Please wait and
> > try your command again later.
> > 1>
> >
>
> Is "statement cache" config parameter is enabled in your
> server ? , if yes, disable it and try again
>
> -HTH
> Manish Negandhi
> [TeamSybase]
>
>


Raj Posted on 2011-07-15 06:09:17.0Z
Sender: d37.4e1d19ec.1804289383@sybase.com
From: Raj
Newsgroups: sybase.public.ase.general
Subject: Re: trace flag 990
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e1fd98d.63a9.1681692777@sybase.com>
References: <4e1d1a57.d41.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Jul 2011 23:09:17 -0700
X-Trace: forums-1-dub 1310710157 10.22.241.41 (14 Jul 2011 23:09:17 -0700)
X-Original-Trace: 14 Jul 2011 23:09:17 -0700, 10.22.241.41
Lines: 221
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30352
Article PK: 72535

Hi Manish, can I learn how "statement cache" help to
manipulate the offline database along with trace 990.
This helps to resolve the upgrade item:
"upgrade step [ID 245]:

> Thanks Manish.
>
> Yes, its worked.
>
> > <Raj> wrote in message
> > > news:4e12a5d4.5dad.1681692777@sybase.com... Thanks
> > Manish for your response. >
> > > Please find the scenario :
> > >
> > > 1> load database test from
> > > "compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp"
> > > 2> go
> > > Backup Server session id is: 44. Use this value when
> > > executing the 'sp_volchanged' system stored procedure
> > > after fulfilling any
> > > volume change request from the Backup Server.
> > > Backup Server: 4.132.1.1: Attempting to open byte
> > > stream device:
> > >
> > >
> >
> > >
> 'compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp::00'
> > > Backup Server: 6.28.1.1: Dumpfile name 'test1118605DB2
> > ' section number 1 mounted on byte stream >
> >
> > >
> 'compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp::00'
> > > Backup Server: 4.188.1.1: Database test: 39942
> kilobytes (13%) LOADED. Backup Server: 4.188.1.1: Database
> > > test: 82954 kilobytes (27%) LOADED.
> > > Backup Server: 4.188.1.1: Database test: 126474
> > > kilobytes (41%) LOADED.
> > > Backup Server: 4.188.1.1: Database test: 169486
> > > kilobytes (55%) LOADED.
> > > Backup Server: 4.188.1.1: Database test: 213006
> > > kilobytes (69%) LOADED.
> > > Backup Server: 4.188.1.1: Database test: 256018
> > > kilobytes (83%) LOADED.
> > > Backup Server: 4.188.1.1: Database test: 299538
> > > kilobytes (97%) LOADED.
> > > Backup Server: 4.188.1.1: Database test: 307220
> > > kilobytes (100%) LOADED.
> > > Backup Server: 4.188.1.1: Database test: 307228
> > > kilobytes (100%) LOADED.
> > > Backup Server: 3.42.1.1: LOAD is complete (database
> > > test). Caution: You have set up this database to
> > > include space on disk 17 for both data and the
> > > transaction log. This can make recovery
> > > impossible if that disk fails.
> > > Started estimating recovery log boundaries for
> > > database 'test'.
> > > Database 'test', checkpoint=(153523, 18),
> > > first=(153523, 18), last=(153523, 21).
> > > Completed estimating recovery log boundaries for
> > > database 'test'.
> > > Started ANALYSIS pass for database 'test'.
> > > Completed ANALYSIS pass for database 'test'.
> > > Started REDO pass for database 'test'. The total
> > > number of log records to process is 4.
> > > Redo pass of recovery has processed 1 committed and 0
> > > aborted transactions.
> > > Completed REDO pass for database 'test'.
> > > Use the ONLINE DATABASE command to bring this database
> > > online; ASE will not bring it online automatically.
> > > 1> online database test
> > > 2> go
> > > Started estimating recovery log boundaries for
> > > database 'test'.
> > > Database 'test', checkpoint=(153523, 18),
> > > first=(153523, 18), last=(153523, 21).
> > > Completed estimating recovery log boundaries for
> > > database 'test'.
> > > Started ANALYSIS pass for database 'test'.
> > > Completed ANALYSIS pass for database 'test'.
> > > Recovery of database 'test' will undo incomplete
> > > nested top actions.
> > > Database 'test' appears to be at an older revision
> > > than the present installation; ASE will assess it, and
> > > upgrade it as required.
> > > Database 'test': beginning upgrade step [ID 247]:
> > > verifying sysstatistics row format
> > > Database 'test': beginning upgrade step [ID 2]:
> > > validate basic system type data
> > > Database 'test': beginning upgrade step [ID 3]:
> > > alter table (table sysindexes)
> > > (60 rows affected)
> > > Database 'test': beginning upgrade step [ID 4]:
> > > creating table (table sysanchors)
> > > Database 'test': beginning upgrade step [ID 5]:
> > > changing name of system table (table syspartitions,
> > > new name: sysslices)
> > > (1 row affected)
> > > (1 row affected)
> > > Database 'test': beginning upgrade step [ID 6]:
> > > creating table (table syspartitions)
> > > Database 'test': beginning upgrade step [ID 7]:
> > > insert rows into syspartitions for each system table
> > > and index Database 'test': beginning upgrade step [ID
> > > 8]: alter table (table systypes)
> > > Non-clustered index (index id = 2) is being rebuilt.
> > > Database 'test': beginning upgrade step [ID 9]:
> > > alter table (table sysobjects)
> > > Non-clustered index (index id = 2) is being rebuilt.
> > > Database 'test': beginning upgrade step [ID 997]:
> > > executing SQL statement ("if exists (select 1 from
> > > syscolumns...")
> > > Database 'test': beginning upgrade step [ID 998]:
> > > executing SQL statement ("update syscolumns set colid
> > > = colid...")
> > > (5 rows affected)
> > > Database 'test': beginning upgrade step [ID 10]:
> > > alter table (table syscolumns)
> > > (538 rows affected)
> > > Database 'test': beginning upgrade step [ID 11]:
> > > insert basic data into bootstrap system tables (table
> > > sysobjects) Database 'test': beginning upgrade step
> > > [ID 69]: executing SQL statement ("if 2 = (select
> > > count(name) from sys...")
> > > Database 'test': beginning upgrade step [ID 12]:
> > > alter table (table systabstats)
> > > (65 rows affected)
> > > (63 rows affected)
> > > Database 'test': beginning upgrade step [ID 26]:
> > > alter table (table sysprocedures)
> > > Database 'test': beginning upgrade step [ID 27]:
> > > alter table (table syscomments)
> > > (0 rows affected)
> > > Database 'test': beginning upgrade step [ID 28]:
> > > alter table (table syssegments)
> > > Database 'test': beginning upgrade step [ID 29]:
> > > alter table (table sysprotects)
> > > Database 'test': beginning upgrade step [ID 30]:
> > > alter table (table sysusers)
> > > Non-clustered index (index id = 2) is being rebuilt.
> > > Non-clustered index (index id = 3) is being rebuilt.
> > > Database 'test': beginning upgrade step [ID 31]:
> > > alter table (table sysalternates)
> > > Database 'test': beginning upgrade step [ID 32]:
> > > alter table (table sysdepends)
> > > Database 'test': beginning upgrade step [ID 33]:
> > > alter table (table syskeys)
> > > Database 'test': beginning upgrade step [ID 47]:
> > > creating table (table syspartitionkeys)
> > > Database 'test': beginning upgrade step [ID 51]:
> > > alter table (table sysreferences)
> > > Non-clustered index (index id = 2) is being rebuilt.
> > > Non-clustered index (index id = 3) is being rebuilt.
> > > Database 'test': beginning upgrade step [ID 52]:
> > > alter table (table sysconstraints)
> > > Non-clustered index (index id = 2) is being rebuilt.
> > > Database 'test': beginning upgrade step [ID 54]:
> > > alter table (table systhresholds)
> > > Database 'test': beginning upgrade step [ID 55]:
> > > alter table (table sysroles)
> > > Database 'test': beginning upgrade step [ID 60]:
> > > alter table (table sysstatistics)
> > > (0 rows affected)
> > > (1 row affected)
> > > (0 rows affected)
> > > Database 'test': beginning upgrade step [ID 62]:
> > > alter table (table sysjars)
> > > Database 'test': beginning upgrade step [ID 65]:
> > > alter table (table sysqueryplans)
> > > Database 'test': beginning upgrade step [ID 68]:
> > > creating table (table sysencryptkeys)
> > > Database 'test': beginning upgrade step [ID 80]:
> > > alter table (table sysattributes)
> > > Non-clustered index (index id = 2) is being rebuilt.
> > > Database 'test': beginning upgrade step [ID 91]:
> > > executing SQL statement ("update systypes set
> > > length=@@maxpag...")
> > > (0 rows affected)
> > > Database 'test': beginning upgrade step [ID 245]:
> > > Update system roles in system tables.
> > > (0 rows affected)
> > > Initialization of sysusers in database test found that
> > > the row for system role 'sa_role' is missing. Adding
> > > the required row to
> > > sysusers.
> > > ASE is terminating this process.
> > > 1> exit
> > >
> > >
> > > [sybase_dev]sybase@devbox $ isql -Usa -w2000
> > > Password:
> > > 1> dbcc traceon (990)
> > > 2> go
> > > DBCC execution completed. If DBCC printed error
> > > messages , contact a user with System Administrator
> > > (SA) role. 1> use test
> > > 2> go
> > > 1> select * from sysusers
> > > 2> go
> > > Msg 950, Level 14, State 1:
> > > Server 'lon_oms_sql_dev':
> > > Database 'test' is currently offline. Please wait and
> > > try your command again later.
> > > 1> insert into sysusers values ( -2, 16384, 16384,
> > > "sa_role", NULL)
> > > 2> go
> > > Msg 950, Level 14, State 1:
> > > Server 'lon_oms_sql_dev':
> > > Database 'test' is currently offline. Please wait and
> > > try your command again later.
> > > 1>
> > >
> >
> > Is "statement cache" config parameter is enabled in
> > your server ? , if yes, disable it and try again
> >
> > -HTH
> > Manish Negandhi
> > [TeamSybase]
> >
> >


Manish Negandhi [TeamSybase] Posted on 2011-07-18 03:44:46.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4e1d1a57.d41.1681692777@sybase.com> <4e1fd98d.63a9.1681692777@sybase.com>
Subject: Re: trace flag 990
Lines: 230
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e23ac2e$1@forums-1-dub>
Date: 17 Jul 2011 20:44:46 -0700
X-Trace: forums-1-dub 1310960686 10.22.241.152 (17 Jul 2011 20:44:46 -0700)
X-Original-Trace: 17 Jul 2011 20:44:46 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30353
Article PK: 72532

Can you post output of dbcc prsqlcache just before using traceflag 990 ?

-HTH
Manish Negandhi
[TeamSybase]

<Raj> wrote in message news:4e1fd98d.63a9.1681692777@sybase.com...
> Hi Manish, can I learn how "statement cache" help to
> manipulate the offline database along with trace 990.
> This helps to resolve the upgrade item:
> "upgrade step [ID 245]:
>
>> Thanks Manish.
>>
>> Yes, its worked.
>>
>> > <Raj> wrote in message
>> > > news:4e12a5d4.5dad.1681692777@sybase.com... Thanks
>> > Manish for your response. >
>> > > Please find the scenario :
>> > >
>> > > 1> load database test from
>> > > "compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp"
>> > > 2> go
>> > > Backup Server session id is: 44. Use this value when
>> > > executing the 'sp_volchanged' system stored procedure
>> > > after fulfilling any
>> > > volume change request from the Backup Server.
>> > > Backup Server: 4.132.1.1: Attempting to open byte
>> > > stream device:
>> > >
>> > >
>> >
>> > >
>> 'compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp::00'
>> > > Backup Server: 6.28.1.1: Dumpfile name 'test1118605DB2
>> > ' section number 1 mounted on byte stream >
>> >
>> > >
>> 'compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp::00'
>> > > Backup Server: 4.188.1.1: Database test: 39942
>> kilobytes (13%) LOADED. Backup Server: 4.188.1.1: Database
>> > > test: 82954 kilobytes (27%) LOADED.
>> > > Backup Server: 4.188.1.1: Database test: 126474
>> > > kilobytes (41%) LOADED.
>> > > Backup Server: 4.188.1.1: Database test: 169486
>> > > kilobytes (55%) LOADED.
>> > > Backup Server: 4.188.1.1: Database test: 213006
>> > > kilobytes (69%) LOADED.
>> > > Backup Server: 4.188.1.1: Database test: 256018
>> > > kilobytes (83%) LOADED.
>> > > Backup Server: 4.188.1.1: Database test: 299538
>> > > kilobytes (97%) LOADED.
>> > > Backup Server: 4.188.1.1: Database test: 307220
>> > > kilobytes (100%) LOADED.
>> > > Backup Server: 4.188.1.1: Database test: 307228
>> > > kilobytes (100%) LOADED.
>> > > Backup Server: 3.42.1.1: LOAD is complete (database
>> > > test). Caution: You have set up this database to
>> > > include space on disk 17 for both data and the
>> > > transaction log. This can make recovery
>> > > impossible if that disk fails.
>> > > Started estimating recovery log boundaries for
>> > > database 'test'.
>> > > Database 'test', checkpoint=(153523, 18),
>> > > first=(153523, 18), last=(153523, 21).
>> > > Completed estimating recovery log boundaries for
>> > > database 'test'.
>> > > Started ANALYSIS pass for database 'test'.
>> > > Completed ANALYSIS pass for database 'test'.
>> > > Started REDO pass for database 'test'. The total
>> > > number of log records to process is 4.
>> > > Redo pass of recovery has processed 1 committed and 0
>> > > aborted transactions.
>> > > Completed REDO pass for database 'test'.
>> > > Use the ONLINE DATABASE command to bring this database
>> > > online; ASE will not bring it online automatically.
>> > > 1> online database test
>> > > 2> go
>> > > Started estimating recovery log boundaries for
>> > > database 'test'.
>> > > Database 'test', checkpoint=(153523, 18),
>> > > first=(153523, 18), last=(153523, 21).
>> > > Completed estimating recovery log boundaries for
>> > > database 'test'.
>> > > Started ANALYSIS pass for database 'test'.
>> > > Completed ANALYSIS pass for database 'test'.
>> > > Recovery of database 'test' will undo incomplete
>> > > nested top actions.
>> > > Database 'test' appears to be at an older revision
>> > > than the present installation; ASE will assess it, and
>> > > upgrade it as required.
>> > > Database 'test': beginning upgrade step [ID 247]:
>> > > verifying sysstatistics row format
>> > > Database 'test': beginning upgrade step [ID 2]:
>> > > validate basic system type data
>> > > Database 'test': beginning upgrade step [ID 3]:
>> > > alter table (table sysindexes)
>> > > (60 rows affected)
>> > > Database 'test': beginning upgrade step [ID 4]:
>> > > creating table (table sysanchors)
>> > > Database 'test': beginning upgrade step [ID 5]:
>> > > changing name of system table (table syspartitions,
>> > > new name: sysslices)
>> > > (1 row affected)
>> > > (1 row affected)
>> > > Database 'test': beginning upgrade step [ID 6]:
>> > > creating table (table syspartitions)
>> > > Database 'test': beginning upgrade step [ID 7]:
>> > > insert rows into syspartitions for each system table
>> > > and index Database 'test': beginning upgrade step [ID
>> > > 8]: alter table (table systypes)
>> > > Non-clustered index (index id = 2) is being rebuilt.
>> > > Database 'test': beginning upgrade step [ID 9]:
>> > > alter table (table sysobjects)
>> > > Non-clustered index (index id = 2) is being rebuilt.
>> > > Database 'test': beginning upgrade step [ID 997]:
>> > > executing SQL statement ("if exists (select 1 from
>> > > syscolumns...")
>> > > Database 'test': beginning upgrade step [ID 998]:
>> > > executing SQL statement ("update syscolumns set colid
>> > > = colid...")
>> > > (5 rows affected)
>> > > Database 'test': beginning upgrade step [ID 10]:
>> > > alter table (table syscolumns)
>> > > (538 rows affected)
>> > > Database 'test': beginning upgrade step [ID 11]:
>> > > insert basic data into bootstrap system tables (table
>> > > sysobjects) Database 'test': beginning upgrade step
>> > > [ID 69]: executing SQL statement ("if 2 = (select
>> > > count(name) from sys...")
>> > > Database 'test': beginning upgrade step [ID 12]:
>> > > alter table (table systabstats)
>> > > (65 rows affected)
>> > > (63 rows affected)
>> > > Database 'test': beginning upgrade step [ID 26]:
>> > > alter table (table sysprocedures)
>> > > Database 'test': beginning upgrade step [ID 27]:
>> > > alter table (table syscomments)
>> > > (0 rows affected)
>> > > Database 'test': beginning upgrade step [ID 28]:
>> > > alter table (table syssegments)
>> > > Database 'test': beginning upgrade step [ID 29]:
>> > > alter table (table sysprotects)
>> > > Database 'test': beginning upgrade step [ID 30]:
>> > > alter table (table sysusers)
>> > > Non-clustered index (index id = 2) is being rebuilt.
>> > > Non-clustered index (index id = 3) is being rebuilt.
>> > > Database 'test': beginning upgrade step [ID 31]:
>> > > alter table (table sysalternates)
>> > > Database 'test': beginning upgrade step [ID 32]:
>> > > alter table (table sysdepends)
>> > > Database 'test': beginning upgrade step [ID 33]:
>> > > alter table (table syskeys)
>> > > Database 'test': beginning upgrade step [ID 47]:
>> > > creating table (table syspartitionkeys)
>> > > Database 'test': beginning upgrade step [ID 51]:
>> > > alter table (table sysreferences)
>> > > Non-clustered index (index id = 2) is being rebuilt.
>> > > Non-clustered index (index id = 3) is being rebuilt.
>> > > Database 'test': beginning upgrade step [ID 52]:
>> > > alter table (table sysconstraints)
>> > > Non-clustered index (index id = 2) is being rebuilt.
>> > > Database 'test': beginning upgrade step [ID 54]:
>> > > alter table (table systhresholds)
>> > > Database 'test': beginning upgrade step [ID 55]:
>> > > alter table (table sysroles)
>> > > Database 'test': beginning upgrade step [ID 60]:
>> > > alter table (table sysstatistics)
>> > > (0 rows affected)
>> > > (1 row affected)
>> > > (0 rows affected)
>> > > Database 'test': beginning upgrade step [ID 62]:
>> > > alter table (table sysjars)
>> > > Database 'test': beginning upgrade step [ID 65]:
>> > > alter table (table sysqueryplans)
>> > > Database 'test': beginning upgrade step [ID 68]:
>> > > creating table (table sysencryptkeys)
>> > > Database 'test': beginning upgrade step [ID 80]:
>> > > alter table (table sysattributes)
>> > > Non-clustered index (index id = 2) is being rebuilt.
>> > > Database 'test': beginning upgrade step [ID 91]:
>> > > executing SQL statement ("update systypes set
>> > > length=@@maxpag...")
>> > > (0 rows affected)
>> > > Database 'test': beginning upgrade step [ID 245]:
>> > > Update system roles in system tables.
>> > > (0 rows affected)
>> > > Initialization of sysusers in database test found that
>> > > the row for system role 'sa_role' is missing. Adding
>> > > the required row to
>> > > sysusers.
>> > > ASE is terminating this process.
>> > > 1> exit
>> > >
>> > >
>> > > [sybase_dev]sybase@devbox $ isql -Usa -w2000
>> > > Password:
>> > > 1> dbcc traceon (990)
>> > > 2> go
>> > > DBCC execution completed. If DBCC printed error
>> > > messages , contact a user with System Administrator
>> > > (SA) role. 1> use test
>> > > 2> go
>> > > 1> select * from sysusers
>> > > 2> go
>> > > Msg 950, Level 14, State 1:
>> > > Server 'lon_oms_sql_dev':
>> > > Database 'test' is currently offline. Please wait and
>> > > try your command again later.
>> > > 1> insert into sysusers values ( -2, 16384, 16384,
>> > > "sa_role", NULL)
>> > > 2> go
>> > > Msg 950, Level 14, State 1:
>> > > Server 'lon_oms_sql_dev':
>> > > Database 'test' is currently offline. Please wait and
>> > > try your command again later.
>> > > 1>
>> > >
>> >
>> > Is "statement cache" config parameter is enabled in
>> > your server ? , if yes, disable it and try again
>> >
>> > -HTH
>> > Manish Negandhi
>> > [TeamSybase]
>> >
>> >


Raj Posted on 2011-07-21 07:00:07.0Z
Sender: 3806.4e2786ab.846930886@sybase.com
From: Raj
Newsgroups: sybase.public.ase.general
Subject: Re: trace flag 990
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e27ce77.4702.1681692777@sybase.com>
References: <4e23ac2e$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Jul 2011 00:00:07 -0700
X-Trace: forums-1-dub 1311231607 10.22.241.41 (21 Jul 2011 00:00:07 -0700)
X-Original-Trace: 21 Jul 2011 00:00:07 -0700, 10.22.241.41
Lines: 577
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30360
Article PK: 72539

Hi Manish, please find the desired output.

1> dbcc traceon (3604)
2> go
DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.
1> dbcc prsqlcache
2> go

Start of SSQL Hash Table at 0x10023a83050

Memory configured: 100 2k pages Memory used: 155 2k pages


Bucket# 114 address 0x10023a83770

SSQL_DESC 0x10023df5050
ssql_name *ss1777061467_1277116508ss*
ssql_hashkey 0x4c1f405c ssql_id 1777061467
ssql_suid 3 ssql_uid 1 ssql_dbid 1
ssql_spid 0
ssql_status 0x20 ssql_parallel_deg 1
ssql_isolate 1 ssql_tranmode 0
ssql_keep 0 ssql_usecnt 52 ssql_pgcount 48
SQL TEXT: SELECT typ = "1-> ", time = @time, spid = right(
str(p.spid), 4), blocked = right( str(p.blocked), 4),
hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
10), dbname = substring('xxxxxxxxxxxxxxxxxxx', 1, 15),
login_name = substring(u.name, 1, 15), program_name =
substring(ltrim(p.program_name + "<<<NO_PROGRAM>>>"), 1,
16), locktype = substring('xxxxxxxxxxxxxx', 1, 14),
table_name = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', page = right(
str(0000000), 7
) FROM master..sysprocesses p, master..syslogins u WHERE
p.blocked <> @@@V0_INT AND p.spid not in (select spid from
master..syslocks) AND p.suid = u.suid AND p.spid <> @@spid
UNION ALL SELECT typ = "1-> ", time = @time, spid = right(
str(l.spid), 4), blocked = right( str(p.blocked), 4),
hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
10), dbname = substring(db_name(l.dbid), 1, 15), login_name
= substring(u.name, 1, 15), program_name = substring(ltrim(
p.program_name + "<<<NO_PROGRAM>>>"), 1, 16), locktype =
substring(v.name, 1, 14), table_name = object_name(l.id,
l.dbid), page = right( str(l.page), 7) FROM master..syslocks
l, master..sysprocesses p, master..syslogins u,
master..spt_values v WHERE l.type = v.number AND v.type =
@@@V1_VCHAR1 AND l.spid = p.spid AND p.suid = u.suid AND
l.spid <> @@spid ORDER BY spid, dbname, table_name,
locktype, page

ssql_obdcl: (@@@V1_VCHAR1 VARCHAR(64), @time CHAR(8) output,
@@@V0_INT INT)

Bucket# 161 address 0x10023a83a60

SSQL_DESC 0x10023df6a30
ssql_name *ss1825061638_1628596030ss*
ssql_hashkey 0x6112673e ssql_id 1825061638
ssql_suid 3 ssql_uid 1 ssql_dbid 1
ssql_spid 0
ssql_status 0x20 ssql_parallel_deg 1
ssql_isolate 1 ssql_tranmode 0
ssql_keep 0 ssql_usecnt 52 ssql_pgcount 24
SQL TEXT: select typ = "4-> ", time = @time, spid = right(
str(spid), 4), db_name = substring(db_name(dbid), 1, 20),
dbid, page, xactid, name = substring(name, 1, 30), startime
= convert(char(8), starttime, 8), duration =
right(convert(char(3), (100+datediff(hour, starttime,
getdate())) % 1000), 2) + right(convert(char(8),
dateadd(second, datediff(second, starttime, getdate()),
'19000101'), 8), 6) from master..syslogshold where starttime
<= getdate() UNION sel
ect typ = "4-> ", time = @time, spid = right( str(spid), 4),
db_name = substring(db_name(dbid), 1, 20), dbid, page,
xactid, name = substring(name, 1, 30), startime =
convert(char(8), starttime, 8), duration = "00:00:00" from
master..syslogshold where starttime > getdate() -- for
Sybase 12 bug
--
-- Detail set 5: Information from sysusages about space
usage in tempdb
--

ssql_obdcl: (@time CHAR(8) output)

Bucket# 293 address 0x10023a842a0

SSQL_DESC 0x10023df5950
ssql_name *ss1793061524_0326508089ss*
ssql_hashkey 0x13761e39 ssql_id 1793061524
ssql_suid 3 ssql_uid 1 ssql_dbid 1
ssql_spid 0
ssql_status 0x20 ssql_parallel_deg 1
ssql_isolate 1 ssql_tranmode 0
ssql_keep 0 ssql_usecnt 52 ssql_pgcount 32
SQL TEXT: SELECT typ = "2-> ", time = @time, spid = right(
str(p.spid), 4), blocked = right( str(p.blocked), 4),
hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
10), db_name = convert(char(15),d.name), login_name =
convert(char(15),isnull(u.name,'null')), program_name =
substring(ltrim(p.program_name + "<<<NO_PROGRAM>>>"), 1,
16), p.cmd, p.status, p.cpu, p.physical_io FROM
master..sysprocesses p, master..syslogins u,
master..sysdatabases d WHERE u.sui
d =* p.suid AND d.dbid = p.dbid AND p.spid <> @@spid AND
p.cmd not in (@@@V0_VCHAR1, @@@V1_VCHAR1, @@@V2_VCHAR1,
@@@V3_VCHAR1, -- 'CHECKPOINT SLEEP', -- want to see this
every sample, for the time being; needs outer join to
syslogins
@@@V4_VCHAR1, @@@V5_VCHAR1, @@@V6_VCHAR1, @@@V7_VCHAR1,
@@@V8_VCHAR1, @@@V9_VCHAR1, @@@V10_VCHAR1, @@@V11_VCHAR1)
AND NOT (p.cmd = @@@V12_VCHAR1 and p.program_name =
@@@V13_VCHAR1) -- non-rep. server DBCCs will be reported
ORDER BY p.spid

ssql_obdcl: (@@@V11_VCHAR1 VARCHAR(64), @@@V10_VCHAR1
VARCHAR(64), @@@V9_VCHAR1 VARCHAR(64), @@@V8_VCHAR1
VARCHAR(64), @@@V7_VCHAR1 VARCHAR(64), @@@V6_VCHAR1
VARCHAR(64), @@@V5_VCHAR1 VARCHAR(64), @@@V4_VCHAR1
VARCHAR(64), @@@V3_VCHAR1 VARCHAR(64), @@@V2_VCHAR1
VARCHAR(64), @@@V1_VCHAR1 VARCHAR(64), @@@V0_VCHAR1
VARCHAR(64), @@@V12_VCHAR1 VARCHAR(64), @@@V13_VCHAR1
VARCHAR(64), @time CHAR(8) output)

Bucket# 511 address 0x10023a85040

SSQL_DESC 0x10023df6130
ssql_name *ss1809061581_1947078997ss*
ssql_hashkey 0x740e1155 ssql_id 1809061581
ssql_suid 3 ssql_uid 1 ssql_dbid 1
ssql_spid 0
ssql_status 0x20 ssql_parallel_deg 1
ssql_isolate 1 ssql_tranmode 0
ssql_keep 0 ssql_usecnt 52 ssql_pgcount 32
SQL TEXT: SELECT typ = "3-> ", time = @time, spid = right(
str(p.spid), 4), blocked = right( str(p.blocked), 4),
hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
10), db_name = convert(char(15),d.name), login_name =
convert(char(15),isnull(u.name,'null')), program_name =
substring(ltrim(p.program_name + "<<<NO_PROGRAM>>>"), 1,
16), p.cmd, p.status, proc_name = isnull(object_name(p.id,
p.dbid), "<no_procedure>"), line = right( str(p.linenum),
5), tmblk
= right( str(p.time_blocked), 5) FROM master..sysprocesses
p, master..syslogins u, master..sysdatabases d WHERE u.suid
=* p.suid AND d.dbid = p.dbid AND p.spid <> @@spid AND p.cmd
not in (@@@V0_VCHAR1, @@@V1_VCHAR1, @@@V2_VCHAR1,
@@@V3_VCHAR1, -- 'CHECKPOINT SLEEP', -- want to see this
every sample, for the time being; needs outer join to
syslogins
@@@V4_VCHAR1, @@@V5_VCHAR1, @@@V6_VCHAR1, @@@V7_VCHAR1,
@@@V8_VCHAR1, @@@V9_VCHAR1, @@@V10_VCHAR1, @@@V11_VCHAR1)
AND NOT (p.cmd = @@@V12_VCHAR1 and p.program_name =
@@@V13_VCHAR1) -- non-rep. server DBCCs will be reported
ORDER BY p.spid -- #endif

ssql_obdcl: (@@@V11_VCHAR1 VARCHAR(64), @@@V10_VCHAR1
VARCHAR(64), @@@V9_VCHAR1 VARCHAR(64), @@@V8_VCHAR1
VARCHAR(64), @@@V7_VCHAR1 VARCHAR(64), @@@V6_VCHAR1
VARCHAR(64), @@@V5_VCHAR1 VARCHAR(64), @@@V4_VCHAR1
VARCHAR(64), @@@V3_VCHAR1 VARCHAR(64), @@@V2_VCHAR1
VARCHAR(64), @@@V1_VCHAR1 VARCHAR(64), @@@V0_VCHAR1
VARCHAR(64), @@@V12_VCHAR1 VARCHAR(64), @@@V13_VCHAR1
VARCHAR(64), @time CHAR(8) output)

End of SSQL Hash Table

DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.
1>
2>
3> reset




1> dbcc traceon (990)
2> go
DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.
1> dbcc prsqlcache
2> go

Start of SSQL Hash Table at 0x10023a83050

Memory configured: 100 2k pages Memory used: 155 2k pages


Bucket# 114 address 0x10023a83770

SSQL_DESC 0x10023df5050
ssql_name *ss1777061467_1277116508ss*
ssql_hashkey 0x4c1f405c ssql_id 1777061467
ssql_suid 3 ssql_uid 1 ssql_dbid 1
ssql_spid 0
ssql_status 0x20 ssql_parallel_deg 1
ssql_isolate 1 ssql_tranmode 0
ssql_keep 0 ssql_usecnt 53 ssql_pgcount 48
SQL TEXT: SELECT typ = "1-> ", time = @time, spid = right(
str(p.spid), 4), blocked = right( str(p.blocked), 4),
hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
10), dbname = substring('xxxxxxxxxxxxxxxxxxx', 1, 15),
login_name = substring(u.name, 1, 15), program_name =
substring(ltrim(p.program_name + "<<<NO_PROGRAM>>>"), 1,
16), locktype = substring('xxxxxxxxxxxxxx', 1, 14),
table_name = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', page = right(
str(0000000), 7
) FROM master..sysprocesses p, master..syslogins u WHERE
p.blocked <> @@@V0_INT AND p.spid not in (select spid from
master..syslocks) AND p.suid = u.suid AND p.spid <> @@spid
UNION ALL SELECT typ = "1-> ", time = @time, spid = right(
str(l.spid), 4), blocked = right( str(p.blocked), 4),
hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
10), dbname = substring(db_name(l.dbid), 1, 15), login_name
= substring(u.name, 1, 15), program_name = substring(ltrim(
p.program_name + "<<<NO_PROGRAM>>>"), 1, 16), locktype =
substring(v.name, 1, 14), table_name = object_name(l.id,
l.dbid), page = right( str(l.page), 7) FROM master..syslocks
l, master..sysprocesses p, master..syslogins u,
master..spt_values v WHERE l.type = v.number AND v.type =
@@@V1_VCHAR1 AND l.spid = p.spid AND p.suid = u.suid AND
l.spid <> @@spid ORDER BY spid, dbname, table_name,
locktype, page

ssql_obdcl: (@@@V1_VCHAR1 VARCHAR(64), @time CHAR(8) output,
@@@V0_INT INT)

Bucket# 161 address 0x10023a83a60

SSQL_DESC 0x10023df6a30
ssql_name *ss1825061638_1628596030ss*
ssql_hashkey 0x6112673e ssql_id 1825061638
ssql_suid 3 ssql_uid 1 ssql_dbid 1
ssql_spid 0
ssql_status 0x20 ssql_parallel_deg 1
ssql_isolate 1 ssql_tranmode 0
ssql_keep 0 ssql_usecnt 53 ssql_pgcount 24
SQL TEXT: select typ = "4-> ", time = @time, spid = right(
str(spid), 4), db_name = substring(db_name(dbid), 1, 20),
dbid, page, xactid, name = substring(name, 1, 30), startime
= convert(char(8), starttime, 8), duration =
right(convert(char(3), (100+datediff(hour, starttime,
getdate())) % 1000), 2) + right(convert(char(8),
dateadd(second, datediff(second, starttime, getdate()),
'19000101'), 8), 6) from master..syslogshold where starttime
<= getdate() UNION sel
ect typ = "4-> ", time = @time, spid = right( str(spid), 4),
db_name = substring(db_name(dbid), 1, 20), dbid, page,
xactid, name = substring(name, 1, 30), startime =
convert(char(8), starttime, 8), duration = "00:00:00" from
master..syslogshold where starttime > getdate() -- for
Sybase 12 bug
--
-- Detail set 5: Information from sysusages about space
usage in tempdb
--

ssql_obdcl: (@time CHAR(8) output)

Bucket# 293 address 0x10023a842a0

SSQL_DESC 0x10023df5950
ssql_name *ss1793061524_0326508089ss*
ssql_hashkey 0x13761e39 ssql_id 1793061524
ssql_suid 3 ssql_uid 1 ssql_dbid 1
ssql_spid 0
ssql_status 0x20 ssql_parallel_deg 1
ssql_isolate 1 ssql_tranmode 0
ssql_keep 0 ssql_usecnt 53 ssql_pgcount 32
SQL TEXT: SELECT typ = "2-> ", time = @time, spid = right(
str(p.spid), 4), blocked = right( str(p.blocked), 4),
hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
10), db_name = convert(char(15),d.name), login_name =
convert(char(15),isnull(u.name,'null')), program_name =
substring(ltrim(p.program_name + "<<<NO_PROGRAM>>>"), 1,
16), p.cmd, p.status, p.cpu, p.physical_io FROM
master..sysprocesses p, master..syslogins u,
master..sysdatabases d WHERE u.sui
d =* p.suid AND d.dbid = p.dbid AND p.spid <> @@spid AND
p.cmd not in (@@@V0_VCHAR1, @@@V1_VCHAR1, @@@V2_VCHAR1,
@@@V3_VCHAR1, -- 'CHECKPOINT SLEEP', -- want to see this
every sample, for the time being; needs outer join to
syslogins
@@@V4_VCHAR1, @@@V5_VCHAR1, @@@V6_VCHAR1, @@@V7_VCHAR1,
@@@V8_VCHAR1, @@@V9_VCHAR1, @@@V10_VCHAR1, @@@V11_VCHAR1)
AND NOT (p.cmd = @@@V12_VCHAR1 and p.program_name =
@@@V13_VCHAR1) -- non-rep. server DBCCs will be reported
ORDER BY p.spid

ssql_obdcl: (@@@V11_VCHAR1 VARCHAR(64), @@@V10_VCHAR1
VARCHAR(64), @@@V9_VCHAR1 VARCHAR(64), @@@V8_VCHAR1
VARCHAR(64), @@@V7_VCHAR1 VARCHAR(64), @@@V6_VCHAR1
VARCHAR(64), @@@V5_VCHAR1 VARCHAR(64), @@@V4_VCHAR1
VARCHAR(64), @@@V3_VCHAR1 VARCHAR(64), @@@V2_VCHAR1
VARCHAR(64), @@@V1_VCHAR1 VARCHAR(64), @@@V0_VCHAR1
VARCHAR(64), @@@V12_VCHAR1 VARCHAR(64), @@@V13_VCHAR1
VARCHAR(64), @time CHAR(8) output)

Bucket# 511 address 0x10023a85040

SSQL_DESC 0x10023df6130
ssql_name *ss1809061581_1947078997ss*
ssql_hashkey 0x740e1155 ssql_id 1809061581
ssql_suid 3 ssql_uid 1 ssql_dbid 1
ssql_spid 0
ssql_status 0x20 ssql_parallel_deg 1
ssql_isolate 1 ssql_tranmode 0
ssql_keep 0 ssql_usecnt 53 ssql_pgcount 32
SQL TEXT: SELECT typ = "3-> ", time = @time, spid = right(
str(p.spid), 4), blocked = right( str(p.blocked), 4),
hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
10), db_name = convert(char(15),d.name), login_name =
convert(char(15),isnull(u.name,'null')), program_name =
substring(ltrim(p.program_name + "<<<NO_PROGRAM>>>"), 1,
16), p.cmd, p.status, proc_name = isnull(object_name(p.id,
p.dbid), "<no_procedure>"), line = right( str(p.linenum),
5), tmblk
= right( str(p.time_blocked), 5) FROM master..sysprocesses
p, master..syslogins u, master..sysdatabases d WHERE u.suid
=* p.suid AND d.dbid = p.dbid AND p.spid <> @@spid AND p.cmd
not in (@@@V0_VCHAR1, @@@V1_VCHAR1, @@@V2_VCHAR1,
@@@V3_VCHAR1, -- 'CHECKPOINT SLEEP', -- want to see this
every sample, for the time being; needs outer join to
syslogins
@@@V4_VCHAR1, @@@V5_VCHAR1, @@@V6_VCHAR1, @@@V7_VCHAR1,
@@@V8_VCHAR1, @@@V9_VCHAR1, @@@V10_VCHAR1, @@@V11_VCHAR1)
AND NOT (p.cmd = @@@V12_VCHAR1 and p.program_name =
@@@V13_VCHAR1) -- non-rep. server DBCCs will be reported
ORDER BY p.spid -- #endif

ssql_obdcl: (@@@V11_VCHAR1 VARCHAR(64), @@@V10_VCHAR1
VARCHAR(64), @@@V9_VCHAR1 VARCHAR(64), @@@V8_VCHAR1
VARCHAR(64), @@@V7_VCHAR1 VARCHAR(64), @@@V6_VCHAR1
VARCHAR(64), @@@V5_VCHAR1 VARCHAR(64), @@@V4_VCHAR1
VARCHAR(64), @@@V3_VCHAR1 VARCHAR(64), @@@V2_VCHAR1
VARCHAR(64), @@@V1_VCHAR1 VARCHAR(64), @@@V0_VCHAR1
VARCHAR(64), @@@V12_VCHAR1 VARCHAR(64), @@@V13_VCHAR1
VARCHAR(64), @time CHAR(8) output)

End of SSQL Hash Table

DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.
1>

> Can you post output of dbcc prsqlcache just before using
> traceflag 990 ?
>
> -HTH
> Manish Negandhi
> [TeamSybase]
>
> <Raj> wrote in message
> > news:4e1fd98d.63a9.1681692777@sybase.com... Hi Manish,
> > can I learn how "statement cache" help to manipulate the
> > offline database along with trace 990. This helps to
> > resolve the upgrade item: "upgrade step [ID 245]:
> >
> >> Thanks Manish.
> >>
> >> Yes, its worked.
> >>
> >> > <Raj> wrote in message
> >> > > news:4e12a5d4.5dad.1681692777@sybase.com... Thanks
> >> > Manish for your response. >
> >> > > Please find the scenario :
> >> > >
> >> > > 1> load database test from
> >> > >
> "compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp" >>
> > > 2> go >> > > Backup Server session id is: 44. Use
> this value when >> > > executing the 'sp_volchanged'
> system stored procedure >> > > after fulfilling any
> >> > > volume change request from the Backup Server.
> >> > > Backup Server: 4.132.1.1: Attempting to open byte
> >> > > stream device:
> >> > >
> >> > >
> >> >
> >> > >
> >>
> 'compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp::00'
> >> > > Backup Server: 6.28.1.1: Dumpfile name
> 'test1118605DB2 >> > ' section number 1 mounted on byte
> stream > >> >
> >> > >
> >>
> 'compress::/data/sybase/lon_oms_sql_dev/dump/test.dmp::00'
> >> > > Backup Server: 4.188.1.1: Database test: 39942 >>
> kilobytes (13%) LOADED. Backup Server: 4.188.1.1: Database
> >> > > test: 82954 kilobytes (27%) LOADED. >> > > Backup
> Server: 4.188.1.1: Database test: 126474 >> > > kilobytes
> (41%) LOADED. >> > > Backup Server: 4.188.1.1: Database
> test: 169486 >> > > kilobytes (55%) LOADED.
> >> > > Backup Server: 4.188.1.1: Database test: 213006
> >> > > kilobytes (69%) LOADED.
> >> > > Backup Server: 4.188.1.1: Database test: 256018
> >> > > kilobytes (83%) LOADED.
> >> > > Backup Server: 4.188.1.1: Database test: 299538
> >> > > kilobytes (97%) LOADED.
> >> > > Backup Server: 4.188.1.1: Database test: 307220
> >> > > kilobytes (100%) LOADED.
> >> > > Backup Server: 4.188.1.1: Database test: 307228
> >> > > kilobytes (100%) LOADED.
> >> > > Backup Server: 3.42.1.1: LOAD is complete (database
> >> > > test). Caution: You have set up this database to
> >> > > include space on disk 17 for both data and the
> >> > > transaction log. This can make recovery
> >> > > impossible if that disk fails.
> >> > > Started estimating recovery log boundaries for
> >> > > database 'test'.
> >> > > Database 'test', checkpoint=(153523, 18),
> >> > > first=(153523, 18), last=(153523, 21).
> >> > > Completed estimating recovery log boundaries for
> >> > > database 'test'.
> >> > > Started ANALYSIS pass for database 'test'.
> >> > > Completed ANALYSIS pass for database 'test'.
> >> > > Started REDO pass for database 'test'. The total
> >> > > number of log records to process is 4.
> >> > > Redo pass of recovery has processed 1 committed and
> 0 >> > > aborted transactions.
> >> > > Completed REDO pass for database 'test'.
> >> > > Use the ONLINE DATABASE command to bring this
> database >> > > online; ASE will not bring it online
> automatically. >> > > 1> online database test
> >> > > 2> go
> >> > > Started estimating recovery log boundaries for
> >> > > database 'test'.
> >> > > Database 'test', checkpoint=(153523, 18),
> >> > > first=(153523, 18), last=(153523, 21).
> >> > > Completed estimating recovery log boundaries for
> >> > > database 'test'.
> >> > > Started ANALYSIS pass for database 'test'.
> >> > > Completed ANALYSIS pass for database 'test'.
> >> > > Recovery of database 'test' will undo incomplete
> >> > > nested top actions.
> >> > > Database 'test' appears to be at an older revision
> >> > > than the present installation; ASE will assess it,
> and >> > > upgrade it as required.
> >> > > Database 'test': beginning upgrade step [ID 247]:
> >> > > verifying sysstatistics row format
> >> > > Database 'test': beginning upgrade step [ID 2]:
> >> > > validate basic system type data
> >> > > Database 'test': beginning upgrade step [ID 3]:
> >> > > alter table (table sysindexes)
> >> > > (60 rows affected)
> >> > > Database 'test': beginning upgrade step [ID 4]:
> >> > > creating table (table sysanchors)
> >> > > Database 'test': beginning upgrade step [ID 5]:
> >> > > changing name of system table (table syspartitions,
> >> > > new name: sysslices)
> >> > > (1 row affected)
> >> > > (1 row affected)
> >> > > Database 'test': beginning upgrade step [ID 6]:
> >> > > creating table (table syspartitions)
> >> > > Database 'test': beginning upgrade step [ID 7]:
> >> > > insert rows into syspartitions for each system
> table >> > > and index Database 'test': beginning upgrade
> step [ID >> > > 8]: alter table (table systypes)
> >> > > Non-clustered index (index id = 2) is being
> rebuilt. >> > > Database 'test': beginning upgrade step
> [ID 9]: >> > > alter table (table sysobjects)
> >> > > Non-clustered index (index id = 2) is being
> rebuilt. >> > > Database 'test': beginning upgrade step
> [ID 997]: >> > > executing SQL statement ("if exists
> (select 1 from >> > > syscolumns...")
> >> > > Database 'test': beginning upgrade step [ID 998]:
> >> > > executing SQL statement ("update syscolumns set
> colid >> > > = colid...")
> >> > > (5 rows affected)
> >> > > Database 'test': beginning upgrade step [ID 10]:
> >> > > alter table (table syscolumns)
> >> > > (538 rows affected)
> >> > > Database 'test': beginning upgrade step [ID 11]:
> >> > > insert basic data into bootstrap system tables
> (table >> > > sysobjects) Database 'test': beginning
> upgrade step >> > > [ID 69]: executing SQL statement
> ("if 2 = (select >> > > count(name) from sys...")
> >> > > Database 'test': beginning upgrade step [ID 12]:
> >> > > alter table (table systabstats)
> >> > > (65 rows affected)
> >> > > (63 rows affected)
> >> > > Database 'test': beginning upgrade step [ID 26]:
> >> > > alter table (table sysprocedures)
> >> > > Database 'test': beginning upgrade step [ID 27]:
> >> > > alter table (table syscomments)
> >> > > (0 rows affected)
> >> > > Database 'test': beginning upgrade step [ID 28]:
> >> > > alter table (table syssegments)
> >> > > Database 'test': beginning upgrade step [ID 29]:
> >> > > alter table (table sysprotects)
> >> > > Database 'test': beginning upgrade step [ID 30]:
> >> > > alter table (table sysusers)
> >> > > Non-clustered index (index id = 2) is being
> rebuilt. >> > > Non-clustered index (index id = 3) is
> being rebuilt. >> > > Database 'test': beginning upgrade
> step [ID 31]: >> > > alter table (table sysalternates)
> >> > > Database 'test': beginning upgrade step [ID 32]:
> >> > > alter table (table sysdepends)
> >> > > Database 'test': beginning upgrade step [ID 33]:
> >> > > alter table (table syskeys)
> >> > > Database 'test': beginning upgrade step [ID 47]:
> >> > > creating table (table syspartitionkeys)
> >> > > Database 'test': beginning upgrade step [ID 51]:
> >> > > alter table (table sysreferences)
> >> > > Non-clustered index (index id = 2) is being
> rebuilt. >> > > Non-clustered index (index id = 3) is
> being rebuilt. >> > > Database 'test': beginning upgrade
> step [ID 52]: >> > > alter table (table sysconstraints)
> >> > > Non-clustered index (index id = 2) is being
> rebuilt. >> > > Database 'test': beginning upgrade step
> [ID 54]: >> > > alter table (table systhresholds)
> >> > > Database 'test': beginning upgrade step [ID 55]:
> >> > > alter table (table sysroles)
> >> > > Database 'test': beginning upgrade step [ID 60]:
> >> > > alter table (table sysstatistics)
> >> > > (0 rows affected)
> >> > > (1 row affected)
> >> > > (0 rows affected)
> >> > > Database 'test': beginning upgrade step [ID 62]:
> >> > > alter table (table sysjars)
> >> > > Database 'test': beginning upgrade step [ID 65]:
> >> > > alter table (table sysqueryplans)
> >> > > Database 'test': beginning upgrade step [ID 68]:
> >> > > creating table (table sysencryptkeys)
> >> > > Database 'test': beginning upgrade step [ID 80]:
> >> > > alter table (table sysattributes)
> >> > > Non-clustered index (index id = 2) is being
> rebuilt. >> > > Database 'test': beginning upgrade step
> [ID 91]: >> > > executing SQL statement ("update
> systypes set >> > > length=@@maxpag...")
> >> > > (0 rows affected)
> >> > > Database 'test': beginning upgrade step [ID 245]:
> >> > > Update system roles in system tables.
> >> > > (0 rows affected)
> >> > > Initialization of sysusers in database test found
> that >> > > the row for system role 'sa_role' is missing.
> Adding >> > > the required row to
> >> > > sysusers.
> >> > > ASE is terminating this process.
> >> > > 1> exit
> >> > >
> >> > >
> >> > > [sybase_dev]sybase@devbox $ isql -Usa -w2000
> >> > > Password:
> >> > > 1> dbcc traceon (990)
> >> > > 2> go
> >> > > DBCC execution completed. If DBCC printed error
> >> > > messages , contact a user with System Administrator
> >> > > (SA) role. 1> use test
> >> > > 2> go
> >> > > 1> select * from sysusers
> >> > > 2> go
> >> > > Msg 950, Level 14, State 1:
> >> > > Server 'lon_oms_sql_dev':
> >> > > Database 'test' is currently offline. Please wait
> and >> > > try your command again later.
> >> > > 1> insert into sysusers values ( -2, 16384, 16384,
> >> > > "sa_role", NULL)
> >> > > 2> go
> >> > > Msg 950, Level 14, State 1:
> >> > > Server 'lon_oms_sql_dev':
> >> > > Database 'test' is currently offline. Please wait
> and >> > > try your command again later.
> >> > > 1>
> >> > >
> >> >
> >> > Is "statement cache" config parameter is enabled in
> >> > your server ? , if yes, disable it and try again
> >> >
> >> > -HTH
> >> > Manish Negandhi
> >> > [TeamSybase]
> >> >
> >> >
>
>


Manish Negandhi [TeamSybase] Posted on 2011-07-25 16:41:47.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4e23ac2e$1@forums-1-dub> <4e27ce77.4702.1681692777@sybase.com>
Subject: Re: trace flag 990
Lines: 352
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e2d9ccb@forums-1-dub>
Date: 25 Jul 2011 09:41:47 -0700
X-Trace: forums-1-dub 1311612107 10.22.241.152 (25 Jul 2011 09:41:47 -0700)
X-Original-Trace: 25 Jul 2011 09:41:47 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30373
Article PK: 72552


<Raj> wrote in message news:4e27ce77.4702.1681692777@sybase.com...
> Hi Manish, please find the desired output.
>
> 1> dbcc traceon (3604)
> 2> go
> DBCC execution completed. If DBCC printed error messages,
> contact a user with System Administrator (SA) role.
> 1> dbcc prsqlcache
> 2> go
>
> Start of SSQL Hash Table at 0x10023a83050
>
> Memory configured: 100 2k pages Memory used: 155 2k pages
>
>
> Bucket# 114 address 0x10023a83770
>
> SSQL_DESC 0x10023df5050
> ssql_name *ss1777061467_1277116508ss*
> ssql_hashkey 0x4c1f405c ssql_id 1777061467
> ssql_suid 3 ssql_uid 1 ssql_dbid 1
> ssql_spid 0
> ssql_status 0x20 ssql_parallel_deg 1
> ssql_isolate 1 ssql_tranmode 0
> ssql_keep 0 ssql_usecnt 52 ssql_pgcount 48
> SQL TEXT: SELECT typ = "1-> ", time = @time, spid = right(
> str(p.spid), 4), blocked = right( str(p.blocked), 4),
> hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
> 10), dbname = substring('xxxxxxxxxxxxxxxxxxx', 1, 15),
> login_name = substring(u.name, 1, 15), program_name =
> substring(ltrim(p.program_name + "<<<NO_PROGRAM>>>"), 1,
> 16), locktype = substring('xxxxxxxxxxxxxx', 1, 14),
> table_name = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', page = right(
> str(0000000), 7
> ) FROM master..sysprocesses p, master..syslogins u WHERE
> p.blocked <> @@@V0_INT AND p.spid not in (select spid from
> master..syslocks) AND p.suid = u.suid AND p.spid <> @@spid
> UNION ALL SELECT typ = "1-> ", time = @time, spid = right(
> str(l.spid), 4), blocked = right( str(p.blocked), 4),
> hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
> 10), dbname = substring(db_name(l.dbid), 1, 15), login_name
> = substring(u.name, 1, 15), program_name = substring(ltrim(
> p.program_name + "<<<NO_PROGRAM>>>"), 1, 16), locktype =
> substring(v.name, 1, 14), table_name = object_name(l.id,
> l.dbid), page = right( str(l.page), 7) FROM master..syslocks
> l, master..sysprocesses p, master..syslogins u,
> master..spt_values v WHERE l.type = v.number AND v.type =
> @@@V1_VCHAR1 AND l.spid = p.spid AND p.suid = u.suid AND
> l.spid <> @@spid ORDER BY spid, dbname, table_name,
> locktype, page
>
> ssql_obdcl: (@@@V1_VCHAR1 VARCHAR(64), @time CHAR(8) output,
> @@@V0_INT INT)
>
> Bucket# 161 address 0x10023a83a60
>
> SSQL_DESC 0x10023df6a30
> ssql_name *ss1825061638_1628596030ss*
> ssql_hashkey 0x6112673e ssql_id 1825061638
> ssql_suid 3 ssql_uid 1 ssql_dbid 1
> ssql_spid 0
> ssql_status 0x20 ssql_parallel_deg 1
> ssql_isolate 1 ssql_tranmode 0
> ssql_keep 0 ssql_usecnt 52 ssql_pgcount 24
> SQL TEXT: select typ = "4-> ", time = @time, spid = right(
> str(spid), 4), db_name = substring(db_name(dbid), 1, 20),
> dbid, page, xactid, name = substring(name, 1, 30), startime
> = convert(char(8), starttime, 8), duration =
> right(convert(char(3), (100+datediff(hour, starttime,
> getdate())) % 1000), 2) + right(convert(char(8),
> dateadd(second, datediff(second, starttime, getdate()),
> '19000101'), 8), 6) from master..syslogshold where starttime
> <= getdate() UNION sel
> ect typ = "4-> ", time = @time, spid = right( str(spid), 4),
> db_name = substring(db_name(dbid), 1, 20), dbid, page,
> xactid, name = substring(name, 1, 30), startime =
> convert(char(8), starttime, 8), duration = "00:00:00" from
> master..syslogshold where starttime > getdate() -- for
> Sybase 12 bug
> --
> -- Detail set 5: Information from sysusages about space
> usage in tempdb
> --
>
> ssql_obdcl: (@time CHAR(8) output)
>
> Bucket# 293 address 0x10023a842a0
>
> SSQL_DESC 0x10023df5950
> ssql_name *ss1793061524_0326508089ss*
> ssql_hashkey 0x13761e39 ssql_id 1793061524
> ssql_suid 3 ssql_uid 1 ssql_dbid 1
> ssql_spid 0
> ssql_status 0x20 ssql_parallel_deg 1
> ssql_isolate 1 ssql_tranmode 0
> ssql_keep 0 ssql_usecnt 52 ssql_pgcount 32
> SQL TEXT: SELECT typ = "2-> ", time = @time, spid = right(
> str(p.spid), 4), blocked = right( str(p.blocked), 4),
> hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
> 10), db_name = convert(char(15),d.name), login_name =
> convert(char(15),isnull(u.name,'null')), program_name =
> substring(ltrim(p.program_name + "<<<NO_PROGRAM>>>"), 1,
> 16), p.cmd, p.status, p.cpu, p.physical_io FROM
> master..sysprocesses p, master..syslogins u,
> master..sysdatabases d WHERE u.sui
> d =* p.suid AND d.dbid = p.dbid AND p.spid <> @@spid AND
> p.cmd not in (@@@V0_VCHAR1, @@@V1_VCHAR1, @@@V2_VCHAR1,
> @@@V3_VCHAR1, -- 'CHECKPOINT SLEEP', -- want to see this
> every sample, for the time being; needs outer join to
> syslogins
> @@@V4_VCHAR1, @@@V5_VCHAR1, @@@V6_VCHAR1, @@@V7_VCHAR1,
> @@@V8_VCHAR1, @@@V9_VCHAR1, @@@V10_VCHAR1, @@@V11_VCHAR1)
> AND NOT (p.cmd = @@@V12_VCHAR1 and p.program_name =
> @@@V13_VCHAR1) -- non-rep. server DBCCs will be reported
> ORDER BY p.spid
>
> ssql_obdcl: (@@@V11_VCHAR1 VARCHAR(64), @@@V10_VCHAR1
> VARCHAR(64), @@@V9_VCHAR1 VARCHAR(64), @@@V8_VCHAR1
> VARCHAR(64), @@@V7_VCHAR1 VARCHAR(64), @@@V6_VCHAR1
> VARCHAR(64), @@@V5_VCHAR1 VARCHAR(64), @@@V4_VCHAR1
> VARCHAR(64), @@@V3_VCHAR1 VARCHAR(64), @@@V2_VCHAR1
> VARCHAR(64), @@@V1_VCHAR1 VARCHAR(64), @@@V0_VCHAR1
> VARCHAR(64), @@@V12_VCHAR1 VARCHAR(64), @@@V13_VCHAR1
> VARCHAR(64), @time CHAR(8) output)
>
> Bucket# 511 address 0x10023a85040
>
> SSQL_DESC 0x10023df6130
> ssql_name *ss1809061581_1947078997ss*
> ssql_hashkey 0x740e1155 ssql_id 1809061581
> ssql_suid 3 ssql_uid 1 ssql_dbid 1
> ssql_spid 0
> ssql_status 0x20 ssql_parallel_deg 1
> ssql_isolate 1 ssql_tranmode 0
> ssql_keep 0 ssql_usecnt 52 ssql_pgcount 32
> SQL TEXT: SELECT typ = "3-> ", time = @time, spid = right(
> str(p.spid), 4), blocked = right( str(p.blocked), 4),
> hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
> 10), db_name = convert(char(15),d.name), login_name =
> convert(char(15),isnull(u.name,'null')), program_name =
> substring(ltrim(p.program_name + "<<<NO_PROGRAM>>>"), 1,
> 16), p.cmd, p.status, proc_name = isnull(object_name(p.id,
> p.dbid), "<no_procedure>"), line = right( str(p.linenum),
> 5), tmblk
> = right( str(p.time_blocked), 5) FROM master..sysprocesses
> p, master..syslogins u, master..sysdatabases d WHERE u.suid
> =* p.suid AND d.dbid = p.dbid AND p.spid <> @@spid AND p.cmd
> not in (@@@V0_VCHAR1, @@@V1_VCHAR1, @@@V2_VCHAR1,
> @@@V3_VCHAR1, -- 'CHECKPOINT SLEEP', -- want to see this
> every sample, for the time being; needs outer join to
> syslogins
> @@@V4_VCHAR1, @@@V5_VCHAR1, @@@V6_VCHAR1, @@@V7_VCHAR1,
> @@@V8_VCHAR1, @@@V9_VCHAR1, @@@V10_VCHAR1, @@@V11_VCHAR1)
> AND NOT (p.cmd = @@@V12_VCHAR1 and p.program_name =
> @@@V13_VCHAR1) -- non-rep. server DBCCs will be reported
> ORDER BY p.spid -- #endif
>
> ssql_obdcl: (@@@V11_VCHAR1 VARCHAR(64), @@@V10_VCHAR1
> VARCHAR(64), @@@V9_VCHAR1 VARCHAR(64), @@@V8_VCHAR1
> VARCHAR(64), @@@V7_VCHAR1 VARCHAR(64), @@@V6_VCHAR1
> VARCHAR(64), @@@V5_VCHAR1 VARCHAR(64), @@@V4_VCHAR1
> VARCHAR(64), @@@V3_VCHAR1 VARCHAR(64), @@@V2_VCHAR1
> VARCHAR(64), @@@V1_VCHAR1 VARCHAR(64), @@@V0_VCHAR1
> VARCHAR(64), @@@V12_VCHAR1 VARCHAR(64), @@@V13_VCHAR1
> VARCHAR(64), @time CHAR(8) output)
>
> End of SSQL Hash Table
>
> DBCC execution completed. If DBCC printed error messages,
> contact a user with System Administrator (SA) role.
> 1>
> 2>
> 3> reset
>
>
>
>
> 1> dbcc traceon (990)
> 2> go
> DBCC execution completed. If DBCC printed error messages,
> contact a user with System Administrator (SA) role.
> 1> dbcc prsqlcache
> 2> go
>
> Start of SSQL Hash Table at 0x10023a83050
>
> Memory configured: 100 2k pages Memory used: 155 2k pages
>
>
> Bucket# 114 address 0x10023a83770
>
> SSQL_DESC 0x10023df5050
> ssql_name *ss1777061467_1277116508ss*
> ssql_hashkey 0x4c1f405c ssql_id 1777061467
> ssql_suid 3 ssql_uid 1 ssql_dbid 1
> ssql_spid 0
> ssql_status 0x20 ssql_parallel_deg 1
> ssql_isolate 1 ssql_tranmode 0
> ssql_keep 0 ssql_usecnt 53 ssql_pgcount 48
> SQL TEXT: SELECT typ = "1-> ", time = @time, spid = right(
> str(p.spid), 4), blocked = right( str(p.blocked), 4),
> hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
> 10), dbname = substring('xxxxxxxxxxxxxxxxxxx', 1, 15),
> login_name = substring(u.name, 1, 15), program_name =
> substring(ltrim(p.program_name + "<<<NO_PROGRAM>>>"), 1,
> 16), locktype = substring('xxxxxxxxxxxxxx', 1, 14),
> table_name = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', page = right(
> str(0000000), 7
> ) FROM master..sysprocesses p, master..syslogins u WHERE
> p.blocked <> @@@V0_INT AND p.spid not in (select spid from
> master..syslocks) AND p.suid = u.suid AND p.spid <> @@spid
> UNION ALL SELECT typ = "1-> ", time = @time, spid = right(
> str(l.spid), 4), blocked = right( str(p.blocked), 4),
> hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
> 10), dbname = substring(db_name(l.dbid), 1, 15), login_name
> = substring(u.name, 1, 15), program_name = substring(ltrim(
> p.program_name + "<<<NO_PROGRAM>>>"), 1, 16), locktype =
> substring(v.name, 1, 14), table_name = object_name(l.id,
> l.dbid), page = right( str(l.page), 7) FROM master..syslocks
> l, master..sysprocesses p, master..syslogins u,
> master..spt_values v WHERE l.type = v.number AND v.type =
> @@@V1_VCHAR1 AND l.spid = p.spid AND p.suid = u.suid AND
> l.spid <> @@spid ORDER BY spid, dbname, table_name,
> locktype, page
>
> ssql_obdcl: (@@@V1_VCHAR1 VARCHAR(64), @time CHAR(8) output,
> @@@V0_INT INT)
>
> Bucket# 161 address 0x10023a83a60
>
> SSQL_DESC 0x10023df6a30
> ssql_name *ss1825061638_1628596030ss*
> ssql_hashkey 0x6112673e ssql_id 1825061638
> ssql_suid 3 ssql_uid 1 ssql_dbid 1
> ssql_spid 0
> ssql_status 0x20 ssql_parallel_deg 1
> ssql_isolate 1 ssql_tranmode 0
> ssql_keep 0 ssql_usecnt 53 ssql_pgcount 24
> SQL TEXT: select typ = "4-> ", time = @time, spid = right(
> str(spid), 4), db_name = substring(db_name(dbid), 1, 20),
> dbid, page, xactid, name = substring(name, 1, 30), startime
> = convert(char(8), starttime, 8), duration =
> right(convert(char(3), (100+datediff(hour, starttime,
> getdate())) % 1000), 2) + right(convert(char(8),
> dateadd(second, datediff(second, starttime, getdate()),
> '19000101'), 8), 6) from master..syslogshold where starttime
> <= getdate() UNION sel
> ect typ = "4-> ", time = @time, spid = right( str(spid), 4),
> db_name = substring(db_name(dbid), 1, 20), dbid, page,
> xactid, name = substring(name, 1, 30), startime =
> convert(char(8), starttime, 8), duration = "00:00:00" from
> master..syslogshold where starttime > getdate() -- for
> Sybase 12 bug
> --
> -- Detail set 5: Information from sysusages about space
> usage in tempdb
> --
>
> ssql_obdcl: (@time CHAR(8) output)
>
> Bucket# 293 address 0x10023a842a0
>
> SSQL_DESC 0x10023df5950
> ssql_name *ss1793061524_0326508089ss*
> ssql_hashkey 0x13761e39 ssql_id 1793061524
> ssql_suid 3 ssql_uid 1 ssql_dbid 1
> ssql_spid 0
> ssql_status 0x20 ssql_parallel_deg 1
> ssql_isolate 1 ssql_tranmode 0
> ssql_keep 0 ssql_usecnt 53 ssql_pgcount 32
> SQL TEXT: SELECT typ = "2-> ", time = @time, spid = right(
> str(p.spid), 4), blocked = right( str(p.blocked), 4),
> hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
> 10), db_name = convert(char(15),d.name), login_name =
> convert(char(15),isnull(u.name,'null')), program_name =
> substring(ltrim(p.program_name + "<<<NO_PROGRAM>>>"), 1,
> 16), p.cmd, p.status, p.cpu, p.physical_io FROM
> master..sysprocesses p, master..syslogins u,
> master..sysdatabases d WHERE u.sui
> d =* p.suid AND d.dbid = p.dbid AND p.spid <> @@spid AND
> p.cmd not in (@@@V0_VCHAR1, @@@V1_VCHAR1, @@@V2_VCHAR1,
> @@@V3_VCHAR1, -- 'CHECKPOINT SLEEP', -- want to see this
> every sample, for the time being; needs outer join to
> syslogins
> @@@V4_VCHAR1, @@@V5_VCHAR1, @@@V6_VCHAR1, @@@V7_VCHAR1,
> @@@V8_VCHAR1, @@@V9_VCHAR1, @@@V10_VCHAR1, @@@V11_VCHAR1)
> AND NOT (p.cmd = @@@V12_VCHAR1 and p.program_name =
> @@@V13_VCHAR1) -- non-rep. server DBCCs will be reported
> ORDER BY p.spid
>
> ssql_obdcl: (@@@V11_VCHAR1 VARCHAR(64), @@@V10_VCHAR1
> VARCHAR(64), @@@V9_VCHAR1 VARCHAR(64), @@@V8_VCHAR1
> VARCHAR(64), @@@V7_VCHAR1 VARCHAR(64), @@@V6_VCHAR1
> VARCHAR(64), @@@V5_VCHAR1 VARCHAR(64), @@@V4_VCHAR1
> VARCHAR(64), @@@V3_VCHAR1 VARCHAR(64), @@@V2_VCHAR1
> VARCHAR(64), @@@V1_VCHAR1 VARCHAR(64), @@@V0_VCHAR1
> VARCHAR(64), @@@V12_VCHAR1 VARCHAR(64), @@@V13_VCHAR1
> VARCHAR(64), @time CHAR(8) output)
>
> Bucket# 511 address 0x10023a85040
>
> SSQL_DESC 0x10023df6130
> ssql_name *ss1809061581_1947078997ss*
> ssql_hashkey 0x740e1155 ssql_id 1809061581
> ssql_suid 3 ssql_uid 1 ssql_dbid 1
> ssql_spid 0
> ssql_status 0x20 ssql_parallel_deg 1
> ssql_isolate 1 ssql_tranmode 0
> ssql_keep 0 ssql_usecnt 53 ssql_pgcount 32
> SQL TEXT: SELECT typ = "3-> ", time = @time, spid = right(
> str(p.spid), 4), blocked = right( str(p.blocked), 4),
> hostname = substring(ltrim(p.hostname + "<NO_HOST>"), 1,
> 10), db_name = convert(char(15),d.name), login_name =
> convert(char(15),isnull(u.name,'null')), program_name =
> substring(ltrim(p.program_name + "<<<NO_PROGRAM>>>"), 1,
> 16), p.cmd, p.status, proc_name = isnull(object_name(p.id,
> p.dbid), "<no_procedure>"), line = right( str(p.linenum),
> 5), tmblk
> = right( str(p.time_blocked), 5) FROM master..sysprocesses
> p, master..syslogins u, master..sysdatabases d WHERE u.suid
> =* p.suid AND d.dbid = p.dbid AND p.spid <> @@spid AND p.cmd
> not in (@@@V0_VCHAR1, @@@V1_VCHAR1, @@@V2_VCHAR1,
> @@@V3_VCHAR1, -- 'CHECKPOINT SLEEP', -- want to see this
> every sample, for the time being; needs outer join to
> syslogins
> @@@V4_VCHAR1, @@@V5_VCHAR1, @@@V6_VCHAR1, @@@V7_VCHAR1,
> @@@V8_VCHAR1, @@@V9_VCHAR1, @@@V10_VCHAR1, @@@V11_VCHAR1)
> AND NOT (p.cmd = @@@V12_VCHAR1 and p.program_name =
> @@@V13_VCHAR1) -- non-rep. server DBCCs will be reported
> ORDER BY p.spid -- #endif
>
> ssql_obdcl: (@@@V11_VCHAR1 VARCHAR(64), @@@V10_VCHAR1
> VARCHAR(64), @@@V9_VCHAR1 VARCHAR(64), @@@V8_VCHAR1
> VARCHAR(64), @@@V7_VCHAR1 VARCHAR(64), @@@V6_VCHAR1
> VARCHAR(64), @@@V5_VCHAR1 VARCHAR(64), @@@V4_VCHAR1
> VARCHAR(64), @@@V3_VCHAR1 VARCHAR(64), @@@V2_VCHAR1
> VARCHAR(64), @@@V1_VCHAR1 VARCHAR(64), @@@V0_VCHAR1
> VARCHAR(64), @@@V12_VCHAR1 VARCHAR(64), @@@V13_VCHAR1
> VARCHAR(64), @time CHAR(8) output)
>
> End of SSQL Hash Table

Raj, are you sure you posted the output after loading the test database ?
alternatively can you load the database again
and save the output of dbcc prsqlcache and post it, also as a next step
execute dbcc purgesqlcache and see if TF 990 works

-Manish Negandhi
[TeamSybase]