Microsoft SQL Server Future Editions | A complete set of enterprise-ready technologies and tools

Microsoft SQL Server Code Name "Denali" CTP1 is available for download now.
You can download it from Microsoft SQL Server Future Editions A complete set of enterprise-ready technologies and tools

Expired Subscriptions in SQL Server

Hi all,
Currently I am working on a huge setup of servers which utilizes replication quite extensively. Each of these servers has about 4 to 10 publication and around 10 to 40 Subscribers running 24x7. An issue, I was facing quite regularly on many subscribers was that they tend to expire and then the second step of the replication job simply goes in the retry mode.
I investigated the issue by first finding the replication and then going into one of the tables in 'Distribution' database of my distributor. The table is dbo.MSsubscriptions. It is a very usefull table with the complete info about all the Subscriptions configured on this distributor. It contains the following columns of our interest -
publisher_database_id, publisher_db, subscriber_db and status
if you look for the value of the status column corresponding to the right Publisher and Subscriber Database, it would be either 0, 1 or 2.
Status 0 = Inactive,
Status 1 = Subscribed and
Status 2 = Active.
Now all you have to do is to change this value for it to start working normally. You can use a simple update statement like this -
UPDATE distribution.msdb.MSsubscriptions
SET status = 2
WHERE publisher_db = 'MyPubDB' and
subscriber_db = 'MySubscriberDB' and
status = 0
After running this query wait for a couple of minutes for the replication job to retry and it should be running smoothly. I am still trying to find out the way to completely eliminate this problem so that subscriptions do not expire at all. Will update you soon on that. In the meantime, if you have a solution, you can post it in the comments.
Hope it helps.

Can not map a login from Windows

Today, I was trying to create a Login into SQL Server 2008 instance. The group that I wanted to be mapped was "Administrators" Group on a machine name "DBServer".

I tried to create the login, but recieved the error message --

"Windows NT user or group Administrators not found".

It was a bit strange because we all know that it exists on all the windows machines. Then I referred to a very helpful Microsoft documentation regarding that, whose link I am providing here

http://support.microsoft.com/kb/324321/en-us

If you go through that documentation, it says that the local Administrators group should be mapped as "BUILTIN\Administrators", and not like "MachineName\Administrators".

sp_replcmds Error

Hello all,
I was setting up a transacitonal replication today, when I came accross this error with my LogReader Agent. Both the Publisher and Subscriber setups gave me a success message and then when I checked the data on the subscriber, it wasn't there.
I tried to find the reason for that with the help of job logs and the error it gave me was ---
"The process could not execute 'sp_replcmds' on 'MyMachineName'.
Status: 0, code: 15517, text: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. '

