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/defaul...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...
82M
size."
>
>.
>|||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/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...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=...ublic.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/defaul...kb;en-us;256650
> INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
> http://support.microsoft.com/defaul...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=...ublic.sqlserver
>
> "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
message
> news:135201c3fbb0$ee90d1b0$a501280a@.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=...ublic.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...
> the
> the
SHRINKFILE
>
http://groups.google.com/groups?oi=...ublic.sqlserver
> 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=...ublic.sqlserver
>
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
specific
> in
your
SHRINKFILE,
of
at
> SHRINKFILE
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>|||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...
that you can shrink
>the
the usage of DBCC
and I would be very
way doubt what you
>are
wasn't possible. :-)
transaction log file
>a
where deletion of
Extreme caution has to be
) )
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
and it is a
>specific
to look it up)
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
23DHA.2180@.TK2MSFTNGP09.phx.gbl...
DATABASE (which by
>your
use DBCC
>SHRINKFILE,
shrink from the end
>of
there are log records
>at
Log
scid=kb;en-us;256650
2000 with DBCC
scid=kb;en-us;272318
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in
only 82M
used the
transaction data
transaction log
it.
current size."
same
allocated
>
>.
>