Best practice method for shrinking the log file in dev environments

Hi all,
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
SimonHello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon

Best practice method for shrinking the log file in dev environments

Hi all,
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
SimonHello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon

Best practice method for shrinking the log file in dev environments

Hi all,
Can someone tell me what the best way to reduce my log file size is when
it gets too big. I can't switch recovery mode to Simple but every so
often I'd like to go in and clear it out.
What is the preffered command to do this?
I've heard the backup command with the TRUNCATE_ONLY isnt the best way
to do this? Is that the case and if so, whats the alternative?
Also, could someone tell me if doing a full backup automatically
truncates the transaction log?
Many thanks
Simon
Hello,
Could someone tell me if doing a full backup automatically truncates the
transaction log?
NO, FULL database backup will not clear the transaction log. You need to
backup the transaction log backup using BACKUP LOG to clear the log or else
if you do not
want the transaction log backup you could use Backup LOG with TRUNCATE_ONLY
to clear the transaction log from LDF file.
If you do not require a Transaction log backup then change the recovery
model for the database to "SIMPLE", in this
case after the commit the transaction log will be cleared. This recovery
mode will not allow transaction log backup.
In the otherway around, if your data is very critical / production data, set
the recovery model to "FULL". This allows you to perform
a transaction log backup. In this model after the commit the transaction log
still remains in the log file and will get cleared
when you perform a backup of log or issue "Truncate_only". So Truncate_only
is not a good option in production server.
If it is production / critical database follow the steps:-
1. Set the database recovery model to "FULL"
2. Perform a Full database backup once
3. Schedule Transaction log backup using (Backup Log dbname to
disk='d:\backup\dbname.tr1'
4. Perform the step 3 every 30 minutes (decide up on the volume of
transaction), but give new file names each backup dbname.tr1,...tr2...tr3
5. After the step 3 and 4 the transaction log will be cleared from
transaction log file
if you follow this step, even if yor database creach you can recover till
the last transaction log backup as well you can do a PINT_IN_TIME recovery
if needed
If it is non production or data is not critical
1. Set the recovery model to "SIMPLE"
2. Perform a Full database backup daily
3. If needed once in a while you can execute backup log dbname with
truncate_only
If you this methodology we can restore only till last backup.
Thanks
Hari
"Simon" <simon@.nothanks.com> wrote in message
news:%23$ozNe8RHHA.3440@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Can someone tell me what the best way to reduce my log file size is when
> it gets too big. I can't switch recovery mode to Simple but every so often
> I'd like to go in and clear it out.
> What is the preffered command to do this?
> I've heard the backup command with the TRUNCATE_ONLY isnt the best way to
> do this? Is that the case and if so, whats the alternative?
> Also, could someone tell me if doing a full backup automatically truncates
> the transaction log?
> Many thanks
> Simon
|||Thats a great answer - thanks sincerely for your time and advice
Kindest Regards
Simon

Best practice for shrinking Databases

Is there a "best practice" for shrinking databases?
Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages to
the beginning before shrinking?
I already have Maintenance Plans to backup logs and the database etc. but
wondered about automating the shrink process as well. There seems to be
alot of free space 67%+ in the database and logfile.
Dan
In general you want to avoide shrinking databases on a regular basis.
If the database grows back every week, or month, then it obviously
needs the space, so leave the space there! It is a terrible
performance hit to wait while SQL Server allocates more space, you
really don't want it happening more than it must.
So shrinking should be on an as-needed basis.
As for how much elbow room to leave, one rule of thumb I have used is
at least 25% more free space than the largest table. That allows room
for rebuilding a clustered index.
Another rule of thumb is that DBA time costs a lot more than disk
space; managing a database server without plenty of extra disk space
will cost far more dollars in administration than it saves on
hardware.
Roy
On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:

>Is there a "best practice" for shrinking databases?
>Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages to
>the beginning before shrinking?
>I already have Maintenance Plans to backup logs and the database etc. but
>wondered about automating the shrink process as well. There seems to be
>alot of free space 67%+ in the database and logfile.
>Dan
|||That's good advice Roy.
When I do shrink it, should I use the option to move the pages to the
beginning before shrinking?
Dan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:234qv1h601176jknofkr8furr4lr7gc1o1@.4ax.com... [vbcol=seagreen]
> In general you want to avoide shrinking databases on a regular basis.
> If the database grows back every week, or month, then it obviously
> needs the space, so leave the space there! It is a terrible
> performance hit to wait while SQL Server allocates more space, you
> really don't want it happening more than it must.
> So shrinking should be on an as-needed basis.
> As for how much elbow room to leave, one rule of thumb I have used is
> at least 25% more free space than the largest table. That allows room
> for rebuilding a clustered index.
> Another rule of thumb is that DBA time costs a lot more than disk
> space; managing a database server without plenty of extra disk space
> will cost far more dollars in administration than it saves on
> hardware.
> Roy
>
> On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:
|||Moving the pages is the only way you will be able to shrink to the
leve you want.
Roy
On Wed, 22 Feb 2006 20:48:23 -0500, "Dan" <someone@.yahoo.com> wrote:

>That's good advice Roy.
>When I do shrink it, should I use the option to move the pages to the
>beginning before shrinking?
>Dan

Best practice for shrinking Databases

Is there a "best practice" for shrinking databases?
Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages to
the beginning before shrinking?
I already have Maintenance Plans to backup logs and the database etc. but
wondered about automating the shrink process as well. There seems to be
alot of free space 67%+ in the database and logfile.
DanIn general you want to avoide shrinking databases on a regular basis.
If the database grows back every week, or month, then it obviously
needs the space, so leave the space there! It is a terrible
performance hit to wait while SQL Server allocates more space, you
really don't want it happening more than it must.
So shrinking should be on an as-needed basis.
As for how much elbow room to leave, one rule of thumb I have used is
at least 25% more free space than the largest table. That allows room
for rebuilding a clustered index.
Another rule of thumb is that DBA time costs a lot more than disk
space; managing a database server without plenty of extra disk space
will cost far more dollars in administration than it saves on
hardware.
Roy
On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:

>Is there a "best practice" for shrinking databases?
>Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages t
o
>the beginning before shrinking?
>I already have Maintenance Plans to backup logs and the database etc. but
>wondered about automating the shrink process as well. There seems to be
>alot of free space 67%+ in the database and logfile.
>Dan|||That's good advice Roy.
When I do shrink it, should I use the option to move the pages to the
beginning before shrinking?
Dan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:234qv1h601176jknofkr8furr4lr7gc1o1@.
4ax.com...[vbcol=seagreen]
> In general you want to avoide shrinking databases on a regular basis.
> If the database grows back every week, or month, then it obviously
> needs the space, so leave the space there! It is a terrible
> performance hit to wait while SQL Server allocates more space, you
> really don't want it happening more than it must.
> So shrinking should be on an as-needed basis.
> As for how much elbow room to leave, one rule of thumb I have used is
> at least 25% more free space than the largest table. That allows room
> for rebuilding a clustered index.
> Another rule of thumb is that DBA time costs a lot more than disk
> space; managing a database server without plenty of extra disk space
> will cost far more dollars in administration than it saves on
> hardware.
> Roy
>
> On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:
>|||Moving the pages is the only way you will be able to shrink to the
leve you want.
Roy
On Wed, 22 Feb 2006 20:48:23 -0500, "Dan" <someone@.yahoo.com> wrote:

>That's good advice Roy.
>When I do shrink it, should I use the option to move the pages to the
>beginning before shrinking?
>Dan

Best practice for shrinking Databases

Is there a "best practice" for shrinking databases?
Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages to
the beginning before shrinking?
I already have Maintenance Plans to backup logs and the database etc. but
wondered about automating the shrink process as well. There seems to be
alot of free space 67%+ in the database and logfile.
DanIn general you want to avoide shrinking databases on a regular basis.
If the database grows back every week, or month, then it obviously
needs the space, so leave the space there! It is a terrible
performance hit to wait while SQL Server allocates more space, you
really don't want it happening more than it must.
So shrinking should be on an as-needed basis.
As for how much elbow room to leave, one rule of thumb I have used is
at least 25% more free space than the largest table. That allows room
for rebuilding a clustered index.
Another rule of thumb is that DBA time costs a lot more than disk
space; managing a database server without plenty of extra disk space
will cost far more dollars in administration than it saves on
hardware.
Roy
On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:
>Is there a "best practice" for shrinking databases?
>Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages to
>the beginning before shrinking?
>I already have Maintenance Plans to backup logs and the database etc. but
>wondered about automating the shrink process as well. There seems to be
>alot of free space 67%+ in the database and logfile.
>Dan|||That's good advice Roy.
When I do shrink it, should I use the option to move the pages to the
beginning before shrinking?
Dan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:234qv1h601176jknofkr8furr4lr7gc1o1@.4ax.com...
> In general you want to avoide shrinking databases on a regular basis.
> If the database grows back every week, or month, then it obviously
> needs the space, so leave the space there! It is a terrible
> performance hit to wait while SQL Server allocates more space, you
> really don't want it happening more than it must.
> So shrinking should be on an as-needed basis.
> As for how much elbow room to leave, one rule of thumb I have used is
> at least 25% more free space than the largest table. That allows room
> for rebuilding a clustered index.
> Another rule of thumb is that DBA time costs a lot more than disk
> space; managing a database server without plenty of extra disk space
> will cost far more dollars in administration than it saves on
> hardware.
> Roy
>
> On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:
>>Is there a "best practice" for shrinking databases?
>>Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages
>>to
>>the beginning before shrinking?
>>I already have Maintenance Plans to backup logs and the database etc. but
>>wondered about automating the shrink process as well. There seems to be
>>alot of free space 67%+ in the database and logfile.
>>Dan|||Moving the pages is the only way you will be able to shrink to the
leve you want.
Roy
On Wed, 22 Feb 2006 20:48:23 -0500, "Dan" <someone@.yahoo.com> wrote:
>That's good advice Roy.
>When I do shrink it, should I use the option to move the pages to the
>beginning before shrinking?
>Dan