Sql-server – SQL Maintenance Rebuild Fails

maintenancesql server

I am pretty new to SQL and I have this maintenance job that keeps failing and I am not sure how to troubleshoot this. I have troubleshot other job failures with google help to success but this one is eluding me. Any help is appreciated. Here is the log…BTW..is there an easier way to read these logs, or does it just take time and experience?

Date        2/1/2015 4:00:00 AM
Log     Job History (SERVER MAINTENANCE.REBUILD)

Step ID     1
Server      SERVER
Job Name        SERVER MAINTENANCE.REBUILD
Step Name       REBUILD
Duration        04:01:38
Sql Severity        0
Sql Message ID      0
Operator Emailed        
Operator Net sent       
Operator Paged      
Retries Attempted       0

Progress: 2015-02-01 06:00:01.80 Source:
Rebuild Index Task Executing query "ALTER INDEX
[DTA_reports_indexcolumn_index2] ON [d…".: 40% complete End Pro…
The package execution fa… The step failed.

Best Answer

Firstly, make sure the account executing the job has sufficient privileges to run alter index. If that fails, then find the index "DTA_reports_indexcolumn_index2" and see if you can rebuild it yourself by hand. If that works, then you may have another process hitting this table at the same time the index maintenance is running. You'll have to look through what else is running around this time.

If rebuilding it by hand doesn't work, then explore what error you get, if you get one. You might have to drop and recreate the index. You might also run dbcc checkdb against the database to ensure that there's no corruption and run dbcc checktable against said table to ensure it's not corrupted.

Related Solutions

Sql-server – The job failed. The owner (pc\user) of job FULL DB BACKUP does not have server access

Try to create the PC\user as a login and give him the backup privilege in your database with this code.

USE [master]
GO
CREATE LOGIN [SERVER\Test1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [mydb]
GO
CREATE USER [SERVER\Test1] FOR LOGIN [SERVER\Test1]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [SERVER\Test1]
GO

Sql-server – Transaction log has grown huge after maintenance plan to rebuild idex

Rebuilding an index needs enough space to create the new index. A simplified rule of thumb seems to be that you need about 120% of the space used by the original index. This may be in the database or in tempdb, depending on whether SORT_IN_TEMPDB is ON or OFF.

If possible, have SORT_IN_TEMPDB = ON reduced some of the logging that is done.

If you rebuild all of the indexes between LOG backups, then all of the logs for reindexing all of the indexes will be in the log file. Therefore, major reorganization needs to have the appropriate resources of disk space, log space, and so forth. (For example, you might try reorganizing one table at a time and doing a log backup after each.)

For index space needs: http://msdn.microsoft.com/en-us/library/ms191183(v=sql.110).aspx For transaction logs: http://msdn.microsoft.com/en-us/library/ms184246.aspx

You could try changing to a minimally logged recovery model such as SIMPLE or BULK_LOGGED. However, for a production database you would have to weigh the negative side-effects and determine what is best. (A change to SIMPLE for reorganizing the databsse should probably be followed by changing to FULL and doing a FULL backup.)

A log file can be shrunk, but only after the high order pages are freed by a log backup. (This is usually a cycle of backup log, DBCC SHRINKFILE, then check the space and try again.)

For shrinking the log file do not use DBCC SHRINKDATABASE. Use DBCC SHRINKFILE (logfilename, targetsize).

Best Answer

Related Solutions

Sql-server – The job failed. The owner (pc\user) of job FULL DB BACKUP does not have server access

Sql-server – Transaction log has grown huge after maintenance plan to rebuild idex

Related Question