Hello,
I created an login account using 'create login' command and tried to drop the account using 'drop login' command. But the drop login command seems to get stuck somewhere as it does not return anything and connection is stuck. I checked the locks using sp_lock. It shows active 'update row' lock on 'syslogins' table. When I abort the drop login query, it seems to release the locke thenonly. Why is this happening?
In the logs I see the logs as 'tasks are sleeping waiting for space to become available in the log segment for database ***'. Is there any relation of this with my problem. I'm new to sybase. Please help.
'
You've masked the name of the database in the errorlog message.
-----------
*ASSUMING* the errorlog message is referring to the 'master' database then yes, this is related to your drop login command hanging.
The process of creating/dropping a login requires modifications to the master..syslogins table.
Just as with changes to user tables, changes to system tables generate log records that are appended to the database's transaction log.
As with user databases, the master database's transaction log must periodically be dumped in order to keep the transaction log from filling up.
If the master database's transaction log fills up then any operations which attempt to modify a table in the master database (eg, drop login) will hang until space is free'd up in (or added to) the master database's transaction log.
It is up to the DBA to insure the master transaction log is routinely dumped and truncated (eg, thresholds on the logsegment segment, a regularly scheduled job, etc). It's normal routine to dump the master transaction log (dump transaction master with truncate_only) followed by performing a full dump of the master database so as to have an up-to-date backup/copy of the master database.
As always excellent feedback/suggestion by Mark.
System databases like master & sybsystemprocs also need some administration and maintenance work.
Usually after taking a full backup, truncate the transaction log of a system database (like master) by the "dump transaction .... truncate_only" command.
Avinash