From: John Couch on
I was thinking of literally deleting the file, and switching to a new file,
thus making the new file the primary. Not taking the DB offline or detaching
it. Is there a way to do it?

"Mark Hickin" wrote:

> It sounds as if you want to move your transaction log, or perhaps just rename
> it.
>
> The easiest way to do this is to detach the database (it will cease to be
> available), move the file, and re-attach specifying the new log file
> location.
>
>
>
>
From: Andrew J. Kelly on
Deleting the log file will put the db into suspect mode and you will be in
questionable shape, certainly not useable at the very least. Why do you want
to do this in the first place? If you tell us what you are trying to
accomplish we might be able to suggest alternate approaches.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"John Couch" <JohnCouch(a)discussions.microsoft.com> wrote in message
news:D9097D64-8B30-4520-B188-C2BF2CEA75BE(a)microsoft.com...
> I was thinking of literally deleting the file, and switching to a new
> file,
> thus making the new file the primary. Not taking the DB offline or
> detaching
> it. Is there a way to do it?
>
> "Mark Hickin" wrote:
>
>> It sounds as if you want to move your transaction log, or perhaps just
>> rename
>> it.
>>
>> The easiest way to do this is to detach the database (it will cease to be
>> available), move the file, and re-attach specifying the new log file
>> location.
>>
>>
>>
>>
From: Andrew J. Kelly on
Simply deleting the log file was never supported and will always crash your
database and put it into suspect mode. If you shut it down and delete it you
may be able to recover by creating a new one but there is simply no reason
to even try that.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Simon Whale" <simon(a)nospam.com> wrote in message
news:O4uyE9OeKHA.1652(a)TK2MSFTNGP05.phx.gbl...
> even thought not recommended it was something that you could do with SQL
> 2000.. but its been stopped in 2005 as far as i know
>
> "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message
> news:%233MRc0NeKHA.4636(a)TK2MSFTNGP04.phx.gbl...
>> No you cannot delete the primary log file. Just move it to where you want
>> it to be with either a detach & attach or backup restore with move
>> option.
>>
>> --
>>
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "John Couch" <JohnCouch(a)discussions.microsoft.com> wrote in message
>> news:0E5B82B2-6713-4C4E-95A5-3CE29C6DD7C2(a)microsoft.com...
>>> Does anyone know if this is possible? I want to create a new transaction
>>> log,
>>> and remove the existing primary one, essential making the new
>>> transaction log
>>> the primary.
>>
>
>
From: Tibor Karaszi on
Hi Andy!

> Simply deleting the log file was never supported and will always crash
> your database and put it into suspect mode. If

I think it is even worth than that. I agree fully it isn't supported. But
the scary part is that *sometimes*, SQL Server can and will create a new
ldf file for you at startup. The scary bit is, of course, than a person who
experienced this may believe that this is always safe - and most if us know
it isn't. I believe there's a bit in some early page in the mdf file to tell
whether the ldf is needed for recovery at startup. Thing is we can't see
this and not knowing is a bit like playing Russian roulette - you never know
if the bullet is next or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message
news:e1QeDGQeKHA.6096(a)TK2MSFTNGP02.phx.gbl...
> Simply deleting the log file was never supported and will always crash
> your database and put it into suspect mode. If you shut it down and delete
> it you may be able to recover by creating a new one but there is simply no
> reason to even try that.
>
> --
>
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Simon Whale" <simon(a)nospam.com> wrote in message
> news:O4uyE9OeKHA.1652(a)TK2MSFTNGP05.phx.gbl...
>> even thought not recommended it was something that you could do with SQL
>> 2000.. but its been stopped in 2005 as far as i know
>>
>> "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message
>> news:%233MRc0NeKHA.4636(a)TK2MSFTNGP04.phx.gbl...
>>> No you cannot delete the primary log file. Just move it to where you
>>> want it to be with either a detach & attach or backup restore with move
>>> option.
>>>
>>> --
>>>
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>> "John Couch" <JohnCouch(a)discussions.microsoft.com> wrote in message
>>> news:0E5B82B2-6713-4C4E-95A5-3CE29C6DD7C2(a)microsoft.com...
>>>> Does anyone know if this is possible? I want to create a new
>>>> transaction log,
>>>> and remove the existing primary one, essential making the new
>>>> transaction log
>>>> the primary.
>>>
>>
>>