I have created a new device in sybase and set it as a transaction log accidentially. Actually, I want to set it as a datafile instead. As I have set the size is so large, is there any method I can reduce the size or changing it from log to a datafile. Besides, what is the side effect if the transaction log is too large?
To change it to a data device
drop the logsegment and add the default segment to the device
No way to remove it that is supported by Sybase
Make sure you have full backups including master if you want to attempt removing the last device added as log. (play on test server)
I've done it before and took some experimenting.
If I remember the steps correct.
Remove all segments from the last device (sp_dropsegment)
select logptr from sysdatabases
do dummy transactions until the logptr is not on the last device added
as can be seen from sysusages
logptr between lstart and lstart+size-1
delete the database's last entry from sysusages
dbcc dbrepair (<dbname>, remap)
dump the database, recreate and load the database to ensure everything work as expected (i.e. database does load the now smaller size)