Live server running out of space - Can't reduce Transaction Log!

Hi,
I have just come across a bit of a problem of my Windows 200 server running
SQL Server 2000....I am running out of space!
I have aout 2GB left. I have know this for some time, and for technical
reasons just found out I can't add a new disk...but that's another story.
I have looked at what I can reduce to increase the space, and it seems one
of the smaller database I have on the server, i.e. 16MB, has a log file of
4.8GB!
I am not sure why this has occured, possibly due to not setting a checkpoint
during backup or something!?!?!
...anyway I am desperately tying to reduce the size of this file, and have
tried running the following:
USE Comms
GO
DBCC SHRINKFILE (Comms_log, 10)
and it just comes back and tells me:
- CurrentSize 608616
- Minimum Size 63
- UsedPages 608616
- EstimatedPages 56
with no reduction in LogFile size.
I also tried backing up just the databse and then restoring it under a
different name (so I can completely delete the old one and it's log!), but
when I try to restore it the system just comes back and tells me it hasn't
got enough space, even though it has just over 2GB (presumably it is trying,
or thinks it is trying to restore the log file as well, but goodness knows
where it's getting it from?!).
Any ideas?
Cheers, desperate Mike.Mike,
What is the database recovery model? Is the log space unrestricted? Do you
take log backups?
If this is a Full recovery model DB then take a log backup. If the log in
unrestricted filegrowth give it a maximum size, just bigger than it is now.
Then you need to follow this: - http://support.microsoft.com/kb/873235/en-us
Chris
"Mike Owen" <whatnospam@.nospam.nospam> wrote in message
news:8CFFFB57-6B03-4DA3-95E7-BFD6B27C95A3@.microsoft.com...
> Hi,
> I have just come across a bit of a problem of my Windows 200 server
> running
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a
> checkpoint
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is
> trying,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>|||Hi Mike,
Just do a transaction log backup and then try the shrink operation again.
If that does not work check if you have transactions or jobs running for a
long time.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:

> Hi,
> I have just come across a bit of a problem of my Windows 200 server runnin
g
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a checkpoi
nt
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is tryin
g,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>