Re: Changing database size
mcamci_at_ozoptics.com
Date: 02/26/04
- Next message: Tamara: "tempdb unrestricted growth"
- Previous message: Guru: "Domain Collapse. Sql permission question."
- In reply to: Nico De Greef: "Re: Changing database size"
- Next in thread: Nico De Greef: "Re: Changing database size"
- Reply: Nico De Greef: "Re: Changing database size"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 26 Feb 2004 13:09:11 -0800
Gentlmen,
I don't think we are talking about the same think here.
I already tried DBCC
SHRINKFILE or DBCC SHRINKDATABASE procedures as per given
MS artciles. These procedures does not change ALLOCATED
database file size, nor database lof file size.
I am trying to change allocated database file size.
Once you allocated a database size after the database
creation, you can not reduce the allocated size as per MS.
These procedures doesn't do that. What it did is this.
My log file size in SQL Enterprise manager shows 1.8G
It showed that 1.6G was used by data. DBCC
SHRINKFILE or DBCC SHRINKDATABASE procedures
brought the used space down to 80M. But the actual
allocated log file size did not change.
The only easy option I can think of is to create a new
database and DTS all the data to new database.
MC
>-----Original Message-----
>No, I didn't say it was possible with ALTER DATABASE, in
my opinion it
>isn't.
>But it is possible to shrink the file, even if the active
part is at the end
>of the file.
>I'll try to dig up the exact script soon.
>
>--
>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:egDGqK$%23DHA.1796@TK2MSFTNGP12.phx.gbl...
>> 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
>> > > >
>> > >
>> > >
>> >
>> >
>>
>>
>
>
>.
>
- Next message: Tamara: "tempdb unrestricted growth"
- Previous message: Guru: "Domain Collapse. Sql permission question."
- In reply to: Nico De Greef: "Re: Changing database size"
- Next in thread: Nico De Greef: "Re: Changing database size"
- Reply: Nico De Greef: "Re: Changing database size"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
