I am adding my steps here, We are not supposed to change the core files but I didn’t see any solution to it otherwise… If you can come up with something better, please share. 
First, in CDbCriteria add a property that will store the total Item count
public $totalItemCount;
Then, in CActiveDataProvider fetchData() method, add this after $criteria=clone $this->getCriteria();
$criteria->totalItemCount = $this->getTotalItemCount();
In CActiveFinder createCommand($builder) method, change this line $sql=$builder->applyLimit($sql,$this->limit,$this->offset);
To
$sql=$builder->applyLimit($sql,$this->limit,$this->offset,$this->totalItemCount);
Finally, in CMssqlCommandBuilder replace applyLimit and rewriteLimitOffsetSql methods with this
public function applyLimit($sql, $limit, $offset, $totalItemCount = null )
{
$limit = $limit!==null ? intval($limit) : -1;
$offset = $offset!==null ? intval($offset) : -1;
if ($limit > 0 && $offset <= 0) //just limit
$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);
else if($limit > 0 && $offset > 0)
$sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset, $totalItemCount);
return $sql;
}
protected function rewriteLimitOffsetSql($sql, $limit, $offset, $totalItemCount = null )
{
$fetch = $limit+$offset;
if ( $totalItemCount !== null && $limit + $offset > $totalItemCount )
{
$limit = $totalItemCount - $offset;
$fetch = $limit+$offset;
}
$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $fetch", $sql);
$ordering = $this->findOrdering($sql);
$orginalOrdering = $this->joinOrdering($ordering, '[__outer__]');
$reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering), '[__inner__]');
$sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner__] {$reverseOrdering}) as [__outer__] {$orginalOrdering}";
return $sql;
}