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

Yet another question about log files! Expand / Collapse
Author
Message
Posted Tuesday, November 13, 2012 8:41 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 3:37 AM
Points: 708, Visits: 3,288
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
Post #1384131
Posted Tuesday, November 13, 2012 9:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:54 AM
Points: 1,247, Visits: 9,879
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.
Post #1384138
Posted Tuesday, November 13, 2012 9:12 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 3:37 AM
Points: 708, Visits: 3,288
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
Post #1384147
Posted Tuesday, November 13, 2012 9:23 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: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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 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 #1384153
Posted Tuesday, November 13, 2012 9:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:54 AM
Points: 1,247, Visits: 9,879
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.
Post #1384154
Posted Tuesday, November 13, 2012 9:35 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 3:37 AM
Points: 708, Visits: 3,288
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
Post #1384164
Posted Tuesday, November 13, 2012 9:40 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: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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 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 #1384167
Posted Tuesday, November 13, 2012 9:41 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 3:37 AM
Points: 708, Visits: 3,288
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
Post #1384170
Posted Tuesday, November 13, 2012 9:55 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: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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 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 #1384175
Posted Tuesday, November 13, 2012 10:18 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 3:37 AM
Points: 708, Visits: 3,288
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
Post #1384181
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse