Rebuild Index Task fails

Question

Rebuild Index Task fails

babu.gunalan

Old Hand

Points: 322
More actions
August 10, 2009 at 8:51 am

#132638

Hi Folks,
The scheduled reindexing job has failed with the below error:
Source: Rebuild Index Executing query "USE [Msql2d] ".: 6% complete End Progress Error: 2009-08-08 16:46:31.95 Code: 0xC002F210 Source: Rebuild Index Execute SQL Task Description: Executing the query "ALTER INDEX [XPKACCOUNTING_LINE] ON [dbo].[ACCOUNTING_LINE] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF ) " failed with the following error: "The transaction log for database 'Msql2d' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Progress: 2009-08-08 16:46... The package execution fa... The step failed.
Guys, though i shrunk the log files, the reindex job fails regularly.
The log file settings is restricted to Max of 5000 MB and growth of 500 MB.
Please advice for rectifiying the issue..
regards
Babu

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

I don't know how much actual data you have, but rebuilding indexes is a fully logged task. It looks to me like the DB simply doesn't have enough room to grow to the task. Depending on how the indexes are structured, a 5 GB database can grow the log file to 11 GB during index rebuilds. Just to be clear... doing a shrink after that basically undoes all the work that rebuilding the indexes did because pages get moved during the shrink.

One of the things you can do is to add the setting to "sort in tempdb" to relieve some of the "pressure". I don't know the exact syntax off the top of my head so I'd have to look it up in BOL. Instead, I'll let you look it up.

Of course, that'll also mean that you'll need a pretty hefty TempDB but, to make you feel better, my production boxes typically start up with a 12GB TempDB.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 2

You can alter the database from full recovery model to bulk-logged during the index rebuilds. That will cause the rebuild operation itself to be a minimally logged operation. However, when you run your transaction log backup following the index rebuilds, don't be surprised if the backup is larger than your full backup. Even though the operation is minimally logged, the changes to the data are still recorded and have to be backed up.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ALI SQLDBA SSCrazy Points: 2071 More actions · Answer 3

I am also facing the same problem. Today, our disk which holds the LDF's ran out of space, as a temporary action, I ran shrink on ldfs through management studio leaving all defaults checked. We have rebuild and Update stats running this night and they both failed showing the same error message as posted in the first post. Do you think increasing the disk space resolves this issue?

ALI SQLDBA SSCrazy Points: 2071 More actions · Answer 4

ALI SQLDBA

SSCrazy

Points: 2071

January 9, 2010 at 3:29 am

#1101344

Please advice Immediately.

Thanks

Ali

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 5

Ali_SQLDBA (1/9/2010)
I am also facing the same problem. Today, our disk which holds the LDF's ran out of space, as a temporary action, I ran shrink on ldfs through management studio leaving all defaults checked. We have rebuild and Update stats running this night and they both failed showing the same error message as posted in the first post. Do you think increasing the disk space resolves this issue?

S u need to increase the disk space.

What's ur t-log size ?

What's the size of Auto growth ?

How much free space do u have in ur drive (T-log resides)

Also,Don't shrink the t-log files.(If u have no other way then u can)

Find the % of log used using Dbcc Sqlperf

I'd suggested read following article

Read the Gail's article Managing Transaction Logs[/url]

Edit:Added more info.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

ALI SQLDBA SSCrazy Points: 2071 More actions · Answer 6

Thank you for your immediate response.

Yeah, at that point of time there was no other go for us. So I shrinked the t-log. i was able to recover 3gb. when th nightly jobs rbuild and update stats have started, we lost the recovered 3 gb and the errorlog is filled up with "The transaction log for database is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". So this error message is gonna be resolved by adding free space? Is that correct.

My T-log size is 43058.81 MB

Thanks

Ali

ALI SQLDBA SSCrazy Points: 2071 More actions · Answer 7

