Changing database size

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?
MCHave you checked out this article?
http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"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
>|||Hi,
Check the database option "Truncate Log on Checkpoint" for this database,
If option is not selected then perform a transaction log
backup using "Backup Log" command (refer BOL) and then try to shrink the
Transaction log using DBCC SHRINKFILE
command (Refer BOL).
Note: Incase ur database is not production or data is not critical please
enable the option "Truncate Log on Checkpoint" , so as Transaction log file
will
be cleared after comitting the trasaction and the file will not grow.
Thanks
Hari
MCDBA
"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
>|||You can not shrink 'fragmented' log files.
You can have a lot of free space in the beginning of a log file and active
information only at the end.
Shrinking will not help you, even if there's a lot of free space.
I think running the statement BACKUP log with no_log command could help.
Currently I don't have my backup scripts nearby.
A full backup or differential backup is recommended after running this
statement.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"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
>|||Well,
To get rid of un-used space from the transaction log,you
can not use shrink or backup and use truncate option.
This doesn't alter the over all database log file size.
I tried backup and restore to a new database, but restore
will restore orgional size of the file. So that didn't help
If you folks have a script that you know works, I
appreciate if you can send it to me
Thnks
>--Original Message--
>You can not shrink 'fragmented' log files.
>You can have a lot of free space in the beginning of a
log file and active
>information only at the end.
>Shrinking will not help you, even if there's a lot of
free space.
>I think running the statement BACKUP log with no_log
command could help.
>Currently I don't have my backup scripts nearby.
>A full backup or differential backup is recommended after
running this
>statement.
>--
>Nico De Greef
>Belgium
>Freelance Software Architect
>MCP, MCSD, .NET certified
>
>"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
>
>.
>|||These articles do not help to re-size trasaction log file
MC
>--Original Message--
>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
>.
>|||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
>|||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
> >
>|||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
> > >
> >
> >
>|||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
> > > >
> > >
> > >
> >
> >
>|||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
>> > > >
>> > >
>> > >
>> >
>> >
>>
>
>.
>|||These steps should give the result you're looking for:
BACKUP LOG WITH NO_LOG
DBCC SHRINKDATABASE
(full BACKUP)
DBCC SHRINKDATABASE
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in message
news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
> 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
> >> > > >
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >|||Nico,
Were these the steps you referred to earlier? I.e., the steps that could
shrink the log file even there are log records at the end of the log file?
--
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:%23RYptXQ$DHA.3188@.TK2MSFTNGP09.phx.gbl...
> These steps should give the result you're looking for:
> BACKUP LOG WITH NO_LOG
> DBCC SHRINKDATABASE
> (full BACKUP)
> DBCC SHRINKDATABASE
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
> "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
> > 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
> > >> > > >
> > >> > >
> > >> > >
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> > >.
> > >
>|||Yes
--
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:%236E4Z1R$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> Nico,
> Were these the steps you referred to earlier? I.e., the steps that could
> shrink the log file even there are log records at the end of the log file?
> --
> 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:%23RYptXQ$DHA.3188@.TK2MSFTNGP09.phx.gbl...
> > These steps should give the result you're looking for:
> >
> > BACKUP LOG WITH NO_LOG
> > DBCC SHRINKDATABASE
> > (full BACKUP)
> > DBCC SHRINKDATABASE
> >
> > --
> > Nico De Greef
> > Belgium
> > Freelance Software Architect
> > MCP, MCSD, .NET certified
> >
> > "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
> message
> > news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
> > > 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
> > > >> > > >
> > > >> > >
> > > >> > >
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||OK, thanks. :-)
In SQK2K, DBCC SHRINKFILE (and I presume SHRINKDATABASE as well) will
generate "dummy" log records so the beginning of the log will move to the
beginning of the log file. A later backup of the log (a normal backup should
do just as good) will remove the tail of the log. The log is not entirely at
the beginning of the file, meaning that DBCC SHRINKFILE (-DATABASE) should
be able to cot then end of the log file.
--
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:uwXKG2S$DHA.712@.tk2msftngp13.phx.gbl...
> Yes
> --
> 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:%236E4Z1R$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> > Nico,
> >
> > Were these the steps you referred to earlier? I.e., the steps that could
> > shrink the log file even there are log records at the end of the log
file?
> >
> > --
> > 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:%23RYptXQ$DHA.3188@.TK2MSFTNGP09.phx.gbl...
> > > These steps should give the result you're looking for:
> > >
> > > BACKUP LOG WITH NO_LOG
> > > DBCC SHRINKDATABASE
> > > (full BACKUP)
> > > DBCC SHRINKDATABASE
> > >
> > > --
> > > Nico De Greef
> > > Belgium
> > > Freelance Software Architect
> > > MCP, MCSD, .NET certified
> > >
> > > "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > > news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
> > > > 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
> > > > >> > > >
> > > > >> > >
> > > > >> > >
> > > > >> >
> > > > >> >
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>|||I know these steps.
But these steps do not alter the database log file size.
Didn't do it.
MC
>--Original Message--
>These steps should give the result you're looking for:
>BACKUP LOG WITH NO_LOG
>DBCC SHRINKDATABASE
>(full BACKUP)
>DBCC SHRINKDATABASE
>--
>Nico De Greef
>Belgium
>Freelance Software Architect
>MCP, MCSD, .NET certified
>"mcamci@.ozoptics.com"
<anonymous@.discussions.microsoft.com> wrote in message
>news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
>> 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
>> >> > > >
>> >> > >
>> >> > >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>