Caveman's Blog

My commitment to learning.

Posts Tagged ‘Sql Server

Start/Stop SQL Server Replication Agent using TSQL

leave a comment »

On a recently client engagement we ran into an issue with a nightly SQL Server job. The job was unable to generate data for a certain warehouse database. As it tuns out to be via process of elimination, I was able to figure out that there were some SQL distribution agents that were interfering with the nightly SQL job that caused the issue. I have stopped the distribution agents and ran the job manually to able to successfully populate the data, followed by re-starting the agents. The process of stopping and starting was done using the SQL Server Management studio.

Now that I knew how to populate the data, I had to automate the process of stopping and starting the distribution agents before and after running the nightly SQL job. Here are the stored procedures that can be used to achieve the same:

--STOP the Distribution Agent:
sp_MSstopdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db

--START the Distribution Agent:
sp_MSstartdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db</pre>
  • @publisher is the name of the Server that is publishing
  • @publisher_db is the database of the publisher server
  • @publication is the name of the table/object that is getting published
  • @subscriber is the name of the subscriber server
  • @subscriber_db is the name of the subscriber database

The above commands have to be executed in the publisher server against the ‘distribution’ database. Here is an example:

exec distribution.dbo.sp_MSstartdistribution_agent @publisher  = 'PUBLISHERSERVER',
@publisher_db   = 'PUBLISHERDB',
@publication    = 'TABLE_EMPLOYEE',
@subscriber     = 'SUBSCRIBERSERVER',
@subscriber_db  = 'WAREHOUSEDB'

Written by cavemansblog

March 12, 2012 at 11:21 pm

SQL Server: Index defragmentation

leave a comment »

DBCC INDEXDEFRAG: Index defragmentation is the process that reduces the amount of index fragmentation.This process does not hold any table locks long term while defragmenting an index, hence does not block running queries or updates. This is unlike the index building process or the re-indexing process when a table lock is enforced. The underlying table cannot be modified, truncated, or dropped while an online index operation is in process.To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has been completed; however, the log can be backed up during the index operation. It is not suggested to use on very fragmented indexes.  Here is an example of MSDN as to what happens when an index is defragmented:

Figure: Index defragmentation in action [1].

DBCC DBREINDEX: Faster than dropping and re-creating, but during rebuilding a clustered-index, an exclusive table lock is put on the table, preventing any table access by users. And during rebuilding a non-clustered index a shared table lock is put on the table, preventing all but SELECT operations to be performed on it.

REBUILD INDEX: Best performance, but places an exclusive table lock on the table, preventing any table access by users and shared table lock on the table, preventing all but SELECT operations to be performed on it.

Note: According to Microsoft best practices, index defragmentation is most effective when an index has at least 8 pages. DBCC INDEXDEFRAG is one of the deprecated command. The equivalent contemporary command is ALTER INDEX REORGANIZE

Here is an award winning solution for the SQL Server Indexes and Statistics maintenance. You can download IndexOptimize procedure and use it as a comprehensive solution for this purpose. The SQL Server Maintenance Solution website seems to me like a must have for all DBA’s. Following is the syntax for rebuilding or reorganizing indexes with fragmentation on all user databases

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30

1. Microsoft SQL Server 2000 Index Defragmentation Best Practices
2. SQL Server Mainenance Solution

Sql Server: Insert multiple rows with one insert statement

with 2 comments

Typical style of scripting the insert of multple rows into a table is by writing multiple insert statments as follows:

declare @TempTable table(myData varchar(50))

insert into @TempTable(myData)
values('data 1')
insert into @TempTable(myData)
values('data 2')
insert into @TempTable(myData)
values('data 3')
insert into @TempTable(myData)
values('data 4')

Following are two ways of doing the same with less script code. The first style is supported uses comma seperated values:

declare @TempTable table(myData varchar(50))

insert into @TempTable(myData)
values('data 1'),
('data 2'),
('data 3'),
('data 4')

This style of scrpting uses UNION ALL:

declare @TempTable table(myData varchar(50))
insert into @TempTable(myData)
select 'data 1'
union all
select 'data 2'
union all
select 'data 3'
union all
select 'data 4'

Written by cavemansblog

July 11, 2011 at 11:51 am

SQL Server: Delete duplicates from a table.

with one comment

This article discusses how to remove duplicate rows from a table in a SQL Server database. For example consider the following “employee” table of variable type. In this table, empId is the row identifier, empName and Salary are employee details.

--Create a temporary table variable
declare @employee Table(empId int identity(1,1), empName Varchar(50), Salary int)
declare @empName varchar(50)
declare @Salary int

Let us add a few rows in this table. Since we have not defined a unique key in this table, there is a scope for multiple records to have similar empName and Salary values. The following script will insert 7 rows into the employee table, with 3 duplicate rows. You can see from the screen capture below that the rows 5, 6 and 7 have duplicate data.

-- Insert dummy data(empName, Salary)
Insert into @employee Values('test1', 1000)
Insert into @employee Values('test2', 2000)
Insert into @employee Values('test3', 3000)
Insert into @employee Values('test4', 4000)
Insert into @employee Values('test1', 1000)
Insert into @employee Values('test1', 1000)
Insert into @employee Values('test3', 3000)

There are many approaches for deleting the duplicate data in a table. I am about to demonstrate one of the ways using a cursor to de-duplicate the table. Following is the process of de-duplication:

1. The first step of the process is to identify the data that is redundant.

2. Followed by fetching all the identifiers of each of the distinct set of data.
3. Then delete all but one of the rows identified by the primary keys that was fetched in the last step.
4. Repeat the steps 2 and 3 on each of the unique set of data from step 1 using the cursor.

-- delete the duplicates
FOR select distinct a.empName, a.Salary from  @employee a
where 1 < (select count(*) from @employee b where a.empName = b.empName and a.Salary = b.Salary)

OPEN my_cursor
FETCH NEXT FROM my_cursor into @empName, @Salary


delete from @employee where empName = @empName and Salary = @Salary and empId not in
(select top 1 empId from @employee where empName = @empName and Salary = @Salary)

FETCH NEXT FROM my_cursor into @empName, @Salary

close my_cursor
deallocate my_cursor

Following is a screen capture of the table after the duplicates have been removed:

Tip: The following script can be used to get a list of duplicate tables in a database. The duplication in this case is assumed to be caused by the Schema. The same script can be tweaked easily to identify other SQL Server duplicate objects.

select a.Table_Schema, a.Table_Name from  INFORMATION_SCHEMA.TABLES a
where 1 < (select count(*) from INFORMATION_SCHEMA.TABLES b where a.Table_Name = b.Table_Name)

SQL Server: Maximum number of indexes per table

leave a comment »

One of the favorite questions in a technical interviews seems to be: What is the maximum number of indexes for a table in the SQL Server database? Following is a breakdown of the maximum number of indexes by different versions of SQL Server. I am yet to come across a scenario when a table needed to have huge number of indexes like mentioned in the listing below.

Maximum Sizes
Object SQL Server 7.0 SQL Server 2000 SQL Server 2005 SQL Server 2008
Clustered Index 1 1 1 1
Non-clustered Index 249 249 249 999


1. Maximum Capacity Specifications
2. Maximum Capacity Specifications for SQL Server 2005
3. Maximum Capacity Specifications for SQL Server

Written by coolgirlsblog

March 25, 2011 at 4:18 pm