Re: How do I shrink my logfile?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Sydney Lotterby (sydney_at_infosearch.com)
Date: 07/23/04 

Date: Thu, 22 Jul 2004 19:50:23 -0500

Kalen,
You're right - I apologize for the duh. Thanks for all the info.

btw I am using SQL2K and I did check out all the links suggested in this
thread.

fyi http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
has this example using pubs_log not a dummy dbname, hence my original
question.

    "Run this code: DBCC SHRINKFILE(pubs_log, 2)"

"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:ehm5DyEcEHA.3804@TK2MSFTNGP10.phx.gbl...
> I assumed that because you were posting a question on the newsgroup that
you
> did not know EVERYTHING about SQL Server. I had to guess how much you knew
> and how much you didn't know.
> The Duh! is really uncalled for. It seems to assume that I should have
known
> exactly how much you already knew, and there was no way I could know that.
> If you peruse the newsgroups for a while, instead of just posting your own
> question and waiting for an answer, you will see there is an extremely
wide
> range of experience in the people who ask questions here.
>
> Without more details, I would also assume that your db was restored from a
> backup of one with a different name. The sysfiles table is populated when
> the db is created, and that can happen during a restore if you are not
> restoring on top of an existing db.
>
> You can use ALTER DATABASE to change the logical filenames, but only if
you
> are running SQL Server 2000. You never once said what version you are
> running, and there is no way I could know that either.
> You can only change the physical file names of tempdb. If you need to
change
> the physical file names, you can detach the db, move the files, reattach
> with the MOVE option.
>
> Please see ALTER DATABASE, sp_detach_db and sp_attach_db in the Books
Online
> for full details.
>
> --
> HTH
> ----------------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "Sydney Lotterby" <sydney@infosearch.com> wrote in message
> news:#mlWxpDcEHA.808@tk2msftngp13.phx.gbl...
> > Duh! I know that! I was making a generic statement.
> >
> > My comment still stands ... mydb_log (alias cpmain_log) is not in
SysFiles
> > and I would like to know why.
> > The entry in cpmain.sysfiles is cphooks_log and cphooks_data
> > I suspect this db was restored from one named cphooks.
> > So ...
> > 1) When is the entry made to sysfiles?
> > 2) Can I change the entries in sysfiles manually with impunity?
> >
> > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
> > news:uzmxUeBcEHA.2468@TK2MSFTNGP09.phx.gbl...
> > > Usually when you see MY in an object name, it means you should replace
> it
> > > with whatever YOUR name really is.
> > >
> > > What is the name of the log file for YOUR db? That is the name you
> should
> > > use instead of mydb_log. Sysfiles, or sp_helpfile, should show you the
> > name
> > > used for your database.
> > >
> > > --
> > > HTH
> > > ----------------
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Sydney Lotterby" <sydney@infosearch.com> wrote in message
> > > news:#qNPqBBcEHA.3460@TK2MSFTNGP10.phx.gbl...
> > > > I followed the instructions in the link but when I run DBCC
> > > > SHRINKFILE(mydb_log,2) it says "could not locate mydb_log in
sysfiles.
> > I
> > > > checked sysfiles and, true, it is not there.
> > > > What can I do?
> > > >
> > > > "Narayana Vyas Kondreddi" <answer_me@hotmail.com> wrote in message
> > > > news:%23pOUoKAcEHA.3988@tk2msftngp13.phx.gbl...
> > > > > See DBCC SHRINKFILE in SQL Server Books Online. Also see:
> > > > >
> > > > > INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
> > > SHRINKFILE
> > > > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> > > > > --
> > > > > HTH,
> > > > > Vyas, MVP (SQL Server)
> > > > > http://vyaskn.tripod.com/
> > > > > Is .NET important for a database professional?
> > > > > http://vyaskn.tripod.com/poll.htm
> > > > >
> > > > >
> > > > > "Joseph Geretz" <jgeretz@nospam.com> wrote in message
> > > > > news:ewNWyDAcEHA.1656@TK2MSFTNGP09.phx.gbl...
> > > > > I set my recovery model to simple. I set my database option to
> > > AutoShrink.
> > > > I
> > > > > backed up my database. But the logfile is still 2.3 GB(!). How do
I
> > > shrink
> > > > > this?
> > > > >
> > > > > Thanks!
> > > > >
> > > > > - Joe Geretz -
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: How do I shrink my logfile?
    ... the database. ... >> did not know EVERYTHING about SQL Server. ... and that can happen during a restore if you are not ... >> You can only change the physical file names of tempdb. ...
    (microsoft.public.sqlserver.server)
  • Re: How do I shrink my logfile?
    ... did not know EVERYTHING about SQL Server. ... and that can happen during a restore if you are not ... You can only change the physical file names of tempdb. ... mydb_log is not in SysFiles ...
    (microsoft.public.sqlserver.server)
  • Re: Statistics IO logical reads sometimes 15 million and other times down to 90 thousand?
    ... different physical file names, I run my query, the first time it takes ... I am sure now if I restore this same db on another SQL Server ... Then that SQL Server Instance ...
    (comp.databases.ms-sqlserver)
  • Re: Which hardware upgrades are more important
    ... > Of course most of those bulletins described exploits that could ... > happen only if you had no password on the default sa account (well ... > duh!). ... Well, at one time, there were updates to SQL Server that would clear ...
    (comp.databases.ms-access)
  • Re: Checking state of Sql Server without permission
    ... so Windows Authentication let me know how's my Remote SqlServer ... Duh again! ... > I've heard also that's possible to check state of my sql server using ... >> SQLDMO or simply try to connect and handle possible errors? ...
    (microsoft.public.sqlserver.programming)