Need to Shrink Transaction Log

Hi everyone,
I urgently need to shrink the transaction log on our MSDE2000 database. It's
coming up to 3gig (for a 25mb database) and we're running out of room on the
server. I think it's using Full Recovery, but more on that in a moment.
Just wondering if someone could go over my plan and point out anything I
might be thinking of doing that might not work?
First of all, I want to make a complete backup of the database, just in case
anything breaks, then backup the log with truncate only, then do another full
backup, then use DBCC ShrinkFile...
In osql (db name is danV2)...
> BACKUP DATABASE danV2 TO DISK = 'E:\danV2.dat.bak'
> BACKUP LOG danV2 WITH TRUNCATE_ONLY
> BACKUP DATABASE danV2 TO DISK 'E:\danV2-Full.dat.bak'
> DBCC SHRINKFILE(danV2, 5)
does that look right?!
Although we're using Full Recovery mode on the Logs, but we don't have a
watertight strategy for failsafe.
I'm wondering if the best thing to do is to set the LOG files to SIMPLE, and
then run an Osql backup command every night!? That way, we can always restore
to the previous day if anything drastic happens.
How can I change the recovery mode for the database using OSQL?!
Thanks
DanHello,
You steps looks good to shrik the LDF file. To chnage the recovery model in
OSQL use the below command.
ALTER DATABASE danV2 SET RECOVERY SIMPLE
Probably after shrinking the file. If you do a Log backup in frequent
intervals you could have LDF file not growing
as well this will help you to recover the database when required. Just setup
the backup strategy based on your data criticality.
Thanks
Hari
"musosdev" <musoswire@.community.nospam> wrote in message
news:BCBA828D-A70C-4D58-A7AA-9A46BDA42995@.microsoft.com...
> Hi everyone,
> I urgently need to shrink the transaction log on our MSDE2000 database.
> It's
> coming up to 3gig (for a 25mb database) and we're running out of room on
> the
> server. I think it's using Full Recovery, but more on that in a moment.
> Just wondering if someone could go over my plan and point out anything I
> might be thinking of doing that might not work?
> First of all, I want to make a complete backup of the database, just in
> case
> anything breaks, then backup the log with truncate only, then do another
> full
> backup, then use DBCC ShrinkFile...
> In osql (db name is danV2)...
>> BACKUP DATABASE danV2 TO DISK = 'E:\danV2.dat.bak'
>> BACKUP LOG danV2 WITH TRUNCATE_ONLY
>> BACKUP DATABASE danV2 TO DISK 'E:\danV2-Full.dat.bak'
>> DBCC SHRINKFILE(danV2, 5)
> does that look right?!
> Although we're using Full Recovery mode on the Logs, but we don't have a
> watertight strategy for failsafe.
> I'm wondering if the best thing to do is to set the LOG files to SIMPLE,
> and
> then run an Osql backup command every night!? That way, we can always
> restore
> to the previous day if anything drastic happens.
> How can I change the recovery mode for the database using OSQL?!
> Thanks
>
> Dan
>|||Hari,
Thanks for that, although you're answer has given me food for thought, and
therefore more questions!
If I my plan and leave the database at full recovery, and then backup the
log file periodically,
1) how often should I do that (daily / weekly / monthly) ?
2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
will the backup help to keep it down anyway?
3) do I need to keep all the transaction log backups?
I'm thinking... weekly full backup on a sunday, then daily log file backups.
Would I just need to create 6 backup files (for the week), just the last one,
or more than 6?!
Thanks again!
Dan
"Hari Prasad" wrote:
> Hello,
> You steps looks good to shrik the LDF file. To chnage the recovery model in
> OSQL use the below command.
> ALTER DATABASE danV2 SET RECOVERY SIMPLE
> Probably after shrinking the file. If you do a Log backup in frequent
> intervals you could have LDF file not growing
> as well this will help you to recover the database when required. Just setup
> the backup strategy based on your data criticality.
> Thanks
> Hari
>
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:BCBA828D-A70C-4D58-A7AA-9A46BDA42995@.microsoft.com...
> > Hi everyone,
> >
> > I urgently need to shrink the transaction log on our MSDE2000 database.
> > It's
> > coming up to 3gig (for a 25mb database) and we're running out of room on
> > the
> > server. I think it's using Full Recovery, but more on that in a moment.
> >
> > Just wondering if someone could go over my plan and point out anything I
> > might be thinking of doing that might not work?
> >
> > First of all, I want to make a complete backup of the database, just in
> > case
> > anything breaks, then backup the log with truncate only, then do another
> > full
> > backup, then use DBCC ShrinkFile...
> >
> > In osql (db name is danV2)...
> >
> >> BACKUP DATABASE danV2 TO DISK = 'E:\danV2.dat.bak'
> >> BACKUP LOG danV2 WITH TRUNCATE_ONLY
> >> BACKUP DATABASE danV2 TO DISK 'E:\danV2-Full.dat.bak'
> >> DBCC SHRINKFILE(danV2, 5)
> >
> > does that look right?!
> >
> > Although we're using Full Recovery mode on the Logs, but we don't have a
> > watertight strategy for failsafe.
> >
> > I'm wondering if the best thing to do is to set the LOG files to SIMPLE,
> > and
> > then run an Osql backup command every night!? That way, we can always
> > restore
> > to the previous day if anything drastic happens.
> >
> > How can I change the recovery mode for the database using OSQL?!
> >
> > Thanks
> >
> >
> >
> > Dan
> >
>
>|||"musosdev" <musoswire@.community.nospam> wrote in message
news:B3FC3C3F-ED33-4DA5-B244-0004C1B56BC8@.microsoft.com...
> Hari,
> Thanks for that, although you're answer has given me food for thought, and
> therefore more questions!
> If I my plan and leave the database at full recovery, and then backup the
> log file periodically,
> 1) how often should I do that (daily / weekly / monthly) ?
What are your needs?
At my old job, we did them every 15 minutes.
So basically ask yourself "how much work can you afford to lose and how much
time/effort do you want to spend recovering it?"
> 2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
> will the backup help to keep it down anyway?
No. Don't do this.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> 3) do I need to keep all the transaction log backups?
>
You need to keep at least everything since the last full backup. So the
recovery would be restore full backup (WITH NORECOVERY) and then restore
subsequent transaction logs.
Generally I'd keep several full backups with all subsequent transaction
logs.
Again, the particular answer depends on the churn of your data. If you
replace all the data in your database every other day (we basically had a DB
like that), then full backups going back a week may be overkill.
On the other hand, if the data changes infrequently, or there's auditing
required, you may need to keep backups going back years.
> I'm thinking... weekly full backup on a sunday, then daily log file
> backups.
> Would I just need to create 6 backup files (for the week), just the last
> one,
> or more than 6?!
I'd probably do a full backup once a week and then 2-3 log backups during
the day, just to keep potential data loss down, but have a happy medium when
it comes to how much work I want to do to recover data.
> Thanks again!
>
> Dan
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hello,
Answer to your queries:-
1) how often should I do that (daily / weekly / monthly) ?
Transaction log backup needs to beaked up every 30 minutes atleast. This
will make sure you have log backup for recovery and LDF wil l not grow.
With the log backup during a database crash you can recover the database
atleast till last log backup. Means u maximum loose 29 minutes data.
2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
will the backup help to keep it down anyway?
No need to shrink the LDF file. If you do 30 minutes log backup after the
backup the LDF file will be cleared automatically. So no need to SHRINK the
LDF file.
3) do I need to keep all the transaction log backups?
All the log backups after the last FULL database backup. So do a FULL
database backup every night and clear all the old log backup files.
My best recommendation is dality FULL database backup during night and
subsequent 30 minutes transactioin log backup will be good for you.
Thanks
Hari
"musosdev" <musoswire@.community.nospam> wrote in message
news:B3FC3C3F-ED33-4DA5-B244-0004C1B56BC8@.microsoft.com...
> Hari,
> Thanks for that, although you're answer has given me food for thought, and
> therefore more questions!
> If I my plan and leave the database at full recovery, and then backup the
> log file periodically,
> 1) how often should I do that (daily / weekly / monthly) ?
> 2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
> will the backup help to keep it down anyway?
> 3) do I need to keep all the transaction log backups?
> I'm thinking... weekly full backup on a sunday, then daily log file
> backups.
> Would I just need to create 6 backup files (for the week), just the last
> one,
> or more than 6?!
> Thanks again!
>
> Dan
>
>
> "Hari Prasad" wrote:
>> Hello,
>> You steps looks good to shrik the LDF file. To chnage the recovery model
>> in
>> OSQL use the below command.
>> ALTER DATABASE danV2 SET RECOVERY SIMPLE
>> Probably after shrinking the file. If you do a Log backup in frequent
>> intervals you could have LDF file not growing
>> as well this will help you to recover the database when required. Just
>> setup
>> the backup strategy based on your data criticality.
>> Thanks
>> Hari
>>
>> "musosdev" <musoswire@.community.nospam> wrote in message
>> news:BCBA828D-A70C-4D58-A7AA-9A46BDA42995@.microsoft.com...
>> > Hi everyone,
>> >
>> > I urgently need to shrink the transaction log on our MSDE2000 database.
>> > It's
>> > coming up to 3gig (for a 25mb database) and we're running out of room
>> > on
>> > the
>> > server. I think it's using Full Recovery, but more on that in a moment.
>> >
>> > Just wondering if someone could go over my plan and point out anything
>> > I
>> > might be thinking of doing that might not work?
>> >
>> > First of all, I want to make a complete backup of the database, just in
>> > case
>> > anything breaks, then backup the log with truncate only, then do
>> > another
>> > full
>> > backup, then use DBCC ShrinkFile...
>> >
>> > In osql (db name is danV2)...
>> >
>> >> BACKUP DATABASE danV2 TO DISK = 'E:\danV2.dat.bak'
>> >> BACKUP LOG danV2 WITH TRUNCATE_ONLY
>> >> BACKUP DATABASE danV2 TO DISK 'E:\danV2-Full.dat.bak'
>> >> DBCC SHRINKFILE(danV2, 5)
>> >
>> > does that look right?!
>> >
>> > Although we're using Full Recovery mode on the Logs, but we don't have
>> > a
>> > watertight strategy for failsafe.
>> >
>> > I'm wondering if the best thing to do is to set the LOG files to
>> > SIMPLE,
>> > and
>> > then run an Osql backup command every night!? That way, we can always
>> > restore
>> > to the previous day if anything drastic happens.
>> >
>> > How can I change the recovery mode for the database using OSQL?!
>> >
>> > Thanks
>> >
>> >
>> >
>> > Dan
>> >
>>|||>> 2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
>> will the backup help to keep it down anyway?
> No. Don't do this.
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
And here's another way of putting it:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23UeKDacXHHA.3952@.TK2MSFTNGP04.phx.gbl...
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:B3FC3C3F-ED33-4DA5-B244-0004C1B56BC8@.microsoft.com...
>> Hari,
>> Thanks for that, although you're answer has given me food for thought, and
>> therefore more questions!
>> If I my plan and leave the database at full recovery, and then backup the
>> log file periodically,
>> 1) how often should I do that (daily / weekly / monthly) ?
> What are your needs?
> At my old job, we did them every 15 minutes.
> So basically ask yourself "how much work can you afford to lose and how much time/effort do you
> want to spend recovering it?"
>
>> 2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
>> will the backup help to keep it down anyway?
> No. Don't do this.
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>> 3) do I need to keep all the transaction log backups?
> You need to keep at least everything since the last full backup. So the recovery would be restore
> full backup (WITH NORECOVERY) and then restore subsequent transaction logs.
> Generally I'd keep several full backups with all subsequent transaction logs.
> Again, the particular answer depends on the churn of your data. If you replace all the data in
> your database every other day (we basically had a DB like that), then full backups going back a
> week may be overkill.
> On the other hand, if the data changes infrequently, or there's auditing required, you may need to
> keep backups going back years.
>
>> I'm thinking... weekly full backup on a sunday, then daily log file backups.
>> Would I just need to create 6 backup files (for the week), just the last one,
>> or more than 6?!
> I'd probably do a full backup once a week and then 2-3 log backups during the day, just to keep
> potential data loss down, but have a happy medium when it comes to how much work I want to do to
> recover data.
>
>> Thanks again!
>>
>> Dan
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>|||Thanks everyone,
Very useful information from all of you - I feel much clearer now.
One final question... we're currently using MSDE as our database (currently
setting up an SQL Server machine), but until then, how do I setup these
nightly full/periodic log backups' (no Enterprise Manager!)
Thanks,
Dan
"Hari Prasad" wrote:
> Hello,
> Answer to your queries:-
> 1) how often should I do that (daily / weekly / monthly) ?
> Transaction log backup needs to beaked up every 30 minutes atleast. This
> will make sure you have log backup for recovery and LDF wil l not grow.
> With the log backup during a database crash you can recover the database
> atleast till last log backup. Means u maximum loose 29 minutes data.
> 2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
> will the backup help to keep it down anyway?
> No need to shrink the LDF file. If you do 30 minutes log backup after the
> backup the LDF file will be cleared automatically. So no need to SHRINK the
> LDF file.
> 3) do I need to keep all the transaction log backups?
> All the log backups after the last FULL database backup. So do a FULL
> database backup every night and clear all the old log backup files.
> My best recommendation is dality FULL database backup during night and
> subsequent 30 minutes transactioin log backup will be good for you.
> Thanks
> Hari
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:B3FC3C3F-ED33-4DA5-B244-0004C1B56BC8@.microsoft.com...
> > Hari,
> >
> > Thanks for that, although you're answer has given me food for thought, and
> > therefore more questions!
> >
> > If I my plan and leave the database at full recovery, and then backup the
> > log file periodically,
> >
> > 1) how often should I do that (daily / weekly / monthly) ?
> > 2) when I do a periodic log backup, do I need to do SHRINKFILE as well, or
> > will the backup help to keep it down anyway?
> > 3) do I need to keep all the transaction log backups?
> >
> > I'm thinking... weekly full backup on a sunday, then daily log file
> > backups.
> > Would I just need to create 6 backup files (for the week), just the last
> > one,
> > or more than 6?!
> >
> > Thanks again!
> >
> >
> > Dan
> >
> >
> >
> >
> > "Hari Prasad" wrote:
> >
> >> Hello,
> >>
> >> You steps looks good to shrik the LDF file. To chnage the recovery model
> >> in
> >> OSQL use the below command.
> >>
> >> ALTER DATABASE danV2 SET RECOVERY SIMPLE
> >>
> >> Probably after shrinking the file. If you do a Log backup in frequent
> >> intervals you could have LDF file not growing
> >> as well this will help you to recover the database when required. Just
> >> setup
> >> the backup strategy based on your data criticality.
> >>
> >> Thanks
> >> Hari
> >>
> >>
> >> "musosdev" <musoswire@.community.nospam> wrote in message
> >> news:BCBA828D-A70C-4D58-A7AA-9A46BDA42995@.microsoft.com...
> >> > Hi everyone,
> >> >
> >> > I urgently need to shrink the transaction log on our MSDE2000 database.
> >> > It's
> >> > coming up to 3gig (for a 25mb database) and we're running out of room
> >> > on
> >> > the
> >> > server. I think it's using Full Recovery, but more on that in a moment.
> >> >
> >> > Just wondering if someone could go over my plan and point out anything
> >> > I
> >> > might be thinking of doing that might not work?
> >> >
> >> > First of all, I want to make a complete backup of the database, just in
> >> > case
> >> > anything breaks, then backup the log with truncate only, then do
> >> > another
> >> > full
> >> > backup, then use DBCC ShrinkFile...
> >> >
> >> > In osql (db name is danV2)...
> >> >
> >> >> BACKUP DATABASE danV2 TO DISK = 'E:\danV2.dat.bak'
> >> >> BACKUP LOG danV2 WITH TRUNCATE_ONLY
> >> >> BACKUP DATABASE danV2 TO DISK 'E:\danV2-Full.dat.bak'
> >> >> DBCC SHRINKFILE(danV2, 5)
> >> >
> >> > does that look right?!
> >> >
> >> > Although we're using Full Recovery mode on the Logs, but we don't have
> >> > a
> >> > watertight strategy for failsafe.
> >> >
> >> > I'm wondering if the best thing to do is to set the LOG files to
> >> > SIMPLE,
> >> > and
> >> > then run an Osql backup command every night!? That way, we can always
> >> > restore
> >> > to the previous day if anything drastic happens.
> >> >
> >> > How can I change the recovery mode for the database using OSQL?!
> >> >
> >> > Thanks
> >> >
> >> >
> >> >
> >> > Dan
> >> >
> >>
> >>
> >>
>
>|||Hi, Dan,
You can use OSQL command and the stored procedures "sp_add_job",
"sp_add_jobstep ", "sp_add_jobserver ", "sp_start_job" and
"sp_add_jobschedule" to periodically back up your database.
I recommend that you first create a script file to use the stored
procedures to schedule your backup job; and then use OSQL command to run
the script file. Of course, you need to ensure that the SQLSERVERAGENT
service has been started.
For the detailed information, please refer to:
How to back up a Microsoft Data Engine database by using Transact-SQL
http://support.microsoft.com/kb/241397/EN-US/
How to manage the SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005
Express Edition by using the osql utility
http://support.microsoft.com/kb/325003
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi, Dan,
Just check with you to see if you need further assistance on this issue.
Please feel free to let us know if you have any other questions or concerns.
Have a great day!
Charles Wang
Microsoft Online Partner Support