LOGS Files

Hi All,
SQL 2000 sp4 under Windows 2000 server sp4.
I have problem with my logs files, they are growing so much. I have
optimization job schedule once a week but looks like it's no helping with
logs files.
To fix a problem this morning on my server we have to detach the data,
delete the log file and attach the data again to be able to work.
Should I create a script to detach, delete the log files and attach the data
again everyday at midnight?
I have a low budget so I can't use third party software
It's a better way to fix this?
Tks in advance.
JFBJFB,
What is your recovery model set to. Is it set to full? if yes I am
assuming this is a production database.
Have you tried using DBCC SHRINKFILE as a nightly job, rather than detaching
and reattaching data everyday.
Thanks,
Rocky A
"JFB" wrote:

> Hi All,
> SQL 2000 sp4 under Windows 2000 server sp4.
> I have problem with my logs files, they are growing so much. I have
> optimization job schedule once a week but looks like it's no helping with
> logs files.
> To fix a problem this morning on my server we have to detach the data,
> delete the log file and attach the data again to be able to work.
> Should I create a script to detach, delete the log files and attach the da
ta
> again everyday at midnight?
> I have a low budget so I can't use third party software
> It's a better way to fix this?
> Tks in advance.
> JFB
>
>|||Run regular transaction log backups to address the issues
with the size of the log files.
-Sue
On Mon, 1 May 2006 17:43:35 -0400, "JFB" <help@.jfb.com>
wrote:

>Hi All,
>SQL 2000 sp4 under Windows 2000 server sp4.
>I have problem with my logs files, they are growing so much. I have
>optimization job schedule once a week but looks like it's no helping with
>logs files.
>To fix a problem this morning on my server we have to detach the data,
>delete the log file and attach the data again to be able to work.
>Should I create a script to detach, delete the log files and attach the dat
a
>again everyday at midnight?
>I have a low budget so I can't use third party software
>It's a better way to fix this?
>Tks in advance.
>JFB
>|||If you not require transaction log backup set recovery model to simple.
else if recovery model is not simple
Take transaction log backup regularaly , it will truncate commited log.
or in critical condition you can use
backup log <dbname> with no_log.
This will truncate log without backup , but its not recommended , since
it will break chain of transaction log.
Dont play with database by detaching backup and deleting log files.
Regards
Amish shah|||Tks for reply.
I have daily log backups as part of my maintenance schedule jobs using this
EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIfC
lean
-CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
2WEEKS -CrBkSubDir -BkExt "TRN"'
But looks like it's not truncated commited log.
It's a way to include the no_log in this command line?
Rgds
JFB
"amish" <shahamishm@.gmail.com> wrote in message
news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
> If you not require transaction log backup set recovery model to simple.
> else if recovery model is not simple
> Take transaction log backup regularaly , it will truncate commited log.
> or in critical condition you can use
> backup log <dbname> with no_log.
> This will truncate log without backup , but its not recommended , since
> it will break chain of transaction log.
> Dont play with database by detaching backup and deleting log files.
> Regards
> Amish shah
>|||Did you verify that transaction log backups are actually
going to the BackupLogs directory?
You may also need to increase the frequency of your log
backups.
With maintenance plans, you don't have as much flexibility
on the various commands. There is no option to do a no_log
or truncate_only through maintenance plans.
-Sue
On Tue, 2 May 2006 10:37:43 -0400, "JFB" <help@.jfb.com>
wrote:

>Tks for reply.
>I have daily log backups as part of my maintenance schedule jobs using this
>EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
>581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIf
Clean
> -CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
>2WEEKS -CrBkSubDir -BkExt "TRN"'
>But looks like it's not truncated commited log.
>It's a way to include the no_log in this command line?
>Rgds
>JFB
>
>"amish" <shahamishm@.gmail.com> wrote in message
>news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
>|||Tks for you reply Sue,
So how can I do it on a store procedure?
Any link or example... I'm going to install probably another HD to my raid
to fix this problem temporaly but I need to cut this logs..
Please help
Rgds
JFB
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:j44i52d1v8ta0lch1tm8od9di8vdsd911t@.
4ax.com...
> Did you verify that transaction log backups are actually
> going to the BackupLogs directory?
> You may also need to increase the frequency of your log
> backups.
> With maintenance plans, you don't have as much flexibility
> on the various commands. There is no option to do a no_log
> or truncate_only through maintenance plans.
> -Sue
> On Tue, 2 May 2006 10:37:43 -0400, "JFB" <help@.jfb.com>
> wrote:
>
>|||The SQL statement would be:
Backup Log YourDB
with truncate_only
-Sue
On Fri, 5 May 2006 21:30:00 -0400, "JFB" <help@.jfb.com>
wrote:

>Tks for you reply Sue,
>So how can I do it on a store procedure?
>Any link or example... I'm going to install probably another HD to my raid
>to fix this problem temporaly but I need to cut this logs..
>Please help
>Rgds
>JFB
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:j44i52d1v8ta0lch1tm8od9di8vdsd911t@.
4ax.com...
>