Re: Detach - Attach in SQL Server 7

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Hari Prasad (hari_prasad_k_at_hotmail.com)
Date: 07/13/04 

Date: Tue, 13 Jul 2004 08:35:47 +0530

Hi,

Can you go thru this article and try shrinking the LDF file based on steps
mentioned.

http://www.support.microsoft.com/?id=256650 

--
Thanks
Hari
MCDBA
"Sergio Florez M." <sergioflorezm@hotmail.com> wrote in message
news:OmZtgzBaEHA.212@TK2MSFTNGP12.phx.gbl...
> Doesn't this only truncate the file though? It really won't make much of a
> difference. The reason I asked for a way to detach - attach is because
this
> way I can start with a brand new log file thus freeing 4GB in my hard
drive.
>
> "Hari Prasad" <hari_prasad_k@hotmail.com> escribió en el mensaje
> news:efrkEcBaEHA.4048@TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > It is not rquired to detach and attach the database to shrink the file.
> > Follow the below steps:-
> >
> > 1. backup the Transaction log
> >
> >     Backup log <dbname> to disk='d:\backup\dbname.trn'
> >
> > 2. If the log file really huge make the databse single user after
removing
> > all users connected to database
> >
> >     sp_dboption 'dbname','sinle user',true   -- for SQL 2000 you could
use
> > ALTER DATABASE <dbname> set single_user with rollback immediate
> >
> > 3. Shrink the Transaction log file
> >
> >    DBCC SHRINKFILE('Logical_ldf_name','truncateonly'
> >
> >
> > 4. After this see the log size by using
> >
> > DBCC SQLPERF(LOGSPACE)
> >
> > 5. Now turn back the database to multi user
> >
> > sp_dboption 'dbname','sinle user',false    -- for SQL 2000 you could use
> > ALTER DATABASE <dbname> set multi_user
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Sergio Florez M." <sergioflorezm@hotmail.com> wrote in message
> > news:#GmetWBaEHA.4092@TK2MSFTNGP11.phx.gbl...
> > > How do I detach and attach a database in SQL Server 7? The idea is to
> > erase
> > > the humongous log file and this is how I do it in 2000.
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: MDF and LDF size
    ... You can execute sp_helpfile in your database. ... >> You need to shrink the transaction log file to reduce the size. ... Now shrink the transaction log file using DBCC SHRINK FILE from the ...
    (microsoft.public.sqlserver.server)
  • Re: LOG Device Corrupted
    ... Check the documentation for the correct DBCC ... It seems your old database have mutiple LDF files. ... Stop sql server and rename the existing MDF to a new one and copy the ... Now execute the undocumented DBCC to create a log file ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Server 2000: Cant get the trans log to shrink
    ... to the log file? ... the t-log backup appears to have worked. ... Manager> All Tasks> Shrink Database> Shrink Files ...
    (microsoft.public.sqlserver.setup)
  • Re: Lost Log File !
    ... DBCC Rebuild_Log is not a documented method and it is not at all ... So even if it succeeds there will be data consistency issue. ... Use DTS to move data and objects to new database ... > Here is the following stuff which I do for recovery log file ... ...
    (microsoft.public.sqlserver.clients)
  • Re: Cant shrink log file
    ... Run DBCC Loginfoto see if you have in the status column at the bottom ... I was trying both and still it doesn't shrink. ... Cannot shrink log file 2 because the logical log file located ...
    (microsoft.public.sqlserver.server)