Reduce log file Size (LDF)

Hi,
MY ldf file is 100GB while the database is only 200MB. I am running SQL 2000
SP4 and the databse recovery model is "FULL" and this cannot change due to
the DR stipulations.
Firstly I need to greatly reduce the size of the transaction log. I cannot
back iy up as its too big (100GB) so how do I truncate the log so that I can
shrink it? I prefer to use Enterprise Manager.
Secondly
I need to ensure FULL mode stays while keeping the transaction log to a
normal size. Should I be backing up the transaction log on a weekly basis to
ensure that it doesn't grow out of control?
Any help would be greatly appreciated. I apologize for only being familiar
with Enterprise Manager.
Your help is always appreciated.
Thanks
ElvisRecommended way to reduce the log file size is to backup the log and then
run DBCC SHRINKFILE.
If space is the constraint try the below
1. Backup the Transaction log with NO_LOG / TRUNCATE_ONLY Option
The above option when used with backup log removes the inactive part of the
log without making a backup copy of it and truncates the log only if
inactive portions is at the end of the file. Backup device is not required
since the log records are not saved.
To continue with TLOG backups . IMMEDIATELY PERFORM A FULL DATABASE BACKUP
after this step.
2. Run DBCC SHRINKFILE.
You can monitor the size of TLOG over period and schedule a TLOG backup
weekly and perform DBCC SHRINKFILE
Hope this helps
Vishal Gandhi|||> Should I be backing up the transaction log on a weekly basis to
> ensure that it doesn't grow out of control?
The purpose of transaction log backups is to minimize data loss in the event
you need to restore from database backup. A side effect of regularly log
backups is that the log file size is kept reasonably small. The log file
should be sized to accommodate activity between log backups and you should
not need to regularly shrink the log.
The frequency of log backups depends on the amount of data loss that is
acceptable in your environment. If a failure results in the loss of the
database log, you can only recover from your database and log backups.
Typically, database backups are performed daily with transaction log backups
done hourly. You can use a maintenance plan to facilitate this adjust the
schedule according to your needs.
Hope this helps.
Dan Guzman
SQL Server MVP
"Elvis" <Elvis@.discussions.microsoft.com> wrote in message
news:FD281620-B520-459E-A0A6-33FE419DBEF2@.microsoft.com...
> Hi,
> MY ldf file is 100GB while the database is only 200MB. I am running SQL
> 2000
> SP4 and the databse recovery model is "FULL" and this cannot change due to
> the DR stipulations.
> Firstly I need to greatly reduce the size of the transaction log. I cannot
> back iy up as its too big (100GB) so how do I truncate the log so that I
> can
> shrink it? I prefer to use Enterprise Manager.
> Secondly
> I need to ensure FULL mode stays while keeping the transaction log to a
> normal size. Should I be backing up the transaction log on a weekly basis
> to
> ensure that it doesn't grow out of control?
> Any help would be greatly appreciated. I apologize for only being familiar
> with Enterprise Manager.
> Your help is always appreciated.
> Thanks
> Elvis|||Hi,
Thank you for such a quick and accurate response. Do you know how to do this
with Enterprise Manager...or could you post me the exact commands that I
will copy and paste into Query Analyzer.
Your help is greatly appreciated.
Thanking You
Elvis
"Vishal Gandhi" wrote:

> Recommended way to reduce the log file size is to backup the log and then
> run DBCC SHRINKFILE.
> If space is the constraint try the below
> 1. Backup the Transaction log with NO_LOG / TRUNCATE_ONLY Option
> The above option when used with backup log removes the inactive part of th
e
> log without making a backup copy of it and truncates the log only if
> inactive portions is at the end of the file. Backup device is not required
> since the log records are not saved.
> To continue with TLOG backups . IMMEDIATELY PERFORM A FULL DATABASE BACKUP
> after this step.
> 2. Run DBCC SHRINKFILE.
> You can monitor the size of TLOG over period and schedule a TLOG backup
> weekly and perform DBCC SHRINKFILE
> Hope this helps
> Vishal Gandhi
>
>
>|||Hi,
Thanks Dan for answering my questions. I will defs schedule a Maintenance
plan.
My last bit of help...Please could some one tell me how to use Enterprise
Manager to
1) Backup the Transaction log with NO_LOG / TRUNCATE_ONLY Option
2)Run DBCC SHRINKFILE.
This is the part I am struggling with the most.
Thanks again
Sipho
"Dan Guzman" wrote:

> The purpose of transaction log backups is to minimize data loss in the eve
nt
> you need to restore from database backup. A side effect of regularly log
> backups is that the log file size is kept reasonably small. The log file
> should be sized to accommodate activity between log backups and you should
> not need to regularly shrink the log.
> The frequency of log backups depends on the amount of data loss that is
> acceptable in your environment. If a failure results in the loss of the
> database log, you can only recover from your database and log backups.
> Typically, database backups are performed daily with transaction log backu
ps
> done hourly. You can use a maintenance plan to facilitate this adjust the
> schedule according to your needs.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Elvis" <Elvis@.discussions.microsoft.com> wrote in message
> news:FD281620-B520-459E-A0A6-33FE419DBEF2@.microsoft.com...
>
>|||> My last bit of help...Please could some one tell me how to use Enterprise
> Manager to
> 1) Backup the Transaction log with NO_LOG / TRUNCATE_ONLY Option
I don't believe EM has this functionality but I could be wrong since I don't
use that tool for these admin tasks. The SQL command is trivial, though:
BACKUP LOG MyDatabase
WITH TRUNCATE_ONLY

