Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Yet another question about log files!
Add to briefcase

Yet another question about log files! Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Abu Dina
Posted Tuesday, November 13, 2012 8:41 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 512, Visits: 2,244
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?

-----------------------------------
http://www.SQL4n00bs.com
Post #1384131
HowardW
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: Today @ 10:16 AM
Points: 1,092, Visits: 7,931
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
Abu Dina
Posted Tuesday, November 13, 2012 9:12 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 512, Visits: 2,244
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?

-----------------------------------
http://www.SQL4n00bs.com
Post #1384147
GilaMonster
Posted Tuesday, November 13, 2012 9:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 38,091, Visits: 30,384
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
HowardW
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: Today @ 10:16 AM
Points: 1,092, Visits: 7,931
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
Abu Dina
Posted Tuesday, November 13, 2012 9:35 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 512, Visits: 2,244
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?

-----------------------------------
http://www.SQL4n00bs.com
Post #1384164
GilaMonster
Posted Tuesday, November 13, 2012 9:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 38,091, Visits: 30,384
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
Abu Dina
Posted Tuesday, November 13, 2012 9:41 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 512, Visits: 2,244
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?

-----------------------------------
http://www.SQL4n00bs.com
Post #1384170
GilaMonster
Posted Tuesday, November 13, 2012 9:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 38,091, Visits: 30,384
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
Abu Dina
Posted Tuesday, November 13, 2012 10:18 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 512, Visits: 2,244
Thanks for the confirmation.

-----------------------------------
http://www.SQL4n00bs.com
Post #1384181
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.