Delete Primary transaction Log
in [Programming SQL]
|
From: John Couch on 9 Dec 2009 13:51 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 9 Dec 2009 13:56 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 9 Dec 2009 13:57 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 10 Dec 2009 02:42
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. >>> >> >> |