[dba-SQLServer] Shrinking Log File

Thanks Stephen.  That solved my immediate problem.

Now my problem is that the files backed up from the end of last year 
are restoring fine with transaction logs in the size 
expected.  However, when I try to restore a back up file from this 
year (I have 2 from 2 different databases) the transaction log is way 
out of proportion to the actual size used (eg 13779MB in size with 
113MB space used).  The original files on the server are closer to 
what I would expect them to be but for some reason the backups are 
not restoring well.

Any thoughts on what went wrong.

David

At 17/01/2006, Stephen Hait wrote:
>If it doesn't matter if the log file is usable, you could try:
>1 detach database.
>2 rename or delete log file.
>3 execute code below to have sqlserver generate a new transaction log of
>about 500k.
>EXEC sp_attach_single_file_db @dbname = 'yourDBname',
>@physname = 'd:\full\path\to\your\yourDBname_data.mdf'
>
>Regards,
>Stephen
>
>On 1/15/06, David Emerson <newsgrps at dalyn.co.nz> wrote:
> >
> > OK, Now I have tried the following in Query Analyser:
> >
> > DBCC SHRINKFILE (2, 150)
> >
> > This came up with the following error message in the messages pane:
> >
> > Cannot shrink log file 2 (egasSQLLtdbe_log) because all logical log
> > files are in use.
> >
> > How do I free up logical log files?
> >
> > David
> >
> > At 16/01/2006, you wrote:
> > >I have restored an SQL2K bak file from a different server to my stand
> > >alone development computer.
> > >
> > >In the Shrink File box for the database in Enterprise Manager the log
> > >file is showing 13779MB in size with 113MB space used.
> > >
> > >Under shrink action I have tried Shrink File To 150MB but an error
> > >8985 appears saying it can't find file SQLbe_Log in sysfiles.  The
> > >strange thing is that the name refers to the original file that this
> > >data base was created from over 3 years ago.  The database that I am
> > >trying to shrink is called SQLLtdbe_Log.  This name doesn't appear in
> > >the database file list.
> > >
> > >If I just delete the log file, will a new one be created?  As this is
> > >just for testing purposes I don't need to be able to recover any
> > >previous transactions.
> > >
> > >Regards
> > >
> > >David Emerson
> > >Dalyn Software Ltd
> > >999 Moonshine Rd, RD 1
> > >Judgeford, Porirua  6006
> > >Phone    (04) 235-6782
> > >Fax      (04) 235-6783
> > >Mob      (027) 280-9348