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 12»»

transaction log size reduction Expand / Collapse
Author
Message
Posted Wednesday, March 27, 2013 6:17 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 1,754, Visits: 3,161
Does backup of transaction log reduce the log file size or it is DBCC SHRINKFILE really make the size small?

I know it is not good practice to shrink log, but we have special case that need to do that to save space. For example we have some archived databases and set to readonly, and simple recovery mode,
we don't need those big log file there, so I was able to shrink the log size to a smaller one.

But I remember somewhere I read only backup transaction log reduces the size, not shrink , or maybe I didn't remmember it correctly?

Thanks
Post #1436233
Posted Thursday, March 28, 2013 3:21 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 @ 12:11 PM
Points: 42,470, Visits: 35,541
Log backups don't change the size of the file, they just mark the space inside as reusable.

Please read through this - Managing Transaction Logs



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 #1436326
Posted Thursday, March 28, 2013 4:42 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:27 PM
Points: 15,541, Visits: 27,919
And this one too.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1436378
Posted Thursday, March 28, 2013 6:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 28, 2013 6:31 AM
Points: 1, Visits: 0
If the database is in Full recovery model then you can able to take the log backup.
Post #1436441
Posted Thursday, March 28, 2013 10:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 1,754, Visits: 3,161
Thanks all, so I think I remember it wrong,

What is correct should be:

Backup transaction log is only mark part of transaction log file active, so it could be reused, and it will not make the size small.
Shrink will actually reduce the size of the log file.

Post #1436587
Posted Thursday, March 28, 2013 10:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 1,754, Visits: 3,161
ganasulochana (3/28/2013)
If the database is in Full recovery model then you can able to take the log backup.


for simple recovery mode, when it will do the checkpoints?
Post #1436598
Posted Thursday, March 28, 2013 11:07 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 @ 12:11 PM
Points: 42,470, Visits: 35,541
sqlfriends (3/28/2013)
ganasulochana (3/28/2013)
If the database is in Full recovery model then you can able to take the log backup.


for simple recovery mode, when it will do the checkpoints?


Checkpoint runs on a regular basis in databases in all recovery models. Recovery model doesn't change how often checkpoint runs, it depends on data changes and the recovery interval setting.



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 #1436620
Posted Thursday, March 28, 2013 11:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 1,754, Visits: 3,161
GilaMonster (3/28/2013)
sqlfriends (3/28/2013)
ganasulochana (3/28/2013)
If the database is in Full recovery model then you can able to take the log backup.


for simple recovery mode, when it will do the checkpoints?


Checkpoint runs on a regular basis in databases in all recovery models. Recovery model doesn't change how often checkpoint runs, it depends on data changes and the recovery interval setting.


Thanks much, is there a way that I can find out what it the 'recovery interval setting'?

Post #1436622
Posted Thursday, March 28, 2013 11:17 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 @ 12:11 PM
Points: 42,470, Visits: 35,541
Sure, look it up on the server properties window, in sys.configurations or sp_configure.


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 #1436629
Posted Thursday, March 28, 2013 1:23 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
If you haven't specified it during the initial install, it's set to 0 by default, meaning that it happens automatically (running approximately every 1 minutes for active DB's)

Reference MSDN here


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1436688
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse