Do While in SQL: February 2012

Saturday, February 25, 2012

Distribution Server - Replication

What happens when Distributor Server goes down in Transactional
Replication, no corruption (power , hardware failure)??
Thanks,
JohnCan you bring the distributor back up or the distribution database is lost? Before you bring the distributor back up, you need to shut down all subscriptions. If you can restart the distributor, you need to stop the logreader right after you have started the SQL Agent on the distributor. Open the subscription first to see if they can be synced. If the subscription doesn't errored out, open the logreader. Good luck!

Distribution Question

Hello
I'm just starting to look at replication for a job I have and I have a
question about setting up a distributor.
1. Can i have multiple distributors?
2. Is it possible to define which distributor is used for a certain
publication?
3. Can i set up the distributor to also be on the subscriber? (external to
the publisher)
Thanks for any and all help!
One distributor per publisher is what we can use.
The distributor can indeed be on the subscriber or the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Distribution Problem

I have a crystal reports porject that runs great on my computer, but when I put it on another computer, it has an unhandled exception. It won't run on any computer that doesn't have visual studio installed. What do I do to fix this?You have to search for a list of the dlls that need to be installed. CR 8.5 came with a file called runtime.hlp. If you can't find that file, try Crystal's website:
http://support.businessobjects.com/search/advsearch.asp

Distribution of SQLServer Database

Hi,
We're in the process of developing a .NET application which will use a
SQLServer database. When installed/deployed the application will query this
database. I assume each time the application is installed the SQL Server
database will need to be installed. Is this the case - sorry I know it's a
dumb question but I am a complete SQL Server novice. If this is the case then
which licensing option should we go for?
Any help would be greatly appreciated.
-Kim
Hi Kim
There is a version of SQL Server which is designed to be deployed with user
programs. It's called MSDE & you can read more about it here:
http://msdn.microsoft.com/library/de...ar_ts_67ax.asp
There are limits on it's growth (2Gb per db, max 2 CPUs etc) & if you exceed
these, you'd probably be looking at SQL Server Standard Edition.
Regards,
Greg Linwood
SQL Server MVP
"kim d" <kimd@.discussions.microsoft.com> wrote in message
news:6DFA23FC-DADB-48A1-BF7B-03EF46AEE5D8@.microsoft.com...
> Hi,
> We're in the process of developing a .NET application which will use a
> SQLServer database. When installed/deployed the application will query
> this
> database. I assume each time the application is installed the SQL Server
> database will need to be installed. Is this the case - sorry I know it's a
> dumb question but I am a complete SQL Server novice. If this is the case
> then
> which licensing option should we go for?
> Any help would be greatly appreciated.
> -Kim
|||It also depends on what you mean about deplying SQL... YOu might wish all of
the clients to share data from a single database, or you might wish to
install the MSDE version on each client, so there is no data sharing...
In either case, at least you will have to the the SQL Connectivity piece on
the client.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kim d" <kimd@.discussions.microsoft.com> wrote in message
news:6DFA23FC-DADB-48A1-BF7B-03EF46AEE5D8@.microsoft.com...
> Hi,
> We're in the process of developing a .NET application which will use a
> SQLServer database. When installed/deployed the application will query
this
> database. I assume each time the application is installed the SQL Server
> database will need to be installed. Is this the case - sorry I know it's a
> dumb question but I am a complete SQL Server novice. If this is the case
then
> which licensing option should we go for?
> Any help would be greatly appreciated.
> -Kim
|||Ideally clients would share data from a single database but there would be
the capability to work 'off-line' - in other words users could access the
database even while not explicitly connected to the server. I presume in this
case it would require a separate MSDE installation on each client as well as
on the server database. Does MSDE support this type of behaviour?
"Wayne Snyder" wrote:

> It also depends on what you mean about deplying SQL... YOu might wish all of
> the clients to share data from a single database, or you might wish to
> install the MSDE version on each client, so there is no data sharing...
> In either case, at least you will have to the the SQL Connectivity piece on
> the client.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "kim d" <kimd@.discussions.microsoft.com> wrote in message
> news:6DFA23FC-DADB-48A1-BF7B-03EF46AEE5D8@.microsoft.com...
> this
> then
>
>
|||Great, so does this mean that we would only need to purchase a SQLServer
developers license?
"Greg Linwood" wrote:

> Hi Kim
> There is a version of SQL Server which is designed to be deployed with user
> programs. It's called MSDE & you can read more about it here:
> http://msdn.microsoft.com/library/de...ar_ts_67ax.asp
> There are limits on it's growth (2Gb per db, max 2 CPUs etc) & if you exceed
> these, you'd probably be looking at SQL Server Standard Edition.
> Regards,
> Greg Linwood
> SQL Server MVP
> "kim d" <kimd@.discussions.microsoft.com> wrote in message
> news:6DFA23FC-DADB-48A1-BF7B-03EF46AEE5D8@.microsoft.com...
>
>
|||In this case, you'd be looking at Personal Edition, which is on the SS
media. Then you replicate the Central Database to your clients, which can
then go "offline."
That's what this edition was created for. It has restrictions, but
different than those of MSDE, which is more an Access database replacement.
SS Personal Edition is more for business, semi-connected users, like sales
staff.
Sincerely,
Anthony Thomas

"kim d" <kimd@.discussions.microsoft.com> wrote in message
news:7E0720DF-6224-4B91-A92E-5D4C542D7FC3@.microsoft.com...
Ideally clients would share data from a single database but there would be
the capability to work 'off-line' - in other words users could access the
database even while not explicitly connected to the server. I presume in
this
case it would require a separate MSDE installation on each client as well as
on the server database. Does MSDE support this type of behaviour?
"Wayne Snyder" wrote:

> It also depends on what you mean about deplying SQL... YOu might wish all
of
> the clients to share data from a single database, or you might wish to
> install the MSDE version on each client, so there is no data sharing...
> In either case, at least you will have to the the SQL Connectivity piece
on[vbcol=seagreen]
> the client.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "kim d" <kimd@.discussions.microsoft.com> wrote in message
> news:6DFA23FC-DADB-48A1-BF7B-03EF46AEE5D8@.microsoft.com...
> this
a
> then
>
>

Distribution of SQLServer Database

Hi,
We're in the process of developing a .NET application which will use a
SQLServer database. When installed/deployed the application will query this
database. I assume each time the application is installed the SQL Server
database will need to be installed. Is this the case - sorry I know it's a
dumb question but I am a complete SQL Server novice. If this is the case then
which licensing option should we go for?
Any help would be greatly appreciated.
-KimHi Kim
There is a version of SQL Server which is designed to be deployed with user
programs. It's called MSDE & you can read more about it here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_67ax.asp
There are limits on it's growth (2Gb per db, max 2 CPUs etc) & if you exceed
these, you'd probably be looking at SQL Server Standard Edition.
Regards,
Greg Linwood
SQL Server MVP
"kim d" <kimd@.discussions.microsoft.com> wrote in message
news:6DFA23FC-DADB-48A1-BF7B-03EF46AEE5D8@.microsoft.com...
> Hi,
> We're in the process of developing a .NET application which will use a
> SQLServer database. When installed/deployed the application will query
> this
> database. I assume each time the application is installed the SQL Server
> database will need to be installed. Is this the case - sorry I know it's a
> dumb question but I am a complete SQL Server novice. If this is the case
> then
> which licensing option should we go for?
> Any help would be greatly appreciated.
> -Kim|||It also depends on what you mean about deplying SQL... YOu might wish all of
the clients to share data from a single database, or you might wish to
install the MSDE version on each client, so there is no data sharing...
In either case, at least you will have to the the SQL Connectivity piece on
the client.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kim d" <kimd@.discussions.microsoft.com> wrote in message
news:6DFA23FC-DADB-48A1-BF7B-03EF46AEE5D8@.microsoft.com...
> Hi,
> We're in the process of developing a .NET application which will use a
> SQLServer database. When installed/deployed the application will query
this
> database. I assume each time the application is installed the SQL Server
> database will need to be installed. Is this the case - sorry I know it's a
> dumb question but I am a complete SQL Server novice. If this is the case
then
> which licensing option should we go for?
> Any help would be greatly appreciated.
> -Kim|||Ideally clients would share data from a single database but there would be
the capability to work 'off-line' - in other words users could access the
database even while not explicitly connected to the server. I presume in this
case it would require a separate MSDE installation on each client as well as
on the server database. Does MSDE support this type of behaviour?
"Wayne Snyder" wrote:
> It also depends on what you mean about deplying SQL... YOu might wish all of
> the clients to share data from a single database, or you might wish to
> install the MSDE version on each client, so there is no data sharing...
> In either case, at least you will have to the the SQL Connectivity piece on
> the client.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "kim d" <kimd@.discussions.microsoft.com> wrote in message
> news:6DFA23FC-DADB-48A1-BF7B-03EF46AEE5D8@.microsoft.com...
> > Hi,
> > We're in the process of developing a .NET application which will use a
> > SQLServer database. When installed/deployed the application will query
> this
> > database. I assume each time the application is installed the SQL Server
> > database will need to be installed. Is this the case - sorry I know it's a
> > dumb question but I am a complete SQL Server novice. If this is the case
> then
> > which licensing option should we go for?
> > Any help would be greatly appreciated.
> > -Kim
>
>|||Great, so does this mean that we would only need to purchase a SQLServer
developers license?
"Greg Linwood" wrote:
> Hi Kim
> There is a version of SQL Server which is designed to be deployed with user
> programs. It's called MSDE & you can read more about it here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_67ax.asp
> There are limits on it's growth (2Gb per db, max 2 CPUs etc) & if you exceed
> these, you'd probably be looking at SQL Server Standard Edition.
> Regards,
> Greg Linwood
> SQL Server MVP
> "kim d" <kimd@.discussions.microsoft.com> wrote in message
> news:6DFA23FC-DADB-48A1-BF7B-03EF46AEE5D8@.microsoft.com...
> > Hi,
> > We're in the process of developing a .NET application which will use a
> > SQLServer database. When installed/deployed the application will query
> > this
> > database. I assume each time the application is installed the SQL Server
> > database will need to be installed. Is this the case - sorry I know it's a
> > dumb question but I am a complete SQL Server novice. If this is the case
> > then
> > which licensing option should we go for?
> > Any help would be greatly appreciated.
> > -Kim
>
>|||In this case, you'd be looking at Personal Edition, which is on the SS
media. Then you replicate the Central Database to your clients, which can
then go "offline."
That's what this edition was created for. It has restrictions, but
different than those of MSDE, which is more an Access database replacement.
SS Personal Edition is more for business, semi-connected users, like sales
staff.
Sincerely,
Anthony Thomas
"kim d" <kimd@.discussions.microsoft.com> wrote in message
news:7E0720DF-6224-4B91-A92E-5D4C542D7FC3@.microsoft.com...
Ideally clients would share data from a single database but there would be
the capability to work 'off-line' - in other words users could access the
database even while not explicitly connected to the server. I presume in
this
case it would require a separate MSDE installation on each client as well as
on the server database. Does MSDE support this type of behaviour?
"Wayne Snyder" wrote:
> It also depends on what you mean about deplying SQL... YOu might wish all
of
> the clients to share data from a single database, or you might wish to
> install the MSDE version on each client, so there is no data sharing...
> In either case, at least you will have to the the SQL Connectivity piece
on
> the client.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "kim d" <kimd@.discussions.microsoft.com> wrote in message
> news:6DFA23FC-DADB-48A1-BF7B-03EF46AEE5D8@.microsoft.com...
> > Hi,
> > We're in the process of developing a .NET application which will use a
> > SQLServer database. When installed/deployed the application will query
> this
> > database. I assume each time the application is installed the SQL Server
> > database will need to be installed. Is this the case - sorry I know it's
a
> > dumb question but I am a complete SQL Server novice. If this is the case
> then
> > which licensing option should we go for?
> > Any help would be greatly appreciated.
> > -Kim
>
>

Distribution of SQL Server 2005 DB with Full-Text Search

Using SQL Server 2005 Express (Advanced SP2) I have created a Full-Text Search application in VB for distribution on CD for single PCs. Works fine on my local machine during development.

Although the SQL Server 2005 Express edition can be distributed freely, it does not seem to support Full-Text searches in the distributed version. Is this true? Or am I missing something with my deployment?

If I need another version of Sql Server for distribution of a Full-Text Search app, how do I go about obtaining the proper DB and permission for distribution? The DB size is about 600 MB.

Oh yes, it does support those. YOu will have to install the fulltext search service with the setup and it should work then. If you are installing it using unattended setup file, make sure that you include the feature of fulltext search. There is a sample within the template file which shows how to do it.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens,

Great to hear that it does support full text search when distributed.

I have installed Sql Server Express 2005 (Adv. SP2) on three machines with full text search enabled during setup. So I think I am getting that part OK.I am not sure I know what you mean by, “There is a sample within the template file which shows how to do it.”

I am able to do full text searches in Management Studio but have never been able to do full-text searches in Visual Basic Express due to the limitation associated with user instance (“Cannot use full-text search in user instance.”)

I have been trying out Visual Studio 2008 Beta 2 and am able to do full-text search in Visual Basic when connecting directly to the DB attached to the server instance.But still get the same error when adding the Pubs DB to the Solution within VB that relies on the User Instance.

Here is a simple app that demonstrates the problem.The full-text search works with the connection to the server instance but not through the User Instance within VB.

Code Snippet

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim strSearch

strSearch = TextBox1.Text

'Added to Solution as existing item and User Instance – does not work

'Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PUBS.MDF;Integrated Security=True;User Instance=True")

'Connected through Sql Server Express and this works!

Dim conn As New SqlConnection("Data Source=OFFICE\SQLEXPRESS;Initial Catalog=Pubs;Integrated Security=True")

Dim ds As New DataSet

conn.Open()

Dim adp As New SqlDataAdapter("Select * From Titles WHERE CONTAINS(Notes, ' """ & strSearch & """ ')", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

End Sub

End Class

I guess if I could distribute the program so that it does not rely on the User Instance during runtime, it would work.Just don’t know how to do that.

So I think I am close to getting the distribution issue resolved, but just don't know what to try next. Thanks for any help you can provide in working this out.

|||

OK, you did not mention that you are using user instances, this feature is NOT supported on a user instance as the error message tells you.

http://msdn2.microsoft.com/en-us/library/ms143684.aspx

|||

Thanks for the info - a bit discouraging but not entirely unexpected. I have been wrestling with this issue for awhile and had basically given up on doing full text search outside Management Studio. Then with the VS 2008 beta I have the option of connecting directly to the server and doing the FTS in Visual Basic.

So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

I am assuming there must be a way to create a FTS program with SQL Server for distribution on a CD or DVD. If SQL Server Express will not do it, what other options do I have? Or is the problem more a matter of the limitation of User Instances within Visual Basic itself?

Thanks again for the info and any further guidance you can provide.

|||

Hi,

"So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?"

No, the only version which supports user instances is Express. YOu probably will need a variation of the planned envrionment.

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

You will need server instances. Install SQL Server Express on the computer and attach the database to the server instance, instead of just attaching it via a user instance. This can be either done using the sp_attachdb command or the Management GUI.|||

I believe that I have attached the DB to the server instance of SQL Server Express using the Management GUI. Otherwise I would not be able to do FTS in Managment Studio. I also think that is why I am able to do FTS on the DB within Visual Studio as attached to the server rather than through a User Instance which does not support FTS.

I am not clear on what happens when I deploy the application including SQL Server 2005 Express. When I have done it before I was able to do regular DB activities, but got he error message about not supporting FTS only when I tried to do the FTS. So I am able to deploy the DB and use it, but it must be in a User Instance which apparently will never support FTS.

Can SQL Server Express be deployed in a way that the distributed version does not have to rely on a User Instance? If not, I don't see how the Express version will ever be usable for FTS in a distributed application.

Thanks for your support in helping me sort this out.

|||

I have been trying to deploy the application with the DB attached to a Server Instance rather than User Instance. No luck yet. I have no problem running the FTS on a Server Instance in VB during development. So it seems to be a matter of getting it deployed properly.

Just so that I am clear as to what I am trying to do, am I correct in assuming that I can deploy Sql Server 2005 Express (Advanced SP2) with my Visual Basic app and it will support Full Text Search so long as the DB is attached to a server instance rather than user instance? Thus I just need to learn how to access the DB with a server instance in the distributed program. Do I understand you correctly on this?

Distribution of SQL Server 2005 DB with Full-Text Search

Using SQL Server 2005 Express (Advanced SP2) I have created a Full-Text Search application in VB for distribution on CD for single PCs. Works fine on my local machine during development.

If I need another version of Sql Server for distribution of a Full-Text Search app, how do I go about obtaining the proper DB and permission for distribution? The DB size is about 600 MB.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens,

Great to hear that it does support full text search when distributed.

Here is a simple app that demonstrates the problem.The full-text search works with the connection to the server instance but not through the User Instance within VB.

Code Snippet

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim strSearch

strSearch = TextBox1.Text

'Added to Solution as existing item and User Instance – does not work

'Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PUBS.MDF;Integrated Security=True;User Instance=True")

'Connected through Sql Server Express and this works!

Dim conn As New SqlConnection("Data Source=OFFICE\SQLEXPRESS;Initial Catalog=Pubs;Integrated Security=True")

Dim ds As New DataSet

conn.Open()

Dim adp As New SqlDataAdapter("Select * From Titles WHERE CONTAINS(Notes, ' """ & strSearch & """ ')", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

End Sub

End Class

I guess if I could distribute the program so that it does not rely on the User Instance during runtime, it would work.Just don’t know how to do that.

So I think I am close to getting the distribution issue resolved, but just don't know what to try next. Thanks for any help you can provide in working this out.

|||

OK, you did not mention that you are using user instances, this feature is NOT supported on a user instance as the error message tells you.

http://msdn2.microsoft.com/en-us/library/ms143684.aspx

|||

So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

Thanks again for the info and any further guidance you can provide.

|||

Hi,

No, the only version which supports user instances is Express. YOu probably will need a variation of the planned envrionment.

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

Thanks for your support in helping me sort this out.

|||

Distribution of MSDE to 5,000 end users - good idea or bad?

Our company has an application that we distribute to our agents for generating sales quotes and it currently runs against an Access database. There is some interest in changing to MSDE and we're just trying to solicit some feedback from people that either investigated this or have tried it.

Here is our application and end user profile:

The application is a relatively small VB application that is distributed to about 5,000 agents. The data is stored in two separate physical database files (with many tables in each). One database stores the user-created data like prospects and configured options, and the other database stores the configuration options and rating data. The configuration option and rating data changes quarterly which is handled today through a new download of the software which overwrites the Access database that contains just that data. The agents are independent and sell for many companies, not just ours, and they own and use their own computer equipment. I would say they are probably at or below average for technical experience for end users. The proposal is to convert the application to run against an MSDE database and to include and install a copy of MSDE as part of the application installation. We would probably distribute service packs along with the quarterly updates, but agents do not always install the updates or could stop selling for us at any time, so there is no guarantee that the updates will ever be applied.

Dramatic overhauls of the application are not being considered at this time, and the users frequently do quotes while on-site with clients and they do not always have access to the Internet. In effect, while a web-based model may seem ideal, we would definitely lose sales if the agents are not able to quote with an off-line source of data and application, and the project is only evaluating whether or not it is a good idea to convert the database from Access to MSDE.

Thanks in advance for your comments!
ChrisYou've already pointed out the biggest issues. Service pack updates are a must. Beyond that, I would think this kind of situation lends itself to Access. I would think unless you plan on upgrading all of these machines to .NET, that it would be best to stick with the most distributable option of Access and the VB App.|||Should you decide to move this to .NET then an option might also be to just use XML for your data storage. If the amount of data is not very large, then Access or MSDE might actually be overkill. Deployment of XML based data is pretty straightforward too.

Distribution of Application with SQL Server DB

If I create a Window's application that uses a MS Sql Server DB created with the express edition, do I need to get permission or submit royalties for distribution of my application? If so, where can I find the required info? I have been Googling without success and want to understand what is involved.

My projects are mostly for fun and my own amazement at this point, but I thinking about creating something that may actually become a product and need some guidance before I get much further along with it.

You may freely distribute SQL Server Express Edition.

See this link to register and for more details.

SQL Server 2005 Express Redistribution
http://www.microsoft.com/sql/editions/express/redistregister.mspx

The following may help you customize your own installer for SQL Server.

SQL Server 2005 UnAttended Installations
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
http://msdn2.microsoft.com/en-us/library/bb264562.aspx
http://www.devx.com/dbzone/Article/31648

|||Thanks Arnie. Very encouraging info.

Distribution licensing

Hi
If I write app with a sql server as backend, what are the licensing
requirements for distribution of the app sql server wise i.e. does the
client need to buy their on sql server ort can I distribute parts of it as
part of my app? I need to use Merge Replication so I doubt I can distribute
the app with sql server express.
Thanks
Regards
If you use SQL Server Express or MSDE you do not need to have licenses on
the subscribers, but you will need to license the publisher for every
connection made to it. So if you have 300 subscribers you will need 300
Calls.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23GIlwXpKIHA.4752@.TK2MSFTNGP05.phx.gbl...
> Hi
> If I write app with a sql server as backend, what are the licensing
> requirements for distribution of the app sql server wise i.e. does the
> client need to buy their on sql server ort can I distribute parts of it as
> part of my app? I need to use Merge Replication so I doubt I can
> distribute the app with sql server express.
> Thanks
> Regards
>
>
>
>

Distribution licensing

Hi
If I write app with a sql server as backend, what are the licensing
requirements for distribution of the app sql server wise i.e. does the
client need to buy their on sql server ort can I distribute parts of it as
part of my app? I need to use Merge Replication so I doubt I can distribute
the app with sql server express.
Thanks
RegardsIf you use SQL Server Express or MSDE you do not need to have licenses on
the subscribers, but you will need to license the publisher for every
connection made to it. So if you have 300 subscribers you will need 300
Calls.
--
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23GIlwXpKIHA.4752@.TK2MSFTNGP05.phx.gbl...
> Hi
> If I write app with a sql server as backend, what are the licensing
> requirements for distribution of the app sql server wise i.e. does the
> client need to buy their on sql server ort can I distribute parts of it as
> part of my app? I need to use Merge Replication so I doubt I can
> distribute the app with sql server express.
> Thanks
> Regards
>
>
>
>

Distribution licensing

Hi
If I write app with a sql server as backend, what are the licensing
requirements for distribution of the app sql server wise i.e. does the
client need to buy their on sql server ort can I distribute parts of it as
part of my app? I need to use Merge Replication so I doubt I can distribute
the app with sql server express.
Thanks
RegardsIf you use SQL Server Express or MSDE you do not need to have licenses on
the subscribers, but you will need to license the publisher for every
connection made to it. So if you have 300 subscribers you will need 300
Calls.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23GIlwXpKIHA.4752@.TK2MSFTNGP05.phx.gbl...
> Hi
> If I write app with a sql server as backend, what are the licensing
> requirements for distribution of the app sql server wise i.e. does the
> client need to buy their on sql server ort can I distribute parts of it as
> part of my app? I need to use Merge Replication so I doubt I can
> distribute the app with sql server express.
> Thanks
> Regards
>
>
>
>

Distribution licensing

Distribution Job Step Retries all in one connection and/or transaction?

When a distribution job step "Retry Attempts" is > 0 and the step has certain problems, the step is "retried" after "Retry Interval".

But I am uncertain as to the details. Are the retries within one database transaction? Does each "try" get its own transaction? And what about connection? Is the "retry" done with the same connection? I know this may sound funny but I would like to know exactly what is going on here.

Thanks,

I am pretty sure that each retry spawns its own transaction - any failed attempt will rollback the current transaction. And the retry is done in the same connection unless it's a retry due to a connection issue. I can try to clarify later.

Distribution job failed

When the distribution agent runs trying to apply the snapshot at the subscriber I get the following error 20253 sql 2005

End of file reached , terminator missing or field data incomplete in SQL 2005

Consult the BOL for more information on the bcp utility and its supported options.

To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.
Consult the BOL for more information on the bcp utility and its supported options.

It could be that the snapshot files for one of your articles is corrupted. Try manually deleting the snapshot files from the snapshot folder and start over - regenerate the snapshot and try to apply it.

If it still fails, you need to tell us what's different about the article that's failing: what kinds of datatypes are in the table, did you manually edit any of the bcp files, what kind of subscriber is this, is this oracle publisher, etc.

|||

Hi,

Did you ever get this issue sorted?

I am receiving the same error message on 1 table during the snapshot sync phase on SQL2005 and it is failing the sync.

Cheers

Distribution job failed

When the distribution agent runs trying to apply the snapshot at the subscriber I get the following error 20253 sql 2005

End of file reached , terminator missing or field data incomplete in SQL 2005

Consult the BOL for more information on the bcp utility and its supported options.

|||

Hi,

Did you ever get this issue sorted?

I am receiving the same error message on 1 table during the snapshot sync phase on SQL2005 and it is failing the sync.

Cheers

Distribution executable running at 100% cpu but doesn't process any transactions

MSSQL Server 2000 SP3a -> MSSQL Server 2000 SP3a
Transactional Replication
Push subscriptions
Transactional replication has been performing well for years between
two servers. I added a new publication to handle some other tables.
Both publications had push subscriptions to our Production and
Development SQL servers. I added an article for a relatively large
table (2,000,000 rows or so) to the second publication and a few days
later the distribution agent seemed to get "stuck" after this new
article was added.
The distribution agent maxes out the CPU, but I don't see any
transactions going through. Nothing appears to be happening in
profiler, and neither the sqlserver nor the distrib.exe processes seem
to be performing any I/O related to replication with the exception of
a very slow incrementing I/O other for distrib.exe.
I killed the push subscription for the second publication and this
fixed the problem for a day or so. However this morning the
publication that has been working fine for months is now creating the
same problem. As a result no transactions are getting through to the
production server.
I've tried running the distrib.exe through the command prompt but it
causes the same issue.
Any advice or guidance?
Thanks!
- Mike
You can bet it will get "stuck" - 2,000,000 rows is a lot of data to push.
I suggest you do DTS it over (create a package for this and use the fast
insert option), and then do a nosync subscription. Ensure you build the
replication stored procedures using sp_scriptcustomprocs.
"Mike" <ngposterMikeBain@.gmail.com> wrote in message
news:ea5d5311.0410210729.264b3427@.posting.google.c om...
> MSSQL Server 2000 SP3a -> MSSQL Server 2000 SP3a
> Transactional Replication
> Push subscriptions
> Transactional replication has been performing well for years between
> two servers. I added a new publication to handle some other tables.
> Both publications had push subscriptions to our Production and
> Development SQL servers. I added an article for a relatively large
> table (2,000,000 rows or so) to the second publication and a few days
> later the distribution agent seemed to get "stuck" after this new
> article was added.
> The distribution agent maxes out the CPU, but I don't see any
> transactions going through. Nothing appears to be happening in
> profiler, and neither the sqlserver nor the distrib.exe processes seem
> to be performing any I/O related to replication with the exception of
> a very slow incrementing I/O other for distrib.exe.
> I killed the push subscription for the second publication and this
> fixed the problem for a day or so. However this morning the
> publication that has been working fine for months is now creating the
> same problem. As a result no transactions are getting through to the
> production server.
> I've tried running the distrib.exe through the command prompt but it
> causes the same issue.
> Any advice or guidance?
> Thanks!
> - Mike

Distribution DB on its own server

When would one consider to have the distribution database on another server
different from the publisher ?
The distributor can use a fair amount of resources on a very busy system so
if it starts to impact performance that would be a good clue.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
> server
> different from the publisher ?
>
|||TO add to Andrew's comments, be aware that it is difficult to change the
server that the distribution database lives on because that server name is
in registry entries for each publisher and subscriber... So try to make a
good decision the first time...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
server
> different from the publisher ?
>

Distribution DB on its own server

When would one consider to have the distribution database on another server
different from the publisher ?
When locking becomes a problem. There is no hard and fast rule, ie when you
get more than 1023 transactions per second, or you are running on a 2 Ghz
processor.
If you have a high load replication solution you might want to migrate to a
remote distributor from the start. However, you must keep in mind that if
the distributor goes down your tlogs can balloon, and it can be difficult,
but not impossible, to recover from.
Generally I like to remove this single point of failure and use a local
distribution database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O6sibAI7EHA.1564@.TK2MSFTNGP09.phx.gbl...
> When would one consider to have the distribution database on another
> server
> different from the publisher ?
>

Distribution DB on its own server

When would one consider to have the distribution database on another server
different from the publisher ?The distributor can use a fair amount of resources on a very busy system so
if it starts to impact performance that would be a good clue.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
> server
> different from the publisher ?
>|||TO add to Andrew's comments, be aware that it is difficult to change the
server that the distribution database lives on because that server name is
in registry entries for each publisher and subscriber... So try to make a
good decision the first time...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
server
> different from the publisher ?
>

Distribution DB on its own server

When would one consider to have the distribution database on another server
different from the publisher ?The distributor can use a fair amount of resources on a very busy system so
if it starts to impact performance that would be a good clue.
--
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
> server
> different from the publisher ?
>|||TO add to Andrew's comments, be aware that it is difficult to change the
server that the distribution database lives on because that server name is
in registry entries for each publisher and subscriber... So try to make a
good decision the first time...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
server
> different from the publisher ?
>

distribution db in suspect mode

Hi All !!!!
I clone sqlserver's files from one server(A) to another server(B), . On the
(A) server there were transactional replication from server(A) to subscriber
server(XX). There are ditributor and publiatio on same server(A).
After cloning sql files to server(B) i got the distribution and the
piblication databases in suspect mode. I can't perform anything while these
databases in suspecet mode. I can't also to disable distribution (this thing
will also help me.)
Any ideas??//
TNX in advance.
"Imagination is more important than knolwege" (Albert Einshtein)
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200509/1
These artivles should help:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
http://www.windowsitpro.com/Article/...D/492/492.html
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Distribution db doesn't show up after enabling transactional repl

We are using SQL 2k with SP4. After I used the 'Create and Manage
publication wizard..' to create my transaction replication (I took the
default settings), it finished successfully. When I check the database
listing in EM, the 'distribution' database doesn't show up in the list but
the physical .mdf and .ldf files do exist. I thought this is strange, I
went ahead to disable the publication and thought I could start over again
but it gave me an error message " Eror 945: Database 'distribution' cannot be
opened dur to inaccessible files or insufficient memory or disk space'. We
are out out of space or memory. I stop by restart SQL service but it didn't
help. Can anyone help?
Wingman
Do you have the show system databases enabled? This could account for the
invisibility of it. I would clear up space on your machine and then try to
disable publishing.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:8A3D4193-03DC-4D43-A3D5-A7FE0D2E97CB@.microsoft.com...
> We are using SQL 2k with SP4. After I used the 'Create and Manage
> publication wizard..' to create my transaction replication (I took the
> default settings), it finished successfully. When I check the database
> listing in EM, the 'distribution' database doesn't show up in the list but
> the physical .mdf and .ldf files do exist. I thought this is strange, I
> went ahead to disable the publication and thought I could start over again
> but it gave me an error message " Eror 945: Database 'distribution' cannot
> be
> opened dur to inaccessible files or insufficient memory or disk space'.
> We
> are out out of space or memory. I stop by restart SQL service but it
> didn't
> help. Can anyone help?
> Wingman

Distribution DB constantly grows

Hi,
I have set up transactional replication for a database from one db
server to a second. The replication is working fine but the distribution
database constantly grows. The size of the replicated db is 524 MB, the
size of the distribution db is 24 GB, growing daily.
How can I shrink my distribution database and how can I limit the growth
without damaging the replication?
Markus
you need to maintain the tlog on the distribution database. Make sure it
using the full recovery and dump the tlog every 5 minutes or so. You might
want to backup the log with truncate_only, shrink the tlog, and then backup
the database.
Then check your transaction retention period, it should be 48 hours. Make
sure your distribution clean up job is enabled and running every 10 minutes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Markus Renschler" <SP-news@.renschler.net> wrote in message
news:%23uv0qzXdFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have set up transactional replication for a database from one db server
> to a second. The replication is working fine but the distribution database
> constantly grows. The size of the replicated db is 524 MB, the size of the
> distribution db is 24 GB, growing daily.
> How can I shrink my distribution database and how can I limit the growth
> without damaging the replication?
> Markus
|||Hi Hilary,
thanks for the advice.

> you need to maintain the tlog on the distribution database. Make sure it
> using the full recovery and dump the tlog every 5 minutes or so. You might
> want to backup the log with truncate_only, shrink the tlog, and then backup
> the database.
The distribution database's backup model is set to simple. Is this a
problem (except the worse disaster recovery options)?

> Then check your transaction retention period, it should be 48 hours. Make
> sure your distribution clean up job is enabled and running every 10 minutes.
The transaction retention period has been set to the default value
(72h). I changed it to 48h. Then I checked the distribution cleanup job.
It is scheduled to run every 10 minutes. It had been started 5 hours ago
and was still running. I stopped it and started it again. Now it is
running since 15 Minutes. Is it possible that this job lasts more than 5
hours?
Markus
|||It is possible that it could run for more than 5 hours at first. Subsequent
runs should not take as long. Do you have anonymous subscribers? Metadata
hangs around a lot longer for them than for names subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Markus Renschler" <SP-news@.renschler.net> wrote in message
news:uzVIaOZdFHA.3488@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi Hilary,
> thanks for the advice.
might[vbcol=seagreen]
backup[vbcol=seagreen]
> The distribution database's backup model is set to simple. Is this a
> problem (except the worse disaster recovery options)?
Make[vbcol=seagreen]
minutes.
> The transaction retention period has been set to the default value
> (72h). I changed it to 48h. Then I checked the distribution cleanup job.
> It is scheduled to run every 10 minutes. It had been started 5 hours ago
> and was still running. I stopped it and started it again. Now it is
> running since 15 Minutes. Is it possible that this job lasts more than 5
> hours?
> Markus
|||Hilary Cotter wrote:
> It is possible that it could run for more than 5 hours at first. Subsequent
> runs should not take as long.
It has been running 3:35. The next runs took less than a second, each.
After the cleanup the database was still 22GB in size. I tried to shrink
it, but without an effect.
For testing, I have set the transaction retention period to 1h. Then I
started a distribution database cleanup, but without an effect.

> Do you have anonymous subscribers? Metadata
> hangs around a lot longer for them than for names subscribers.
No, I just have one subscriber which had its subscription pushed from
the origin server (which acts as publisher and distributor).
Could it be helpful if I re-create the distribution database? Is there
any possibility to do this?
Thanks for helping,
Markus

Distribution DB

HI all:
How to use query to get Distribution DB name ?
Cheers
Nick
sp_helpdistributiondb
"nick" <fsheng@.ebreathe.co.nz> wrote in message
news:eb3waFjZFHA.1512@.TK2MSFTNGP10.phx.gbl...
> HI all:
> How to use query to get Distribution DB name ?
>
> Cheers
> Nick
>
|||thanks
also i worked it out another way to get distribution info is that select *
from msdb.dbo.MSdistributiondbs
Cheers
nick
"Khooseeraj Moloye" <rkmoloye@.hotmail.com> wrote in message
news:OH1gszjZFHA.3488@.tk2msftngp13.phx.gbl...
> sp_helpdistributiondb
> "nick" <fsheng@.ebreathe.co.nz> wrote in message
> news:eb3waFjZFHA.1512@.TK2MSFTNGP10.phx.gbl...
>

Distribution Database Table Creation Date?

Hi,
In my distribution data base, the system tables creation date is showing Dec
2002, but i replicated the database on feb 2006,
I want to know why the table creation date is in year 2002.
Please clarify me.
rgds,
Soura
Soura,
this is the date that the distribution database was created using
sp_adddistributiondb.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

distribution database SUSPECT - HELP! HELP!

We have SQL 2000 server replicate to another box.
I just found replication stopped! and distribution database was marked SUSPECT.
I am a SQL server newby, I checked sysdatabases table in master database and found
the status column is 280 and status2 is 1090519040.
Is there any way I can quickly recover this database and make replication moving?
Thanks in advance
David
David,
have a look in BOL for sp_resetstatus. Here is a brief synopsis:
"sp_resetstatus turns off the suspect flag on a database. This procedure
updates the mode and status columns of the named database in sysdatabases.
The SQL Server error log should be consulted and all problems resolved
before running this procedure. Stop and restart SQL Server after executing
sp_resetstatus.
A database can become suspect for several reasons. Possible causes include
denial of access to a database resource by the operating system, and the
unavailability or corruption of one or more database files."
Regards,
Paul Ibison
|||Thank you very much!
|||i m also in same case.
if u know pls let me know
- delwar
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Delwar,
the main reason for this is faulty hardware - drive array etc. Sp_resetstatus will help if the MDF or LDF file for the database is not available during startup. If there's still an issue, start the database in Emergency mode, Update the Status column in
master..sysdatabases table for that database to 32768. After this database will be usable with out transaction log so you can create a new database and use DTS to transfer objects and data (thanks to Hari Prasad for this). DBCC CHECKDB with REPAIR_REBUIL
D can be used if there is still a problem, and after that it's a PSS call.
HTH,
Paul Ibison

distribution database size and other file questions

I think my distribution database blew up in size a long time ago for a
specific problem, and I don't know if its large size (34 GB) will cause
other problems. Is there a way I can flush out old irrelevent data and then
shrink it to a smaller size? The published DB is about 110 GB and has pull
subscriptions to 2 other servers.
Also, what types of RAID disks should the distribution database be placed
on? What about the temp db, log files, and main published database files?
The published database has a separate .ndf for non clustered indexes. Are
these better to be on the same disk or different disks?
Is there anywhere someone can point me to in order to find out more
information about this stuff? I have searched high and low, and can't find a
good place to research this info and apply the information to our particular
setting.
Thanks in advance
--Kristy
As the distribution database involves high write activity it should be raid
10. temp db and logs should be on raid 10 as well. If the database has over
20% of its io being write, it should be raid 10, otherwise make it raid 5.
Ideally the ndf will be on a separate physical disk on a separate array
(raid 5 as its high read activity normally).
Can you shrink the distribution db as it is? Also you might want to issue
the following select * from distribution.dbo.msreplication_status to tell
you how many commands are in the queue. If its small you should be able to
shrink the db, if it is large you have to work on getting these commands to
the subscriber database.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:Ox%23q79CBHHA.204@.TK2MSFTNGP04.phx.gbl...
>I think my distribution database blew up in size a long time ago for a
> specific problem, and I don't know if its large size (34 GB) will cause
> other problems. Is there a way I can flush out old irrelevent data and
> then
> shrink it to a smaller size? The published DB is about 110 GB and has pull
> subscriptions to 2 other servers.
> Also, what types of RAID disks should the distribution database be placed
> on? What about the temp db, log files, and main published database files?
> The published database has a separate .ndf for non clustered indexes. Are
> these better to be on the same disk or different disks?
> Is there anywhere someone can point me to in order to find out more
> information about this stuff? I have searched high and low, and can't find
> a
> good place to research this info and apply the information to our
> particular
> setting.
> Thanks in advance
> --Kristy
>
|||Thanks for the info. Is there a place you can point me to that will teach me
how to determine this on my own? I have your Transactional and Snapshot
book; is it in there?
We only have RAID 10 and RAID 1 set up on our server. But I am moving the
files around because I see a lot of things that indicate file placement
causing performance problems.
I check on distribution.dbo.msreplication_status. I know I tried shrinking
it many months before, but nothing worked.
--Kristy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23ivQFtGBHHA.204@.TK2MSFTNGP04.phx.gbl...
> As the distribution database involves high write activity it should be
raid
> 10. temp db and logs should be on raid 10 as well. If the database has
over
> 20% of its io being write, it should be raid 10, otherwise make it raid 5.
> Ideally the ndf will be on a separate physical disk on a separate array
> (raid 5 as its high read activity normally).
> Can you shrink the distribution db as it is? Also you might want to issue
> the following select * from distribution.dbo.msreplication_status to tell
> you how many commands are in the queue. If its small you should be able to
> shrink the db, if it is large you have to work on getting these commands
to[vbcol=seagreen]
> the subscriber database.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:Ox%23q79CBHHA.204@.TK2MSFTNGP04.phx.gbl...
pull[vbcol=seagreen]
placed[vbcol=seagreen]
files?[vbcol=seagreen]
Are[vbcol=seagreen]
find
>
|||My mistake it is msdistribution_status.
There are some white papers on the Microsoft web site - try this one
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:%238I%2302OBHHA.2276@.TK2MSFTNGP03.phx.gbl...
> Thanks for the info. Is there a place you can point me to that will teach
> me
> how to determine this on my own? I have your Transactional and Snapshot
> book; is it in there?
> We only have RAID 10 and RAID 1 set up on our server. But I am moving the
> files around because I see a lot of things that indicate file placement
> causing performance problems.
> I check on distribution.dbo.msreplication_status. I know I tried shrinking
> it many months before, but nothing worked.
> --Kristy
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23ivQFtGBHHA.204@.TK2MSFTNGP04.phx.gbl...
> raid
> over
> to
> pull
> placed
> files?
> Are
> find
>

Distribution database of Transaction Replication publication being marked SUSPECT by recov

Hi experts there,
I have a Publication created for Transactional Replication. All the
while working fine.
Now it failed and I am not able to access to the database at all. It
shows the following error message:
Error 926: Database 'distribution' cannot be opened. It has been
marked SUSPECT by recovery. See the SQLServer error log for more
information.
Tried to detach the database but getting the following error: -
"The database cannot be detached while it is being replicated"
Tried running DBCC CheckDB but cannot run with database still in
suspect mode
Basically I cannot perform backup on it, cannot detach it, cannot even
drop the publication.
Tried also the following method:
1) sp_resetstatus DISTRIBUTION
Prior to updating sysdatabases entry for database 'DISTRIBUTION', mode
= 0 and status = 24 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made.
2) DBCC CHECKDB ('DISTRIBUTION', REPAIR_REBUILD) WITH ALL_ERRORMSGS
Server: Msg 926, Level 10, State 1, Line 1
Database 'distribution' cannot be opened. It has been marked SUSPECT
by recovery. See the SQL Server errorlog for more information.
Anybody know what cause all this and how to resolve it? Please
help!!!!!
I need to make the Replication running back soonest possible.
Thanks/TewI have seen this behavior once before. The only way we could get it out of
suspect mode was to directly update the sysdatabases table and set the
status to 32768 (emergency bypass). After recycling SQL Server we were then
able to run Checkdb. You might find corruption in the database. If not you
can set the status back to 0 and see if it will recover normally.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Distribution Database Log File Growth

SQL Server 2000 | Transactional Replication

Suspected Problem: Distribution Database Transaction Log Not Checkpointing

I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.

I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.

SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.

Any thoughts?

Jeff

I solved my own problem. The log file growth had nothing to do with it being the Distribution database. I stumbled upon a trace flag entry in the SQL Startup Parameters "-T3608" which is required to move certain system databases like Model (See article: http://support.microsoft.com/kb/224071/). The flag has been there for several months and was probably added the last time the server was rebuilt or storage was added. I removed the trace flag and checkpoints started occuring normally.

Distribution Database Log File Growth

SQL Server 2000 | Transactional Replication

Suspected Problem: Distribution Database Transaction Log Not Checkpointing

SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.

Any thoughts?

Jeff

Distribution database is growing large...

Hi!
A few days ago the number of replication commands grew up to ~6 000
000. A normal number used to be ~20 000. The replication agents are
working fine. The data on subscribers are valid. I suspect that the
history clean up agents don't remove old commands, although their
status shows successful completion.
What could be a workout for such situation?
Thanks.
P.S.: I am using Win 2003 Ent Server + MS SQL 2000 sp3 and only
transactional replication.
Do you have anonymous subscriptions? If so the commands and transactions
will remain there until the end of the retention period.
Also it could be that you merely issued transactions that affected a large
number of rows. However, if these commands have been replicated to all
subscribers the distribution clean up agent should purge them every 10
minutes.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0412020529.372a7316@.posting.google.c om...
> Hi!
> A few days ago the number of replication commands grew up to ~6 000
> 000. A normal number used to be ~20 000. The replication agents are
> working fine. The data on subscribers are valid. I suspect that the
> history clean up agents don't remove old commands, although their
> status shows successful completion.
> What could be a workout for such situation?
> Thanks.
> P.S.: I am using Win 2003 Ent Server + MS SQL 2000 sp3 and only
> transactional replication.

Distribution database is 9 GBs

Our distribution database in up to 9 GBs, after only 3 weeks...
What job cleans out data from this database?
What do I need to check to verify this?
Thanks and God Bless,
ThomBeaux
Is that all. What's your secret?
Check space usage to see if this is the actual size and ensure its not the
transaction log bloat.
The distribution database is a queue which is purged by the distribution
clean up task. For anoymous subscribers you can get data hanging around till
the retention period has elapsed (or the lesser of the retention period and
the history).
It is normal for it to grow quite large if your publisher is replicating a
lot of commands.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
news:9421D30A-1F0D-4CC8-8980-00C09DDA6837@.microsoft.com...
> Our distribution database in up to 9 GBs, after only 3 weeks...
> What job cleans out data from this database?
> What do I need to check to verify this?
> --
> Thanks and God Bless,
> ThomBeaux
|||Primary Data is: 12.68 GB , 12.67 Used
Transaction Log: 9.9 GBs, 81.8% used.
Log and data files are growing by the hour.
What Agent Job should I watch to see if it is being cleaned out?
This system was just updeated to 2005, and we never had a distribution
database larger than 4-5 GBs in 2000, with the same system.
Thanks for the responce Hillary. I see your signature on lots of rpelication
issues, and it is appreciated. Will you be at PASS in denver?
Thanks and God Bless,
ThomBeaux
"Hilary Cotter" wrote:

> Is that all. What's your secret?
> Check space usage to see if this is the actual size and ensure its not the
> transaction log bloat.
> The distribution database is a queue which is purged by the distribution
> clean up task. For anoymous subscribers you can get data hanging around till
> the retention period has elapsed (or the lesser of the retention period and
> the history).
> It is normal for it to grow quite large if your publisher is replicating a
> lot of commands.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
> news:9421D30A-1F0D-4CC8-8980-00C09DDA6837@.microsoft.com...
>
>

Distribution database indexes

All,
I can seem to find much info on this. I'm trying to determine when and if I
need to rebuild the indexes on the Distribution database. We're running
transactional replicaiton 24/7 so I'm concerned it will interupt service.
Thanks in advance.
For the most part the transactions/queries which hit the distribution
database are seeks you will probably find that the impact of fragmentation
is minimal.
While you can defrag your indexes online in SQL 2000 you can't rebuild them
online in SQL 2000 (you can in SQL 2005). I suspect your problems are not
related to indexes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:AD2E42BC-DD48-4964-9EE8-5134FED52DD4@.microsoft.com...
> All,
> I can seem to find much info on this. I'm trying to determine when and if
> I
> need to rebuild the indexes on the Distribution database. We're running
> transactional replicaiton 24/7 so I'm concerned it will interupt service.
> Thanks in advance.
|||We're not having problems, I was looking for general info about distribution
db indexes.
Thanks for the info!
"Hilary Cotter" wrote:

> For the most part the transactions/queries which hit the distribution
> database are seeks you will probably find that the impact of fragmentation
> is minimal.
> While you can defrag your indexes online in SQL 2000 you can't rebuild them
> online in SQL 2000 (you can in SQL 2005). I suspect your problems are not
> related to indexes.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:AD2E42BC-DD48-4964-9EE8-5134FED52DD4@.microsoft.com...
>
>

Distribution database in suspect state

I have sqlserver2000 and I have set up replication. Accidentally I
have deleted distribution data and distribution database went to
suspect state, Is there any way I can recover from this state?
Put the database into emergency mode and bcp the data out. Drop it, recreate
it and then bcp it back in.
Drop it using
sp_dropdistpublisher with no_checks and ignore_distributor,
sp_dropdistributiondb and sp_dropdistributor.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ST" <sitatetali@.gmail.com> wrote in message
news:1173548966.133736.226600@.v33g2000cwv.googlegr oups.com...
>I have sqlserver2000 and I have set up replication. Accidentally I
> have deleted distribution data and distribution database went to
> suspect state, Is there any way I can recover from this state?
>

distribution database having problems??

Something is wrong with my distribtion database. When the distribution clean
up agent runs (or I run dbo.sp_MSdistribution_cleanup manually)
I get the following error message:
Executed as user: sa. Table error: Database ID 9, object ID 5575058, index
ID 1. Chain linkage mismatch. (1:89299)->next = (1:59339), but
(1:59339)->prev = (0:0). [SQLSTATE HY000] (Error 8908). The step failed.
SO then I run
DBCC CHECKDB
on the distribution database. The MSrepl_commands is the table with the
problem with repeated messages such as:
Table error: Object ID 5575058, index ID 1. B-tree page (1:91019) has two
parent nodes (1:93624), slot 103 and (1:89299), slot 1.
I run this:
dbcc checktable ('MSrepl_commands')
and get the same type of error message.
I have no idea how to fix this. Any advise?? (If I try and use the repair
options of the dbcc commands than it says I have to have the DB in single
user mode. Not sure if you are even suppose to do that with a distribution
db?)
Thanks so much for any and all help,
Kristy
Believe it or not these problems crop up from time to time on replicated
databases. Stop the sql server agent on the publisher, yank the network
cable (if possible), and put this database in single user mode and do the
repair.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:OCFMw7kPGHA.1696@.TK2MSFTNGP14.phx.gbl...
> Something is wrong with my distribtion database. When the distribution
> clean
> up agent runs (or I run dbo.sp_MSdistribution_cleanup manually)
> I get the following error message:
> Executed as user: sa. Table error: Database ID 9, object ID 5575058, index
> ID 1. Chain linkage mismatch. (1:89299)->next = (1:59339), but
> (1:59339)->prev = (0:0). [SQLSTATE HY000] (Error 8908). The step failed.
> SO then I run
> DBCC CHECKDB
> on the distribution database. The MSrepl_commands is the table with the
> problem with repeated messages such as:
> Table error: Object ID 5575058, index ID 1. B-tree page (1:91019) has two
> parent nodes (1:93624), slot 103 and (1:89299), slot 1.
> I run this:
> dbcc checktable ('MSrepl_commands')
> and get the same type of error message.
> I have no idea how to fix this. Any advise?? (If I try and use the
> repair
> options of the dbcc commands than it says I have to have the DB in single
> user mode. Not sure if you are even suppose to do that with a distribution
> db?)
> Thanks so much for any and all help,
> Kristy
>
>
|||Thanks Hilary. I will check to see if we can yank the network cable. Not
sure if we can though because it is on a remote server hosted by another
company in another state.
If not, is there something else I can do? (Besides redo replication and drop
the distribution DB.)
As always, thanks!
--Kristy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eODseSmPGHA.5516@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Believe it or not these problems crop up from time to time on replicated
> databases. Stop the sql server agent on the publisher, yank the network
> cable (if possible), and put this database in single user mode and do the
> repair.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:OCFMw7kPGHA.1696@.TK2MSFTNGP14.phx.gbl...
index[vbcol=seagreen]
failed.[vbcol=seagreen]
two[vbcol=seagreen]
single[vbcol=seagreen]
distribution
>

Distribution database gone but now can't get rid of publications

I have been messing around with Replication on MS Virtual Server.
Set up a Distributor on a seperate machine from the publication
database.
For some reason unkown my Distribution database went in Suspect Mode.
Finally got rid of it using
sp_dropdistributiondb @.database = 'distribution'
Now I can't get delete any of my publications.
They still look towards my Distribution database which is no longer a
distributor
tried
exec sp_dropsubscription
@.publication='AdventureWorksCentralPublication',@.a rticle='all',@.subscriber='all',
@.ignore_distributor=1
sp_removedbreplication
SHould I just re-set up my Distributor again and leave the
publications? Would that even work?
THere has to be some way to delete my publications
Thanks
Mike
Did you run sp_removedbreplication on the publisher and subscribers also? If
so, and you have removed the distribution database then perhaps you are just
seing residual data from tempdb which is reported in the replication monitor.
try refreshing the replication monitor (sp_MSload_replication_status) to see
if this removes the reference.
Paul Ibison
|||Did you follow the steps outlined here?
http://support.microsoft.com/kb/324401
"jughead" <mike.aarset@.gmail.com> wrote in message
news:1176965268.535472.269400@.b58g2000hsg.googlegr oups.com...
>I have been messing around with Replication on MS Virtual Server.
> Set up a Distributor on a seperate machine from the publication
> database.
> For some reason unkown my Distribution database went in Suspect Mode.
> Finally got rid of it using
> sp_dropdistributiondb @.database = 'distribution'
> Now I can't get delete any of my publications.
> They still look towards my Distribution database which is no longer a
> distributor
> tried
> exec sp_dropsubscription
> @.publication='AdventureWorksCentralPublication',@.a rticle='all',@.subscriber='all',
> @.ignore_distributor=1
> sp_removedbreplication
> SHould I just re-set up my Distributor again and leave the
> publications? Would that even work?
> THere has to be some way to delete my publications
> Thanks
> Mike
>

Distribution database became large

Hi,
I have noticed that distribution database which is used for transactional
replicatiton became large. Can I shrink the database? Transaction log is the
same size as the data file. I run dbcc loginfo(distribution), status field
shows that there are a lot of unused parts. Can I shrink transaction log at
least?
I would make sure the distribution database is in Full recovery model and
dump it frequently.
It is wise to try to size the transaction logs to prevent frequent
autogrows, and it is also wise to try to keep them as small as possible. You
will have to experiment to find the ideal size. 10% of the size of the data
files is frequently as good guess for smaller databases.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Elena" <Elena@.discussions.microsoft.com> wrote in message
news:ADDC4A92-A5A4-4EE5-877A-754D307AC251@.microsoft.com...
> Hi,
> I have noticed that distribution database which is used for transactional
> replicatiton became large. Can I shrink the database? Transaction log is
> the
> same size as the data file. I run dbcc loginfo(distribution), status field
> shows that there are a lot of unused parts. Can I shrink transaction log
> at
> least?
>
|||Hi, Hilary,
1. Why do you suggest having distribution database in Full recovery model?
2. Is it worth to shrink transaction log of the distribution database? I
guess it has grown up in special case when there was very large transaction
to be delivered.
"Hilary Cotter" wrote:

> I would make sure the distribution database is in Full recovery model and
> dump it frequently.
> It is wise to try to size the transaction logs to prevent frequent
> autogrows, and it is also wise to try to keep them as small as possible. You
> will have to experiment to find the ideal size. 10% of the size of the data
> files is frequently as good guess for smaller databases.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Elena" <Elena@.discussions.microsoft.com> wrote in message
> news:ADDC4A92-A5A4-4EE5-877A-754D307AC251@.microsoft.com...
>
>
|||1. because I like to maintain the size of my tlogs as much as possible. Full
does this. You can use bulk which offers best performance (except for
deletes) but you have no recoverability. Likewise you can use simple with no
recoverability but the transaction log can grow and as the log grows you get
performance degradation. With Full Recovery model you can set the size to
something and get recoverability and minimize auto grow.
2) Once it is sized adequately you should not have to shrink it. Right now I
think its a must to conserve space and to get a very slight performance
improvement.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Elena" <Elena@.discussions.microsoft.com> wrote in message
news:CBCAAB13-58C6-407A-A338-040B37BA5961@.microsoft.com...[vbcol=seagreen]
> Hi, Hilary,
> 1. Why do you suggest having distribution database in Full recovery model?
> 2. Is it worth to shrink transaction log of the distribution database? I
> guess it has grown up in special case when there was very large
> transaction
> to be delivered.
> "Hilary Cotter" wrote:
|||Thanks, Hilary,
Your answers were very helpful.
Elena
"Hilary Cotter" wrote:

> 1. because I like to maintain the size of my tlogs as much as possible. Full
> does this. You can use bulk which offers best performance (except for
> deletes) but you have no recoverability. Likewise you can use simple with no
> recoverability but the transaction log can grow and as the log grows you get
> performance degradation. With Full Recovery model you can set the size to
> something and get recoverability and minimize auto grow.
> 2) Once it is sized adequately you should not have to shrink it. Right now I
> think its a must to conserve space and to get a very slight performance
> improvement.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Elena" <Elena@.discussions.microsoft.com> wrote in message
> news:CBCAAB13-58C6-407A-A338-040B37BA5961@.microsoft.com...
>
>

Distribution Database

Can I move the Distribution Database from one location to another without
impacting replecation.?
Thanks
Location - meaning drive, or server?
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
<msnews.microsoft.com> wrote in message
news:ODFUVI8fIHA.5624@.TK2MSFTNGP02.phx.gbl...
> Can I move the Distribution Database from one location to another without
> impacting replecation.?
> Thanks
>
|||to a new drive on the same server
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:uZh1PR8fIHA.4376@.TK2MSFTNGP05.phx.gbl...
> Location - meaning drive, or server?
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> <msnews.microsoft.com> wrote in message
> news:ODFUVI8fIHA.5624@.TK2MSFTNGP02.phx.gbl...
>
|||Hi,
I'v moved in this way:
1. Run ALTER DATABASE database_name SET OFFLINE WITH ROLLBACK
IMMEDIATE.
2. Move the file to the new location.
3. Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name,
FILENAME = 'new_path/os_file_name'.
4. Run ALTER DATABASE database_name SET ONLINE.
Reference -
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/89f01b10-5fae-4ed8-
b0fb-a4b9f540fd28.htm
http://groups.google.pl/group/microsoft.public.sqlserver.server/browse_thread/thread/89b28d54a808c91e/
Regards

Saturday, February 25, 2012

Blog Archive

About Me