Re: Changing database size

From: Nico De Greef (ndg_at_denco.be)
Date: 02/25/04 

Date: Wed, 25 Feb 2004 23:47:01 +0100

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

  • Troubles with log truncation between SQL versions
    ... I have a problem with log truncation. ... make a backup of the transaction log, ... I just shrink the t-log (DBCC SHRINKFILE TRUNCATEONLY) if it ... In SQL 2000, this works perfectly. ...
    (microsoft.public.sqlserver.server)
  • Re: Backing up transaction logs gives poor performance.
    ... I cannot get the SQL Maintenance plan to save the transaction log files any where over the network. ... Tibor Karaszi, SQL Server MVP ... While there (the maintenance plan dialog box), I noticed that I have the verify log backup option ticked. ... Transaction log backup files from this database are typically about 20MB while the application is busy. ...
    (microsoft.public.sqlserver.setup)
  • Re: Cluster or Mirror
    ... it takes for the transaction log to be applied from the failed node to the ... Looking for a SQL Server replication book? ... The application only knows 1 SQL server connection. ...
    (microsoft.public.sqlserver.clustering)
  • 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: Edit botton does not work
    ... If you are using SQL Server 2005, you receive an error message that is ... The transaction log for database 'databasename' is full. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.fulltext)