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 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.
>
|||Thanks for you reply Ben, and you Chris. I don't think I can take a log file
backup because I can oly do it on the local server and I haven't the space,
i.e. 2GB space and 4GB+ log file.
I have got a netwrok drive to a different server with space, but the SQL
server backp program doesn't see netwrok drives.
Any other ideas or a way I can backup to the network drive?
Cheers, Mike.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> 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:
|||If you do not have enough disk space then use the TRUNCATE_ONLY option like
in
backup log my_db with truncate_only
We are supposing you are not running in the SIMPLE recovery model. Is this
correct?
If that works, you must do a database backup at the end because by using
TRUNCATE_ONLY you are breaking the log chain.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
[vbcol=seagreen]
> Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> backup because I can oly do it on the local server and I haven't the space,
> i.e. 2GB space and 4GB+ log file.
> I have got a netwrok drive to a different server with space, but the SQL
> server backp program doesn't see netwrok drives.
> Any other ideas or a way I can backup to the network drive?
>
> Cheers, Mike.
> "Ben Nevarez" wrote:
|||Excuse my ignorance - It knows no bounds!
How do I know if I am using the SIMPLE recovery model?
I am quite happy to use: backup log my_db with truncate_only
The database isn't used very much, and I only backed it up about 1 1/2 hours
ago, so as long as I can recover that should something go wrong that would be
fine....but perhaps that isn't possible with the backup I have?!!?
Cheers, Mike.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> If you do not have enough disk space then use the TRUNCATE_ONLY option like
> in
> backup log my_db with truncate_only
> We are supposing you are not running in the SIMPLE recovery model. Is this
> correct?
> If that works, you must do a database backup at the end because by using
> TRUNCATE_ONLY you are breaking the log chain.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
|||Looks like you are not using the SIMPLE recovery model but try this
select databasepropertyex('my_db', 'recovery')
And also try the backup log with truncate_only and then shrink the log files.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
[vbcol=seagreen]
> Excuse my ignorance - It knows no bounds!
> How do I know if I am using the SIMPLE recovery model?
> I am quite happy to use: backup log my_db with truncate_only
> The database isn't used very much, and I only backed it up about 1 1/2 hours
> ago, so as long as I can recover that should something go wrong that would be
> fine....but perhaps that isn't possible with the backup I have?!!?
>
> Cheers, Mike.
> "Ben Nevarez" wrote:
|||The answer to select databasepropertyex('my_db', 'recovery')
is FULL.
I tried the '...try the backup log with truncate_only and then shrink the
log files' as you suggested and it WORKED!
I am now back to 7GB of space, plenty of room for growth for all the DB's,
and have restricted the size of all log files on the server.
Thanks for all your help, you got me out of a scrape!
Have a good weekend, cheers, Mike.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Looks like you are not using the SIMPLE recovery model but try this
> select databasepropertyex('my_db', 'recovery')
> And also try the backup log with truncate_only and then shrink the log files.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
|||Be careful about restricting the size of the log files ... SQL Server may
need this disk space.
Instead I would recommend scheduling transaction log backups periodically,
minimum daily. This will allow SQL Server to reuse the disk space used by the
transaction log files.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
[vbcol=seagreen]
> The answer to select databasepropertyex('my_db', 'recovery')
> is FULL.
> I tried the '...try the backup log with truncate_only and then shrink the
> log files' as you suggested and it WORKED!
> I am now back to 7GB of space, plenty of room for growth for all the DB's,
> and have restricted the size of all log files on the server.
> Thanks for all your help, you got me out of a scrape!
> Have a good weekend, cheers, Mike.
>
> "Ben Nevarez" wrote:
|||You can also create an alert that will run the log backup if you fill it
over a certain percent. Providing that you do not have any very large
transactions this should keep you safe.
Chris
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:7BBF5A0F-45E0-4493-9A95-F50FF7D05D20@.microsoft.com...[vbcol=seagreen]
> Be careful about restricting the size of the log files ... SQL Server may
> need this disk space.
> Instead I would recommend scheduling transaction log backups periodically,
> minimum daily. This will allow SQL Server to reuse the disk space used by
> the
> transaction log files.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote: