August 31, 2009 at 11:51 am
Hello Experts, while working in SQL 2008 using the full recovery model I have noticed our LDF files growing and growing. I currently have two backup routines, one the full back up of the database and the second it so do transaction logs. From what I have read in the past when you did the full backups this would truncate the LDF files. This isn't the case in SQL 2008. So, I thought it would happen during the t-log backup, this isn't the case either.
I found that you can do this..
DBCC SHRINKFILE (N'DBName_LOG' , 0, TRUNCATEONLY)
You can also do this through task=>Shrink=>Files and choose the log file.
But doing this I think would put me in risk screwing up the log chain. MS says this..The transaction log is automatically truncated when the database is using the simple recovery model. If you must remove the log backup chain from a database, switch to the simple recovery model.
Seems a little ridiculous that you would just let these get bigger and bigger. There has to be something I am missing. When looking at the database properties the log is 99% empty in some cases. There is a maintenance task to shrink but this only does the databases and not the log files. Any help is appreciated.
Thanks
August 31, 2009 at 2:00 pm
jeremym (8/31/2009)
From what I have read in the past when you did the full backups this would truncate the LDF files. This isn't the case in SQL 2008. So, I thought it would happen during the t-log backup, this isn't the case either.
Full backups do not and never have truncated the transaction log. I don't know where you read that, but it was wrong. Log backups truncate the log, but that just marks space in the log as reusable. It doesn't affect the size of the log file.
I found that you can do this..
DBCC SHRINKFILE (N'DBName_LOG' , 0, TRUNCATEONLY)
TruncateOnly is ignored when the file is a log file. It's only valid for data files.
But doing this I think would put me in risk screwing up the log chain.
No it does not. Shrink just removes unused space. It won't touch the log chain
MS says this..The transaction log is automatically truncated when the database is using the simple recovery model. If you must remove the log backup chain from a database, switch to the simple recovery model.
Perfectly correct. The log is also truncated after a log backup in full and bulk-logged recovery models
Seems a little ridiculous that you would just let these get bigger and bigger. There has to be something I am missing.
They shouldn't get bigger and bigger. If you're doing log backups the log should be a consistent size, and you should not be shrinking it on a regular basis. Find the size it needs to be for the amount of transactions and frequency of log backups and then leave the log file along. Repeated shrinks and grows are a waste of time and resources.
Please read through this - Managing Transaction Logs[/url]
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
August 31, 2009 at 2:23 pm
In addition to what Gail has stated - remove that maintenance plan that shrinks your databases. You shouldn't be scheduling a shrink of data files (or log files) anyways.
You should only shrink a data file after an exceptional process has occurred. One that you know is not going to occur again - at least not in the next 6 months or more.
If you think you need to shrink your data files all the time, do you also understand that every time you shrink the data file you are fragmenting the indexes? If you don't immediately follow up a shrink operation with an index rebuild (which, btw - generally grows the data file back to it's original or larger size), you are basically causing performance issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 31, 2009 at 2:47 pm
Shrinking data file is useful only if you have delete or truncate operations going on in your database. Most of the time production databases do not delete data. So shrinking is not that useful. However Databases which hold staging tables do delete and truncate data. In such database shrinking data file will be useful.
If you want to shrink your log file in 2008 use the following technique.
--Use DatabaseName
--GO
--Alter Database DatabaseName Set Recovery Simple
--GO
--Alter Database DatabaseName Set Recovery Full
--GO
--DBCC SHRINKFILE ('DatabaseName_log')
this will remove your unused log space.
August 31, 2009 at 3:00 pm
Apurva (8/31/2009)
If you want to shrink your log file in 2008 use the following technique.--Use DatabaseName
--GO
--Alter Database DatabaseName Set Recovery Simple
--GO
--Alter Database DatabaseName Set Recovery Full
--GO
And take a full backup straight after to restart the log chain.
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
August 31, 2009 at 3:06 pm
Apurva (8/31/2009)
Shrinking data file is useful only if you have delete or truncate operations going on in your database. Most of the time production databases do not delete data. So shrinking is not that useful. However Databases which hold staging tables do delete and truncate data. In such database shrinking data file will be useful.If you want to shrink your log file in 2008 use the following technique.
--Use DatabaseName
--GO
--Alter Database DatabaseName Set Recovery Simple
--GO
--Alter Database DatabaseName Set Recovery Full
--GO
--DBCC SHRINKFILE ('DatabaseName_log')
this will remove your unused log space.
And I would disagree with that - since the very next time you run your processes to stage the data you are going to wait for the database to grow again, as well as wait for the log file - and of course, if you have small increments to grow by, a very fragmented file.
I just don't get this idea that you need to 'save' disk space. If your processing takes 100GB of data space - then allocate 120GB to the data file and leave it alone. Why shrink it - just to grow it again. Makes no sense to me at all.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 31, 2009 at 3:28 pm
And I have to agree with Jeff as well. Don't shrink the staging database, it just has to grow again.
August 31, 2009 at 4:20 pm
Jeffery I agree with you on the data file part. The data file is going to grow when your process runs again.
Many times I prefer to move the data from our main warehouse database tables to temporary tables when we have to make specific changes to the main tables. These tables are huge and some activities like adding columns and changing data types have a possibility of corrupting the table. Once done I drop the temporary table and prefer shrinking the data file so that it doesn't occupy unnecessary space. May be I am little over cautious on this.
August 31, 2009 at 4:34 pm
Apurva - if that is a process you perform on a regular basis, then yeah - it's really a waste of time to shrink it because you are going to do it again.
However, if it's a one off type of process where you don't think it is going to happen again - then it is perfectly okay to do that. I would not schedule it - since that implies a regular process.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 1, 2009 at 10:11 am
Thanks for all your insight everyone, very helpful.
Apurva nailed it, we had a big move and it blew the log files up which increased our storage reqs for our mirror in our DR site. We want to get it back to the normal size for every day usage.
I think what I am going to do is script it for an as needed use using the DBCC SHRINKFILE (N'DBName_LOG' , 0, TRUNCATEONLY) command.
Anyone happen to know how to query for LDF files? 🙂
Thanks again for your help.
Jeremy
September 1, 2009 at 10:30 am
jeremym (9/1/2009)
I think what I am going to do is script it for an as needed use using the DBCC SHRINKFILE (N'DBName_LOG' , 0, TRUNCATEONLY) command.
No!
Firstly, truncateonly is not a valid option when shrinking log files. It is only for data files. It will be completely ignored when you shrink a log file.
Second, do not shrink to 0. You should know how big the log usually is, before whatever occurrence grew it. Shrink it to that size, no smaller.
Anyone happen to know how to query for LDF files? 🙂
Query for ldfs or query the ldf?
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
September 1, 2009 at 10:52 am
If you are trying to read the ldf file then its going to be difficult. There is some sql command to do read the ldf file, but the output doenst make any sense.
Some time back I was trying it and found a software Apexsql. There were lot of installation problems. When I finally got it to work, It was not reading the full log. Just some part of it. May be coz I had trial version.
I dont think even red gate has anyhting for readign the log. Not sure now.
September 1, 2009 at 11:03 am
Apurva (9/1/2009)
I dont think even red gate has anyhting for readign the log. Not sure now.
They do, but only for SQL 2000.
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
September 1, 2009 at 11:09 am
Firstly, truncateonly is not a valid option when shrinking log files. It is only for data files. It will be completely ignored when you shrink a log file.
Second, do not shrink to 0. You should know how big the log usually is, before whatever occurrence grew it. Shrink it to that size, no smaller.
Gail, I took this directly from the Shrink file command in the studio when testing on the Northwind LDF which had 78% free. After the command I had 16% free. I have no idea how big the log files was before the move, all I know is that they are much bigger and the properties of the LDF tell me it has X% amount of free space.
USE [Northwind]
GO
DBCC SHRINKFILE (N'Northwind_log' , 0, TRUNCATEONLY)
GO
From what I gather the the 0 is the same as unspecified and will decrease the file as much as possible. I did drop the Truncateonly command and it did produce the same results.
Looking to query to get a list of LDF's so I can pop them in the script.
September 1, 2009 at 11:20 am
jeremym (9/1/2009)
From what I gather the the 0 is the same as unspecified and will decrease the file as much as possible.
Correct, but that is not a good idea. The log should not be shrink as much as possible, it should be shrunk to the size it needs to be for the transactional activity of the database
Looking to query to get a list of LDF's so I can pop them in the script.
Not a good idea. Don't shrink all logs automatically just because you can. Each one should be examined and, if necessary, shrunk once to a reasonable size.
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply