Unable to drop table; acquired NOWAIT

Occasionally, you will find that you cannot do something with a particular table, be it an insert, delete, or even a drop. Usually this is caused by a lock on that table. The way around this is to remove the session that has the lock.

In many cases, it may be best to just wait for the lock to go away on its own. This is particularly true if you are working on a busy database during production hours. However, there are times when you cannot wait, or you know that the process is not a user process and can be safely killed. To do this you would use the KILL SESSION syntax of the ALTER SYSTEM command.

What happens if you don"t know the offending session number?

If you have a known locked object, you can utilize the V$LOCKED_OBJECT dba view.

SELECT object_id,

session_id,
oracle_username,
os_user_name,
process,
locked_mode

FROM V$SESSION

If you are lucky, there will only be one object and one session, you can then use the session id and the object_id to get the remaining information. First, we need to verify that we're lookint at the right object

SELECT owner,

object_name,
object_type

FROM dba_objects
WHERE object_id = '%variable%'

(You would, of course, put the number you obtained from the V$LOCKED_OBJECT view in where I have put %variable%) Assuming the query returns the object name and type that you were expecting, we should go ahead and get the serial number of the locking process.

SELECT sid,

serial#

FROM V$SESSION
WHERE sid = '%sessionid%'

Again, replace the variable as appropriate.

Now, we have all the information we need to kill the troublesome session and get on with our day.

ALTER SYSTEM KILL SESSION '%sessionid%,%serial#%' IMMEDIATE

Note that you need BOTH seesionid and the serial# and that they are contained in single quotes and separated by a comma. Also note that IMMEDIATE is optional and is equivalent to a forced command. This command will release the lock on your object, allowing you to finish what you started.

Truncate SQL Logs

I am off today.

Yeah, right.

The webmaster called me in a panic because the Content Management Server was really poking along and causing people to complain of web-site slowness. He determined that we needed, "more RAM, more processors, or maybe a new machine." Which I doubted.

I looked around at the MS SQL Server 2000 that resides behind the CMS and found an 18GB log file slowly growing larger. What to do, what to do?

Truncate the Logs.

When you truncate the transaction logs for a database you are, in essence, telling the database to stop worry about any transaction logs that are older than those that will fit in the allotted space so, there are some important steps that need to be taken before you do this.

First, you must make sure you have a good backup up the db in question. If your last scheduled full backup (and you DO have scheduled backups...right) was relatively recent, you should be fine. In this case, I had one taken on Sunday night. However, if you do not have a good backup, you can quickly run one by either re-running your existing job or just creating a new one. This is important. You MUST alter the RETAINDAYS statement in your existing job to be fewer than the number of days until the next backup. For instance, if you run backups once a week on Sundays, your RETAINDAYS is probably set to 6. If you want to run a job on Wednesday, you would have to alter the RETAINDAYS value to 3, otherwise your backups on Sunday will not run, since the last backup has not yet expired.

If you would rather just run a special job, you can do so rather easily as below:

USE %databasename%;
go
BACKUP DATABASE [%databasename%] TO [%backupdevice%]
WITH FORMAT

%databasename% and %backupdevice% will need to replaced with parameters from your site. The 'use' statement above is a bit redundant, but I always use it in order to be POSITIVE I am dealing with the right DB. Also note that using the WITH FORMAT option will overwrite anything you had previously stored to that device.

Assuming you do not have a backup device built, you can backup to a file directly with:

USE %databasename%;
go
BACKUP DATABASE [%databasename%] TO DISK = 'C:\Path\to\file.bak'
WITH FORMAT

Again, place the appropriate names in the appropriate places.

Once that is done, you will need to actually truncate the logfile. This is done with the code shown below:

USE %databasename%;
BACKUP LOG %databasename% WITH NO_LOG
go;

Rather than using NO_LOG you could use TRUNCATE_ONLY, they are equivalent in this case.

Now that you have gotten rid of the excess transaction logs, you will need to let the database know you have done so and that it can release the space on the disk. Time for some DBCC!

USE %databasename%;
DBCC SHRINKFILE (%logfilename%|%logfileid%);
go;

You may use the fully qualified log filename or you may use the file id. I usually use the file id because it is a smallish number. In my case, it was '2.' You can find the fileid in the sysfiles table in the database you are working with. Like so:

SELECT fileid,
size,
filename
FROM sysfiles

You will now need to make another full backup, as shown above, in case you have some disaster between now and the next backup, you won't need to re-truncate your logs. Also, since most of the transaction logs were just dropped, you need to run a full backup to have a valid retore path anyway.