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 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
>
>
>|||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:
> > 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
>
>|||> 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...
> 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:
>> > 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 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:
> > 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...
> > 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:
> >
> >> > 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
> >>
> >>
> >>
>
>|||> 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...
> 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:
>> > 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...
>> > 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:
>> >
>> >> > 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
>> >>
>> >>
>> >>
>>|||Elvis 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:
>> 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...
>> 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:
>> 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
>>
>>
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 an
hourly basis?
Thanks
Elvis
"Steen Persson (DK)" wrote:
> Elvis 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:
> >
> >> 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...
> >> 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:
> >>
> >> 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
> >>
> >>
> >>
> >>
> 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 Dan,
Thanks for all your help...it has really helped me a lot.
I just need to clarify with you:
1) to backup the transaction logs without using mush disk space I would open
Query analyzer, select the database that I want to reduce the transaction log
size and type:
"BACKUP LOG MyDatabase WITH TRUNCATE_ONLY" How long would this take for
100GB approx'
2) to shrink the database I do the same a s step 1 except I use the
following command:
" DBCC SHRINKFILE ('MyDatabase_Log', 1000) " what do you recommend I should
shrink it to?1GB as you have given in the example.
The reason why I ask these questions is because I have never used Query
Anayzer and I guess now is as good time as any...Thanks again Dan for helping
out the Noob!!
Thanks
Elvis
"Dan Guzman" wrote:
> > 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...
> > 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:
> >
> >> > 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...
> >> > 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:
> >> >
> >> >> > 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,
I don't want to loose any data in this process...so if I backup my 200mb
database before I do anything and then run the outlined commands on my 100GB
transaction log which has never been backed up before to reduce its size to
1gb am I going to loose data? Could my database grow to 100gb then' I am
really confused now'
Your help is greatly appreciated.
Thanking You
"Dan Guzman" wrote:
> > 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...
> > 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:
> >
> >> > 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...
> >> > 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:
> >> >
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||> 1) to backup the transaction logs without using mush disk space I would
> open
> Query analyzer, select the database that I want to reduce the transaction
> log
> size and type:
> "BACKUP LOG MyDatabase WITH TRUNCATE_ONLY" How long would this take for
> 100GB approx'
I wouldn't expect this to take very long - probably less than a minute.
Note that you don't have to be in the context of the target database because
the database name is specified on the command.
> " DBCC SHRINKFILE ('MyDatabase_Log', 1000) " what do you recommend I
> should
> shrink it to?1GB as you have given in the example.
You do need to set the current database to the target database before
running this command. The log needs to be sized for the update activity
between log backups so the proper size depends on your application. You can
still allow autogrow as a safety net. Since index builds are fully logged,
the log size should be at least 1.2 times the size of your largest table to
accommodate building the clustered index. You can always start large (e.g.
200 MB), monitor the space and gradually shrink the log size if you don't
want to waste space.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Elvis" <Elvis@.discussions.microsoft.com> wrote in message
news:33A72F93-F109-4DF6-B5D1-8564213993FB@.microsoft.com...
> Hi Dan,
> Thanks for all your help...it has really helped me a lot.
> I just need to clarify with you:
> 1) to backup the transaction logs without using mush disk space I would
> open
> Query analyzer, select the database that I want to reduce the transaction
> log
> size and type:
> "BACKUP LOG MyDatabase WITH TRUNCATE_ONLY" How long would this take for
> 100GB approx'
> 2) to shrink the database I do the same a s step 1 except I use the
> following command:
> " DBCC SHRINKFILE ('MyDatabase_Log', 1000) " what do you recommend I
> should
> shrink it to?1GB as you have given in the example.
> The reason why I ask these questions is because I have never used Query
> Anayzer and I guess now is as good time as any...Thanks again Dan for
> helping
> out the Noob!!
> Thanks
> Elvis
> "Dan Guzman" wrote:
>> > 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...
>> > 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:
>> >
>> >> > 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...
>> >> > 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:
>> >> >
>> >> >> > 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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Truncating the log will not cause data loss. It will only remove
transactions from the log that have already been committed and written to
the data file(s). However, since you've never backed up the log before,
your only recovery recourse will be to restore from your last full database
backup until you begin a new backup sequence. So the process is:
1) Full database backup
2) Backup log with truncate_only
3) Shink log file
4) regularly scheduled database and log backups
> Could my database grow to 100gb then'
It is highly unlikely that your the log file will grow to 100 GB again as
long as you perform regular log backups between your database backups.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Elvis" <Elvis@.discussions.microsoft.com> wrote in message
news:BF26D3F8-CF47-4966-B974-5B58E03DB0DE@.microsoft.com...
> Hi,
> I don't want to loose any data in this process...so if I backup my 200mb
> database before I do anything and then run the outlined commands on my
> 100GB
> transaction log which has never been backed up before to reduce its size
> to
> 1gb am I going to loose data? Could my database grow to 100gb then' I am
> really confused now'
> Your help is greatly appreciated.
> Thanking You
> "Dan Guzman" wrote:
>> > 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...
>> > 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:
>> >
>> >> > 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...
>> >> > 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:
>> >> >
>> >> >> > 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,
Thanks again Dan...
Last thing I am unsure of:
>You do need to set the current database to the target database before
>running this command. The log needs to be sized for the update activity
>between log backups so the proper size depends on your application.
I am lost with this statement above. I am running Sage CRM as my application
and my database is 200MB. I am still unsure as to what the best size would be
to use with the DBCC SHRINKFILE command to shrink my 100gb ldf file down to'
Thanks again Dan...sorry for all the questions...but I don't want to
destroy this sql Server.
Elvis
"Dan Guzman" wrote:
> Truncating the log will not cause data loss. It will only remove
> transactions from the log that have already been committed and written to
> the data file(s). However, since you've never backed up the log before,
> your only recovery recourse will be to restore from your last full database
> backup until you begin a new backup sequence. So the process is:
> 1) Full database backup
> 2) Backup log with truncate_only
> 3) Shink log file
> 4) regularly scheduled database and log backups
> > Could my database grow to 100gb then'
> It is highly unlikely that your the log file will grow to 100 GB again as
> long as you perform regular log backups between your database backups.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Elvis" <Elvis@.discussions.microsoft.com> wrote in message
> news:BF26D3F8-CF47-4966-B974-5B58E03DB0DE@.microsoft.com...
> > Hi,
> >
> > I don't want to loose any data in this process...so if I backup my 200mb
> > database before I do anything and then run the outlined commands on my
> > 100GB
> > transaction log which has never been backed up before to reduce its size
> > to
> > 1gb am I going to loose data? Could my database grow to 100gb then' I am
> > really confused now'
> > Your help is greatly appreciated.
> >
> > Thanking You
> >
> > "Dan Guzman" wrote:
> >
> >> > 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...
> >> > 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:
> >> >
> >> >> > 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...
> >> >> > 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:
> >> >> >
> >> >> >> > 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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||> I am lost with this statement above. I am running Sage CRM as my
> application
> and my database is 200MB. I am still unsure as to what the best size would
> be
> to use with the DBCC SHRINKFILE command to shrink my 100gb ldf file down
> to'
This database is small so you might consider just shrinking the log to 200MB
(same size as the db). You don't need to shrink it further unless you want
to save the disk space.
> Thanks again Dan...sorry for all the questions...but I don't want to
> destroy this sql Server.
I understand - better to be safe than sorry.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Elvis" <Elvis@.discussions.microsoft.com> wrote in message
news:3D82633F-6082-4FDF-A64E-ABFE38B5F844@.microsoft.com...
> Hi,
> Thanks again Dan...
> Last thing I am unsure of:
>>You do need to set the current database to the target database before
>>running this command. The log needs to be sized for the update activity
>>between log backups so the proper size depends on your application.
> I am lost with this statement above. I am running Sage CRM as my
> application
> and my database is 200MB. I am still unsure as to what the best size would
> be
> to use with the DBCC SHRINKFILE command to shrink my 100gb ldf file down
> to'
> Thanks again Dan...sorry for all the questions...but I don't want to
> destroy this sql Server.
> Elvis
>
> "Dan Guzman" wrote:
>> Truncating the log will not cause data loss. It will only remove
>> transactions from the log that have already been committed and written to
>> the data file(s). However, since you've never backed up the log before,
>> your only recovery recourse will be to restore from your last full
>> database
>> backup until you begin a new backup sequence. So the process is:
>> 1) Full database backup
>> 2) Backup log with truncate_only
>> 3) Shink log file
>> 4) regularly scheduled database and log backups
>> > Could my database grow to 100gb then'
>> It is highly unlikely that your the log file will grow to 100 GB again as
>> long as you perform regular log backups between your database backups.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Elvis" <Elvis@.discussions.microsoft.com> wrote in message
>> news:BF26D3F8-CF47-4966-B974-5B58E03DB0DE@.microsoft.com...
>> > Hi,
>> >
>> > I don't want to loose any data in this process...so if I backup my
>> > 200mb
>> > database before I do anything and then run the outlined commands on my
>> > 100GB
>> > transaction log which has never been backed up before to reduce its
>> > size
>> > to
>> > 1gb am I going to loose data? Could my database grow to 100gb then' I
>> > am
>> > really confused now'
>> > Your help is greatly appreciated.
>> >
>> > Thanking You
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > 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...
>> >> > 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:
>> >> >
>> >> >> > 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...
>> >> >> > 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:
>> >> >> >
>> >> >> >> > 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
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Elvis wrote:
> 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 an
> hourly basis?
> Thanks
> Elvis
>
How often you backup your log, depends on how many hours of work you (or
your company) can accept to loose in case of a break down.
Let me try to bring up a few examples.
1. You run a FULL backup at 2 AM and no log backup. First of all this
will not truncate your logfile hence it will continue to grow. If
something goes wrong with the database e.g. at 1.55 AM and you need to
restore your backup, you'll loose 23 hours and 55 minutes of work.
2. You run a FULL backup at 2 AM and a log backup at 2.30 AM. This will
truncate your logfile so it doesn't grow, but that's a bout all good the
log backup will do for you. If something happens at 1.55 AM, you'll
loose 23 hours and 25 minutes of work. You can restore your full backup
from 2 AM and then apply the log from 2.30 AM and then your database
will contain the data it had at 2.30 AM when you did the log backup.
3. You run a FULL backup at 2 AM and a log backup at 2 PM. This will
still truncate your logfile, but from a recovery point of view you're
doing a little better. Again if something happens at 1.55 AM you'll only
loose 11 hours and 55 minutes of work.
4. You run a FULL backup at 2 AM and do log backups every hour from 8 AM
to 6 PM (assuming that's your average working day). Now if something
happens at e.g. 2.30 PM, you'll only loose 30 minutes of work. You
simply restore your FULL backup from 2 AM and then all the hourly log
backup up to 2 PM.
If you do FULL backup and log backups, you are "always" guaranteed that
you can restore your database up to the state it had at the time of
latest log file backup. In some case though - depending on what happens
to your database, you'll be able to backup your logfile after the
problem ocoured and then you can restore the logfile with the STOPAT
command so you can restore it up to the time just before things went
wrong meaning your data loss will be minimal.
I hope the above cleared things up a little bit. Just keep in mind that
there are no definitive answer to how often you should backup your log -
it all depends on how much data you are willing to loose in case of a
failure of some kind. In many cases there aren't really any penalty for
backing up too much - but there is for not having the right backup at
the right time...:-).
--
Regards
Steen Schlüter Persson
DBA|||Hi Steen,
Thanks..that explains it perfectly ..I completely understand what I need to
do now with regards to backups of the database, transactional logs and the
frequency of both.
I have another question:
When I do the transactional log backups I am most likely going to be doing
them to disk and I am going to be Using Enterprise Manager to schedule backup
job to disk.
Will these log backups overwrite themselves?
otherwise the disk may become full from all these backups'
Thanks again! you are helping tremendously.
Elvis
"Steen Persson (DK)" wrote:
> Elvis wrote:
> > 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 an
> > hourly basis?
> > Thanks
> >
> > Elvis
> >
> How often you backup your log, depends on how many hours of work you (or
> your company) can accept to loose in case of a break down.
> Let me try to bring up a few examples.
> 1. You run a FULL backup at 2 AM and no log backup. First of all this
> will not truncate your logfile hence it will continue to grow. If
> something goes wrong with the database e.g. at 1.55 AM and you need to
> restore your backup, you'll loose 23 hours and 55 minutes of work.
> 2. You run a FULL backup at 2 AM and a log backup at 2.30 AM. This will
> truncate your logfile so it doesn't grow, but that's a bout all good the
> log backup will do for you. If something happens at 1.55 AM, you'll
> loose 23 hours and 25 minutes of work. You can restore your full backup
> from 2 AM and then apply the log from 2.30 AM and then your database
> will contain the data it had at 2.30 AM when you did the log backup.
> 3. You run a FULL backup at 2 AM and a log backup at 2 PM. This will
> still truncate your logfile, but from a recovery point of view you're
> doing a little better. Again if something happens at 1.55 AM you'll only
> loose 11 hours and 55 minutes of work.
> 4. You run a FULL backup at 2 AM and do log backups every hour from 8 AM
> to 6 PM (assuming that's your average working day). Now if something
> happens at e.g. 2.30 PM, you'll only loose 30 minutes of work. You
> simply restore your FULL backup from 2 AM and then all the hourly log
> backup up to 2 PM.
> If you do FULL backup and log backups, you are "always" guaranteed that
> you can restore your database up to the state it had at the time of
> latest log file backup. In some case though - depending on what happens
> to your database, you'll be able to backup your logfile after the
> problem ocoured and then you can restore the logfile with the STOPAT
> command so you can restore it up to the time just before things went
> wrong meaning your data loss will be minimal.
> I hope the above cleared things up a little bit. Just keep in mind that
> there are no definitive answer to how often you should backup your log -
> it all depends on how much data you are willing to loose in case of a
> failure of some kind. In many cases there aren't really any penalty for
> backing up too much - but there is for not having the right backup at
> the right time...:-).
> --
> Regards
> Steen Schlüter Persson
> DBA
>|||> When I do the transactional log backups I am most likely going to be doing
> them to disk and I am going to be Using Enterprise Manager to schedule backup
> job to disk.
> Will these log backups overwrite themselves?
> otherwise the disk may become full from all these backups'
That depends on whether you specify INIT or NOINIT for the backup command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Elvis" <Elvis@.discussions.microsoft.com> wrote in message
news:551B3439-AEFF-4BB6-BF0A-31A4658FAA3B@.microsoft.com...
> Hi Steen,
> Thanks..that explains it perfectly ..I completely understand what I need to
> do now with regards to backups of the database, transactional logs and the
> frequency of both.
> I have another question:
> When I do the transactional log backups I am most likely going to be doing
> them to disk and I am going to be Using Enterprise Manager to schedule backup
> job to disk.
> Will these log backups overwrite themselves?
> otherwise the disk may become full from all these backups'
> Thanks again! you are helping tremendously.
> Elvis
> "Steen Persson (DK)" wrote:
>> Elvis wrote:
>> > 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 an
>> > hourly basis?
>> > Thanks
>> >
>> > Elvis
>> >
>> How often you backup your log, depends on how many hours of work you (or
>> your company) can accept to loose in case of a break down.
>> Let me try to bring up a few examples.
>> 1. You run a FULL backup at 2 AM and no log backup. First of all this
>> will not truncate your logfile hence it will continue to grow. If
>> something goes wrong with the database e.g. at 1.55 AM and you need to
>> restore your backup, you'll loose 23 hours and 55 minutes of work.
>> 2. You run a FULL backup at 2 AM and a log backup at 2.30 AM. This will
>> truncate your logfile so it doesn't grow, but that's a bout all good the
>> log backup will do for you. If something happens at 1.55 AM, you'll
>> loose 23 hours and 25 minutes of work. You can restore your full backup
>> from 2 AM and then apply the log from 2.30 AM and then your database
>> will contain the data it had at 2.30 AM when you did the log backup.
>> 3. You run a FULL backup at 2 AM and a log backup at 2 PM. This will
>> still truncate your logfile, but from a recovery point of view you're
>> doing a little better. Again if something happens at 1.55 AM you'll only
>> loose 11 hours and 55 minutes of work.
>> 4. You run a FULL backup at 2 AM and do log backups every hour from 8 AM
>> to 6 PM (assuming that's your average working day). Now if something
>> happens at e.g. 2.30 PM, you'll only loose 30 minutes of work. You
>> simply restore your FULL backup from 2 AM and then all the hourly log
>> backup up to 2 PM.
>> If you do FULL backup and log backups, you are "always" guaranteed that
>> you can restore your database up to the state it had at the time of
>> latest log file backup. In some case though - depending on what happens
>> to your database, you'll be able to backup your logfile after the
>> problem ocoured and then you can restore the logfile with the STOPAT
>> command so you can restore it up to the time just before things went
>> wrong meaning your data loss will be minimal.
>> I hope the above cleared things up a little bit. Just keep in mind that
>> there are no definitive answer to how often you should backup your log -
>> it all depends on how much data you are willing to loose in case of a
>> failure of some kind. In many cases there aren't really any penalty for
>> backing up too much - but there is for not having the right backup at
>> the right time...:-).
>> --
>> Regards
>> Steen Schlüter Persson
>> DBA|||Hi Tibor,
Thanks for the answer...how do I do this with Enterprise Manager.
Thanks
Elvis
"Tibor Karaszi" wrote:
> > When I do the transactional log backups I am most likely going to be doing
> > them to disk and I am going to be Using Enterprise Manager to schedule backup
> > job to disk.
> > Will these log backups overwrite themselves?
> > otherwise the disk may become full from all these backups'
> That depends on whether you specify INIT or NOINIT for the backup command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Elvis" <Elvis@.discussions.microsoft.com> wrote in message
> news:551B3439-AEFF-4BB6-BF0A-31A4658FAA3B@.microsoft.com...
> > Hi Steen,
> >
> > Thanks..that explains it perfectly ..I completely understand what I need to
> > do now with regards to backups of the database, transactional logs and the
> > frequency of both.
> >
> > I have another question:
> > When I do the transactional log backups I am most likely going to be doing
> > them to disk and I am going to be Using Enterprise Manager to schedule backup
> > job to disk.
> > Will these log backups overwrite themselves?
> > otherwise the disk may become full from all these backups'
> >
> > Thanks again! you are helping tremendously.
> >
> > Elvis
> >
> > "Steen Persson (DK)" wrote:
> >
> >> Elvis wrote:
> >> > 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 an
> >> > hourly basis?
> >> > Thanks
> >> >
> >> > Elvis
> >> >
> >>
> >> How often you backup your log, depends on how many hours of work you (or
> >> your company) can accept to loose in case of a break down.
> >>
> >> Let me try to bring up a few examples.
> >>
> >> 1. You run a FULL backup at 2 AM and no log backup. First of all this
> >> will not truncate your logfile hence it will continue to grow. If
> >> something goes wrong with the database e.g. at 1.55 AM and you need to
> >> restore your backup, you'll loose 23 hours and 55 minutes of work.
> >>
> >> 2. You run a FULL backup at 2 AM and a log backup at 2.30 AM. This will
> >> truncate your logfile so it doesn't grow, but that's a bout all good the
> >> log backup will do for you. If something happens at 1.55 AM, you'll
> >> loose 23 hours and 25 minutes of work. You can restore your full backup
> >> from 2 AM and then apply the log from 2.30 AM and then your database
> >> will contain the data it had at 2.30 AM when you did the log backup.
> >>
> >> 3. You run a FULL backup at 2 AM and a log backup at 2 PM. This will
> >> still truncate your logfile, but from a recovery point of view you're
> >> doing a little better. Again if something happens at 1.55 AM you'll only
> >> loose 11 hours and 55 minutes of work.
> >>
> >> 4. You run a FULL backup at 2 AM and do log backups every hour from 8 AM
> >> to 6 PM (assuming that's your average working day). Now if something
> >> happens at e.g. 2.30 PM, you'll only loose 30 minutes of work. You
> >> simply restore your FULL backup from 2 AM and then all the hourly log
> >> backup up to 2 PM.
> >>
> >> If you do FULL backup and log backups, you are "always" guaranteed that
> >> you can restore your database up to the state it had at the time of
> >> latest log file backup. In some case though - depending on what happens
> >> to your database, you'll be able to backup your logfile after the
> >> problem ocoured and then you can restore the logfile with the STOPAT
> >> command so you can restore it up to the time just before things went
> >> wrong meaning your data loss will be minimal.
> >>
> >> I hope the above cleared things up a little bit. Just keep in mind that
> >> there are no definitive answer to how often you should backup your log -
> >> it all depends on how much data you are willing to loose in case of a
> >> failure of some kind. In many cases there aren't really any penalty for
> >> backing up too much - but there is for not having the right backup at
> >> the right time...:-).
> >>
> >> --
> >>
> >> Regards
> >> Steen Schlüter Persson
> >> DBA
> >>
>
>|||Elvis wrote:
> Hi Steen,
> Thanks..that explains it perfectly ..I completely understand what I need to
> do now with regards to backups of the database, transactional logs and the
> frequency of both.
> I have another question:
> When I do the transactional log backups I am most likely going to be doing
> them to disk and I am going to be Using Enterprise Manager to schedule backup
> job to disk.
> Will these log backups overwrite themselves?
> otherwise the disk may become full from all these backups'
> Thanks again! you are helping tremendously.
> Elvis
I prefer to only have one single backup in each file and then just name
the backup files different (in my case it's with the date and a
sequential number).
In Enterprise Manager you can set it up to backup to a new file every
time and then delete files older than x days. I'm not using Enterprise
Manager for backups though so I'm not the right one to guide you through
how to set it up, but it should be fairly self explaining.
--
Regards
Steen Schlüter Persson
DBA|||Hi,
Thanks to all...Shrunk it down to 100MB.
Set transactional log backups hourly and to overwrite. EM puts the INIT
command in for you.
I left the autogrow on and set it to 10%. The tlog backups should keep it
small so that it doesn't grow out of control..
THANKS to all for helping me out here and making SQL such a great product.
Thanks
Elvis
"Steen Persson (DK)" wrote:
> Elvis wrote:
> > Hi Steen,
> >
> > Thanks..that explains it perfectly ..I completely understand what I need to
> > do now with regards to backups of the database, transactional logs and the
> > frequency of both.
> >
> > I have another question:
> > When I do the transactional log backups I am most likely going to be doing
> > them to disk and I am going to be Using Enterprise Manager to schedule backup
> > job to disk.
> > Will these log backups overwrite themselves?
> > otherwise the disk may become full from all these backups'
> >
> > Thanks again! you are helping tremendously.
> >
> > Elvis
> I prefer to only have one single backup in each file and then just name
> the backup files different (in my case it's with the date and a
> sequential number).
> In Enterprise Manager you can set it up to backup to a new file every
> time and then delete files older than x days. I'm not using Enterprise
> Manager for backups though so I'm not the right one to guide you through
> how to set it up, but it should be fairly self explaining.
> --
> Regards
> Steen Schlüter Persson
> DBA
>|||Elvis wrote:
> Hi,
> Thanks to all...Shrunk it down to 100MB.
> Set transactional log backups hourly and to overwrite. EM puts the INIT
> command in for you.
> I left the autogrow on and set it to 10%. The tlog backups should keep it
> small so that it doesn't grow out of control..
> THANKS to all for helping me out here and making SQL such a great product.
> Thanks
> Elvis
Just one little comment. When you say that you've set the log backup to
overwrite, then make sure that you doesn't overwrite the last log backup
file every time. You should generate a new log backup file everytime you
run the log backup if you're not appending. Otherwise your log backup
isn't worth anything...:-).
--
Regards
Steen Schlüter Persson
DBA