Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC SHRINKFILE to clear the log file


DBCC SHRINKFILE to clear the log file

Author
Message
Ashwin M N
Ashwin M N
SSC Eights!
SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)

Group: General Forum Members
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
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

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


Ashwin M N
Ashwin M N
SSC Eights!
SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)

Group: General Forum Members
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
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

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


Ashwin M N
Ashwin M N
SSC Eights!
SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)

Group: General Forum Members
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
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

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


Ashwin M N
Ashwin M N
SSC Eights!
SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)

Group: General Forum Members
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
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

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search