Link to home
Start Free TrialLog in
Avatar of kukiya
kukiya

asked on

Log file is full

Hello,

I have started to get the SQL Server messege:
"The Log File for database 'myDb' is full. Backup the transaction log for the DB to free up some log space".

Since then no query can be executed in the SQL server.
I would like to now how to clean the logfile in both versions of SQL Server 2000 and 7.0.

Thanks in Advance,
Kukiya
Avatar of Danielzt
Danielzt

if you do not need to backup your log file, just user enterprise manage, right click the database, there is a "Truncate log.." . you can use it to truncate your database log file. if you have more than one database has this problem, do the same thing one by one.

====================================================
The following information will help you to understand this.

HOW SQL SERVER DO A full database backup?
1: Backups up any activity that took place during the backup
2: backups up any uncommitted transactions in the transaction log.

How SQL server Backs up the transaction Log
1: Backups up the transaction log from the last successfully executed BACKUP LOG statement to the end of the current transaction log.
 (so the transaction log will become smaller.)

2: Truncates the transaction log up to the begining of the active portion of the transaction log. The active portion of the transaction log starts at the point of the oldest open transaction and continues to the end of the transaction log.

3: Discards information in the inactive portion of the transaction log and reclaims disk space.

You should use the BACKUP LOG statement with the TRUNCATE_ONLY and NO_LOG options to clear transaction logs. You should back up the transaction log regularly to keep it at a resonable size.

BACKUP LOG syntax:

BACKUP LOG {database_name | @database_name_var}
TO <backup_file>[,..n]
[WITH {TRUNCATE_ONLY | NO_LOG|NO_TRUNCATE} ]

TRUNCATE_ONLY
IF you want to clear the transaction log and do not want to keep a backup copy the log, use the TRUNCATE_ONLY option. SQL Server remove the inactive part of the log without making a copy of it.

Clearing the transaction log before you back up the database results in a smaller backup of the full database.

After use this option, you should execute the BACKUP DATABASES statement imeddiately.

You can not use TRUNCATE_ONLY and NO_LOG options in the same statement.

if the transaction log is 100% full, you must backup the transaction log with the NO_LOG option.

Examples  BACKUP LOG northwind WITH TRUNCATE_LOG

NO_LOG
Use this option if you run out of disk space in the transaction log and cannot execute the TRUNCATE_ONLY option. SQL Server removes the inactive part of the transaction log without making a backup copy of it. This saves space by not recording the procedure in the transaction log.

[ Notes ] You can not recover the changes that were recorded in the transaction log. therefore, you should execute the BACKUP DATABASE statement.

Examples  BACKUP LOG northwind WITH NO_LOG

NO_TRUNCATE
SQL SERVER:
1: SAVES THE ENTIRE TRANSACTION LOG, EVEN IF THE DATABASE is inaccessible.
2: Does not purge the transaction log of committed transaction
3: Allows you to recover data up to the time when the system failed.
( but this will make log file bigger and bigger. You are in this case)


SETTING the TRUNC. LOG ON CHKPT. option

You can set the trunc. log on chkpt. option to true to write all committed trsactions to the database when a checkpoint occurs. This option automatically truncates the transaction log.

[NOTES] this means you can not backup the transaction log and use it to restore the database to the time point of system failure.

 
Hi,

Run this in Query Analyzer:

DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)

Just supply the transaction log name and database name.
Cheers
Avatar of kukiya

ASKER

Thanks,
I run the query:
"BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY"
and it did help to continue working.
although:
I manually changed the transaction Log properties from "unrestricted file growth" to "Restricated File growth". then I tried to lower the size
(it was 3740 MB) but I couldn't change the size of it.
my questions are:
1 whether its possible to lower the unrestricted file growth size after the DB was created in "Restricated File growth".
2. is there any way to get the log file size before its get full, in order to prevent it from becoming full.

Thanks in advance,
Kukiya

You can set "Auto shrink" option from Enterprise for your database.


autoshrink
When true, the database files are candidates for periodic shrinking. Both data file and log files can be shrunk automatically by SQL Server. When false, the database files are not automatically shrunk during periodic checks for unused space. By default, this option is set to true for all databases when using SQL Server Desktop Edition, and false for all other editions, regardless of operating system.
The only way to free space in the log files so that they can be shrunk is to back up the transaction log, or set trunc. log on chkpt to true. The log files shrink when the log is backed up or truncated. Therefore, setting autoshrink to true will cause the log to shrink only if the log is backed up or truncated.

The autoshrink option causes files to be shrunk when more than 25 percent of the file contains unused space. The file is shrunk to a size where 25 percent of the file is unused space, or to the size of the file when it was created, whichever is greater.

It is not possible to shrink a read-only database.

The status of this option can be determined by examining the IsAutoShrink property of the DATABASEPROPERTY function.

Avatar of kukiya

ASKER

Danielzt,
How can I write it in the Query analizer ????
Avatar of kukiya

ASKER

And how do I know what size is the log file to prevent its becoming full???

Thanks

You can set "Auto shrink" option from Enterprise for your database.


autoshrink
When true, the database files are candidates for periodic shrinking. Both data file and log files can be shrunk automatically by SQL Server. When false, the database files are not automatically shrunk during periodic checks for unused space. By default, this option is set to true for all databases when using SQL Server Desktop Edition, and false for all other editions, regardless of operating system.
The only way to free space in the log files so that they can be shrunk is to back up the transaction log, or set trunc. log on chkpt to true. The log files shrink when the log is backed up or truncated. Therefore, setting autoshrink to true will cause the log to shrink only if the log is backed up or truncated.

The autoshrink option causes files to be shrunk when more than 25 percent of the file contains unused space. The file is shrunk to a size where 25 percent of the file is unused space, or to the size of the file when it was created, whichever is greater.

It is not possible to shrink a read-only database.

The status of this option can be determined by examining the IsAutoShrink property of the DATABASEPROPERTY function.

you can use this to know the space usage for your database but not for you log file.

USE database_name
sp_spaceused @updateusage = 'TRUE'

ASKER CERTIFIED SOLUTION
Avatar of cccamp
cccamp

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What recovery model is your database set to?

Properties\Options\Model ?

If you make logfile backups then you need it set to full, if not set your model to simple. this will allow the log file to be cleared of transactions once they hav been commited to the database.

No more huge log files...
Avatar of kukiya

ASKER

Danielzt,
As for log file size information,
"sp_spaceused" is not recognized by the query analizer.

How can I know the size of it ????

Thanks

Avatar of kukiya

ASKER

lukas_botha,

How can I know the recovery model  ???

Thanks
Avatar of kukiya

ASKER

lukas_botha,Thanks
I understood where recovery model is.

So... If I set ir to "simple" than the log file always be clean ????? !!!!!
This is how it works....

On full recovery the logfile will stay intact until as full database backup has been made.

When you set the recovery to simple the trasactions gets dumped once the transaction has been commited to the database.

If you use the full recovery model yuo usually bakup your transaction log every 15 - 30 minutes, your are then able to recover upto a point in time if your database has corrupted.

With simple recovery you CANNOT restore transactional backups.

Hope it explains it.
This is the bast I can offer: it is out of SQL Books Online.

How to grant permissions on multiple objects to a user, group, or role (Enterprise Manager)
To grant permissions on multiple objects to a user, group, or role

Expand a server group, and then expand a server.


Expand Databases, and then expand the database to which the user, group, or role belongs.


Depending on the type of user, group, or role to which permissions will be granted, click either Users or Roles.


In the details pane, right-click the user, group, or role to which permissions will be granted, point to All Tasks, and then click Manage Permissions.


Click List all objects, and then select the permission to grant each object.
A check indicates a granted permission. Only permissions applicable to the object are listed.

Avatar of kukiya

ASKER

Thank you very much !