List Server Reserved Words

Run this simple query in isql to find all the reserved words for Adaptive Server Enterprise .
 To list all reserved words for Adaptive Server Enterprise, log in to isql and run this query:

 select name from spt_values where type = "W"







The spt_values table is a look-up table in the master database that stores system values used by other server processes.

Run this simple query in isql to find all the reserved words for Adaptive Server Enterprise .
 To list all reserved words for Adaptive Server Enterprise, log in to isql and run this query:

 select name from spt_values where type = "W"







The spt_values table is a look-up table in the master database that stores system values used by other server processes.

Installing sybase on ubuntu

Just  installed ASE15.0.3 ESD#1  on ubuntu 7.04 inside virtual box.

Download the ASE Developer’s Edition.

1)Install the following package(Need to put  ubuntu 7.04 CD /DVD in Drive).

apt-get install libaio1 

2)Sybase requires at lea Ist 300 MB shared memory,so increase.

/sbin/sysctl -w kernerl.shmmax=300000000

3)Unzip sybase installation file

tar -xvf ase_1503_linuxx86.tgz

4)Now run the setup

chmod +x ./setup

Choose Express Edition install while installing.

5)Append  the contents of the SYBASE.sh to /etc/bash.bashrc.

6)We need to add sybase shared libraries to the library path.

gedit /etc/ld.so.conf.d/sybase.conf

7)Add these lines to the above file

/opt/sybase/OCS-15_0/lib3p
/opt/sybase/ASE-15_0/lib

8)Afterwards, update the paths

/sbin/ldconfig

9)In /opt/sybase/locales/locales.dat

Find the section:
[linux]

 Add the line:

locale = en_IN, us_english, iso_1

Just  installed ASE15.0.3 ESD#1  on ubuntu 7.04 inside virtual box.

Download the ASE Developer’s Edition.

1)Install the following package(Need to put  ubuntu 7.04 CD /DVD in Drive).

apt-get install libaio1 

2)Sybase requires at lea Ist 300 MB shared memory,so increase.

/sbin/sysctl -w kernerl.shmmax=300000000

3)Unzip sybase installation file

tar -xvf ase_1503_linuxx86.tgz

4)Now run the setup

chmod +x ./setup

Choose Express Edition install while installing.

5)Append  the contents of the SYBASE.sh to /etc/bash.bashrc.

6)We need to add sybase shared libraries to the library path.

gedit /etc/ld.so.conf.d/sybase.conf

7)Add these lines to the above file

/opt/sybase/OCS-15_0/lib3p
/opt/sybase/ASE-15_0/lib

8)Afterwards, update the paths

/sbin/ldconfig

9)In /opt/sybase/locales/locales.dat

Find the section:
[linux]

 Add the line:

locale = en_IN, us_english, iso_1

Temp db full

Since ASE 12.5 we can create a user defined tempdb (using sp_tempdb) and bind an admin login
 to this new tempdb. This admin account can then run sp_who to find out who filled tempdb and kill the process.

Sometimes the kill won't work The new 'multiple tempdb' feature in 12.5.0.3 allows you to avoid the problem that tempdb is full and  cannot access the fake tables in master.
In pre-12.5.0.3, we can get some information from different sources, like dbcc pss, dbcc lock and dbcc log

Follow these steps to solve tempdb full

 (i) use 'tempdb_space' resource limits (in 12.5+) and

 (ii) if your tempdb gets full anyway,use lct_admin to abort the transactions causing this.
 Instead of this  it's a good idea to

 (iii) enable the 'abort tran on log full' option for tempdb so that LOG SUSPEND statuses will not remain until the DBA takes action.

Since ASE 12.5 we can create a user defined tempdb (using sp_tempdb) and bind an admin login
 to this new tempdb. This admin account can then run sp_who to find out who filled tempdb and kill the process.

Sometimes the kill won't work The new 'multiple tempdb' feature in 12.5.0.3 allows you to avoid the problem that tempdb is full and  cannot access the fake tables in master.
In pre-12.5.0.3, we can get some information from different sources, like dbcc pss, dbcc lock and dbcc log

