SQL Server Performance Forum – Threads Archive
Transaction Log
Hi,Suddenly my database flashes a message that transaction log is full.What should i do now?
Perform a log backup and shrink it then. You might want to do this on a regular basis.
Also look at BOL for transaction log.
Frank
http://www.insidesql.de
Perform BACUP LOG … WITH TRUNCATE_ONLY and take full backup. To gain more space, you can free disk space on any disk drive containing the transaction log file for the related database. Freeing disk space allows the recovery system to enlarge the log file automatically. Or you can gain space by adding or enlarging a log file for the specified database. Move the transaction log files with an insufficient amount of free disk space to a different disk drive. Another solution is to add a log file to the specified database using the ADD FILE clause of the ALTER DATABASE statement. Or you can enlarge the log file using the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. Adding an additional log file allows the existing log to grow.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
In addition to the above suggestions, I would recommend keeping the auto grow option enabled if disk space is not an issue. This is the recommended setting for production systems. Also as suggested, taking regular backups of the log or regulalrly truncating the log and taking backup of the database also keeps the log file size within limits. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Now that you know how to clear the current message, it might be a good idea to step back and look at your database and logging needs. You should plan a backup schedule of both the database and the log.
There are lots of things to think about and BOL is a great resource, but here are some basisc: Is your database a critical database?
If the server crashes, how many seconds/minutes/hours of data could be loss?
What recovery mode do you have? Do you need?
Is the data just a copy of source data from somewhere else or is this the only place? Like I said, just a start. But lots of people have been burned by not having proper backup/recovery plans. chris
]]>