Free Disk Space,negative logsegment space

1) Display Available Device Space - Free Disk Space .

Determining the amount of free space on a server's set of devices
is not an easy task; the procedure below produces a nice  report ,
showing total allocations and available space on each device.

create procedure sp_freedisk
as
set nocount on
select
logical_name = substring(d.name, 1, 12),
physical_name = substring(d.phyname, 1, 17),
vdevno = d.low / power(2, 24),
size = (d.high - d.low + 1) * 2 / 1024,
reserved = isnull(sum(u.size) * 2 / 1024, 0),
left = (d.high - d.low + 1) * 2 / 1024 -
isnull(sum(u.size) * 2 / 1024, 0)
from
master.dbo.sysdevices d, master.dbo.sysusages u
where
d.status & 2 = 2
and u.vstart / power(2, 24)=*d.low / power(2, 24)
group by
substring(d.name, 1, 12),
substring(d.phyname, 1, 17),
d.low / power(2, 24),
(d.high - d.low + 1) * 2 / 1024
order by vdevno
return

2) logsegment shows negative space

DBName            SegName                Allocated    Available   PctUsed
--------------- -------------------- ---------- ---------- ---------
reports             default                    289000.0  13153.5       95.4
reports             logsegment              3010.0      3857.7       -28.1
reports             system                    289000.0  13153.5       95.4

--Need to recycle server to take effect

--Negative value for log free space

--The following is undocumented from sybase

-- Connect to your server as sa and run the following to fix it.

dbcc gam(dbname, 0, 0, "fix")
go
dbcc usedextents(dbname, 0, 1, 1)
go

“How to check free space in Sybase(ASE) database”

 

                                      

If we would like to check free and occupied space in devices with data and logs in our Sybase(ASE) databases we can use script as below:

declare @pagesize int
select @pagesize=(select @@maxpagesize)
SELECT “Database Name” = CONVERT(char(20), db_name(D.dbid)),
“Data Size” = STR(SUM(CASE WHEN U.segmap != 4 THEN U.size*@pagesize/1048576 END),10,1),
“Used Data” = STR(SUM(CASE WHEN U.segmap != 4 THEN size – curunreservedpgs(U.dbid, U.lstart, U.unreservedpgs)END)*@pagesize/1048576,10,1),
“Data Full%” = STR(100 * (1 – 1.0 * SUM(CASE WHEN U.segmap != 4 THEN curunreservedpgs(U.dbid, U.lstart, U.unreservedpgs) END)/SUM(CASE WHEN U.segmap != 4 THEN U.size END)),9,1) + “%”,
“Log Size” = STR(SUM(CASE WHEN U.segmap = 4 THEN U.size*@pagesize/1048576 END),10,1),
“Free Log” = STR(lct_admin(“logsegment_freepages”,D.dbid)*@pagesize/1048576,10,1),
“Log Full%” = STR(100 * (1 – 1.0 * lct_admin(“logsegment_freepages”,D.dbid) /
SUM(CASE WHEN U.segmap = 4 THEN U.size END)),8,1) + “%”
FROM master..sysdatabases D,
master..sysusages U
WHERE U.dbid = D.dbid
AND ((D.dbid > 3) AND (D.dbid < 31513) AND (D.status != 256))
GROUP BY D.dbid
ORDER BY db_name(D.dbid)
go

Save it to file, eg: check.sql and execute.


Example: isql -Usa -P -i check.sql

If ew want to get information for all databases (including system databases) we need to delete from script one line:

AND ((D.dbid > 3) AND (D.dbid < 31513) AND (D.status != 256))

                          

        

1. In case we have seen the same issue using this script, because we have some larger databases and (unfortunately) some still on Sybase ASE 12.5.4, I had an arithmetic overflow error when running this script.
2. we have to change the first line to declare @pagesize numeric(19,0). If we are using only 15.0+ then change it to declare @pagesize bigint.

3) Table Partitioning

Insert performance on partitioned tables is improved, as multiple
'entry points' (last page entries) are created. Partitioned tables
require slightly more disk space and need a chunk of memory also.

/* create 4 partitions for the invoice table */

alter table invoice
partition 4
go

bcp pubs2..publishers out "/optware/sybase/publishers.dat”

-Uuser1 –Pabc123 -c &

Bcp pub2..publishers out "/optware/sybase/ publishers.dat 

To bcp into several partitions split your file into 4 parts and bcp them into the partitions.

You have to specify the partition number like this:

bcp mydb..mytable:1 in ...
bcp mydb..mytable:2 in ...
bcp mydb..mytable:3 in ...
bcp mydb..mytable:4 in ...

***Partitioning in ASE 15***

Semantic "smart" partitioning is new in ASE 15.
Tables can be partitioned by range, value, or round-robin. By default, all tables in ASE 15 are partitioned, round-robin.

4)Clustered vs non-clustered indexes

Typically, a clustered index will be created on the primary key
of a table, and non-clustered indexes are used where needed.



Non-clustered indexes

l>Leaves are stored in b-tree
li>Lower overhead on inserts, vs clustered
li>Best for single key queries
<li>Last page of index can become a 'hot spot'
</ul>

Clustered indexes
<ul>
<li>Records in table are sorted physically by key values
<li>Only one clustered index per table
<li>Higher overhead on inserts, if re-org on table is required
<li>Best for queries requesting a range of records
<li>Index must exist on same segment as table
</ul>

Note! With "lock datapages" or "lock datarows" ... clustered indexes
are sorted physically only upon creation. After that, the indexes
behave like non-clustered indexes.

6) Setting the thresholds

Threshold settings allow customized procedures to be run when database segments
approach a defined capacity.

The "last chance threshold" is set by default, to execute sp_thresholdaction within the
current database, when a segment reaches 95% of capacity. The procedure sp_thresholdaction
needs to be created by the DBA. Here is a sample:

create proc sp_thresholdaction (
@dbname varchar(30),
@segmentname varchar(30),
@space_left int,
@status int ) as

declare @msg varchar(80),
@date1 datetime,
@fname varchar(80),
@fdate varchar(20),

@fpath varchar(40)

select @fpath = '/usr/dumps/logs/'

select @date1 = getdate()

select @fdate =
convert(varchar(2),datepart(MM,@date1)) +
convert(varchar(2),datepart(DD,@date1)) +
convert(varchar(2),datepart(HH,@date1)) +
convert(varchar(2),datepart(MI,@date1))

select @fname = @fpath + 'log_' + @dbname + @fdate + '.dmp'

select @msg = '***!! Last Chance Threshold reached, for ' + @dbname + '(' + @segmentname + ')'

print @msg

if @segmentname = 'logsegment'
dump tran @dbname to @fname

return
Other threshold levels can be created, for specific segments. They can be set up
to print informational messages to the error log, as a forewarning to the DBA. Here's a
sample which reflects the command syntax:

1> sp_addthreshold dbname,logsegment,400,'proc_log_threshold'
2> go
Adding threshold for segment 'logsegment' at '400' pages.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 0)

8)Renaming a database

 Renaming a database requires that it be put in single-user mode first.


-- Renaming a database

use master
go

sp_dboption 'warehouse003','single user',true
go
use warehouse003
go
checkpoint
go
use master
go



sp_renamedb 'warehouse003','warehouse009'
go



sp_dboption 'warehouse009','single user',false
go
use warehouse009
go
checkpoint
go

use master
go

sp_helpdb warehouse009
go