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»»

Rebuliding Indexes Expand / Collapse
Author
Message
Posted Tuesday, October 09, 2012 7:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 1:44 AM
Points: 34, Visits: 211
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
Post #1370380
Posted Tuesday, October 09, 2012 7:20 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:49 AM
Points: 739, Visits: 2,470
Recovery mode refers to the transaction log, which is not the same as server logs.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1370386
Posted Tuesday, October 09, 2012 7:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 1:44 AM
Points: 34, Visits: 211
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
Post #1370396
Posted Tuesday, October 09, 2012 7:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
What do you mean by 'activity log'?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1370400
Posted Tuesday, October 09, 2012 7:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 1:44 AM
Points: 34, Visits: 211
Sorry SQl server logs
Post #1370403
Posted Tuesday, October 09, 2012 8:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 1:44 AM
Points: 34, Visits: 211
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
Post #1370407
Posted Tuesday, October 09, 2012 8:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 12, 2014 9:02 PM
Points: 351, Visits: 887
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.
Post #1370412
Posted Tuesday, October 09, 2012 8:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
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

Post #1370420
Posted Tuesday, October 09, 2012 8:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 1:44 AM
Points: 34, Visits: 211
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!!
Post #1370435
Posted Tuesday, October 09, 2012 5:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
Not into the SQL Server error log (text file).


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1370631
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse