LOG FILES | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

LOG FILES

HI ALL, How to clear log files in sqlserver database? Thanks,
Sriram.
Log files or transaction log files?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
I would like to clear transaction log files.
If your db use full recovery model, you can try with next script followed by full db backup:
alter database db set recover simple
go
dbcc shrinkfile (tran_log, TargetSize)
go
checkpoint
go
dbcc shrinkfile (tran_log, TargetSize)
alter database db set recovery full
go

Followin Mmarovic idea, is recovery model full that is the way.
If recovery model is simple, with shrinkfile (after checkpoint) is the way.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
AUTO_SHRINK only reduces the size of the transaction log if the database is set to SIMPLE recovery model or if the log is backed up. Make sure you maintain full backup if you choose ot keep the database in SIMPLE recovery model, because the database can be recovered only to the last full database backup or last differential backup. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
use mydb
GO
backup log mydb with no_log
GO
dbcc shrinkdatabase(mydb)
GO
/… but when you use WITH NO_LOG ensure to maintain full good known backups, in case if any issue while performing you can restore the database easily otherwise you will be… *%*&*** Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
My TLog size is big because I run maintenance plan every night. The DB is set to Full mode. Although, I get a full backup after the maintenance plan but still the size is big. Shouldn’t it reduce the TLog size?
DBCC SQLPERF (LogSpace)
showes the log size as 2587.43 MB and Log space used as 1.07%. If I use MMarovic’s script AFTER the maintenance plan and BEFORE the full backup, does it reduce the size?
CanadaDBA
I offered the script as one-time solution. I don’t like to shrink tlog regularly, because I don’t see the point if it will be expanded/shrinked again daily. This is waste of resources IMO and may cause Tran log file fragmentation, especially if your Tran log file growth is specified too low. If you still want to apply this solution I would backup transaction log before the run of db maintenance plan, apply the script after maintenance is complete and run full database backup immediately after that.
So, if I don’t want to use that script, what is the suggestion to avoid the large TLog? Remember that I get a full backup every night and also run maintenance plan (optimization and reindex) every night. Is it possible to order these nightly works in a way that prevent to increase the TLog size? for example: 1. do a full backup
2. do the maint. plan (optimization & reindexing)
3. set the DB to simple mode
4. do a full backup
5. set the DB in full recovery mode Does it make sense?
quote:Originally posted by mmarovic I offered the script as one-time solution. I don’t like to shrink tlog regularly, because I don’t see the point if it will be expanded/shrinked again daily. This is waste of resources IMO and may cause Tran log file fragmentation, especially if your Tran log file growth is specified too low. If you still want to apply this solution I would backup transaction log before the run of db maintenance plan, apply the script after maintenance is complete and run full database backup immediately after that.

CanadaDBA
quote:1. do a full backup
2. do the maint. plan (optimization & reindexing)
3. set the DB to simple mode
4. do a full backup
5. set the DB in full recovery mode Does it make sense?
I would apply only 1, 2 and 4. Actually you can start with tran log backup, then 2 and 4.
quote:So, if I don’t want to use that script, what is the suggestion to avoid the large TLog?
Scheduling transaction log backup every hour or something like that (depending on activitis on your server) may help. But I am afraid that reindexing is main log producer. You have enough disk space to complete index rebuild, if you have backup after that your log file will not grow anymore. Is it the problem to keep that size of your transction log?
A full database backup does NOT truncate the transaction log. If you wish to use the full recovery model, then you need to take transaction log backups on a regular basis. A transaction log backup WILL truncate the log. If you do not take transaction log backups, or otherwise truncate the log, then yes, the log will continue to grow. If you use the full recovery model and do NOT take transaction log backups, but truncate the log via other means, then there is absolutely no point in using the full recovery model, and you should simply change to simple recovery model. In simple recovery model, the log will be truncated after each transaction is written to the database file. However, you will lose point in time recovery with this model (which you don’t have anyway if you’re not taking transaction log backups). Steve
quote:A full database backup does NOT truncate the transaction log. If you wish to use the full recovery model, then you need to take transaction log backups on a regular basis. A transaction log backup WILL truncate the log.
So your suggestion is (in case of keeping full recovery mode) to perform transaction log backup immediately after maintenance, because maintenance (reindexing part) is the biggest log producer, right? I agree about that, i missed that part. If you want to have shorter recovery time the plan would be: 1. tran log backup
2. maintenance
3. full backup
4. tran log backup If you want just too keep log size under control and do backup and maintenance as fast as possible then: 1. full backup
2. maintenance
3. tran log backup
When I re-read thread, i believe problem is that log backup was never done, so it kept growing. Farhardr, is that right? If so you can apply script to on shrink the log, then schedule tran log backups as suggested and your log size will stay much smaller all the time.
]]>