log file

I was out of town for a few days and when I got back found that my
transaction log backups had been failing. Nice. Anyway I now have a big XXX
transaction log that I need to back up. I seem to recall an article a while
ago that if you set a database offline, it shuts it down and commits all the
transactions in the log file. You could then delete the log file and when yo
u
bring the database back online it will create a new empty log file. Is this
true all you people who are WAY smarter than me? (You don't see me answering
question now do you?)
TomWhat I would do is just try to backup the transaction log. Is there a disk
space problem? What about some location on the network?
Ben Nevarez, MCDBA, OCP
Database Administrator
"rk rider" wrote:

> I was out of town for a few days and when I got back found that my
> transaction log backups had been failing. Nice. Anyway I now have a big as
s
> transaction log that I need to back up. I seem to recall an article a whil
e
> ago that if you set a database offline, it shuts it down and commits all t
he
> transactions in the log file. You could then delete the log file and when
you
> bring the database back online it will create a new empty log file. Is thi
s
> true all you people who are WAY smarter than me? (You don't see me answeri
ng
> question now do you?)
> Tom|||yeah, space is an issue. even if I back up the log it's still gonna have thi
s
big empty tranaction log file out there taking up disk. Thanks for your
reply. Any one have a an answer for the specific scenario I outlined?
"Ben Nevarez" wrote:
[vbcol=seagreen]
> What I would do is just try to backup the transaction log. Is there a disk
> space problem? What about some location on the network?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "rk rider" wrote:
>|||"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:2674C20F-453D-4D81-82A9-BF3A65BCDF4E@.microsoft.com...
> yeah, space is an issue. even if I back up the log it's still gonna have
> this
> big empty tranaction log file out there taking up disk. Thanks for your
> reply. Any one have a an answer for the specific scenario I outlined?
>
Back up the log file, then shrink it.
David|||Here is the article I was looking for. Anyone have any comments?
http://www.databasejournal.com/feat...cle.php/1460151
"rk rider" wrote:
[vbcol=seagreen]
> yeah, space is an issue. even if I back up the log it's still gonna have t
his
> big empty tranaction log file out there taking up disk. Thanks for your
> reply. Any one have a an answer for the specific scenario I outlined?
> "Ben Nevarez" wrote:
>|||Yeah, got that. Not the question I was asking however but thanks for taking
the time to reply.
"David Browne" wrote:

> "rk rider" <rkrider@.discussions.microsoft.com> wrote in message
> news:2674C20F-453D-4D81-82A9-BF3A65BCDF4E@.microsoft.com...
> Back up the log file, then shrink it.
> David
>
>|||"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:DCA8DF35-A5B4-4EA8-A2C4-7588E06D74AD@.microsoft.com...
> Here is the article I was looking for. Anyone have any comments?
> http://www.databasejournal.com/feat...cle.php/1460151
>
Yes. Don't do that. It breaks the log chain. So long as your log chan is
unbroken, you can restore your database even if you loose a full backup.
David|||I would never dare to just delete the log file for a database, detached or n
ot. I've seen way too
many posts here where they just won't get the database into SQL Server after
such operations. Call
me paranoid, if you wish... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:DCA8DF35-A5B4-4EA8-A2C4-7588E06D74AD@.microsoft.com...[vbcol=seagreen]
> Here is the article I was looking for. Anyone have any comments?
> http://www.databasejournal.com/feat...cle.php/1460151
> "rk rider" wrote:
>|||"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:06E6E944-E4EE-4BF1-9652-2EC49B4A5160@.microsoft.com...
> I was out of town for a few days and when I got back found that my
> transaction log backups had been failing. Nice. Anyway I now have a big
XXX
> transaction log that I need to back up. I seem to recall an article a
while
> ago that if you set a database offline, it shuts it down and commits all
the
> transactions in the log file. You could then delete the log file and when
you
> bring the database back online it will create a new empty log file. Is
this
> true all you people who are WAY smarter than me? (You don't see me
answering
> question now do you?)
I would simply take the size hit and backup the log and keep your
transaction log chain intact.

> Tom|||"rk rider" <rkrider@.discussions.microsoft.com> wrote in message
news:DCA8DF35-A5B4-4EA8-A2C4-7588E06D74AD@.microsoft.com...
> Here is the article I was looking for. Anyone have any comments?
> http://www.databasejournal.com/feat...cle.php/1460151
Yes.
Don't do it.
While this "usually" works, my understanding is Microsoft does NOT support
this.
And this would completely invalidate your transaction log backup chain
anyway.
You're better off doing a:
backup log <foo> with truncate_only
then a FULL backup and then resume your log backups.
[vbcol=seagreen]
> "rk rider" wrote:
>
this[vbcol=seagreen]
disk[vbcol=seagreen]
big XXX[vbcol=seagreen]
a while[vbcol=seagreen]
all the[vbcol=seagreen]
when you[vbcol=seagreen]
Is this[vbcol=seagreen]
answering[vbcol=seagreen]