Database size bigger than device size

[Former Member]

Hi

I had a log full situation, and I ended up resizing the device to 20 GB, but extending the database-log with 20 GB.

It seems now that the database device is 20 GB, but the log size is almost 40 GB. Please explain why Sybase allowed this, and the impact?

Please see <sp_helpdevice> and <sp_helpdb SID> attached.

Kind Regards

Willem

[former_member188958 · Accepted solution]

Hi Willem,

The DISK RESIZE command adds the specified amount of space to a device rather than changing the device's size to the specified value.

sp_helpdb shows the database with 4 fragments on the SID_log_001 device, the most recent being 20GB in size.
The 4 fragments together total 37,460 MB

sp_helpdevice output shows that SID_log_001 is also exactly 37460 MB in size (though the value is broken across two lines in your original output)

SID_log_001
/sybase/SID/saplog_1/SID_log_001.dat
file system device, special, dsync off, directio on, physical disk, 37460.00 MB, Free: 0.00 MB
2 0
5 0
19179519

Everything looks right to me.

Cheers,
-bret

[Former Member]

Hi Bret

Thanks, the sp_helpdevice output broken across two lines causing me to miss the size.

And the "disk resize" output in the log file was "Performing space allocation for device '/sybase/SID/saplog_1/SID_log_001.dat' (19.53 Gb). This may take some time"; While the "alter database" output was "Extending database by 1280000 pages (20000.0 megabytes) on disk SID_log_001" -- which made it seem to me like it was only Extending the database.

I can see now that both were extended, thanks for the explanation 🙂

[M-B]

Hi Willem,

As a general rule, the log space allocation amounts to 10-25% of the space that you allocate to the database. There is no restriction on allocating more log space than data. For more details, see :

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc31644.1570/html/sag2/sag2173.htm

This is to add to Bret's answer.

Regards

Mehrab