Rebuild Index Job is failing

Question

Rebuild Index Job is failing

varshachyk

SSC Veteran

Points: 243
More actions
April 24, 2010 at 9:15 am

#139880

Hi All,
on one of our server Re-build index job is failing with the below error. Please suggest.
Failed: (-1073548784) Executing the query "ALTER INDEX [PPS_USER_SESSION_COOKIE] ON [dbo].[abc] REBUILD WITH ( FILLFACTOR = 90, 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 'connect_prod' 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.
Thanks in advance
Shree Varsha

Viewing 7 posts - 1 through 6 (of 6 total)

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

varshachyk (4/24/2010)
Hi All,
on one of our server Re-build index job is failing with the below error. Please suggest.
Failed: (-1073548784) Executing the query "ALTER INDEX [PPS_USER_SESSION_COOKIE] ON [dbo].[abc] REBUILD WITH ( FILLFACTOR = 90, 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 'connect_prod' 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.
Thanks in advance
Shree Varsha

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

varshachyk (4/24/2010)
" failed with the following error: "The transaction log for database 'connect_prod' is full.

it clearly showed the problem .What have you done for it ?

Resolution:

When the transaction logs grow to an unacceptable limit, you must immediately back up your transaction log file. While the backup of your transaction log files is created, SQL Server automatically truncates the inactive part of the transaction log. The inactive part of the transaction log file contains the completed transactions, and therefore, the transaction log file is no longer used by SQL Server during the recovery process. SQL Server reuses this truncated, inactive space in the transaction log instead of permitting the transaction log to continue to grow and to use more space

OR

Shrink the transaction log file

The backup operation or the Truncate method does not reduce the log file size. To reduce the size of the transaction log file, you must shrink the transaction log file. To shrink a transaction log file to the requested size and to remove the unused pages, you must use the DBCC SHRINKFILE operation. The DBCC SHRINKFILE Transact-SQL statement can only shrink the inactive part inside the log file.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

Bhuvnesh (4/24/2010)
OR
Shrink the transaction log file

to effectively shrink the log file you will need to back it up first

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

vidya_pande SSCertifiable Points: 5275 More actions · Answer 4

if DB is in simple recovery model you need to create more space for log file. either by increasing he max limit or by adding more files.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

if the database is in Simple recovery the log will truncate on checkpoint, also the alter index is minimally logged so this should not need log file modifications

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

vidya_pande SSCertifiable Points: 5275 More actions · Answer 6

i mean to say that..if database is in simple recovery model and still it is giving log full error, it means there is a requirement of more log space.