SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Yet another question about log files!


Yet another question about log files!

Author
Message
Abu Dina
Abu Dina
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1229 Visits: 3323
So I've run sp_helpDB on a database that's in SIMPLE recovery model.

It's telling me that the size of the ldf file is 38452800 KB so that's 36GB.

So, as a one off exercise, should I just shrink the log file?

This isn't a very active OLTP or anything. Merely a staging database.

I have other databases which are live databases but hardly every change. After agreeing with the business to switch them all to SIMPLE recovery model, can I just shrink the transaction log on these DBs just once?

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
HowardW
HowardW
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2239 Visits: 9892
Assuming that the growth to 36GB was an unusual activity or was caused when it was in FULL Recovery Model and TLog backups weren't being run, then yes, a one off shrink is probably prudent.

In SIMPLE, the log still needs to be big enough to hold the largest set of concurrent transactions that occur during normal operations, so if there are no large batch jobs or large, long running transactions in general, you're unlikely to need a 36GB log. I would suggest reducing to 1GB for example, monitor for a week and see if it has grown at all (make sure you have enough free space for it to grow if needed) to see if you've found an appropriate level.

On some Data Warehouses I still require >50GB logs in SIMPLE mode, so it's very much dependent on the application profile.
Abu Dina
Abu Dina
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1229 Visits: 3323
HowardW (11/13/2012)
Assuming that the growth to 36GB was an unusual activity or was caused when it was in FULL Recovery Model and TLog backups weren't being run, then yes, a one off shrink is probably prudent.


Your second guess is right.

The database has been in FULL recovery model with no T-Log backups ever taken. I've changed to SIMPLE (no need for Point In Time disaster recovery) but then I realised that the backups of all databases on this SQL Server instance take a very long time so I thought I check the transaction log size to see if that's affecting backup size. That's when I noticed the 36GB log file.

I know how to shrink the log via SSMS but I'm not sure how to specify the size of the LDF file.

Also, can I just confirm that by shrinking the log file from 26GB to 1GB for example, my backup will also reduce by the same size?

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87069 Visits: 45267
Abu Dina (11/13/2012)
Also, can I just confirm that by shrinking the log file from 26GB to 1GB for example, my backup will also reduce by the same size?


No. Probably unaffected.

Backups only back up allocated data pages and log records needed for the backup to be restored consistent (active transactions, replication), not the entire of either.

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


HowardW
HowardW
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2239 Visits: 9892
Abu Dina (11/13/2012)
Also, can I just confirm that by shrinking the log file from 26GB to 1GB for example, my backup will also reduce by the same size?



No. Backups do not include unused space in data files or transaction logs, so it shouldn't have an effect on backup size.
Abu Dina
Abu Dina
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1229 Visits: 3323
Okay... I've shrunk the LDF file so now I hope that 36Gb has been given back to the OS.

I've also set the LDF to initial size 50MB. I'm not really sure if this is enough or not. I know it's a staging database but I'm not sure how much data manipulation is performed on the database.

Knowing my luck, someone is going to update 1 million rows tomorrow!

But can I just double confirm that after I change a database from FULL to SIMPLE, it's okay to shrink the log file just so that the space is given back to the OS?

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87069 Visits: 45267
Abu Dina (11/13/2012)
But can I just double confirm that after I change a database from FULL to SIMPLE, it's okay to shrink the log file just so that the space is given back to the OS?


As a once-off operation, if the log is much larger than necessary to support the normal operations on the DB, yes.

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


Abu Dina
Abu Dina
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1229 Visits: 3323
Rrrrright.. so because backups are online, SQL Server will include some transactions with the backup file so that when the DB is restored, it is in the state as it was at the point the backup was complete. Have I understood this correctly?

Also, by shrinking the LDF, I'm telling SQL Server to give back the OS the extra space. Now that's in simple recovery model, and as long as the transactions are not more than the size I allocated now, my LDF will not grow as SQL Server will reuse the space.

am I talking gibberish?

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87069 Visits: 45267
Pretty much right. Just watch out for large data loads and index rebuilds for taking lots of log space.

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


Abu Dina
Abu Dina
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1229 Visits: 3323
Thanks for the confirmation.

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
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