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 Enterprise Manager clone/wannabe?

Hi,
When working with environments that use the MSDE, we usually plug a laptop
into their network that has Enterprise Manager on it, and work that way.
Is there a good possibly third partry product that is not too expensive and
can be loaded on their machine so we don't have to do this? That way I could
go in via the Net and work with out going to their location.
I know there are a couple out there, I just haven't been able to find anyone
who has experience with them.
Thanks,
Steve
Why can't you load EM (Client Tools) only?
http://sqlservercode.blogspot.com/
"SteveInBeloit" wrote:

> Hi,
> When working with environments that use the MSDE, we usually plug a laptop
> into their network that has Enterprise Manager on it, and work that way.
> Is there a good possibly third partry product that is not too expensive and
> can be loaded on their machine so we don't have to do this? That way I could
> go in via the Net and work with out going to their location.
> I know there are a couple out there, I just haven't been able to find anyone
> who has experience with them.
> Thanks,
> Steve
|||My ignorance maybe. . . I was under the impression that legally you could not.
Steve
"SQL" wrote:
[vbcol=seagreen]
> Why can't you load EM (Client Tools) only?
> http://sqlservercode.blogspot.com/
> "SteveInBeloit" wrote:
|||Legally you may not be allowed to so you aren't being
ignorant. Check the following articles for an explanation
and tools to manage MSDE- there are some free ones listed.
Of the third party tools, DbaMgr is good.
If you are just looking for a query tool, Toad for SQL
Server works well.
-Sue
On Thu, 6 Oct 2005 13:59:06 -0700, "SteveInBeloit"
<SteveInBeloit@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>My ignorance maybe. . . I was under the impression that legally you could not.
>Steve
>
>"SQL" wrote:
|||SteveInBeloit wrote:

> Is there a good possibly third partry product that is not too expensive and
http://www.sqlmanager.net/
|||Sorry...forgot the link:
http://www.aspfaq.com/show.asp?id=2442
-Sue
On Thu, 06 Oct 2005 22:44:08 -0600, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:
[vbcol=seagreen]
>Legally you may not be allowed to so you aren't being
>ignorant. Check the following articles for an explanation
>and tools to manage MSDE- there are some free ones listed.
>Of the third party tools, DbaMgr is good.
>If you are just looking for a query tool, Toad for SQL
>Server works well.
>-Sue
>On Thu, 6 Oct 2005 13:59:06 -0700, "SteveInBeloit"
><SteveInBeloit@.discussions.microsoft.com> wrote:
|||SteveInBeloit wrote:
> Hi,
> When working with environments that use the MSDE, we usually plug a laptop
> into their network that has Enterprise Manager on it, and work that way.
> Is there a good possibly third partry product that is not too expensive and
> can be loaded on their machine so we don't have to do this? That way I could
> go in via the Net and work with out going to their location.
Buy SQL Server Developer Edition for $50 and get it all.
Steve Troxell
|||Have you tried, EMS sql manager lite
http://www.hotscripts.com/Detailed/47270.html
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:196C5DC7-6942-4EF3-A057-1126BDE7BC20@.microsoft.com...
> Hi,
> When working with environments that use the MSDE, we usually plug a laptop
> into their network that has Enterprise Manager on it, and work that way.
> Is there a good possibly third partry product that is not too expensive
> and
> can be loaded on their machine so we don't have to do this? That way I
> could
> go in via the Net and work with out going to their location.
> I know there are a couple out there, I just haven't been able to find
> anyone
> who has experience with them.
> Thanks,
> Steve