Re: Changing database size

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

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

Date: Wed, 25 Feb 2004 23:39:44 +0100

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: Unable to shrink Transaction Log
    ... Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE ...
    (microsoft.public.sqlserver.server)
  • Re: Changing database size
    ... It seems like you are saying that you can shrink the ... size of a database file using ALTER DATABASE without the usage of DBCC ... transaction log files renders corrupt databases. ... How to Shrink the SQL Server 7.0 Transaction Log ...
    (microsoft.public.sqlserver.server)
  • Re: Changing database size
    ... You need to use DBCC SHRINKFILE, ... How to Shrink the SQL Server 7.0 Transaction Log ... Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE ...
    (microsoft.public.sqlserver.server)
  • Re: SharePoint Transaction Logs - switched to simple, now what?
    ... You don't want to delete your transaction log. ... shrink the file with DBCC SHRINKFILE. ... Shrinking the Transaction Log in SQL Server 2000 with ... So now that I have moved from full to simple recovery mode. ...
    (microsoft.public.sqlserver.connect)
  • Re: Transaction log and tempdb
    ... - Is it possible to reduce the size of the transaction log fil during ... an execution? ... It sounds as if you are the database is in full recovery. ... If I execute that script sql server seems ...
    (comp.databases.ms-sqlserver)