Rebuliding Indexes

Question

Post reply

Rebuliding Indexes

YarHad

Mr or Mrs. 500

Points: 550
More actions
October 9, 2012 at 7:15 am

#263664

Hi All,
I am rebuilding/reorganizing indexes and I am trying to locate the activity via server logs
However I can't find it.
My database is in Full recovery mode (As simple or bulk logged mode will minimally log the process) and I am not specifing for the rebuild to take place online (as this would also minimally log the process)
Can someone please point me in the right direction or are all rebuilding/reorganizing processes minimally logged regardless
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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

Sean Pearce SSCoach Points: 15999 More actions · Answer 1

Recovery mode refers to the transaction log, which is not the same as server logs.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

YarHad Mr or Mrs. 500 Points: 550 More actions · Answer 2

So How to I locate the activity. ie for example when a database is backed up, one can see the activity logged in the activity log.

How come I can see the rebuild/reorg activity in the activity log

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What do you mean by 'activity log'?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

YarHad Mr or Mrs. 500 Points: 550 More actions · Answer 4

YarHad

Mr or Mrs. 500

Points: 550

October 9, 2012 at 7:57 am

#1547224

Sorry SQl server logs

YarHad Mr or Mrs. 500 Points: 550 More actions · Answer 5

Ok, let me start again.

you know when you check the sql server logs, you can see things that happened in the database

e.g alter recovery mode, database backup, database restore, sql server start up e.t.c

How can I find an entry for an index rebuild/reorg

Scott D. Jacobson SSCertifiable Points: 6149 More actions · Answer 6

Reorgs should look something like this:

USE [DatabaseName]

GO

ALTER INDEX [IndexName] ON [dbo].[TableName] REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

Rebuilds would look something like this:

USE [DatabaseName]

GO

ALTER INDEX [IndexName] ON [dbo].[TableName] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )

GO

The WITH section of each will vary. Check out the ALTER INDEX pages in BOL for the various options.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Index rebuilds don't get written into the SQL Server error log. You could add a piece to your maintenance tasks to log that a rebuild was done if you like, or run a trace or set up an extended events session.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

YarHad Mr or Mrs. 500 Points: 550 More actions · Answer 8

GilaMonster (10/9/2012)
Index rebuilds don't get written into the SQL Server error log. You could add a piece to your maintenance tasks to log that a rebuild was done if you like, or run a trace or set up an extended events session.

Ok, so it doesn't get written into the sql server log. Nice to know

Thank you very much!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Not into the SQL Server error log (text file).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

YarHad (10/9/2012)
GilaMonster (10/9/2012)
Index rebuilds don't get written into the SQL Server error log. You could add a piece to your maintenance tasks to log that a rebuild was done if you like, or run a trace or set up an extended events session.
Ok, so it doesn't get written into the sql server log. Nice to know
Thank you very much!!

Just to clarify, what Gail's repeated above is that the Index Rebuild WILL be written into the log file (.ldf) that's attached to the database. That's not human readable, unfortunately.

It doesn't get into the error logs for the server, however, which is where we could actually find it. 😉

Some of the .ldf writes tend to get confused as to whether or not they're actually written there so it's really just us trying to kill some old myths and make sure they don't start up again.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Evil Kraig F (10/9/2012)
YarHad (10/9/2012)
GilaMonster (10/9/2012)
Index rebuilds don't get written into the SQL Server error log. You could add a piece to your maintenance tasks to log that a rebuild was done if you like, or run a trace or set up an extended events session.
Ok, so it doesn't get written into the sql server log. Nice to know
Thank you very much!!
Just to clarify, what Gail's repeated above is that the Index Rebuild WILL be written into the log file (.ldf) that's attached to the database. That's not human readable, unfortunately.

Welll....... I can read it. Mostly. 🙂

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 12

GilaMonster (10/9/2012)
Evil Kraig F (10/9/2012)
YarHad (10/9/2012)
GilaMonster (10/9/2012)
Index rebuilds don't get written into the SQL Server error log. You could add a piece to your maintenance tasks to log that a rebuild was done if you like, or run a trace or set up an extended events session.
Ok, so it doesn't get written into the sql server log. Nice to know
Thank you very much!!
Just to clarify, what Gail's repeated above is that the Index Rebuild WILL be written into the log file (.ldf) that's attached to the database. That's not human readable, unfortunately.
Welll....... I can read it. Mostly. 🙂

I'm still not entirely convinced you're not a Turing Complete database with the amount of knowledge you inhale for fun, though... 😛 I shouldn't pick on ya much for it though, I certainly benefit a lot from your knowledge. 😎

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 13

YarHad (10/9/2012)
...I am rebuilding/reorganizing indexes and I am trying to locate the activity via server logs...

If you're reorganizing or rebuilding your indexes in a SQL Agent Job, you can examine the Job History for that job to see the details about the job: execution start time, time to execute, success or failure, and more.

The rebuild operation can be minimally logged to the database log file if the database recovery model is set to either bulk-logged or simple.