IT Infrastructure blog

January 24, 2013

Rebuild Index in MS SQL 2008

Filed under: MS SQL Server — Akther @ 4:52 pm
Tags: , , , ,

This post would like to describe how to rebuild indexes in MS SQL 2008 R2.

There are many ways to rebuild indexes in SQL. Here we will see how to use Maintenance Plan tool to rebuild whole indexes in a particular database or whole databases in SQL server instance. It’s simple but need to take care little bit.

SQL Server Management Studio–> Management  –> Maintenance Plans –> Maintenance Plan Wizard

1

 

 

 

 

 

Launch the wizard and click next. Give the Name of the Maintenance Plan wizard. Set schedule if you want to run every week or according to your choice.

2

 

 

 

 

 

 

 

 

 

Click Next and select Check Database Integrity and Rebuild Index tasks.

3

 

 

 

 

 

 

 

 

 

Check Database integrity is an useful choice before do the rebuild because if database has any integrity problem then no need to do the rebuild of index.

One thing you have to notice that Rebuild index task is  high CPU and very I/O intensive and it locks the database resources as well. So better to do this on off business hours.

Rebuild index task will do the update statistics and reorganize task. So no need to select these two tasks if you are doing a rebuild index. It’s a common mistake we could see while using Maintenance Plan Wizard that select all the tasks even if not necessary. It will slow down the performance  of your SQL server.

4

 

 

 

 

 

 

 

 

 

Click Next –> Select which database you want to do the Database Integrity. Or select whole database. This step will repeat to Rebuild index as well.

5

 

 

 

 

 

 

Select the log file location

6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Next screen will finish the task

7

 

 

 

 

 

 

 

 

 

This task will run based on the schedule you have mentioned. Or you can trigger the task when you want from Maintenance Plan section.

8

Advertisements

June 10, 2012

MS SQL Connectivity Error – A network-related or instance-specific error

I have installed SQL Managemant studio in my windows XP machine. I was trying to connect one of my SQL server. OOPS …the below error pop up

 

 

 

 

 

 

Below three steps i found to resolve this issue.

1) Check the SQL Server Browser service is started in SQL server ( If not start this service. you have to restart the SQL server Service as well)

 

 

2) Check TCP/IP is enabled in SQL Server Configuration Manager ( Start –> Programs –> SQL Server 2008 –>Configuration Tools. If it’s disabled make it enable.

 

 

 

 

 

3) Give the Servername\instance name while connecting from SQL Management Studio.

 

 

 

 

 

 

 

 

 

 

Hope you will be able to connect the SQL server now. I was able to connect when i follow above 3 steps.

July 14, 2011

Cannot see newly added disks in MS SQL cluster

Filed under: MS Cluster,MS SQL Server — Akther @ 2:55 pm

There is a request from DBA team to add one disks from storage and assign to MS SQL 2008 server.  I have created a iSCSCi LUN and mount it to the server. Now we can able to see the newly created disk in Server.

After some time DB admin called me and said they cannot see that newly created disks in SQL Management studio while they try to take a backup of one particular database. Here is the screenshot. I have mount the drive as U but it wasn’t showing in SQL backup windows.

Ok, Need to troubleshoot further. MS SQL servers are configured in Cluster. I went to Server manager–> Features–> Failover cluster manager–> Storage–> Right click–> Add disk

Once we add we can see it this disks in Available storage area. Now we need to move this disk in to cluter resource. In this case SQL Server ( MS SQL SERVER) is the service resource

Could able to see this disks in SQL server cluster resource

 

Not yet done. still need to add some more steps to show this disks in to SQL Management studio backup screen

Go to Fail over cluster manager –> serives and applications–> SQL Server–> Other resources–> SQL Server –> Right click–> Properties

Select the dependencies tab–> select the disk and click insert

That’s it. Now we can see this disks in cluster as well as in backup windows

Here is my U drive and DBA team is happy. Thanks for Rafeeq to assist us to troublehoot this.

Create a free website or blog at WordPress.com.