November 13, 2012 at 8:41 am
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[/url]
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
November 13, 2012 at 9:01 am
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.
November 13, 2012 at 9:12 am
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[/url]
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
November 13, 2012 at 9:23 am
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
November 13, 2012 at 9:23 am
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.
November 13, 2012 at 9:35 am
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[/url]
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
November 13, 2012 at 9:40 am
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
November 13, 2012 at 9:41 am
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[/url]
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
November 13, 2012 at 9:55 am
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
November 13, 2012 at 10:18 am
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[/url]
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply