Re: transaction log blow up
From: Uri Dimant (urid_at_iscar.co.il)
Date: 08/08/04
- Next message: Aaron [SQL Server MVP]: "Re: Start SQLServerAgent job Synchronously"
- Previous message: Janet: "Re: transaction log blow up"
- In reply to: Janet: "Re: transaction log blow up"
- Next in thread: Andrew J. Kelly: "Re: transaction log blow up"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 8 Aug 2004 16:17:41 +0200
Janet
It could be a long running transaction.
If you have a FULL recovery model so any transactions which have been
already written to the disk (inactive portion of the log) and you did not
perfom BACKUP LOG which does truncate this portion so the log will keep on
its size.
Run DBCC LOGINFO (db_id) to see any open transactions. If you see a
transaction with status =2 at the end of the list that means SQL Server
'somehow' needs the transaction and you will not be able to shrink the log
intil this status =2 will be at top of the list
I am certain that even though the log file is in SIMPLE recovery model it
still may be filled.
Search on interenet
"Janet" <anonymous@discussions.microsoft.com> wrote in message
news:205901c47d47$c0d9dd70$a301280a@phx.gbl...
> I definitely would like to maintain transaction log
> backups, so you're correct it is set to FULL.
> Uri, do you have any idea how a log could blow up to 30
> GB? There's no way we had a transaction any where near
> that size.
>
> >-----Original Message-----
> >Janet
> >If don't carry about the data you can set recovery model
> to SIMPLE . I
> >presume you are in a FULL recovery model
> >
> >"Janet" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:20e401c47d07$3c0dd8c0$a501280a@phx.gbl...
> >> Hi, I have a database which takes up 2GB for the .mdf
> >> file. It's on a brand new server that has over 30GB of
> >> free space for the disk where the log file resides. The
> >> other day, the log file expanded to fill all 30GB of
> the
> >> disk. I truncated the log and shrunk the log file. I
> also
> >> increased the transaction log backup so occur every 1
> >> hour instead of every 4 hours which is what I had it
> set
> >> to. I also put a limit 2GB limit on the log file.
> >>
> >> My question/concern is why did the log file get so
> large
> >> in the first place? What are usually some of the
> reasons
> >> why a log for such a small database increase to the
> full
> >> capacity of the disk?
> >>
> >> Thanks,
> >> Janet
> >
> >
> >.
> >
- Next message: Aaron [SQL Server MVP]: "Re: Start SQLServerAgent job Synchronously"
- Previous message: Janet: "Re: transaction log blow up"
- In reply to: Janet: "Re: transaction log blow up"
- Next in thread: Andrew J. Kelly: "Re: transaction log blow up"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
