Database log file

hi all

my database log file has been full and the H.D partition not have any space

what's the procedures to remove or empty the log file

thanks & regards

Are you running SQL Server 2000 or SQL Server 2005? What recovery model is your database using (Full or Simple)?

If you are using the Full Recovery model, you have to periodically backup and truncate the log. Otherwise, it will fill up. Most likely, you have a long-running transaction that is causing this.

If you are running SQL 2005, you can run this query to find out why the transaction log cannot be backed up and truncated.

-- Find the problem with the transaction log

SELECT name, database_id, log_reuse_wait_desc

FROM sys.databases

|||

I guess the question to be asked is, do you need to recover your database to a point in time or to the last full backup.

1. Point in time

a. Your recovery model needs to be set to 'Full' - which it is already because it's filling up.

b. You need to backup you log file routinely, the frequency is determined by how much data you are prepared to lose in the event of a failure i.e. if you only want a maximum 15 minutes of data lost then you need to backup your log file every 15 minutes. By backing up your log file the inactive portion of the log file will be truncated hence it will free up space within the log file for new transactions (note however this will not decrease the log file size on the disk) and reduce growth in the log file.

2. Recover to the last full database backup

a. Set your recovery model to 'simple', your log file will not fill up anymore and sql won't fall over because of disk space issues relating to the log file..

b. Backup you database each day (or as per your backup procedures/policies)

The first step is to reduce the size of your log file to make it more manageable:

1. Backup your log file to disk (or just truncate it if you're not interested in retaining the back file)

To Disk:

BACKUP LOG { database_name } TO DISK='your backup location'

Truncate:

BACKUP LOG { database_name } WITH TRUNCATE_ONLY

Both methods will remove the inactive portion of the log file. Once this has completed you need to reduce the size of the file on the disk, to do this have a look at the DBCC SHRINKFILE command in BOL, there are a few options that may be of interest.