| Author |
Topic |
|
bogey
Posting Yak Master
166 Posts |
 Posted - 2007-01-30 : 14:49:54
|
| Can someone help clarify something for me. I have two transaction logs that are around 8 and 15 Gb. From what I've read they will always stay this size no matter what I do. The database is in full recovery mode and I've done full backups and backups of transaction logs with the truncate option |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-30 : 14:54:11
|
| That is not true. Transaction logs can grow.Truncate option? No!!! Just perform regular transaction log backups, such as every 15 minutes or hourly.You are invalidating the transaction log chain by using the truncate option, which means you've lost the ability to restore to a point in time. You might as well use SIMPLE recovery model if you are going to continue to use the truncate option.Tara Kizer |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-30 : 15:08:05
|
| >> From what I've read they will always stay this size no matter what I doWhere did you read that? Do you mean they can't be made smaller or they can't grow - both are probably false. Have a look athttp://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
bogey
Posting Yak Master
166 Posts |
Posted - 2007-01-30 : 15:32:15
|
| Tara - Let me correct myself. We backup the logs every 15 minutes and do not truncate them. We must have had a process(es) that were transaction intensive and pushed the size of the log files up. From what I've read the size is like a high water mark.Thanks for your quick reply. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-30 : 15:51:08
|
| I'm not sure what you mean by a high water mark.Tara Kizer |
|
|
|
bogey
Posting Yak Master
166 Posts |
Posted - 2007-01-30 : 16:01:57
|
| Sorry to be so confusing. Hope this explains it better. Say my log is 15Gb and I backup my transaction log with truncate. The backup will remove data (inactive part) from the transaction log but will not reduce its size (correct?). The high water mark is 15Gb, it represents the largest or highest size of the transaction log to date. My misconception was that a truncate actually reduced the size of the transaction log. I think alot of people may think this who are new to sql server. Upon further investigation, Artice Id 873235 [url]http://support.microsoft.com/kb/873235[/url] explains this better. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-30 : 16:04:33
|
| Yes that is correct. Only a shrink will affect the actual file size.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-31 : 04:00:21
|
| You could have a look at the size of your TLog backup files. If the biggest during the week is a LOT smaller than 15GB then it might be worthwhile shrinking your Log file down to that size.But if there is a chance you might use the space again, and you are not desperate for the disk space, leave it as it is; expanding a file is a resource intensive task therefore no point shrinking unless you are sure it won't re-expand later.(Only issue with larger-than-necessary filesizes, IME, is that the Restore time is longer - which can be a critical issue during disaster recovery when time is tight, of course)Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-31 : 11:31:42
|
| The size of the MDF and LDF doesn't impact the size of the backups. So if there is a ton of extra space in either of them, your restore times won't be impacted.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-31 : 11:58:34
|
| "your restore times won't be impacted"In the situation where two databases have an identical amount of actual data in them it takes me longer to restore the backup made from the bigger database than the smaller one.May just be me of course!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-31 : 12:24:27
|
| The initial restore of the full backup will take longer as it's got to create the larger file sizes. Transaction log restores though should be the same times. I believe that this is only true for SQL Server 2000 and lower. SQL Server 2005 drastically improves restore times.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-31 : 15:14:22
|
| "SQL Server 2005 drastically improves restore times"Yes, I've heard that and look forward to it (but I was mindful that this was a 2000 forum)I did an experiment (SQL 2000)Reasonably posh server:Windows Server 2003, 4GB RAM, Enterprise licence, 4 CPUs, RAID with lots of disksOriginal database size 26GB, free space 18.5GB. Backup size 7GB. MDF = 12GB, LDF = 14GBRestore (from full backup) to a new database (LOG and DATA on same subsystems): 15m05sThen I shrunk the restored database, and made a new full backup file (also 7GB), and then restored:Restore over now pre-existing database: 7m36sDrop databaseRestore again - LOG and DATA on different subsystems: 7m32sRestore again onto pre-existing files (LOG and DATA still on different subsystems): 7m34So if this is representative: it implies that under SQL 2000 (assuming that the physical database files are 4x the size of the backup file) the restore time is twice as long as a system which is shrunk-to-exactly-fitKristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-31 : 15:17:56
|
| Yes I realize that. I changed my mind in my last post.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-31 : 15:29:14
|
| "Yes I realize that"Sorry, I didn't mean to imply anything ... my experiment was for my own benefit (and for anyone here who can make use of the results, of course)Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-31 : 15:40:01
|
Sorry for jumping to conclusions then!  Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-31 : 15:50:51
|
| No problem ... |
|
|
|
|
Transaction Log Size Clarification