SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuild Index Task fails


Rebuild Index Task fails

Author
Message
babu.gunalan
babu.gunalan
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 38
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)

Group: General Forum Members
Points: 202950 Visits: 41947
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17951 Visits: 10039
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

ALI SQLDBA
ALI SQLDBA
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 335
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
ALI SQLDBA
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 335
Please advice Immediately.

Thanks
Ali
muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4267 Visits: 4964
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

Edit:Added more info.

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

ALI SQLDBA
ALI SQLDBA
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 335
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
ALI SQLDBA
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 335
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
muthukkumaran Kaliyamoorthy
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4267 Visits: 4964
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

Helping SQL DBAs and Developers >>>SqlserverBlogForum

ALI SQLDBA
ALI SQLDBA
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 335
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search