The actual solution to the problem was far away from what it reflected in the error messsage. The real problem was that my published database did not had any owner assigned to it. (and may be SQL Server is using the owner account to execute sp_replcmds in the background which if doesn't exists, defaults to dbo. SQL Server does it quite often!!!!) As soon as I assigned the owner to my database, stopped the service and started it again.... bingo... it started working.
Hope, the post is going to be usefull for you as well.

A complete insigt on SQL Server Snapshots

The snapshots are a great new feature of SQL Server 2005. They allow you to keep a static, read only copy of the data in your database without any admin overhead. It can be used for a static copy of the database, reporting database or for testing purpose. It creates the copy of the data files of your original database but without occupying the same amount of disk space. As and when the data is modified in the original database, it is going to copy that extent (a collection of 8 datapages) in to the snapshot file created, otherwise the read request are served from the original databases.

I tested the database snapshots and found some interesting results which I want to share with you. I used the sample AdventureWorks database shipped with SQL Server 2005. I used the following statement to create the database snapshot

CREATE DATABASE AdvWorks_Snaphot

ON

(

NAME = 'AdventureWorks_Data', FILENAME = 'C:\SnapFolder\Adv_Snap.ss'

)

AS SNAPSHOT OF AdventureWorks

If you have more than one data files in your database, just specify all the datafiles e.g.

CREATE DATABASE AdvWorks_Snaphot

ON

(

NAME = 'AdventureWorks_Data1', FILENAME = 'C:\SnapFolder\Adv_Snap1.ss'

),

(

NAME = 'AdventureWorks_Data2', FILENAME = 'C:\SnapFolder\Adv_Snap2.ss'

),

(

NAME = 'AdventureWorks_Data3', FILENAME = 'C:\SnapFolder\Adv_Snap3.ss'

)

AS SNAPSHOT OF AdventureWorks

Now if you look at the SIZE of this file in windows explorer, it is the same size as the original database file, which in our case is 191 MB. Now, if you look at the "SIZE ON DISK" value, it is very low. In my case it was just 2.12 MB. That means that the snapshot requires a very small fraction of the original database size.

Now, if you just modify one row in one of the tables, you will find that the "SIZE ON DISK" value has just increased by 64 KB, which is the size of an extent.

Now comes the interesting part. The disk that I used for this test had a very small amount of free space. I started deleting a lot of tables from the original database so that all the data now starts flowing into the snapshot. after sometime, I recieved an error message stating that there was an error deleting the data from the database because one the dependent snapshot did not had free space in it.

I thought, now I wont be able to delete any more data from the database, but opposite to my belief, the next time I executed the delete statement, it ran without any errors. Then I checked the state of my snapshot and it was in the "suspect mode". To handle this scenario, I created some more space in the drive to hold the data for snapshot, but even after creating the space the snapshot didn't came out of the suspect mode.

So the moral of the story is that, you can have a snapshot on a disk with smaller amount of space as compared to your database, but when the space is full, the snapshot recovery doesn't seems to be possible.

I hope, this article will clear a lot of concepts regarding database snapshots.

Finding the Nth Row from a table

Hi all,

when you are selecting the data from a table and you are looking for the topmost or top N rows, getting that is easy with the TOP clause. But, for getting the Nth row in the table there is no direct method. But there are a couple of workarounds to it, that I am giving here.

The first is the old man's method who likes using the normal SQL query capabilities. (Could be a bit inefficient). Here is a method to get the 18th row from the table Sales.SalesOrderDetail, which you can find in the famous AdventureWorks database. If you dont have it, either download it, or change the query to use your table and column names.

  Select Top 1 LineTotal FROM
(Select Top 1 LineTotal FROM
(Select Top 18 LineTotal From Sales.SalesOrderDetail ORDER BY LineTotal Desc) AS a
Order By LineTotal) as b

The other method is using the Row_Number() function, which assigns the sequential numbering to the rows on the basis of the Order By clause. We have used the CTE here because the Ranking functions can not be used in the where clause. Have a look.

WITH MyCTE
AS
(
    SELECT DepartmentID, Name, ROW_NUMBER() OVER(ORDER BY DepartmentID) AS MyRank
    FROM HumanRescources.Department
)
SELECT * FROM MyCTE
WHERE MyRank = 18

I hope that you find the post useful.

SQL Server View Constraint Removed

Hi all,

You all are familiar with the views in SQL Server. They serve a lot of purposes, but they have there limitations too. One of the limitations of the view is that you can not have an ORDER BY statement a part of the query in SQL Server. I was never able to understand the logical reason for this constraint.

One of my students Paulo Underland V Rocha (phew..... long name)  from Norway. He wanted to avoid this problem and  we were looking at all the different methods to implement ordering the data inside the view (or in any object which can return a table).

But, if you read the error message of SQL Server when you try to do something like this

CREATE VIEW  vContactListByEmail

AS

SELECT  EMailAddress, FirstName, LastName

FROM Person.Contact

ORDER BY FirstName

Go

is

Msg 1033, Level 15, State 1, Procedure abc, Line 5

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

So, by principal you can not use it in views functions, derived tables.....

but then we tried using this

CREATE VIEW  vContactListByEmail

AS

SELECT TOP 100 PERCENT EMailAddress, FirstName, LastName

FROM Person.Contact

ORDER BY FirstName

Go

since, if you know, you can use ORDER BY if you use the top clause. This worked, but to our surprise, it didnt showed us the data in the sorted order. But it was showing us the sorted data if we used any other value except 100. So finally, we used this query

CREATE VIEW  vContactListByEmail

AS

SELECT  TOP 99.999999999 EMailAddress, FirstName, LastName

FROM Person.Contact

ORDER BY FirstName

Go

and this one finally worked and gave us all the rows from the table in the sorted order from the table.

Simple isn't it ?