Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Rebuild Index Task fails Expand / Collapse
Author
Message
Posted Monday, August 10, 2009 8:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 24, 2010 4:37 PM
Points: 4, 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

Post #767929
Posted Monday, August 10, 2009 8:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #768324
Posted Monday, August 10, 2009 11:14 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 4,390, Visits: 9,536
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #768367
Posted Saturday, January 9, 2010 3:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 7, 2011 1:23 PM
Points: 67, 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?
Post #844853
Posted Saturday, January 9, 2010 3:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 7, 2011 1:23 PM
Points: 67, Visits: 335
Please advice Immediately.

Thanks
Ali
Post #844854
Posted Saturday, January 9, 2010 4:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:49 AM
Points: 1,155, Visits: 4,643
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
Post #844856
Posted Saturday, January 9, 2010 4:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 7, 2011 1:23 PM
Points: 67, 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
Post #844857
Posted Saturday, January 9, 2010 4:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 7, 2011 1:23 PM
Points: 67, 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
Post #844858
Posted Saturday, January 9, 2010 4:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:49 AM
Points: 1,155, Visits: 4,643
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
Post #844859
Posted Saturday, January 9, 2010 4:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 7, 2011 1:23 PM
Points: 67, 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
Post #844860
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse