469,081 Members | 1,858 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,081 developers. It's quick & easy.

huge logfile problem

guf
Hi All,

I have SQL Server 2000 sp3, Windows 2000 Server.
I have a database which is used by an application server. I do several
important jobs a day in the application server and never access my
database directly. Those jobs are created through the application
server API and I do not have access to the Application server
procedure.

My database is 500 Mo.
Now my problem is that some jobs that are planed through the
application server can generate huge logfile (2 Go+) in less than 20
minutes work.
What is really strange is if I export my db using the DTS, and
performed the same operation on the new database, the logfile don't
grow more than 4 or 5 Mo. If I switch the production database to the
new one, it takes no more than 2 days to have the same problem.

This is a real problem because backup takes longer, and will
probably be harder to restore in case of crash. The operation is also
slowing down the system because SQL Server needs to add more space to
the logfile.

I cannot switch easily from one database to another, because this is a
production application and we should have 99,99% availability.

Jobs that are planed in my database:
On an hourly basis:
* logfile backup

On a daily basis:
* database backup
* DBCC SHRINKDATABASE (N'IMANDATA', 30,TRUNCATEONLY) every day
* EXECUTE master.dbo.xp_sqlmaint N'-PlanID XXX -Rpt "f:\...."
-DelTxtRpt 2WEEKS -WriteHistory -RebldIdx 10 '
* EXECUTE master.dbo.xp_sqlmaint N'-PlanID XXX -Rpt "f:\...."
-DelTxtRpt 2DAYS -WriteHistory -UpdOptiStats 100 '
* EXECUTE master.dbo.xp_sqlmaint N'-PlanID XXX -Rpt "f:\..."
-DelTxtRpt 2WEEKS -WriteHistory -CkDB '

It would be really great if someone could help me on this.

guillaume
Jul 20 '05 #1
0 1608

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by guf | last post: by
4 posts views Thread by Sebastian Luemmer | last post: by
1 post views Thread by FJ | last post: by
6 posts views Thread by Daniel Walzenbach | last post: by
2 posts views Thread by Andi Clemens | last post: by
6 posts views Thread by roger.lagrilliere | last post: by
reply views Thread by Adam | last post: by
2 posts views Thread by pompom | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.