Re: Changing database size

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/25/04 

Date: Wed, 25 Feb 2004 23:56:53 +0100

Nico,

This makes me curious. It seems like you are saying that you can shrink the
size of a database file using ALTER DATABASE without the usage of DBCC
SHRINKFILE or DBCC SHRINKDATABASE. This is news to me, and I would be very
interested to see how you accomplish that. I do in no way doubt what you are
saying, this is out of pure curiosity, as I though it wasn't possible. :-)

(Just for the record, I do not consider deleting the transaction log file a
usable method, as we see posts here on a daily basis where deletion of
transaction log files renders corrupt databases. Extreme caution has to be
taken if you even want to consider taking that path. :-) ) 

-- 
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico De Greef" <ndg@denco.be> wrote in message
news:eNasIF$%23DHA.2636@TK2MSFTNGP09.phx.gbl...
> Yes you can, i've written this script a few years ago and it is a specific
> order of SQL statements.
> Not an 'out of a book' solution but it is possible.
>
> (Not that I don't want to give it to you, but i have to look it up)
>
> -- 
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote
in
> message news:OvF6EB$%23DHA.2180@TK2MSFTNGP09.phx.gbl...
> > You can't reduce the size of the log using ALTER DATABASE (which by your
> > messages is what you are trying to do). You need to use DBCC SHRINKFILE,
> > which is restricted in the sense that it can only shrink from the end of
> the
> > file towards the beginning of the file. And if there are log records at
> the
> > end of the file, it cannot be shrunk.
> >
> > Check out below KB articles:
> >
> > INF: How to Shrink the SQL Server 7.0 Transaction Log
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
> >
> > INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
SHRINKFILE
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> >
> > Log File Grows too big
> > http://www.support.microsoft.com/?id=317375
> >
> > Log file filling up
> > http://www.support.microsoft.com/?id=110139
> >
> > Considerations for Autogrow and AutoShrink
> > http://www.support.microsoft.com/?id=315512
> >
> > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> >
> >
> > -- 
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "mcamci@ozoptics.com" <anonymous@discussions.microsoft.com> wrote in
> message
> > news:135201c3fbb0$ee90d1b0$a501280a@phx.gbl...
> > > Hello,
> > > My SQL server 7 database, data size is 150M, but only 82M
> > > occupied with data.
> > >
> > > Transaction log size is 1.8Gig, and only 20M is used the
> > > actual data.
> > >
> > > This is I think, due to not backing up transaction data
> > > for more then a year.
> > >
> > > Now we are trying to reduce the database transaction log
> > > size, but SQL server doesn't allow us to reduce it.
> > > It says, "your new size must be larger then current size."
> > > We tried to use alter database commands, says the same
> > > message. Is there another way to reduce the allocated
> > > space to transaction logs?
> > >
> > > MC
> > >
> >
> >
>
>

Relevant Pages

  • Re: Changing database size
    ... You can't reduce the size of the log using ALTER DATABASE (which by your ... Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE ...
    (microsoft.public.sqlserver.server)
  • Re: AutoShrink Database Transaction Log
    ... Shrink implies actually reducing the amount of space used by the actual OS ... There is not a check box setting to autoshrink the log and not the database. ... > I am using SQL Server 2000 Enterprise Edition with SP3A. ... > Is there away that can I autoshrink the database> transaction log and not the database? ...
    (microsoft.public.sqlserver.server)
  • Re: how to reduce log file size?
    ... How to Shrink the SQL Server 7.0 Transaction Log ... Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE ... Log File Grows too big ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction log keeps growing
    ... Introduced in SQL Server 7.0 was the ability automatically grow and to ... shrink the physical size of database data and transaction log files. ...
    (comp.databases.ms-sqlserver)
  • Transaction log growth with MSDE and MSSQL
    ... We have experienced that the LDF file grow out of proportions. ... Knowledge Base we have found some descriptions on how to shrink the file. ... Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE ...
    (microsoft.public.sqlserver.msde)