Features of SQL Server Management Studio and tools

In SQL Server 2005,  Management studio works as central management tool for accessing different components of Sql server like managing database, developing, configuring Sql server. You can also access different graphical tools associated with sql server like sql server profiler, Database Tuning Adviser (DTA) using SQL Server management studio.  Also it provides query editor for writing script ...

SQL Server table partitioning

Table partition is the new feature introduced in sql server 2005 to improve the performance of the sql server. This will help you to split large table into partitions; we can place these partitions either on the same drive or different drive/ Hard Disk. Before going for the partitioning, identify the column that will participate ...

SQL SERVER – Transaction replication out of sync and subscription expiration

Usually Monday morning doesn’t work for DBA most of the issues are come on that day. One more issue related to Transaction replication in the morning task, we use to check the replication jobs status and other activity which was scheduled in night time. Also use to check the replication status for the confirmation. We ...

SQL Server Database Migration vs Upgrade

It is very confusing sometimes what is sql server migration and upgrade. Its favorite question of many clients when you go for interviews, actually they want you to get confuse, if you don’t know about migration and upgrade. They want to find out whether you have working experience on such a scenario or not. Let’s ...

SQL Server 2005 – Internal Query Processor Error

Today morning while health check activity, we found that some jobs are failed in night with some unseen error related to query processor as shown below Executed as user: NT AUTHORITY\SYSTEM. Internal Query Processor Error: The query processor encountered an unexpected error during execution. [SQLSTATE 42000] (Error 8630). The step failed. We tried executing job ...

SQL Server 2005 – Rebuild all indexes in table

Indexes always helps to improve query performance in table, clustered indexes and non clustered indexes are applied on table to faster retrievable of data. Stats of indexes goes down whenever they are accessed by queries or DML (Data Manipulation language ) operations like insert, update and delete. To improve performance, One has to rebuild indexes. How to Rebuild table ...

How to configure database mail in sql server 2005-08 1

Database mail services provides the good option of sending mail to client using SMTP server (Simple Mail transfer Protocol). In earlier versions of sql server this was known as SQL Mailing service which use MAPI(Messaging Application Programming Interface) for sending mails. Service broker need to be running in sql server 2005 for configuring the Database ...

Simple SQL SERVER view with example

What is sql server view ? Views are also known as virtual table in sql server that contain columns and rows. Multiple queries are combined together to define sql server view, it uses the data from one or more tables in sql database. We can insert data through view into table that you have seen ...

SQL Server – How to add column in table

For adding column in table of sql server, We can make use of alter table statement  i.e  alter table [tablename] add [columnname] datatype.  Here datatype could be anything like char, int, varchar, nvarchar that is purely depends upon data that will store in column. Lets take a scenario to understand how to alter table on UAT server. In ...

SQL Server – Move sql database to another drive 2

We can move sql server system database and user database to another drive, in case SAN disk is full then copy the sql server mdf and ldf to another drive that has more drive space. Sql server will not allow to move database files while online.  We have to put database offline and then only it ...