| Author |
Topic |
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
 Posted - 2006-12-12 : 17:41:41
|
So I have been tinkering around all day in a test SQL environment. Best way for me to learn and understand things.Here is what I did.I backedup our one test DB on this box as well as its TLogs.I did it with simple code in SQL analyzer:DB code:BACKUP DATABASE <name here> TO DISK = '<path I put it>' NAME = '<name>' It worked fine.TLog:BACKUP LOG <name here> TO DISK = '<path here>' NAME = '<name>' Worked as well.Some interesting things.The DB file size was around 20gigs.The TLog File size was almost 27gigs.Backing up the DB resulted in a 20gig file.Backing up the TLog resulted in 600mb.I was surprised when I saw this. Very surprised and did not expect it.Ok, so my question is, after backing this up, and to my understanding, after you backup transaction logs when it is in FULL recovery mode, it is automatically trunated correct?Well, after backing up both, I looked at the TLog file and it was still the same size; 27gigs.Is there something else I need to do? I thought that after backing up and the TLog being truncated, it would get smaller. Yet, it is still the same size, which confused me.I started to think since the TLog needs to grow and the TLog had never been backed up, it just kept growing and growing.I feel a little under the gun here because I need to quickly get a method in place (while at the same time, making sure I understand it properly) so I can get started on our Production DB box.Anyway, was bit surprised by my results.Thanks.TCG |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-12 : 18:26:57
|
| The file size will not go down after you backup the transaction log. What happens though is that there is free space within that file now. You can shrink the files, but I wouldn't recommend it unless you know for sure that you don't need that space in the near future.Tara Kizer |
 |
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-12 : 18:29:22
|
quote:
Originally posted by tkizer
The file size will not go down after you backup the transaction log. What happens though is that there is free space within that file now. You can shrink the files, but I wouldn't recommend it unless you know for sure that you don't need that space in the near future.Tara Kizer
Thanks. I appreciate it.If I were to have tlog backups every 15 minutes, would it be worth shrinking? Or should I just leave it alone?Lastly, is that normal for the initial tlog backup to be that big? Then, the following backups much smaller? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-12 : 18:32:42
|
| Full backups will always be about the same size as the database. So if your database is 20GB, then your file will be around 20GB. No it's not worth shrinking unless you know you don't need the space anymore. The reason is because you receive a huge performance penalty if the file needs to expand again.Tara Kizer |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-12 : 19:01:24
|
quote:
Originally posted by tkizer
Full backups will always be about the same size as the database. So if your database is 20GB, then your file will be around 20GB. No it's not worth shrinking unless you know you don't need the space anymore. The reason is because you receive a huge performance penalty if the file needs to expand again.Tara Kizer
Ok, that makes a lot of sense.Let me ask this though.I just backed up our production database and TLogs (locally, then swept to tape).They were:DB Size: 16gigsTLOG Size: 35gigsLooking in EM, they are close in size...That proper?And, if I am understanding now, the following backups should be easier, correct?For example, I will perform another FULL backup here tonight, followed by tlog backups every so often (still working out the details.) however, since I have backed up the DB and TLogs prior, the DB size "will remain the same size, maybe slightly bigger", BUT the TLog file will be significantly smaller?And if I need to restore say tomorrow, 12pm, I would take:Tonights FULL backup followed by all the tlog backups up to the crash?Am I understnading this correctly? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-12 : 19:15:45
|
| Our transaction logs are typically 1-1.5 times the size of the MDF file. If you've ever run a reindex (optimizations is what it's called in the maintenance plan), then the tlog had to expand to accomodate the query. We backup our transaction log every 15 minutes as we can't afford to lose more than 15 minutes worth of data. If you don't have such data criticality, then I'd recommend hourly tlog backups. As far as the sizes go...Well the actual transaction log backup will be smaller but the LDF file will not. Yes to the crash question.Tara Kizer |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-12 : 19:18:48
|
quote:
Originally posted by tkizer As far as the sizes go...Well the actual transaction log backup will be smaller but the LDF file will not.
Are you talking significantly smaller? I ask because looking at the LDF file right now, it is 37gigs. I have about 35gigs of room left on the disk (this is because the DB backup which is 16gigs and the TLOG backup which is 35gigs, is taking up 51gigs of space on the drive.)I wanted to backup the tlog again, to see what the size would be...but if it is going to be anywhere close to 30+gigs, I can't take the chance of filling the disk. quote:
Yes to the crash question.Tara Kizer
Thank you very much. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-12 : 19:23:37
|
| The LDF file is the transaction log. It is not the transaction log backup. The LDF file will not change in size due to a transaction log backup. I can't tell you how big the tlog backup will be, but it should be significantly smaller if you've performed one recently.Tara Kizer |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-12 : 19:25:32
|
quote:
Originally posted by tkizer
The LDF file is the transaction log. It is not the transaction log backup. The LDF file will not change in size due to a transaction log backup. I can't tell you how big the tlog backup will be, but it should be significantly smaller if you've performed one recently.Tara Kizer
Right. I think the reason why the TLog was 35 gigs was that it had never been backed up before. How scary is that?Since I performaned the TLog backup about 30 minutes ago, I am thinking it should be couple hundred megs now? Guessing, but should be anywhere near 30+ gigs...I think.  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-12 : 19:31:16
|
| I'd go ahead and shrink the LDF file then to reclaim disk space. I'd shrink it down to the size of the MDF file as a beginning point.Tara Kizer |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-12 : 19:34:20
|
quote:
Originally posted by tkizer
I'd go ahead and shrink the LDF file then to reclaim disk space. I'd shrink it down to the size of the MDF file as a beginning point.Tara Kizer
Is that pretty safe and common to due on a production box? Should I perform it on off hours? Never done it before so I am weary.  In your opinion, what is the best way to shink the LDF file? Through the analyzer? Through EM? Another method?Thank you. |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-12 : 19:47:42
|
Found a couple Microsoft links:[url]http://support.microsoft.com/default.aspx/kb/873235[/url]and..[url]http://support.microsoft.com/kb/272318/[/url]Very helpful, but I still like talking to people.  Looks like I should do the following:1.) Perform another tlog backup 2.) Shrink the LDF File by using the following:DBCC SHRINKFILE(<database_LOG,20gigs)Can you put 20gigs, or would it need to be 20000?That right? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-12 : 20:09:47
|
According to SQL Server Books Online (which you should have installed already) in the DBCC SHRINKFILE article:quote:
target_sizeIs the desired size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size.If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.
Tara Kizer |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-12 : 20:11:23
|
quote:
Originally posted by thecoffeeguy
quote:
Originally posted by tkizer
I'd go ahead and shrink the LDF file then to reclaim disk space. I'd shrink it down to the size of the MDF file as a beginning point.Tara Kizer
Is that pretty safe and common to due on a production box? Should I perform it on off hours? Never done it before so I am weary. In your opinion, what is the best way to shink the LDF file? Through the analyzer? Through EM? Another method?Thank you.
It should definitely be performed after hours. Enterprise Manager uses the same query that we would use in Query Analyzer. It's up to you which you choose. If you do use Enterprise Manager, then don't be alarmed when EM is unresponsive during the shrink. Leave it be until it finally is responsive. It'll come back once the shrink is complete, which could take quite a bit of time.Tara Kizer |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-12 : 20:13:19
|
quote:
Originally posted by tkizer
According to SQL Server Books Online (which you should have installed already) in the DBCC SHRINKFILE article:quote:
target_sizeIs the desired size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size.If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.
Tara Kizer
Got it.so...DBCC SHRINKFILE(<database_LOG,20000) I am going to try this on a test environment first.On that environment, the DB file size is 20gigs. The LDF file is 28gigs.Think 20gigs is a good number to shoot for?If I blow up test, althought not great, not terrible and we can recover.If I blow up production...well, lets just not go there. |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-12 : 20:14:50
|
quote:
Originally posted by tkizerIt should definitely be performed after hours. Enterprise Manager uses the same query that we would use in Query Analyzer. It's up to you which you choose. If you do use Enterprise Manager, then don't be alarmed when EM is unresponsive during the shrink. Leave it be until it finally is responsive. It'll come back once the shrink is complete, which could take quite a bit of time.Tara Kizer
Ok...good to know...I know I would be stressing out if I didn't see anything happening on the progress indicator bar... I might do it through SQL analyzer...probably should get used to doing things that way I would assume. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-12 : 20:15:35
|
| For your test environment, you should be using SIMPLE recovery model as you most likely have no need to be able to restore to a point in time. I'd change that option first, then do the shrink. Practically all non-prod environments should be using SIMPLE recovery model. Unfortuneately, the default is FULL, which is why I change my model database to SIMPLE on non-prod environments so I don't have to remember to switch it when I create new databases in those environments.Tara Kizer |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-12 : 20:17:01
|
quote:
Originally posted by thecoffeeguy
I might do it through SQL analyzer...probably should get used to doing things that way I would assume.
In my best Teal'c imitation, "Indeed".  Tara Kizer |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-12 : 20:21:29
|
quote:
Originally posted by tkizer
quote:
Originally posted by thecoffeeguy
I might do it through SQL analyzer...probably should get used to doing things that way I would assume.
In my best Teal'c imitation, "Indeed". Tara Kizer
Thanks...can't thank you enough.Think my above code should suffice? Anything you would change? Shall I give it ago on my test box? |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2006-12-13 : 11:44:35
|
Would the following code work ok to shrink the size of the LDF file:DBCC SHRINKFILE(siebeltest_LOG,20000) That should shrink the siebeltest LDF file from 28gigs to 20gigs?The current MDF size is right around 20gigs, so i thought about pairing them up?Thoughts? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-13 : 12:16:39
|
| I disagree with Tara on a couple of points - which is rare!"If you don't have such data criticality, then I'd recommend hourly tlog backups [instead of every 15 minutes]."I find it very hard to justify "We are set up to lose 60 minutes data when we could be backing up every 10~15 minutes". However, I don't see any point backing up MORE frequently than every 10 minutes."Practically all non-prod environments should be using SIMPLE recovery model."Our DEV environments use FULL because the "DELETE FROM DontDeleteThisTable" type accidents are not uncommon WRT the Shrink issue:I think the timing of when you shrink the file should have as much to do with the re-expansion of the LDF file, as the time to make the shrink itself. Given that yours has grown like topsy its not easy to gauge what size it NEEDS to be. I normally reckon on about 120% of MDF.I would hold off shrinking it until you have regular TLog backups in place - otherwise it will grow, and need shrinking again, and get more fragmented ...Then I would be inclined to shrink it with no [or a very modest] size limit and let it grow back again [although I'm a bit undecided on this for a database which is 10's of GB large, rather than a few hundred MB or a few GB]I would recommend shrinking it just before your maintenance routines, as they are most likely the most log-intensive operation, and will push it back to it most likely "largest-operating-size" before the Users get hassled by the log expansions.I would change the File Expansion size to a few hundred megabytes - rather than a percentage - as IME its the percentage Expansion which tends to bring the server to its knees when the file gets above 5GB and the expansions start moving towards a GB or more for each new extension.And once you've got it back to an equilibrium size at the first opportunity take the database offline and physically defrag the MDF and LDF files (using the CONTIG tool from Sysinternals)Take a FULL backup before you make any of these changes - much quicker to sort yourself out then! A DIFF backup will do if you are short of disk space.Kristen |
|
|
|
Previous Page&nsp;
Next Page
|
Another Transaction Log question