Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Log file is full

Posted on 2003-03-09
16
Medium Priority
?
352 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:kukiya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +2
16 Comments
 
LVL 8

Expert Comment

by:Danielzt
ID: 8098256
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.

 
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8098472
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
0
 

Author Comment

by:kukiya
ID: 8101589
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
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 8

Expert Comment

by:Danielzt
ID: 8103384

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.

0
 

Author Comment

by:kukiya
ID: 8103524
Danielzt,
How can I write it in the Query analizer ????
0
 

Author Comment

by:kukiya
ID: 8104442
And how do I know what size is the log file to prevent its becoming full???

Thanks
0
 
LVL 8

Expert Comment

by:Danielzt
ID: 8105310

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.

0
 
LVL 8

Expert Comment

by:Danielzt
ID: 8105459
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'

0
 

Accepted Solution

by:
cccamp earned 500 total points
ID: 8106427
I had the same problem and found you can find every thing that has been said here and more detail by searching for the article # Q256650 (for SQL 7.0) and article # Q272318 (for SQL 2000) in Microsoft Knowledge Base.
0
 

Expert Comment

by:lukas_botha
ID: 8109318
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...
0
 

Author Comment

by:kukiya
ID: 8109427
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

0
 

Author Comment

by:kukiya
ID: 8109442
lukas_botha,

How can I know the recovery model  ???

Thanks
0
 

Author Comment

by:kukiya
ID: 8109617
lukas_botha,Thanks
I understood where recovery model is.

So... If I set ir to "simple" than the log file always be clean ????? !!!!!
0
 

Expert Comment

by:lukas_botha
ID: 8127395
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.
0
 

Expert Comment

by:lukas_botha
ID: 8127492
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.

0
 

Author Comment

by:kukiya
ID: 8129321
Thank you very much !
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question