| Author |
Topic |
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
 Posted - 2007-01-23 : 23:31:10
|
| Very odd.I need to shrink the tlog file down to about 300-400megs.However, when I try to shrink it down the size, down from 37gigs, it only goes down to about 34gigs.Really confused on that right now.Any idea why?I jsut backed up the DB and tlog files. the tlogs were 100mb, DB was 17gb.Wow...I thought this would be easier.Do I need to do something like take the server offline or something?Thanks.TCG |
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2007-01-23 : 23:51:25
|
| Should I execute something like this, to try and recover the space? “dump tran dbname with truncate_only” |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-24 : 00:13:36
|
| DUMP TRAN?Hasn't that been deprecated? I haven't used DUMP TRAN since SQL Server 6.5.BACKUP LOG...TCG,What is your database recovery model set to? If not SIMPLE, are you performing regular tlog backups?Tara Kizer |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2007-01-24 : 11:59:20
|
quote:
Originally posted by tkizerTCG,What is your database recovery model set to? If not SIMPLE, are you performing regular tlog backups?Tara Kizer
Right now, it is full. I had been working on getting a script ready to do regular tlog backups, every hour. As it stands now, we do one tlog backup a day. Long story really, but other things came up.I was just surprised when it didn't shrink the file to the size I specified. |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2007-01-24 : 12:24:31
|
| would it be possible to just set the recovery mode to simple, do a full backup...would that clean up the 37gig tlog file? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-24 : 12:52:03
|
| You may have a "stuck" transaction at the 34GB point. Or that is the minimum size of something relevant - can;t think what though - the MODEL database perhaps? (not sure if that would do it though, and seems very unlikely ...)My general approach would be:USE MyDatabaseGOBACKUP LOG MyDatabase WITH TRUNCATE_ONLYGODBCC SHRINKFILE (MyDatabase_log, 1)GO(The name of your Log MyDatabase_log won't necessarily be to this style)Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-24 : 15:08:37
|
| If you are only backing up the transaction log once a day, then you won't see much improvement in the file size with a shrink since most of it is probably in use.Tara Kizer |
|
|
|
|
Problems shrinking a 37gig transaction log file?