Follow these steps to solve tempdb full

 (i) use 'tempdb_space' resource limits (in 12.5+) and

 (ii) if your tempdb gets full anyway,use lct_admin to abort the transactions causing this.
 Instead of this  it's a good idea to

 (iii) enable the 'abort tran on log full' option for tempdb so that LOG SUSPEND statuses will not remain until the DBA takes action.

orphan tables in tempdb

If an spid that owns the table no longer exists in sysprocesses.
This happens sometimes when a killed spid isn't cleaned up properly,
leaving temp tables behind.  These are called "orphaned" temp tables.

Running

    dbcc orphantables

will list orphaned tables in tempdb .

    dbcc orphantables ("drop")

will try to clean them up.

If an spid that owns the table no longer exists in sysprocesses.
This happens sometimes when a killed spid isn't cleaned up properly,
leaving temp tables behind.  These are called "orphaned" temp tables.

Running

    dbcc orphantables

will list orphaned tables in tempdb .

    dbcc orphantables ("drop")

will try to clean them up.

Top 10 large tables in database

Top 10 largest tables in a database by rowcount:

select

top 10

    name,

    row_count(db_id(), id)

from

    sysobjects

order by

    row_count(db_id(),id) desc

Top 10 largest tables in a database by allocated space:

select

top 10

    name,

    reserved_pages(db_id(),id)

/(1024.0 / (@@maxpagesize/1024.0) ) as "Allocated MB"

from

    sysobjects

order by

    reserved_pages(db_id(),id) desc

go

Top 10 largest tables in a database by used allocated space:

select

top 10

    name,

    used_pages(db_id(),id)

/(1024.0 / (@@maxpagesize/1024.0) ) as "Used MB"

from

    sysobjects

order by

    used_pages(db_id(),id)(db_id(),id) desc

go 

**These are not my own queries..Credit goes to Bret Halford on sybase forums.

Top 10 largest tables in a database by rowcount:

select

top 10

    name,

    row_count(db_id(), id)

from

    sysobjects

order by

    row_count(db_id(),id) desc

Top 10 largest tables in a database by allocated space:

select

top 10

    name,

    reserved_pages(db_id(),id)

/(1024.0 / (@@maxpagesize/1024.0) ) as "Allocated MB"

from

    sysobjects

order by

    reserved_pages(db_id(),id) desc

go

Top 10 largest tables in a database by used allocated space:

select

top 10

    name,

    used_pages(db_id(),id)

/(1024.0 / (@@maxpagesize/1024.0) ) as "Used MB"

from

    sysobjects

order by

    used_pages(db_id(),id)(db_id(),id) desc

go 

**These are not my own queries..Credit goes to Bret Halford on sybase forums.

sa Login locked?

Add "-T4044" to the command line in the RUN_server file.
This will  allow you to log in as 'sa'.

Add "-T4044" to the command line in the RUN_server file.
This will  allow you to log in as 'sa'.

Hi to All

Hi, All will get timely updates on this blog!!!

Hi, All will get timely updates on this blog!!!

Maximum device size for ASE 12.5 and ASE 15.0

Up to ASE 12.5.x the maximum device size is 32GB (provided
that the OS knows how to handle large [>2GB] files) because
of the way the device number is encoded in the sysdevices
table (i.e. only 24 bits are available to describe the file
itself).

In ASE 15 this limit has been lifted.


ASE-15 device and database limits per server are as follow:

- device size limit: 4 terabytes per device
- server limit: 2 billion logical devices per server
- database size limit: 32 terabytes per database
- server limit: 32767 databases per server

So, maximum storage per ASE server is over one million terabytes (32TB x
32767).

Up to ASE 12.5.x the maximum device size is 32GB (provided
that the OS knows how to handle large [>2GB] files) because
of the way the device number is encoded in the sysdevices
table (i.e. only 24 bits are available to describe the file
itself).

In ASE 15 this limit has been lifted.


ASE-15 device and database limits per server are as follow:

- device size limit: 4 terabytes per device
- server limit: 2 billion logical devices per server
- database size limit: 32 terabytes per database
- server limit: 32767 databases per server

So, maximum storage per ASE server is over one million terabytes (32TB x
32767).

