SQL Server Performance Forum – Threads Archive
Minimizing Log file size
Hi! My log file is 96% full.What are the ways to decrease the space used apart from truncating it usingbackup log dbname
with truncate_only
What recovery model do you have? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
If your database is in Full recovery mode, then the best way is backing up the transaction log, truncating the unused space and taking full / differential backup for recovery process. If the DB is in simple recovery mode, I use DBCC SHRINKFILE command. Here are the example SQL commands to shrink Transaction log. SELECT name from database.dbo.sysfiles (nolock) where status & 64 = 64 — This command returns the logical name of the transaction log. Assume it returns DB_LOG
— The next command would be, DBCC SHRINKFILE (db_log, target_size, truncateonly) /* SKChandra */
http://www.skchandra.com
I have a full recovery model for my datbase.
I already have used the following command to minimize the log file BACKUP LOG db_name
WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(2,1,TRUNCATEONLY)
GO The problem arised when I check the logfile size after running the above script.
The log file was still showing the same size.
Well, you have to backup the log more frequently. With Full recovery, is the only way.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
quote:Originally posted by jumbo
I have a full recovery model for my datbase.
I already have used the following command to minimize the log file BACKUP LOG db_name
WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(2,1,TRUNCATEONLY)
GO
I already have used the following command to minimize the log file BACKUP LOG db_name
WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(2,1,TRUNCATEONLY)
GO
Why would you be running a backup with TRUNCATE_ONLY if you are running in Full Recovery mode? From BOL: NO_LOG | TRUNCATE_ONLY Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms. After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE. If you don’t need the transaction log backups change to simple recovery and SQL server will take care of the tlog size for you!
http://www.sql-server-performance.com/absolutenm/templates/?a=260&z=1 for reference. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I would concur you certainly don’t want to be using the truncate_only option. If/when the time came for recovery, the system expects the log to be recoverable in series. If any point in the recovery chain is broken it will fail, and you will be stuck simply backing up from you last full backup. Which is likely not your intended goal since you are using Full Recovery model. I simply continue to append to the Log backup file during the day at whatever periods I set for different databases:
backup log oasisprod to OasisProd_Log And then after my nightly backup is done I do something similar but I "init" my log file so all of the previous days logs are wiped out in it:
backup log oasisprod to OasisProd_Log with init (In my examples OasisProd is the database name, and OasisProd_Log is the name of the backup device I created.)
Why do you want to truncate transaction log file size? This is usually not a very good idea. See http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6593&SearchTerms=tran,log,truncate and http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6894 for explanation.
If the disk space is not a problem then never think about increasing stress on SQL server by usign SHRINK method. If there are optimization jobs and bulk inserts on a regular basis, then better to size the Tlog and leave it as it is. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
The problem is disk space .I cant make the log file unrestricted.So I need to truncate it.
If you issue a backup and then a shrinkfile you can keep the size at a reasonable rate. The point everyone has been trying to make is that if you do the truncate, you literally lose your ability to restore from it. If you only wish to do a restore from the LAST COMPLETE BACKUP then you should swith the database from a FULL RECOVERY model to a SIMPLE RECOVERY model. Then SQL Server will take care of the log file for you. Whenever a checkpoint is reached it will throw away unneded space in the log and you’ll be good to go. But if your disaster recovery plan says "If the server crashses at 4:59 and we need to restore from the last backup and get back to the state the data was in as of 4:00 (last log backup point), then you are subverting that by doing the truncate, as you’ll find that you can’t restore anything beyond the last full backup. If space on the server is an issue, simply create a BACKUP device on another server on say its D drive as such:
\OtherServerD$BackupDirectoryMyLogBackupFileName That would allow you to do the log backup to another machine instead, which is fine, and then you can keep your recovery chain in tact.
The log file size will stop increasing if you schedule frequent enough transaction log backup. Read previous discussions mentioned.
Compile the jobs and activty then ascertain the log size in order to avoid any truncate and auto-size option. If you can afford then deploy SIMPLE recovery model and perform regular database backups in order to use in failure conditions. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>