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_s qlmaint N'-PlanID XXX -Rpt "f:\...."
-DelTxtRpt 2WEEKS -WriteHistory -RebldIdx 10 '
* EXECUTE master.dbo.xp_s qlmaint N'-PlanID XXX -Rpt "f:\...."
-DelTxtRpt 2DAYS -WriteHistory -UpdOptiStats 100 '
* EXECUTE master.dbo.xp_s qlmaint N'-PlanID XXX -Rpt "f:\..."
-DelTxtRpt 2WEEKS -WriteHistory -CkDB '
It would be really great if someone could help me on this.
guillaume
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_s qlmaint N'-PlanID XXX -Rpt "f:\...."
-DelTxtRpt 2WEEKS -WriteHistory -RebldIdx 10 '
* EXECUTE master.dbo.xp_s qlmaint N'-PlanID XXX -Rpt "f:\...."
-DelTxtRpt 2DAYS -WriteHistory -UpdOptiStats 100 '
* EXECUTE master.dbo.xp_s qlmaint N'-PlanID XXX -Rpt "f:\..."
-DelTxtRpt 2WEEKS -WriteHistory -CkDB '
It would be really great if someone could help me on this.
guillaume