dump database three phases

The dump database command makes a copy of the entire
database, including both the data and the transaction log.
dump database does not truncate the log.
dump database allows dynamic dumps. Users can continue to
make changes to the database while the dump takes place.
This makes it convenient to back up databases on a regular
basis.
dump database executes in three phases. A progress message
informs you when each phase completes. When the dump is
finished, it reflects all changes that were made during its
execution, except for those initiated during phase 3."

Phase 1
* Phase 1 is the DBPAGES phase
* The Backup Server scans and dumps all the allocated
pages (both data and log pages) for the given database,
without checking to see whether any of the pages were
updated while this phase is going on.

Phase 2
* Phase 2 is the FLUSHPAGES phase:
* All the data pages that were modified during but not
logged in phase 1 (for example, a fast bcp) are dumped.
* The dump instant is the end of this phase. Recovery
restores the database to the state after phase 2.

Phase 3
* Phase 3 is the SCANLOGPAGES phase:
* The log pages are dumped again to capture
up-to-the-minute changes.
* dump transaction has only one phase: the SCANLOGPAGES
phase.

The dump database command makes a copy of the entire
database, including both the data and the transaction log.
dump database does not truncate the log.
dump database allows dynamic dumps. Users can continue to
make changes to the database while the dump takes place.
This makes it convenient to back up databases on a regular
basis.
dump database executes in three phases. A progress message
informs you when each phase completes. When the dump is
finished, it reflects all changes that were made during its
execution, except for those initiated during phase 3."

Phase 1
* Phase 1 is the DBPAGES phase
* The Backup Server scans and dumps all the allocated
pages (both data and log pages) for the given database,
without checking to see whether any of the pages were
updated while this phase is going on.

Phase 2
* Phase 2 is the FLUSHPAGES phase:
* All the data pages that were modified during but not
logged in phase 1 (for example, a fast bcp) are dumped.
* The dump instant is the end of this phase. Recovery
restores the database to the state after phase 2.

Phase 3
* Phase 3 is the SCANLOGPAGES phase:
* The log pages are dumped again to capture
up-to-the-minute changes.
* dump transaction has only one phase: the SCANLOGPAGES
phase.

how to get host/port,host name,server name,and db name in syabse

select address_info as "host/port"
      ,host_name() as "hostname"
      ,@@servername as "servername"
      ,db_name() "current_dbname"
from master..syslisteners

select address_info as "host/port"
      ,host_name() as "hostname"
      ,@@servername as "servername"
      ,db_name() "current_dbname"
from master..syslisteners

Use of "with overrride" option in database creation

Suppose we have a device, test_dev and you wish to create a database
with data and log on it.

create database testdb1
 on test_dev = 100
 log on test_dev = 20
 with override

You now have distinct data and log segments on the same device.
You can dump this log as usual with dump tran.

The log is always on it's own segment (the "log" segment).
The problem with dumping the tran log comes when the log segment is mixed with any other
kind of segment on the same fragment
(yielding a sysusages segmap that is something other than "4")

Suppose we have a device, test_dev and you wish to create a database
with data and log on it.

create database testdb1
 on test_dev = 100
 log on test_dev = 20
 with override

You now have distinct data and log segments on the same device.
You can dump this log as usual with dump tran.

The log is always on it's own segment (the "log" segment).
The problem with dumping the tran log comes when the log segment is mixed with any other
kind of segment on the same fragment
(yielding a sysusages segmap that is something other than "4")

dsync,direct io

The directio and dsync parameters are mutually exclusive. If a device has dsync set to “true,” you cannot set
directio to “true” for this device. To enable directio for a device, you must first reset dsync to “false.”
Direct I/O is always the preferred way.
dsync only applies to operating system files (meaning that it has no effect on "raw" devices used as Sybase disk devices)

The directio and dsync parameters are mutually exclusive. If a device has dsync set to “true,” you cannot set
directio to “true” for this device. To enable directio for a device, you must first reset dsync to “false.”
Direct I/O is always the preferred way.
dsync only applies to operating system files (meaning that it has no effect on "raw" devices used as Sybase disk devices)