Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC SHRINKFILE to clear the log file Expand / Collapse
Author
Message
Posted Wednesday, June 11, 2008 10:52 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 12:34 AM
Points: 966, Visits: 855
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?
Post #515644
Posted Thursday, June 12, 2008 12:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #515667
Posted Thursday, June 12, 2008 1:40 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 12:34 AM
Points: 966, Visits: 855
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.
Post #515701
Posted Thursday, June 12, 2008 2:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #515707
Posted Thursday, June 12, 2008 2:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 12:34 AM
Points: 966, Visits: 855
Do changing the log_reuse_wait_desc to 'CHECKPOINT' or any other value helps in checking the growth of the log file?
Post #515711
Posted Thursday, June 12, 2008 2:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #515714
Posted Thursday, June 12, 2008 3:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 12:34 AM
Points: 966, Visits: 855
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.
Post #515739
Posted Thursday, June 12, 2008 4:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #515767
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse