reduce databse size: ldf

Showing posts with label ldf. Show all posts

Friday, March 9, 2012

Reference a file name

Hi,
How can I reference the ldf or mdf (using the logical names) file of a
particular database when I'm in a diferent context, for instance, I've got
two databases PG and PG1 and being in PG1 I want to do the following
DBCC SHRINKFILE (PG_Log ,100)
Any ideas?One option,
EXEC('Use PG DBCC SHRINKFILE (PG_Log ,100)')
-Sue
On Thu, 27 Apr 2006 17:35:01 -0700, FJC
<FJC@.discussions.microsoft.com> wrote:

>Hi,
>How can I reference the ldf or mdf (using the logical names) file of a
>particular database when I'm in a diferent context, for instance, I've got
>two databases PG and PG1 and being in PG1 I want to do the following
>DBCC SHRINKFILE (PG_Log ,100)
>Any ideas?|||Thanks Sue, I worked.
"Sue Hoegemeier" wrote:

> One option,
> EXEC('Use PG DBCC SHRINKFILE (PG_Log ,100)')
> -Sue
> On Thu, 27 Apr 2006 17:35:01 -0700, FJC
> <FJC@.discussions.microsoft.com> wrote:
>
>

Reference a file name

Hi,
How can I reference the ldf or mdf (using the logical names) file of a
particular database when I'm in a diferent context, for instance, I've got
two databases PG and PG1 and being in PG1 I want to do the following
DBCC SHRINKFILE (PG_Log ,100)
Any ideas?One option,
EXEC('Use PG DBCC SHRINKFILE (PG_Log ,100)')
-Sue
On Thu, 27 Apr 2006 17:35:01 -0700, FJC
<FJC@.discussions.microsoft.com> wrote:
>Hi,
>How can I reference the ldf or mdf (using the logical names) file of a
>particular database when I'm in a diferent context, for instance, I've got
>two databases PG and PG1 and being in PG1 I want to do the following
>DBCC SHRINKFILE (PG_Log ,100)
>Any ideas?|||Thanks Sue, I worked.
"Sue Hoegemeier" wrote:
> One option,
> EXEC('Use PG DBCC SHRINKFILE (PG_Log ,100)')
> -Sue
> On Thu, 27 Apr 2006 17:35:01 -0700, FJC
> <FJC@.discussions.microsoft.com> wrote:
> >Hi,
> >How can I reference the ldf or mdf (using the logical names) file of a
> >particular database when I'm in a diferent context, for instance, I've got
> >two databases PG and PG1 and being in PG1 I want to do the following
> >
> >DBCC SHRINKFILE (PG_Log ,100)
> >
> >Any ideas?
>

Saturday, February 25, 2012

Reducing log (ldf) file size

I thought that the size of the log file went down when you did a full
database backup. How can I reduce the size of my log file? Do I need to
back it up separately? Thanks.
David
DBCC SHRINKFILE(databasename_log, 0)
This will reduce the log file size to 504 KB.
However, if you don't change your current recovery model and/or the behavior
of your apps, the log file is just going to grow again between backups. So
the above is only a temporary relief.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uC#FtmiLFHA.1392@.TK2MSFTNGP10.phx.gbl...
> I thought that the size of the log file went down when you did a full
> database backup. How can I reduce the size of my log file? Do I need to
> back it up separately? Thanks.
> David
>
|||You are right. I thought I had set it to "Simple" but was set to
"Full". Thanks Aaron.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||and addtionally to Aarons reply, you need to backup the log seperately
(...look up BACKUP LOG xxxx...in Books On Line..) to keep the log in shape.
Setting the recovery to Simple will limit your possibilities to recover data
in case of something happens to the database.
Regards
Steen
David wrote:
> You are right. I thought I had set it to "Simple" but was set to
> "Full". Thanks Aaron.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Reducing log (ldf) file size

