| Author |
Topic |
|
qwertyjjj
Posting Yak Master
131 Posts |
 Posted - 2008-07-14 : 09:59:24
|
| My database only has 178Mb of data yet the log file is 8Gb.I have tried the following but it doesn't reduce the sixe of the log:BACKUP LOG kpi WITH TRUNCATE_ONLYDBCC SHRINKFILE(kpi_log, 128)Any ideas on what to do? |
|
|
cvipin
Yak Posting Veteran
51 Posts |
Posted - 2008-07-14 : 10:08:30
|
| Try this:CHECKPOINT BACKUP LOG kpi WITH TRUNCATE_ONLY DBCC SHRINKFILE ('kpi_Log', 2) |
 |
|
|
qwertyjjj
Posting Yak Master
131 Posts |
Posted - 2008-07-14 : 10:12:53
|
| Nope, same size.The available space is decreasing each time I do a backup.The space allocated in enterprise manager seems to be 8Gb |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2008-07-14 : 10:19:54
|
| Look in sys.databases in a column called log_reuse_wait_desc and tell me what's there? I just had an issue this morning where a transaction was hung and prevented me from truncating.Mike"oh, that monkey is going to pay" |
|
|
|
qwertyjjj
Posting Yak Master
131 Posts |
Posted - 2008-07-14 : 10:33:25
|
| That column doesn't exist, this is SQL 2000, so sysdatabases.Only 2005 has sys.databases soesn't it? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-14 : 10:41:22
|
You can't shrink a file to a size less than originally created, so maybeDBCC SHRINKFILE(kpi_log, 128)doesn't run at all?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2008-07-14 : 10:45:54
|
| ah. damn. you may be right. It's been a while since I've been in 2k.Mike"oh, that monkey is going to pay" |
|
|
|
qwertyjjj
Posting Yak Master
131 Posts |
Posted - 2008-07-14 : 10:45:57
|
| That runs, but it doesn't actually shrink the file at all.:( |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-14 : 10:47:04
|
What size was the log file initially/orginally?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
qwertyjjj
Posting Yak Master
131 Posts |
Posted - 2008-07-14 : 10:52:20
|
quote:
Originally posted by Peso
What size was the log file initially/orginally?
E 12°55'05.25"N 56°04'39.16"
No idea, how can I find that out?Doubt it was 8Gb though... |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-14 : 11:08:39
|
quote:
Originally posted by qwertyjjj
My database only has 178Mb of data yet the log file is 8Gb.I have tried the following but it doesn't reduce the sixe of the log:BACKUP LOG kpi WITH TRUNCATE_ONLYDBCC SHRINKFILE(kpi_log, 128)Any ideas on what to do?
By doing this you can truncating log chain and not able to have point in time of recovery.If you don't care about it, Change your recovery model to simple and shrink the log file. |
|
|
|
qwertyjjj
Posting Yak Master
131 Posts |
Posted - 2008-07-14 : 11:21:03
|
| I run daily backups, so don't need PIT recovery.? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-14 : 11:28:04
|
| Then do it. |
|
|
|
qwertyjjj
Posting Yak Master
131 Posts |
Posted - 2008-07-14 : 11:29:52
|
| That worked. ThanksShould I turn auto shrink on or doesn't it matter? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-14 : 11:37:40
|
| Donot turn Auto shrink on as it will reshuffle your index page and gets fragmented. Best solution is to have Transaction log backup scheduled frequently while DB is in Full recovery model so you can have PTR and minimal log growth. But if you have Log consuming task like bulk insert,massive delete .Then you can change your recovery model to simple unless you don't care about PTR. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-14 : 22:17:34
|
| Don't change recovery model on the fly, better to backup log more frequent when have big data change. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-14 : 22:56:04
|
| OP already said he is not worried about PTR. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-14 : 23:14:25
|
| You mean this comments 'I run daily backups, so don't need PIT recovery'? OP doesn't seem realize what can do with daily backup, so you should give advice on best practice. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-15 : 02:03:38
|
quote:
Originally posted by qwertyjjj
I run daily backups, so don't need PIT recovery.?
Full backups do not give you point-in-time recovery. You need to read up on the backup topic.If you aren't going to be backing up the transaction log, then you need to switch your recovery model to simple. Do this on the model database too so that any future databases are created with this database option.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
qwertyjjj
Posting Yak Master
131 Posts |
Posted - 2008-07-15 : 04:39:32
|
| My other databases need PIT recovery, this one doesn't though.A full backup is fine, I can restore to that if needed. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-15 : 08:08:02
|
| "You mean this comments 'I run daily backups, so don't need PIT recovery'? OP doesn't seem realize what can do with daily backup, so you should give advice on best practice."He knows about that.Please see his latest post. |
|
|
|
Next Page
|
8Gb log, 178Mb data ????