Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jspatz
Starting Member
15 Posts |
 Posted - 2009-04-27 : 11:04:45
|
| We have a maintenance plan that for 2 weeks has been crashing on the step that reorganizes the indexes about 2 and a half minutes into the step. We run that twice a week at 3am on mondays. Prior to these errors we switch the database from simple to full backup mode. Here are the errors I am getting the the associated t-sql from the view t-sql buttonExecuting the query "ALTER INDEX [ItemIdentification_PK] ON [dbo].[ItemIdentification] REORGANIZE WITH ( LOB_COMPACTION = ON )" failed with the following error: "The transaction log for database 'DAReporting' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.ALTER INDEX [ItemIdentification_PK] ON [dbo].[ItemIdentification] REORGANIZE WITH ( LOB_COMPACTION = ON )GONow the DB size is 6 gig and is moderately used. We have the initial size of the Transaction log to be 20 meg, increased by 10% to a max of 2 gig .. any other time of the day when we monitor this file it doesn't even approach 100 meg .. much less 2 gig. Is it possible that since switching to full backup mode it could use that much space on the Transactio Log that I should increase this allowance further .. or could it be something like the TEMPDB running low on space, I was assuming it couldn't be tempdb since the SORT_IN_TEMPDB parameter is not set by the maint plans but being a relative sql newb .. I figured I would asks those wiser then me in such ways. (I have been searching through books online and other blogs but couldn't find anything that directly answered my issue.) |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2009-04-27 : 11:53:07
|
| REORGANIZE is the equivalent of INDEXDEFRAG which is always a fully logged transaction.1)Why switch to full recovery just before an index rebuild? Often the reverse is true, a switch to simple before major reindex activity is undertaken.2)Initial size of 20meg with 10% growth is not very useful as a large optimization or bulk logged operation will create many virtual log files (vlf) within the .ldf (sometimes called internal fragmentation) that may not be optimum. Try 2 gig grow by 300meg."it's definitely useless and maybe harmful". |
 |
|
|
jspatz
Starting Member
15 Posts |
Posted - 2009-04-27 : 12:06:05
|
| Well we had switched to full recovery to be able to make the DB more up to the minute recoverable, and the reorgnize index has always been a part of the maintenance plan. But I will up the initial size of the Log File and change the growth option .. thanks for your reply. |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2009-04-27 : 14:04:11
|
| Lots of times backup maintenance routines that run for years then stop can be traced to disk space shortage. Most of mine have.I set things up the way I want them to work for ease of management then have to compromise and get creative as space becomes an issue. I hate it by the way, less room for error.Full recovery is good, reorganize is always fully logged even in simple recovery mode. Reindex or drop and create index can yield less transactions than reorganize. It depends, but it is not unusual to pattern like thisfull backupCHECKDB of backupswitch to simplereindex (or other larger scale activity)switch back to full.I didn't help with the specific error.But the autogrow from 20meg by 10% is a commmon "trap" that can be sub optimal. So I'm just suggesting start there as a general good practice. Maybe that error goes away. Multicolumn indexes that are targeted well often are the most in need for frequent management (that's why the index is of value). It's nice if your log file has enough dedicated free space and cycling such that it's ready to roll now and in the future without a footprint change.I've got other hunches but as always "it depends"DAReporting may mean that this database may be subject to bulk inserts. [ItemIdentification_PK] ON [dbo].[ItemIdentification] clustered index, would generally show a smaller percentage (per week) of fragmentation as database grew. Many and small sizes of the VLFs may contribute to unnecessary recurrance of fragmentation. (something like that).Maybe others can rise up against the bad help I've tried to provide. It's been a long time since I've tried to help. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-27 : 16:55:28
|
| The important thing is:If you have database in Full Recovery model,then you have to backup log frequently. Also get extra space for log growth. |
|
|
|
|
|
|
|
|
Problem with reorganize index during maint plan