A client just called asking for some help to extend their Sybase database as they were running out of space on saptools database, so the first thing I ask them is how they realized that;s the real problem, and they sent this screenshot:
They made their point, Sybase database saptools was full, the system was trying to allocate something there and there was no space, so once in the database the first thing I do is to check that the actual freespace of the databases with a query I took from benohead.com:
select db_name(d.dbid) as db_name,
ceiling(sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end )) as data_size,
ceiling(sum(case when u.segmap != 4 then size - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1048576.*@@maxpagesize) as data_used,
ceiling(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))) as data_used_pct,
ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end)) as log_size,
ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end) - lct_admin("logsegment_freepages",d.dbid)/1048576.*@@maxpagesize) as log_used,
ceiling(100 * (1 - 1.0 * lct_admin("logsegment_freepages",d.dbid) / sum(case when u.segmap in (4, 7) then u.size end))) as log_used_pct
from master..sysdatabases d, master..sysusages u
where u.dbid = d.dbid and d.status not in (256,4096)
group by d.dbid
order by db_name(d.dbid)
And I found the following (I trimmed the output yo avoid unnecessary information):
db_name data_size data_used data_used_pct log_size log_used log_used_pct
------------------------------ ----------------------------------------------- -------------------------------- --------------------------------------- ----------------------------------------------- ------------------------------------------------ ---------------------------------------
saptools 8192 8169 100 2048 37 2
So, if you want to extend the DB you will have to allocate that somewhere, and that somewhere is a device, a DB itself is allocated in one or more devices, you can check that using sp_helpdevice
And while check ours I found (Spoiler alert, output trimmed again):
1> sp_helpdevice
2> go
device_name physical_name description
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
saptools_data_001 /sybase/PRD/sapdiag/saptools_data_001.dat file system device, special, dsync off, directio on, physical disk, 8192.00 MB, Free: 0.00 MB
No space left on the device, as you can see it has a size of 8192MB with 0MB free, and that is because the database stored there (saptools) has a size of 8192MB too, consuming up its whole usable space.
At this point you would think, "Ok, I'm ready to extend it", but nope, you are not. You have to check the file system usage before extending the device, and even if you have free space you don't want to fall into a situation were you took the last few available GBs from the fs, to check that you just use df.
Now that we are sure that we have enough space to start extending the database let's sit for a second and think about this.
If you are now into DBs you could think about this in the following order:
- I need a place to store information
- Call it a database
- That database has to be stored somewhere
- Call it a data file (one or many)
- That data file has to be stored somewhere else
- Call it a mount point
Now, given that order, you could think about that in reverse while allocating space:
- After some analysis we fall into the conclusion that for a period of 1 year our database should have 120GBs available.
- Give that I create two mount points so I make use of the processor IO capabilites, so I will need two mountpoints of 60GB+10% (That 10% per moun point is needed for internal stuff)
- A mountpoint /sybase/SID/mydb_0 of 66Gbs is created
- A mountpoint /sybase/SID/mydb_1 of 66Gbs is created
- Having the mount points available I create two data files of 60GBs each
- A data file mydb_data_001 is created of 60GBs under /sybase/SID/mydb_0
- A data file mydb_data_002 is created of 60GBs under /sybase/SID/mydb_1
- Having the data files available I create a new database mydb that I will use to store my information, but as there could be variations on the space required for a 1 year period I will allocate just half of that so I do have available the space of 6 months already allocated, and I will configure autoextend so the database gets extended on certain threshold
- A new database mydb is created using 30GBs of data file mydb_data_001 and 30GBs of data file mydb_data_002 , then configured to be autoextended when only 6GBs are left on the DB.
Up to this point you will have the following spaces available and consumed:
| name | type | size | used | free |
|---|---|---|---|---|
| /sybase/SID/mydb_0 | mountpoint | 66G | 60G | 6G |
| /sybase/SID/mydb_1 | mountpoint | 66G | 60G | 6G |
| mydb_data_001 | data file | 60G | 30G | 30G |
| mydb_data_002 | data file | 60G | 30G | 30G |
| mydb | database | 30G | 0G | 30G |
Done with the explanation, let's get our hands dirty and extend the data files.
In the order we mentioned (With the exception of extending the FS that is a whole different story) we have to start by resizing the device, and that is achieved using "disk resize" and to run that one with need to know three things:
- The name of the device
- How much should we increase its size
In our case we want to extend the DB by 2GBs and leave another 2GBs free on the device so it can be auto extended (explanation later), that means that we have to extend it by 4GBs, in Sybase GBs are expressed with just one G, so it will be 4G, and for to extend ours it should look like:
disk resize
name = "saptools_data_001",
size = "4G"
go
Once that is executed you will see that the file on the file system was already extended:
And just to be sure that we extended the right one we could also check its size using sp_helpdevice:
1> sp_helpdevice
2> go
device_name physical_name description
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
saptools_data_001 /sybase/PRD/sapdiag/saptools_data_001.dat file system device, special, dsync off, directio on, physical disk, 12288.00 MB, Free: 4096.00 MB
Check mark for the device, lets make that DB bigger.
To increase the DB instead of using disk resize we will use alter database, in our case will be easy as we have only one device, but if you have multiple devices you have to run this on each device that we want the DB allocated space to be increase, and the resulting free space will be the total of the allocated free space on all of them.
So to run this one you need at least 3 pieces of information:
- The name of the DB
- In our case saptools
- The name of the data file
- In our case saptools_data_001
- The additional space to be allocated
- 2GB
Before showing you the query I would like to explain why we increased the data file by 4GBs and the database just by two. It is because our database is configured to be auto extended, it will auto extend only if there is available space on the device, when there is space available just for one more auto extend an alarm is triggered but it is triggered only if the database was auto extended, that's why we leave those 2GBs. this database is configured to be extended on steps of 128MB, so having 2GBs free means that it has the opportunity of by auto extended by 16 times, the 15 time it is auto extended an alarm will be triggered.
Now that you have that in mind lets get back to the query, it should look like this:
alter database saptools on saptools_data_001="2G"
go
And the result should look like this:
A few checks before we close this post, the first thing to do is to check DB allocated space with Benohead's query:
db_name data_size data_used data_used_pct log_size log_used log_used_pct
-------------- -------------- -------------- -------------- ----------- ----------- ---------
saptools 10240 8169 80 2048 37 2
We are good, 10GBs allocated, 8GBs used.
Another quick check to do is running sp_helpdevice again and check that the space was consumed:
1> sp_helpdevice
2> go
device_name physical_name description
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
saptools_data_001 /sybase/PRD/sapdiag/saptools_data_001.dat file system device, special, dsync off, directio on, physical disk, 12288.00 MB, Free: 2048.00 MB