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
Microsoft SQL Server

Avatar of undefined
Last Comment
EugeneZ

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
David Todd

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
EugeneZ

<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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes