Link to home
Create AccountLog in
Avatar of manojbaba
manojbaba

asked on

Maintenance Plan failure

I have database in sqlserver 2008 Enterprise . some database are 150 Gb some are just 50 and some are 1 GB.
My maintenace job(consists of dbcc check db, index rebuild  ) has been failing after running 3 hours. before it will take 7/8 hours to complete and get succeed.


Note :

Since database log is getting full during the maintenance operation . I have scheduled t-log job every 30 min. but when I see the the job history it doesn't  ran on exact time like 12.30 , 1.00, 1.30 and so on. and one of database log file gets full at 2.50 even after taking frequent backup like every 30 min. and I saw the error msg  package failed at same time.

but last week I get error message   saying tempdb log is full at 3.30 and the package failed at 4.28.
but I don;t have error like having user database  t-log full last week.

How to solve the problem by keeping existing maintenance plan?




Thanks
ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
<How to solve the problem by keeping existing maintenance plan?>

looks like you can not anymore

or use one of the many versions of the "smart reindex" - see one of versions from above post link. or check the DB sizes and separate main  plan  as 2-3 plans for specific databases
also you cant try to uncheck your reindex to sort in tempdb , and in any case to add extra space for tempdb drive - or move tempdb on the bigger drive,

also check for anything extra running during your mainst and make sure reindex \check db do not runb at same time..

there can be some another variations. but it is as start
----
BTW: can you post the error  msg- and tell if it is online or offline reindex