June 11, 2008 at 10:52 pm
I have a full backup plan for our database on every Thursday and Sunday night.Rest of days I do differential backup.The database is in simple recovery model.On every Thursday morning I do a very large insert into database so my log file keeps growing. By friday my disk space would be full,so I have to execute the DBCC SHRINKFILE(@filename) to shrinkfile the log file. My understanding is that once the full backup is done on Thursday night all the log file should be cleared and there should be enough space in disk on Friday.Why does the full backup does not clear the log file?
June 12, 2008 at 12:27 am
Full backups do not truncate transaction logs. If you're in full or bulk logged recovery modes, only a log backup will truncate teh transaction log and make the space available.
Since you're in simple recovery, the transaction log should be truncated at chackpoints, but that doesn't mean that the file on disk shrinks, only that the space within the tran log is made available for reuse.
If you're not seeing that then something is preventing the removal of old log records. See what the value of Log_Reuse_Wait_desc is in sys.databases for that database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2008 at 1:40 am
The Log_Reuse_Wait_desc is 'nothing' for this database.How to make the transaction log truncate at checkpoints? What is the difference between shrinking and truncating the file.
June 12, 2008 at 2:04 am
If you're in simple recovery mode the log automatically truncates at checkpoints. There's nothing you need to do.
The log file contains inside it a number of log segments. These store the log records for SQL.
When a log truncation occurs (either due to a checkpoint in simple recovery or a log backup in full/bulk logged) any log segment that does not contain active log records (records for data changes not committed to disk or needed by replication or database mirroring) is emptied and that space within the log file is marked for reuse. Truncation does not change the size of the log file on disk.
When the log file is shrunk, the size of the log file is reduced to the size requested, or the minimum size to contain all in use log segments in the log
Make sense?
If there's nothing preventing log reuse, then the growth of the log file could be due to large transactions, eg index rebuilds or bulk loads. However it shouldn't keep on growing if you're in smple recovery, unless there's an open transaction or transactional replication.
Check the log reuse after the data load on thursday morning please, and again before and after the backup on thursday evening.
You can also use DBCC SQLPERF(LogSpace) to see what % of the log file is in use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2008 at 2:13 am
Do changing the log_reuse_wait_desc to 'CHECKPOINT' or any other value helps in checking the growth of the log file?
June 12, 2008 at 2:16 am
You can't change sys.databases. It's a system view.
log_reuse_wait_desc tells you what the log is waiting for to be reused. Nothing more. It's a report of database information, not a configuration option.
If you are in simple recovery mode, the log WILL truncate on checkpoint.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2008 at 3:19 am
The main cause is on Thursday morning I insert about 10 million of data to a table. Using the sysfiles I can view that the log file has grown then on Thursday night a full backup occurs and I expect the log file to truncate. If this does not happen then the next day I manually execute the DBCC SHRINKFILE(Logfilename) to truncate the log file.I want to avoid this and SQL Server should some how truncate the log file. Some times the table insert occurs on Monday or friday so the log file even grows on that day.I want the log file to truncate the next day.
June 12, 2008 at 4:32 am
Truncate is not the same as shink. Was my explaination not clear?
Truncate just marks space inside the file for reuse. Shrink changes the size of the file.
Truncates occur on checkpoint in simple recovery mode and on log backup in full recovery mode.
SQL will not shrink a file without been told to do so.
In general, shrinking files is a bad idea. The file will just grow again, causeing file level fragmentation and taking up resources.
It's best to size a log file for the max it needs to be and then leave it alone.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply