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
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
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 –Pabc456 -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
http://itildumps.blogspot.in/2007/10/free-itil-dumps-exam-questions-version.html