Also, in sys.databases, the log_resuse_wait is showing 2 and log_resuse_wait_desc is showing log_backup. Can you advice what is the course of action for this. I read in one article that, if we shrink we need to take logbackup. which I didnt do. according to my understanding, the addition of free space to the disk clears all these issues. correct me if I am wrong.

Thanks

Ali

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 8

Ali_SQLDBA (1/9/2010)
Thank you for your immediate response.
Yeah, at that point of time there was no other go for us. So I shrinked the t-log. i was able to recover 3gb. when th nightly jobs rbuild and update stats have started, we lost the recovered 3 gb and the errorlog is filled up with "The transaction log for database is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". So this error message is gonna be resolved by adding free space? Is that correct.
My T-log size is 43058.81 MB
Thanks
Ali

S,u r correct adding more space ll help u.

First check where ur t-log files located

select filename from sys.sysaltfiles

where db_name(dbid)='Dbname'

My T-log size is 43058.81 MB

43 GB is very huge.

How often did u run the log backups?

Can u tell me the total Db-size.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

ALI SQLDBA SSCrazy Points: 2071 More actions · Answer 9

42.04962GB --LDF

46Gb --Total

4.41632GB-- MDF

Actually, we dont take any log backups. All we do is daily full backups. Also, the recovery model is Full. I know everything is smelling weird. But, this is how it was set-up.

Thanks

Ali

ALI SQLDBA SSCrazy Points: 2071 More actions · Answer 10

ALI SQLDBA

SSCrazy

Points: 2071

January 9, 2010 at 4:34 am

#1101351

Any Suggestion Sir.

Ali

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 11

Ali_SQLDBA (1/9/2010)
Also, in sys.databases, the log_resuse_wait is showing 2 and log_resuse_wait_desc is showing log_backup. Can you advice what is the course of action for this. I read in one article that, if we shrink we need to take logbackup. which I didnt do. according to my understanding, the addition of free space to the disk clears all these issues. correct me if I am wrong.
Thanks
Ali

--Before shrink the log file u need to take a t-log backup

if we take a log backup, the log can be truncated.

--(OR)Change the recover model full to simple and back to full (Please take a full backup after changing to full mode)

In simple recovery mode,when a checkpoint operation runs, all inactive log records are removed from the transaction log and the space is made available for reuse.

Read the Paul's article

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 12

Ali_SQLDBA (1/9/2010)
42.04962GB --LDF
46Gb --Total
4.41632GB-- MDF
Actually, we dont take any log backups. All we do is daily full backups. Also, the recovery model is Full. I know everything is smelling weird. But, this is how it was set-up.
Thanks
Ali

Ohh..what a difference?

42.04962GB --LDF

46Gb --Total

4.41632GB-- MDF

Is it production database ?

What's ur SLA for Backups? (Please do the t-log backup often time.)

Keep monitoring the log size and do the log backups at least every 1 hours once.

I'd suggested read the article's i already gave u.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 13

Ali_SQLDBA (1/9/2010)
Please advice Immediately.
Thanks
Ali

As a bit of a friendly tip, Ali... I know it's just a difference in languages but the words you used combined with the extreme brevity make your message sound terribly arrogant and demanding even though you used the magic word "please". It will chase off many potential respondents in the U.S.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 14

Ali_SQLDBA (1/9/2010)
42.04962GB --LDF
46Gb --Total
4.41632GB-- MDF
Actually, we dont take any log backups. All we do is daily full backups. Also, the recovery model is Full. I know everything is smelling weird. But, this is how it was set-up.
Thanks
Ali

If all you'll ever do is full backups, then you should probably just set the recovery model to Simple and be done with it. It will greatly simplify your life for your current requirements.

On the other hand, there is no chance of a "point in time" recovery other than what your last full backup was with either a recovery model of Simple or the way you're currently doing it. If daily entries are important for recovery, you could use a program like RedGate's Backup (makes both backup and recovery very simple) and do log backups as often as, say, every 15 minutes. That would solve your current log problems and make your server much more bullet proof.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)