War Room: Avoid Silly DBA Mistakes

INTERVIEW PREPARATION IN 30 MINs – Read Following wires for a quick interview preparation.

REBUILD INDEX ONLINE OR OFFLINE??

How to Handle Sexual Harassment at Workplace

ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON)
OR
ALTER INDEX IndexName ON TableName REBUILD

I am just scared why some DBAs don’t take care of small things. I asked one of my DBA to REBUILD the indexes of a table which is nothing but HBI data. What command will you use doing that? Definately ALTER INDEX IndexName ON TableName REBUILD. But wait. Do you think that users would be able to access the table when the rebuild process is going on?

Either inform the users that you are going to have the DB offline (I mean downtime here) or do this WITH (ONLINE=ON). Directly applying the REBUILD command can cause a significant outage.

Just be careful….it goes like this. If Clustered index is rebuilt OFFLINE then any user won’t be able to access the data of the table on which operation is being performed. The reason is SCHEMA lock on the table. If non clustered index is rebuilt then SELECT query can be performed but any update/insert and delete won’t work. So Just be careful…

Following blogs will help you prepare for IT Industry Interviews and Walk-Ins.

  1. SQL Server DBA “Interview Questions And Answers”
  2. MS SQL Corruption: “Msg 2627 – Violation of PRIMARY KEY constraint”
  3. SQL Server 2008 R2 – Foreign Key – DELETE CASCADE and DELETE UDPATE
  4. MSSQL Server – DBCC LOGINFO – Status 2. Log File FULL. Verify log_reuse_wait_desc
  5. SSRS Report – Adding Header and Footer

Regards,
Online MSSQL Classes @ http://tuitionaffordable.webstarts.com

Advertisements

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s