> 2)Run DBCC SHRINKFILE.
An example:
--shrink log to 1GB
DBCC SHRINKFILE ('MyDatabase_Log', 1000)
One method to get the logical file name needed for DBCC SHRINKFILE is to
right-click on the database in EM and then select Properties-->Transaction
log. Another way is to execute sp_helpdb 'MyDatabase'.
Hope this helps.
Dan Guzman
SQL Server MVP
"Elvis" <Elvis@.discussions.microsoft.com> wrote in message
news:3651A093-C050-4859-AD8D-76909CA4778E@.microsoft.com...[vbcol=seagreen]
> Hi,
> Thanks Dan for answering my questions. I will defs schedule a Maintenance
> plan.
> My last bit of help...Please could some one tell me how to use Enterprise
> Manager to
> 1) Backup the Transaction log with NO_LOG / TRUNCATE_ONLY Option
> 2)Run DBCC SHRINKFILE.
> This is the part I am struggling with the most.
> Thanks again
> Sipho
> "Dan Guzman" wrote:
>|||Hi Dan,
Thanks for your quick respone and continued help. It is helping me greatly.
I will use the Shrinkdatabase command as you have outlined. thats perfect..
Can you give me a similar type of example of what command I should be
running to backup the database with NO_LOG / TRUNCATE_ONLY Option?
Thanks again,
Elvis
"Dan Guzman" wrote:

> I don't believe EM has this functionality but I could be wrong since I don
't
> use that tool for these admin tasks. The SQL command is trivial, though:
> BACKUP LOG MyDatabase
> WITH TRUNCATE_ONLY
>
> An example:
> --shrink log to 1GB
> DBCC SHRINKFILE ('MyDatabase_Log', 1000)
> One method to get the logical file name needed for DBCC SHRINKFILE is to
> right-click on the database in EM and then select Properties-->Transaction
> log. Another way is to execute sp_helpdb 'MyDatabase'.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Elvis" <Elvis@.discussions.microsoft.com> wrote in message
> news:3651A093-C050-4859-AD8D-76909CA4778E@.microsoft.com...
>
>|||> Can you give me a similar type of example of what command I should be
> running to backup the database with NO_LOG / TRUNCATE_ONLY Option?
I'm not sure I understand your question. I included a BACKUP
DATABASE...WITH TRUNCATE_ONLY example in my last post. That's the special
version of the backup command you need to run once in order to remove
committed transactions from the log without actually creating backup file.
You shouldn't need to run that again going forward because your scheduled
log backups will remove committed transactions from the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Elvis" <Elvis@.discussions.microsoft.com> wrote in message
news:D679E92C-54AB-49EF-B8A0-02D7A598165B@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> Thanks for your quick respone and continued help. It is helping me
> greatly.
> I will use the Shrinkdatabase command as you have outlined. thats
> perfect..
> Can you give me a similar type of example of what command I should be
> running to backup the database with NO_LOG / TRUNCATE_ONLY Option?
> Thanks again,
> Elvis
>
> "Dan Guzman" wrote:
>|||Elvis wrote:[vbcol=seagreen]
> Hi Dan,
> Thanks for your quick respone and continued help. It is helping me greatly
.
> I will use the Shrinkdatabase command as you have outlined. thats perfect.
.
> Can you give me a similar type of example of what command I should be
> running to backup the database with NO_LOG / TRUNCATE_ONLY Option?
> Thanks again,
> Elvis
>
> "Dan Guzman" wrote:
>
Now when you have managed to get your logfile size reduced, I'd
recommend that you read up on BACKUP and RESTORE in Books On Line. That
will help you set up a backup schedule that works and saves you in case
that something goes wrong.
You mentioned that you couldn't change recovery model due to DR
stipulations, but since you never run a log backup what's then the
purpose of running in FULL recovery?...;-).
It's fine to have a DR plan but the plan it self isn't enough - you also
need to implement it..:-). Once you've set up your backup schedule the
next step is to test the backup and make sure that it works - and you
know how to restore the backups.
Regards
Steen Schlüter Persson
DBA|||Hi Steen,
You make some excellent suggestions. Thank You.
My plan is to backup both the database and the logfiles once a day. If my
database corrupts I can restore from the previous nights backup and use the
existing logfile to bring me to the last transaction? Is this corroct...or
should I be doing a database backup once a day and the transaction logs on a
n
hourly basis?
Thanks
Elvis
"Steen Persson (DK)" wrote:

> Elvis wrote:
> Now when you have managed to get your logfile size reduced, I'd
> recommend that you read up on BACKUP and RESTORE in Books On Line. That
> will help you set up a backup schedule that works and saves you in case
> that something goes wrong.
> You mentioned that you couldn't change recovery model due to DR
> stipulations, but since you never run a log backup what's then the
> purpose of running in FULL recovery?...;-).
> It's fine to have a DR plan but the plan it self isn't enough - you also
> need to implement it..:-). Once you've set up your backup schedule the
> next step is to test the backup and make sure that it works - and you
> know how to restore the backups.
> --
> Regards
> Steen Schlüter Persson
> DBA
>