I thought that the size of the log file went down when you did a full
database backup. How can I reduce the size of my log file? Do I need to
back it up separately? Thanks.
DavidDBCC SHRINKFILE(databasename_log, 0)
This will reduce the log file size to 504 KB.
However, if you don't change your current recovery model and/or the behavior
of your apps, the log file is just going to grow again between backups. So
the above is only a temporary relief.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uC#FtmiLFHA.1392@.TK2MSFTNGP10.phx.gbl...
> I thought that the size of the log file went down when you did a full
> database backup. How can I reduce the size of my log file? Do I need to
> back it up separately? Thanks.
> David
>|||You are right. I thought I had set it to "Simple" but was set to
"Full". Thanks Aaron.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||and addtionally to Aarons reply, you need to backup the log seperately
(...look up BACKUP LOG xxxx...in Books On Line..) to keep the log in shape.
Setting the recovery to Simple will limit your possibilities to recover data
in case of something happens to the database.
Regards
Steen
David wrote:
> You are right. I thought I had set it to "Simple" but was set to
> "Full". Thanks Aaron.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Reducing LDF files

Hi,
If I understand it correctly, you only need an LDF file to restore to a point in time after the last full backup? If this is so, then Could the LDF file not be reduced in size on perfoming a full backup?
Most of the time it's not an issue as there is enough space on the HDD, but is it possible to reduce the ldf file size periodically (manually would be fine). Is changing the recovery mode from FULL to SIMPLE and then back to FULL an option?
If so, is anyone able to tell me how, exactly, I can do this? ... I've sifted through the documentaion to no avail ... :eek:
Many thanks
RobNo, you cannot just truncate the log when you take the full backup, because you do not know whether the error forcing you to restore the database will occur prior to or after the last full backup. You may leave the office at 4pm, at 5 pm some tables disappear. Backups are scheduled to run at midnight. If the logs are truncated when you arrive at the office the next morning, you cannot restore to point in time anymore.

Thus, the ONLY way you should free space in the logfiles is backing them up with the backup log statement, which frees up place internally in the log files.|||do not change recovery mode FULL to SIMPLE. it will break log chain.|||If you are just starting off with SQL Server administration, I would recommend using the maintenance plan wizard (if you are using SQL 2005, be sure to download and install SP2, which has significant improvements to maintenance plans in general).

General guidelines:

SQL 7.0/2000:
1. Create a maintenance plan that includes only the system databases (master, model, msdb). Do a full backup daily.

2. Create another maintenance plan that includes only the user databases. Do a full backup daily and a transaction log backup as often as you are comfortable (and as often as needed to keep the logs at a stable/reasonable size).

3. For both plans:
a. Back up over the network (if your network is stable), or straight to disk (separate from data/log files).
b. Retain full backups on disk for 2-3 days (more if you can afford it).
c. Retain log backups for as long as necessary to get back to your last good full backup.
c. Make sure there's another process that writes the db backups to tape.

SQL 2005
1. Create one plan for full backups for all databases

2. Create another plan for log backups for all user databases.

3. Follow other guidelines as above.

There are lots of tweaks and other things that you can do to improve backup performance, this is just a guide to get you started.

Be sure to also:
1. Practice recovery:
a. Practice full database restore
b. Practive database restore to point in time
c. Practice recovery from tape
d. Practice recovery from a dead server (ie, full rebuild/fresh install)

2. Validate your tape backups

3. Document your backup strategy and document your recovery plan.

Regards,

hmscott

Monday, February 20, 2012

reduce the File Size less then Original Size in SQL 2000

I have one db test with one .mdf and .ldf file.
..mdf file size is 100mb and for some reson i removed all the tables
from that .mdf file and transfer it into new secondary file so all the
tables moved into secondary file now i want to reduce the first .mdf
file from 100 mb to 50mb is that possible,it's showing 90mb is free.

Please reply(ShailendraPune@.gmail.com) writes:

Quote:

Originally Posted by

I have one db test with one .mdf and .ldf file.
.mdf file size is 100mb and for some reson i removed all the tables
from that .mdf file and transfer it into new secondary file so all the
tables moved into secondary file now i want to reduce the first .mdf
file from 100 mb to 50mb is that possible,it's showing 90mb is free.

Look at DBCC SHRINKFILE in Books Online.

Although I would say that with the sizes you have indicated, I would not
consider a secondary file at all. (Unless you are doing this a training
exercise.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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

Friday, March 9, 2012

Reference a file name

Reference a file name

Saturday, February 25, 2012

Reducing log (ldf) file size

Reducing log (ldf) file size

Reducing log (ldf) file size

Reducing LDF files

Monday, February 20, 2012

reduce the File Size less then Original Size in SQL 2000

Reduce log file Size (LDF)

Reduce log file Size (LDF)

reduce databse size

Blog Archive

About Me