locked out of sql server management studio

Hi everyone, I hope someone can help me here!

A friend of mine set up a Windows 2003 Server for me in my home with Sql Server 2005. I'm developing a website for prospective employers to see the kinds of things I can program since I have no field-related work experience. I have a cool site up, but I can't showcase the any features that use sql server (which obviously, are the kinds of things employers want to see) because the login and password my friend remembers produces this error:

Cannot connect to server.

Additional information:

An error has occurred while establishing a connection to the server. When connecting to sql server 2005, this failure may be caused by the fact that under the default settings sql server doesn't allow remote connections,(provider: Named Pipes Provider, error: 40-could not open a connection to sql server)(Microsoft sql server, Error:2)

My friend doesn't know how to fix this. Is there a way to create another login with admin rights in the command prompt so that I can login & get my database functionality going?

Thanks in advance!

Can you connect to sqlserver at all on the server itself?
Do you know which version of sql2k5 you're running? Do "select @.@.version" to find out - this assumes you can connect to the server somehow.
If you're using sqlexpress version, the ability to connect remotely is off by default. You will need to use SQL Surface Area tool to change the setting.|||

Hey OJ, thanks for trying to get me out of this pickle!

No, I don't "think" I can connect to sql server. Don't I need to add the databases my app is using to sql server in order for my app to work? As I said, this is my first time moving an app to a server.

The result I get when running "select @.@.version" is SqlServer 2000! Dang, I don't even have it running on my own laptop in the right instance of sql server! I'm glad you had me check that out. I'll move everything over to 2005. I think I have the developer version. Whatever version of 2005 I have on my laptop, I should have on the server, which I can't reach at all. I just checked the version of 2005 I have, & it's 2005 v.9.00.1399.06.

I have the Surface Area tool, but I've never used it. What do I do?

Thanks, OJ. I appreciate your time.

Kimmy

|||Kimmy,

Before you get yourself in big trouble with licensing, I suggest you take a closer look at your server and make sure it does not run a developer edition - dev edition means for development only and should not be used for any type of production.

To determine whatever version you have on the server, we will have to fix your connection problem. ;-) Let see if I can walk you through this.

1. Logon to server locally - either via remote desktop (mstsc /console) or at the console itself.
2. Execute "net start" to get a list of started services. Your sqlserver would be one if it's started. If it's not started yet, run "services.msc" to bring up services mgr and start it. You should find you sqlserver under "SQL Server (instance_name)".
3. Now startup sql surface area tool. I.e. Start -> Programs -> sql2k5 -> config tools -> sqlSAC (i.e. "C:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe")
4. Select "SAC for services and connections"
5. Pick your sql instance and ensure Remote Connection is set to local & remote.
6. Restart your sqlserver service to take effect.
7. Now you should be able to connect to your sql instance remote from another computer.
8. If your NT login is part of the sql server's local admins, you should be able to run

sqlcmd -S"your_server_name\instance_name" -E -Q"select @.@.version"|||

Hey OJ,

Thanks for your last post...I've been interviewing with recruiters and haven't had time to check what you last said, but I'm on it now.

Mannnnnn, the liscencing issue? What version do I need to use on a production server?

I will respond tomorrow with what I find out about your other comments from your last post.......and THANKS A BUNCH for sticking with me through this!

Kimmy

|||Hi everyone,

I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?

Thanks,|||

nyaung wrote:

Hi everyone,

I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?

Thanks,

please post the full error here. there can be many causes for not able to connect.

locked out of sql server management studio

Hi everyone, I hope someone can help me here!

A friend of mine set up a Windows 2003 Server for me in my home with Sql Server 2005. I'm developing a website for prospective employers to see the kinds of things I can program since I have no field-related work experience. I have a cool site up, but I can't showcase the any features that use sql server (which obviously, are the kinds of things employers want to see) because the login and password my friend remembers produces this error:

Cannot connect to server.

Additional information:

An error has occurred while establishing a connection to the server. When connecting to sql server 2005, this failure may be caused by the fact that under the default settings sql server doesn't allow remote connections,(provider: Named Pipes Provider, error: 40-could not open a connection to sql server)(Microsoft sql server, Error:2)

My friend doesn't know how to fix this. Is there a way to create another login with admin rights in the command prompt so that I can login & get my database functionality going?

Thanks in advance!

Can you connect to sqlserver at all on the server itself?
Do you know which version of sql2k5 you're running? Do "select @.@.version" to find out - this assumes you can connect to the server somehow.
If you're using sqlexpress version, the ability to connect remotely is off by default. You will need to use SQL Surface Area tool to change the setting.|||

Hey OJ, thanks for trying to get me out of this pickle!

No, I don't "think" I can connect to sql server. Don't I need to add the databases my app is using to sql server in order for my app to work? As I said, this is my first time moving an app to a server.

The result I get when running "select @.@.version" is SqlServer 2000! Dang, I don't even have it running on my own laptop in the right instance of sql server! I'm glad you had me check that out. I'll move everything over to 2005. I think I have the developer version. Whatever version of 2005 I have on my laptop, I should have on the server, which I can't reach at all. I just checked the version of 2005 I have, & it's 2005 v.9.00.1399.06.

I have the Surface Area tool, but I've never used it. What do I do?

Thanks, OJ. I appreciate your time.

Kimmy

|||Kimmy,

Before you get yourself in big trouble with licensing, I suggest you take a closer look at your server and make sure it does not run a developer edition - dev edition means for development only and should not be used for any type of production.

To determine whatever version you have on the server, we will have to fix your connection problem. ;-) Let see if I can walk you through this.

1. Logon to server locally - either via remote desktop (mstsc /console) or at the console itself.
2. Execute "net start" to get a list of started services. Your sqlserver would be one if it's started. If it's not started yet, run "services.msc" to bring up services mgr and start it. You should find you sqlserver under "SQL Server (instance_name)".
3. Now startup sql surface area tool. I.e. Start -> Programs -> sql2k5 -> config tools -> sqlSAC (i.e. "C:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe")
4. Select "SAC for services and connections"
5. Pick your sql instance and ensure Remote Connection is set to local & remote.
6. Restart your sqlserver service to take effect.
7. Now you should be able to connect to your sql instance remote from another computer.
8. If your NT login is part of the sql server's local admins, you should be able to run

sqlcmd -S"your_server_name\instance_name" -E -Q"select @.@.version"|||

Hey OJ,

Thanks for your last post...I've been interviewing with recruiters and haven't had time to check what you last said, but I'm on it now.

Mannnnnn, the liscencing issue? What version do I need to use on a production server?

I will respond tomorrow with what I find out about your other comments from your last post.......and THANKS A BUNCH for sticking with me through this!

Kimmy

|||Hi everyone,

I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?

Thanks,|||

nyaung wrote:

Hi everyone,

I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?

Thanks,

please post the full error here. there can be many causes for not able to connect.

sql

locked out of sql server management studio

Hi everyone, I hope someone can help me here!

A friend of mine set up a Windows 2003 Server for me in my home with Sql Server 2005. I'm developing a website for prospective employers to see the kinds of things I can program since I have no field-related work experience. I have a cool site up, but I can't showcase the any features that use sql server (which obviously, are the kinds of things employers want to see) because the login and password my friend remembers produces this error:

Cannot connect to server.

Additional information:

An error has occurred while establishing a connection to the server. When connecting to sql server 2005, this failure may be caused by the fact that under the default settings sql server doesn't allow remote connections,(provider: Named Pipes Provider, error: 40-could not open a connection to sql server)(Microsoft sql server, Error:2)

My friend doesn't know how to fix this. Is there a way to create another login with admin rights in the command prompt so that I can login & get my database functionality going?

Thanks in advance,

Monster

Have you enabled remote connections for the SQL2005 instance with SQL Surface Area Configuration? You can also try to remove User Instance="true" attribute from the connection string if you don't need to generate user instnace.|||

No, because I don't know how to do those things. I'm used to just developing on my laptop, where everything always works great! Could you tell me how to do the first suggestion? And regarding the second suggestion, I think there's something there I need to understand when you transfer an application to a server. Does that mean that somehow the database is included in the app & doesn't need to interact with sql server at all? Obviously, I'm very new to this.

Thank you so much for your suggestions! I'll await your reply!

|||OK, for suggestion 1 you can click start->All Programs->Microsoft SQL Server2005->Configuration Tools-> SQL Server Surface Area Configuration->click?"Surface Area Configuration for Services and Connections"->expand the SQL instance in the tree on the left->Database Engine->Remote Connections->Using both TCP/IP and Named Pipes. For details, you can refer to:
How to configure SQL Server 2005 to allow remote connections

For the User Instance attribute,?if?it?is?true?it?indicates to redirect the connection from the default SQL Server Express instance to a runtime-initiated instance running under the account of the caller. you can refer to:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx

You can use database file included in your application, but that also requires an accessible SQL2005/Express instance to host the database file: we accomplish this by using the AttachDBFileName attribute in the connection string.

I recommend you take a look at this article which may help to understand the way VS2005 using .mdf file:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

|||

Iori_Jay,

THANK YOU FOR YOUR RESPONSE...I haven't been able to check out what you've said, but I will do that tomorrow & get back to you.

Thank you for sticking with me through this!

Monster

|||It's my pleasure What's going on now?|||

Well, I'm finally into mgmt studio and my databases are there. It turned out that in order for sql server to work on the test server it needed more ram, which was why I was locked out (it had nothing to do with the login, apparently).

But now, of course, there are other issues

|||

Whoops...must have pressed something and it posted before I was done...

I have 2 db's there, 1 for a fictitious bookstore, which works absolutely fine. The other is for a fictitious event booking site, which is accessed both traditionally via the db & then the app is rewritten to extract the data access to a web service. Neither of these work. I also have another simple web service that loads Northwind customer info, which doesn't work either.

Here are the connect strings (remember I'm a newbie):

Hey...just had a brainstorm...I'm going to try it & get back...

|||

Ok, brainstorm didn't work.

Here are the relevant sections of web.config:

<

appSettings> //the brainstorm had to do with changing the localhost below to the name of my website, but that didn't help

<

addkey="NorthwindCustomers.CustomerService"value="http://localhost:2285/KSMCADNET/WebServicesRemoting/CustomerService.asmx"/>

<

addkey="EventBookingWebService.EventBookingService"value="http://localhost:2285/KSMCADNET/EventBookingWebService/EventBookingClient/EventBookingService.asmx"/>

</

appSettings>

<

connectionStrings>

<

addname="pubsConnectionString1"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\pubs.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>

<

addname="KimsBookstoreConnectionString"connectionString="Data Source=notebook\SQL2K5DEV;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>

<

addname="KimsBookstoreConnectionString2"connectionString="Data Source=notebook\SQL2K5DEV;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>

<

addname="EventBookingConnectionString"connectionString="Data Source=notebook\SQL2K5DEV;Integrated Security=True;Initial Catalog=EventBooking"providerName="System.Data.SqlClient"/>

<

addname="EventBookingConnectionStringServer"connectionString="Data Source=sqlsvr;Initial Catalog=EventBooking;Integrated Security=True"providerName="System.Data.SqlClient"/>

<

addname="KimsBookstoreConnectionStringServer"connectionString="Data Source=sqlsvr;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>

<

addname="KimsBookstoreConnectionString2Server"connectionString="Data Source=sqlsvr;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>

<

addname="NorthwindConnectionString"connectionString="Data Source=notebook\SQL2K5DEV;Initial Catalog=Northwind;Integrated Security=True"providerName="System.Data.SqlClient" />

<

addname="NorthwindConnectionStringServer"connectionString="Data Source=sqlsvr;Initial Catalog=Northwind;Integrated Security=True"providerName="System.Data.SqlClient" />

</

connectionStrings> //sqlsvr is my test server, that my friend built, the notebook source is my dev laptop where everything works fine

The error I get when I try to connect to the data driven EventBooking site is this (so odd, because the bookstore connects fine, although 2 images consistently err, where the others work fine):

Server Error in '/' Application.

---

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

Source Error:

Line 37: da = new SqlDataAdapter();Line 38: da.SelectCommand = cmd;Line 39: da.Fill(ds, "Rooms");Line 40: Line 41: ddlRooms.DataSource = ds;

Source File:e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingClient.aspx.cs Line:39

Stack Trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 EventBookingWebService_EventBookingClient_EventBookingClient.Page_Load(Object sender, EventArgs e) in e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingClient.aspx.cs:39 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

The error I get when I try to run the same app written with the web service is this:

Server Error in '/' Application.

---

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

Source Error:

Line 29: Calendar1.TodaysDate = DateTime.Now;Line 30: Calendar1.TodayDayStyle.BackColor = Color.Tan;Line 31: DataBind();Line 32: Line 33:

Source File:e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingWebServiceClient.aspx.cs Line:31

Stack Trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92 System.Web.UI.WebControls.ListControl.PerformSelect() +31 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.Control.DataBindChildren() +216 System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding) +216 System.Web.UI.Control.DataBind() +12 System.Web.UI.Control.DataBindChildren() +216 System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding) +216 System.Web.UI.Control.DataBind() +12 EventBookingWebService_EventBookingClient_EventBookingWebServiceClient.Page_Load(Object sender, EventArgs e) in e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingWebServiceClient.aspx.cs:31 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

And the error I get when trying to run the Northwind web service is this:

Server Error in '/' Application.

---

No connection could be made because the target machine actively refused it

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it

Source Error:

Line 45: [System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://www.1ocmcad.net/GetCustomers", RequestNamespace="http://www.1ocmcad.net", ResponseNamespace="http://www.1ocmcad.net", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]Line 46: public System.Data.DataSet GetCustomers() {Line 47: object[] results = this.Invoke("GetCustomers", new object[0]);Line 48: return ((System.Data.DataSet)(results[0]));Line 49: }

Source File:c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\fdd4322a\74322047\App_WebReferences.vmho5c06.1.cs Line:47

Stack Trace:

[SocketException (0x274d): No connection could be made because the target machine actively refused it] System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress) +1002146 System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP) +33 System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception) +431[WebException: Unable to connect to the remote server] System.Net.HttpWebRequest.GetRequestStream() +1504525 System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) +103 NorthwindCustomers.CustomerService.GetCustomers() in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\fdd4322a\74322047\App_WebReferences.vmho5c06.1.cs:47 WebServicesRemoting_ConsumeService.btnGetCustomers_Click(Object sender, EventArgs e) in e:\1ocmcad\WebServicesRemoting\ConsumeService.aspx.cs:22 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

I've enabled remote connections via named pipes & tcp/ip for the dev version of sql server 2005 as you suggested & also enabled sql server browser like the article you referred me to suggested.

I'm going to work on this all weekend, so there won't be any gaps in communication...I hope to hear from you soon!

Kim

|||

Ok, this is for all you guys who are looking for answers. I spent days on this problem (I won't say how many!) and it was all because of my lack of Sql Server knowledge. The initial problem "locked out of sql server mgmt studio" was an issue of my server not having sufficient memory. After that, I kept getting a myriad of login errors, which changed every time I tried another way to connect to the databases.

What the issue was, was that I didn't have permissions & users & logins set up right in management studio. I ended up creating a new user for each database under each databases' Security > Users folder of "BUILTIN\Administrators" and set role memberships so that that account could access the db's.

I hope this helps someone who is as frustrated as I was!

Thanks for all your help, Iori_Jay.

Monster

|||I'm glad to see you've solved the issue, really great job for a person not familiar with SQL|||

Thank you for the compliment! Hopefully one day in the future I will be answering other people's questions rather than asking a ton of them!!!

And on that issue, could you please take a look at my other question?http://forums.asp.net/thread/1471646.aspx

Thanks so much!

Monster

Locked out

I am using the desktop sqlexpress 2005 and I was experimenting with the account setting and I accidentally disabled my access. I am using windows authentication and there is no password, just the windows login name.

How can I reactivate the login for this connection. Any help is greatly appreciated.Log on as administrator, then try.sql

Locked files during backups on a cluster

Hello,
I am running an active-active cluster on SQL 2000 SP3a over a Windows 2003
Enterprise server. We have two production instances running (SQL1 and SQL2).
Each was setup on a different node originally. When the two instances are
running on the same node, the instance that was not setup on that node
experiences locked files and failed backups.
Here is an example:
SQL1 was setup on Node1 and SQL2 was setup on Node2. When SQL1 and SQL2 are
both running on Node1, our backup job fails because it sees that a couple of
the database backup files are locked. Even after I clear the locks and re-ru
n
the backup script, the job still fails for the same reason. Now when I run
the same job when SQL2 is back on Node2, the job completes successfully. Not
all of the database backups fail, just the ones that are over 2Gb.
Has anyone experienced this or heard about this before? I've checked posting
s
here as well as TechNet, but haven't found anything.
Thanks for your assistance.
Michael
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1> both running on Node1, our backup job fails
By 'our backup job', I assume you meant the SQL Server database backup job.
The problem should not have anything to do with how the two instances were
originally set up. Nor should it necessarily have anything to do with the
fact that the two instances were running on the same node. It was a
coincident that it did happen when the two instances were running on the sam
e
node.
To resolve the problem, you need to first determine what process(es) or
programs are locking the backup files. My hunch is that they are most likely
locked either by an anti-virus program or a network file backup job. In othe
r
words, the anti-virus or the network file backup job happened to collide wit
h
the database backup on this particular node.
One way to be sure about what is locking the backup file is to add another
step immediately before the SQL Server backup to dump out all the NT handles
or just the handles on the backup file. You can use the sysinternals tool
handle.exe for this purpose.
Linchi
"michaelg via droptable.com" wrote:

> Hello,
> I am running an active-active cluster on SQL 2000 SP3a over a Windows 2003
> Enterprise server. We have two production instances running (SQL1 and SQL2
).
> Each was setup on a different node originally. When the two instances are
> running on the same node, the instance that was not setup on that node
> experiences locked files and failed backups.
> Here is an example:
> SQL1 was setup on Node1 and SQL2 was setup on Node2. When SQL1 and SQL2 ar
e
> both running on Node1, our backup job fails because it sees that a couple
of
> the database backup files are locked. Even after I clear the locks and re-
run
> the backup script, the job still fails for the same reason. Now when I run
> the same job when SQL2 is back on Node2, the job completes successfully. N
ot
> all of the database backups fail, just the ones that are over 2Gb.
> Has anyone experienced this or heard about this before? I've checked posti
ngs
> here as well as TechNet, but haven't found anything.
> Thanks for your assistance.
> Michael
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200609/1
>|||Yes, I meant the SQL Server database backup jobs.
This is not a one-time event. No matter what time I run the database backup
job on Node1, the job fails. This makes me think that it is not a tape backu
p
or any scheduled event. When I moved back to Node2 this morning, the job ran
fine multiple times.
I am not familiar with the handle.exe. Is this a 3rd party tool or is it
included in Microsoft?
Thanks!
Linchi Shea wrote:[vbcol=seagreen]
>By 'our backup job', I assume you meant the SQL Server database backup job.
>The problem should not have anything to do with how the two instances were
>originally set up. Nor should it necessarily have anything to do with the
>fact that the two instances were running on the same node. It was a
>coincident that it did happen when the two instances were running on the sa
me
>node.
>To resolve the problem, you need to first determine what process(es) or
>programs are locking the backup files. My hunch is that they are most likel
y
>locked either by an anti-virus program or a network file backup job. In oth
er
>words, the anti-virus or the network file backup job happened to collide wi
th
>the database backup on this particular node.
>One way to be sure about what is locking the backup file is to add another
>step immediately before the SQL Server backup to dump out all the NT handle
s
>or just the handles on the backup file. You can use the sysinternals tool
>handle.exe for this purpose.
>Linchi
>
>[quoted text clipped - 18 lines]
Message posted via http://www.droptable.com|||This is a sysinternals tool available from www.sysinternals.com.
Linchi
"michaelg via droptable.com" wrote:

> Yes, I meant the SQL Server database backup jobs.
> This is not a one-time event. No matter what time I run the database backu
p
> job on Node1, the job fails. This makes me think that it is not a tape bac
kup
> or any scheduled event. When I moved back to Node2 this morning, the job r
an
> fine multiple times.
> I am not familiar with the handle.exe. Is this a 3rd party tool or is it
> included in Microsoft?
> Thanks!
> Linchi Shea wrote:
> --
> Message posted via http://www.droptable.com
>

Locked files during backups on a cluster

Hello,
I am running an active-active cluster on SQL 2000 SP3a over a Windows 2003
Enterprise server. We have two production instances running (SQL1 and SQL2).
Each was setup on a different node originally. When the two instances are
running on the same node, the instance that was not setup on that node
experiences locked files and failed backups.
Here is an example:
SQL1 was setup on Node1 and SQL2 was setup on Node2. When SQL1 and SQL2 are
both running on Node1, our backup job fails because it sees that a couple of
the database backup files are locked. Even after I clear the locks and re-run
the backup script, the job still fails for the same reason. Now when I run
the same job when SQL2 is back on Node2, the job completes successfully. Not
all of the database backups fail, just the ones that are over 2Gb.
Has anyone experienced this or heard about this before? I've checked postings
here as well as TechNet, but haven't found anything.
Thanks for your assistance.
Michael
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1> both running on Node1, our backup job fails
By 'our backup job', I assume you meant the SQL Server database backup job.
The problem should not have anything to do with how the two instances were
originally set up. Nor should it necessarily have anything to do with the
fact that the two instances were running on the same node. It was a
coincident that it did happen when the two instances were running on the same
node.
To resolve the problem, you need to first determine what process(es) or
programs are locking the backup files. My hunch is that they are most likely
locked either by an anti-virus program or a network file backup job. In other
words, the anti-virus or the network file backup job happened to collide with
the database backup on this particular node.
One way to be sure about what is locking the backup file is to add another
step immediately before the SQL Server backup to dump out all the NT handles
or just the handles on the backup file. You can use the sysinternals tool
handle.exe for this purpose.
Linchi
"michaelg via SQLMonster.com" wrote:
> Hello,
> I am running an active-active cluster on SQL 2000 SP3a over a Windows 2003
> Enterprise server. We have two production instances running (SQL1 and SQL2).
> Each was setup on a different node originally. When the two instances are
> running on the same node, the instance that was not setup on that node
> experiences locked files and failed backups.
> Here is an example:
> SQL1 was setup on Node1 and SQL2 was setup on Node2. When SQL1 and SQL2 are
> both running on Node1, our backup job fails because it sees that a couple of
> the database backup files are locked. Even after I clear the locks and re-run
> the backup script, the job still fails for the same reason. Now when I run
> the same job when SQL2 is back on Node2, the job completes successfully. Not
> all of the database backups fail, just the ones that are over 2Gb.
> Has anyone experienced this or heard about this before? I've checked postings
> here as well as TechNet, but haven't found anything.
> Thanks for your assistance.
> Michael
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1
>|||Yes, I meant the SQL Server database backup jobs.
This is not a one-time event. No matter what time I run the database backup
job on Node1, the job fails. This makes me think that it is not a tape backup
or any scheduled event. When I moved back to Node2 this morning, the job ran
fine multiple times.
I am not familiar with the handle.exe. Is this a 3rd party tool or is it
included in Microsoft?
Thanks!
Linchi Shea wrote:
>> both running on Node1, our backup job fails
>By 'our backup job', I assume you meant the SQL Server database backup job.
>The problem should not have anything to do with how the two instances were
>originally set up. Nor should it necessarily have anything to do with the
>fact that the two instances were running on the same node. It was a
>coincident that it did happen when the two instances were running on the same
>node.
>To resolve the problem, you need to first determine what process(es) or
>programs are locking the backup files. My hunch is that they are most likely
>locked either by an anti-virus program or a network file backup job. In other
>words, the anti-virus or the network file backup job happened to collide with
>the database backup on this particular node.
>One way to be sure about what is locking the backup file is to add another
>step immediately before the SQL Server backup to dump out all the NT handles
>or just the handles on the backup file. You can use the sysinternals tool
>handle.exe for this purpose.
>Linchi
>> Hello,
>[quoted text clipped - 18 lines]
>> Thanks for your assistance.
>> Michael
--
Message posted via http://www.sqlmonster.com|||This is a sysinternals tool available from www.sysinternals.com.
Linchi
"michaelg via SQLMonster.com" wrote:
> Yes, I meant the SQL Server database backup jobs.
> This is not a one-time event. No matter what time I run the database backup
> job on Node1, the job fails. This makes me think that it is not a tape backup
> or any scheduled event. When I moved back to Node2 this morning, the job ran
> fine multiple times.
> I am not familiar with the handle.exe. Is this a 3rd party tool or is it
> included in Microsoft?
> Thanks!
> Linchi Shea wrote:
> >> both running on Node1, our backup job fails
> >
> >By 'our backup job', I assume you meant the SQL Server database backup job.
> >
> >The problem should not have anything to do with how the two instances were
> >originally set up. Nor should it necessarily have anything to do with the
> >fact that the two instances were running on the same node. It was a
> >coincident that it did happen when the two instances were running on the same
> >node.
> >
> >To resolve the problem, you need to first determine what process(es) or
> >programs are locking the backup files. My hunch is that they are most likely
> >locked either by an anti-virus program or a network file backup job. In other
> >words, the anti-virus or the network file backup job happened to collide with
> >the database backup on this particular node.
> >
> >One way to be sure about what is locking the backup file is to add another
> >step immediately before the SQL Server backup to dump out all the NT handles
> >or just the handles on the backup file. You can use the sysinternals tool
> >handle.exe for this purpose.
> >
> >Linchi
> >
> >> Hello,
> >>
> >[quoted text clipped - 18 lines]
> >> Thanks for your assistance.
> >> Michael
> --
> Message posted via http://www.sqlmonster.com
>

lock pages in memory in standard edition

If the lock pages in memory cannot be used in standard edition, what exactly
do I lose ?
Using SQL 2005 x64 standard edition on windows 2003 standard editionAllocated memory if Windows decides to take it. "Trimming the working set"
has caused pain for several of my customers using the same versions as you
reference below
--
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Hassan" <hassan@.test.com> wrote in message
news:%23FJscm8TIHA.4532@.TK2MSFTNGP02.phx.gbl...
> If the lock pages in memory cannot be used in standard edition, what
> exactly do I lose ?
> Using SQL 2005 x64 standard edition on windows 2003 standard edition

lock pages in memory in standard edition

If the lock pages in memory cannot be used in standard edition, what exactly
do I lose ?
Using SQL 2005 x64 standard edition on windows 2003 standard edition
Allocated memory if Windows decides to take it. "Trimming the working set"
has caused pain for several of my customers using the same versions as you
reference below
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Hassan" <hassan@.test.com> wrote in message
news:%23FJscm8TIHA.4532@.TK2MSFTNGP02.phx.gbl...
> If the lock pages in memory cannot be used in standard edition, what
> exactly do I lose ?
> Using SQL 2005 x64 standard edition on windows 2003 standard edition

Lock Manager does a dead lock search?

I see the following in the SQL (error) log and am curious as to why it shows
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
jl
Sounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl
|||Lori,
We don't have the 1205 flag enabled.
jl
"Lori Clark" wrote:

> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>
>

Lock Manager does a dead lock search?

I see the following in the SQL (error) log and am curious as to why it shows
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
--
jlSounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl|||Lori,
We don't have the 1205 flag enabled.
--
jl
"Lori Clark" wrote:
> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
> >I see the following in the SQL (error) log and am curious as to why it
> >shows
> > up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> > 2000
> > SP4. This server uses the Intel EMT processor. We have two other
> > similarly
> > configured servers and I don't see these mesages on those servers (same
> > databases, etc.).
> >
> >
> > Starting deadlock search 34450
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
> >
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> > ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> > SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4
> >
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> > was not found.
> >
> > --
> > jl
>
>

Lock Manager does a dead lock search?

I see the following in the SQL (error) log and am curious as to why it shows
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
jlSounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl|||Lori,
We don't have the 1205 flag enabled.
jl
"Lori Clark" wrote:

> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBC
C
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>
>

Lock issue during insert

:confused:

I opened 2 sql analizer windows to simulate 2 users:

In the first one I did this:
begin transaction;
insert into tst values (15);

In the second one I sent
begin transaction;
select * from tst where col=3;

The second statement is blocked waiting for the lock to be released.
Why? I tried update in plce of the insert and then there is no lock.

What is the problem with insert ?

regards

phildo you have an index on col?
if not then the select will do a table scan and be blocked. It will return nothing until the output buffer is full or the lock is released.

In both cases you should find a few intent exclusive locks and at least one exclusive.

If the select tries to access a resource that is locked then it will be blocked - if not it won't be.

Maybe your insert was forcing some page splits whereas the update wasn't?|||You're right, it does not happen when an index is on the table.
Why this happen only for inserts , and not updates.
What do you mean by lock return when the buffer is full.
regards

phil|||>> What do you mean by lock return when the buffer is full.
When you run a query in query analyser the output will be dumped to the result window when the output buffer is full or when the query completes. That's why if you run a large select you will see the results in batches - and why it doesn't mean that a query is stuck after the last result displayed.

It should happen for both inserts and updates - it just depends on what is being locked.

lock

Hi,
i have a big problem , i work with sql 2000 on windows 2000.
When user do a select against my database sqlserver lock all the table and nonoe can work.
Haw can i change the isolation level for a ropw and for all the db.
Thanks.That's quite a heavy lock. What client/operation are you using?
Too me, sounds someone restricted the database: a db can be restricted to allow one single user at a time. Did you check that? It's in the database properties, Options tab.|||have you isolated the problematic code using profiler? somebody using nasty table hint or big long transaction? got code?|||Sounds like your user's query is doing a table scan. Get his query, and see if any indexes will help it. An index is a pre-requisite for row locking.

Location of MDF / LDF / Backup / Log

For a finance application, we use a Windows 2003 Server
with C and D are local HD (They are mirrored as RAID 1).
On the other hand, it is connected to a SAN with drive
letter J.
We find that the consultant has installed the SQLS Server
2000 DB MDF and LDF in the D drive. The finance
application is installed on the J drive.
We will backup both C,D and J drive daily. They haven't
setup the database maintenance plan yet.
For both performance purpose, we would like to get your
advie where is the best place to put the backup files and
log files ? Is it D or J drive ? Should we suggest them
to put the transaction log file from D to J as well ?
Thank you for your advice
Stephen wrote:
> For a finance application, we use a Windows 2003 Server
> with C and D are local HD (They are mirrored as RAID 1).
> On the other hand, it is connected to a SAN with drive
> letter J.
> We find that the consultant has installed the SQLS Server
> 2000 DB MDF and LDF in the D drive. The finance
> application is installed on the J drive.
> We will backup both C,D and J drive daily. They haven't
> setup the database maintenance plan yet.
> For both performance purpose, we would like to get your
> advie where is the best place to put the backup files and
> log files ? Is it D or J drive ? Should we suggest them
> to put the transaction log file from D to J as well ?
> Thank you for your advice
If you've paid for a SAN, it seems silly not use it for the data and
t-logs. Are you saying that C is a mirrored set and drive D is a
mirrored set (so that makes four local hard drives)? You don't have to
back up the drives themselves for SQL Server backup and recovery. What
you need to back up are the full, differential, and t-log backups you
make throughout the day. Generally, you want to put the backup files on
different spindles than the drive on which the data originates to
maximize bandwidth. So if the data is on the SAN, you can back up to one
of the local arrays.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Dear David,
Thank you for your advice. Yes, both C and D are mirrored set.
Someone suggest to put the transaction log file in the RAID 1 for recovery
and data in RAID 5. Is it OK from your point of view ?
Where should I put the backup file ? RAID 1 or RAID 5 array ?
Thanks
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> Stephen wrote:
> If you've paid for a SAN, it seems silly not use it for the data and
> t-logs. Are you saying that C is a mirrored set and drive D is a mirrored
> set (so that makes four local hard drives)? You don't have to back up the
> drives themselves for SQL Server backup and recovery. What you need to
> back up are the full, differential, and t-log backups you make throughout
> the day. Generally, you want to put the backup files on different spindles
> than the drive on which the data originates to maximize bandwidth. So if
> the data is on the SAN, you can back up to one of the local arrays.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Stephen
Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
buy RAID 10&1) put the log on
RAID1 device and data on RAID-5
(this option is appropriate if the writes activites are moderate)
Note: The appropriate decision depends on your perfomance charctiristics
and fault tolerance needs.
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Dear David,
> Thank you for your advice. Yes, both C and D are mirrored set.
> Someone suggest to put the transaction log file in the RAID 1 for recovery
> and data in RAID 5. Is it OK from your point of view ?
> Where should I put the backup file ? RAID 1 or RAID 5 array ?
> Thanks
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
mirrored[vbcol=seagreen]
the[vbcol=seagreen]
throughout[vbcol=seagreen]
spindles
>
|||Dear Uri,
Thank you for your advice. However, I am new to RAID array. I would like to
know the underlying reason to put the log on RAID 1 and data on RAID 5.
On the other hand, should we put the Database and Transaction log backup on
RAID 5 array ?
Thank you again.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Stephen
> Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
> buy RAID 10&1) put the log on
> RAID1 device and data on RAID-5
> (this option is appropriate if the writes activites are moderate)
>
> Note: The appropriate decision depends on your perfomance charctiristics
> and fault tolerance needs.
>
>
> "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> mirrored
> the
> throughout
> spindles
>
|||Stepfen
I'd recommend you buy "Inside SQL Server 2000" written by Kalen Delaney (See
topic about RAID solutions)
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:e2dG$6kdFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would like
to
> know the underlying reason to put the log on RAID 1 and data on RAID 5.
> On the other hand, should we put the Database and Transaction log backup
on[vbcol=seagreen]
> RAID 5 array ?
> Thank you again.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
charctiristics[vbcol=seagreen]
to
>
|||Stephen wrote:
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would
> like to know the underlying reason to put the log on RAID 1 and data
> on RAID 5.
> On the other hand, should we put the Database and Transaction log
> backup on RAID 5 array ?
>
I didn't think RAID 5 was one of your installed options given you only
had 4 local drives...
In any case, for small and medium sized databases with a low percentage
of writes, RAID 5 is an inexpensive solution. RAID 5 writes data very
slowly, so it's only appropriate for data files. Transaction logs and
tempdb should reside on a mirrored set (which you have). It's best for
performance to keep data and t-logs on different drives, which are also
separate from the OS. In your environment, you have a SAN (J), and 2
local mirrored sets (C and D).
I would recommend you use C for the OS and applications like SQL Server.
Use D for the t-logs and tempdb, and use the SAN for the data.
Everything needs to backed up in some fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||This website has a good intro to RAID levels and the basic pros & cons
(even if they are basically trying to sell you their products):
http://www.acnc.com/raid.html
(for those that can't afford Kalen's book _Inside SQL Server_, which in
an excellent book that I thoroughly recommend)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Stephen wrote:

>Dear Uri,
>Thank you for your advice. However, I am new to RAID array. I would like to
>know the underlying reason to put the log on RAID 1 and data on RAID 5.
>On the other hand, should we put the Database and Transaction log backup on
>RAID 5 array ?
>Thank you again.
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>
>
>

Location of MDF / LDF / Backup / Log

For a finance application, we use a Windows 2003 Server
with C and D are local HD (They are mirrored as RAID 1).
On the other hand, it is connected to a SAN with drive
letter J.
We find that the consultant has installed the SQLS Server
2000 DB MDF and LDF in the D drive. The finance
application is installed on the J drive.
We will backup both C,D and J drive daily. They haven't
setup the database maintenance plan yet.
For both performance purpose, we would like to get your
advie where is the best place to put the backup files and
log files ? Is it D or J drive ? Should we suggest them
to put the transaction log file from D to J as well ?
Thank you for your adviceStephen wrote:
> For a finance application, we use a Windows 2003 Server
> with C and D are local HD (They are mirrored as RAID 1).
> On the other hand, it is connected to a SAN with drive
> letter J.
> We find that the consultant has installed the SQLS Server
> 2000 DB MDF and LDF in the D drive. The finance
> application is installed on the J drive.
> We will backup both C,D and J drive daily. They haven't
> setup the database maintenance plan yet.
> For both performance purpose, we would like to get your
> advie where is the best place to put the backup files and
> log files ? Is it D or J drive ? Should we suggest them
> to put the transaction log file from D to J as well ?
> Thank you for your advice
If you've paid for a SAN, it seems silly not use it for the data and
t-logs. Are you saying that C is a mirrored set and drive D is a
mirrored set (so that makes four local hard drives)? You don't have to
back up the drives themselves for SQL Server backup and recovery. What
you need to back up are the full, differential, and t-log backups you
make throughout the day. Generally, you want to put the backup files on
different spindles than the drive on which the data originates to
maximize bandwidth. So if the data is on the SAN, you can back up to one
of the local arrays.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dear David,
Thank you for your advice. Yes, both C and D are mirrored set.
Someone suggest to put the transaction log file in the RAID 1 for recovery
and data in RAID 5. Is it OK from your point of view ?
Where should I put the backup file ? RAID 1 or RAID 5 array ?
Thanks
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> Stephen wrote:
>> For a finance application, we use a Windows 2003 Server
>> with C and D are local HD (They are mirrored as RAID 1).
>> On the other hand, it is connected to a SAN with drive
>> letter J.
>> We find that the consultant has installed the SQLS Server
>> 2000 DB MDF and LDF in the D drive. The finance
>> application is installed on the J drive.
>> We will backup both C,D and J drive daily. They haven't
>> setup the database maintenance plan yet.
>> For both performance purpose, we would like to get your
>> advie where is the best place to put the backup files and
>> log files ? Is it D or J drive ? Should we suggest them
>> to put the transaction log file from D to J as well ?
>> Thank you for your advice
> If you've paid for a SAN, it seems silly not use it for the data and
> t-logs. Are you saying that C is a mirrored set and drive D is a mirrored
> set (so that makes four local hard drives)? You don't have to back up the
> drives themselves for SQL Server backup and recovery. What you need to
> back up are the full, differential, and t-log backups you make throughout
> the day. Generally, you want to put the backup files on different spindles
> than the drive on which the data originates to maximize bandwidth. So if
> the data is on the SAN, you can back up to one of the local arrays.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Stephen
Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
buy RAID 10&1) put the log on
RAID1 device and data on RAID-5
(this option is appropriate if the writes activites are moderate)
Note: The appropriate decision depends on your perfomance charctiristics
and fault tolerance needs.
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Dear David,
> Thank you for your advice. Yes, both C and D are mirrored set.
> Someone suggest to put the transaction log file in the RAID 1 for recovery
> and data in RAID 5. Is it OK from your point of view ?
> Where should I put the backup file ? RAID 1 or RAID 5 array ?
> Thanks
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> > Stephen wrote:
> >> For a finance application, we use a Windows 2003 Server
> >> with C and D are local HD (They are mirrored as RAID 1).
> >> On the other hand, it is connected to a SAN with drive
> >> letter J.
> >>
> >> We find that the consultant has installed the SQLS Server
> >> 2000 DB MDF and LDF in the D drive. The finance
> >> application is installed on the J drive.
> >>
> >> We will backup both C,D and J drive daily. They haven't
> >> setup the database maintenance plan yet.
> >>
> >> For both performance purpose, we would like to get your
> >> advie where is the best place to put the backup files and
> >> log files ? Is it D or J drive ? Should we suggest them
> >> to put the transaction log file from D to J as well ?
> >>
> >> Thank you for your advice
> >
> > If you've paid for a SAN, it seems silly not use it for the data and
> > t-logs. Are you saying that C is a mirrored set and drive D is a
mirrored
> > set (so that makes four local hard drives)? You don't have to back up
the
> > drives themselves for SQL Server backup and recovery. What you need to
> > back up are the full, differential, and t-log backups you make
throughout
> > the day. Generally, you want to put the backup files on different
spindles
> > than the drive on which the data originates to maximize bandwidth. So if
> > the data is on the SAN, you can back up to one of the local arrays.
> >
> > --
> > David Gugick
> > Quest Software
> > www.imceda.com
> > www.quest.com
>|||Dear Uri,
Thank you for your advice. However, I am new to RAID array. I would like to
know the underlying reason to put the log on RAID 1 and data on RAID 5.
On the other hand, should we put the Database and Transaction log backup on
RAID 5 array ?
Thank you again.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Stephen
> Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
> buy RAID 10&1) put the log on
> RAID1 device and data on RAID-5
> (this option is appropriate if the writes activites are moderate)
>
> Note: The appropriate decision depends on your perfomance charctiristics
> and fault tolerance needs.
>
>
> "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>> Dear David,
>> Thank you for your advice. Yes, both C and D are mirrored set.
>> Someone suggest to put the transaction log file in the RAID 1 for
>> recovery
>> and data in RAID 5. Is it OK from your point of view ?
>> Where should I put the backup file ? RAID 1 or RAID 5 array ?
>> Thanks
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
>> > Stephen wrote:
>> >> For a finance application, we use a Windows 2003 Server
>> >> with C and D are local HD (They are mirrored as RAID 1).
>> >> On the other hand, it is connected to a SAN with drive
>> >> letter J.
>> >>
>> >> We find that the consultant has installed the SQLS Server
>> >> 2000 DB MDF and LDF in the D drive. The finance
>> >> application is installed on the J drive.
>> >>
>> >> We will backup both C,D and J drive daily. They haven't
>> >> setup the database maintenance plan yet.
>> >>
>> >> For both performance purpose, we would like to get your
>> >> advie where is the best place to put the backup files and
>> >> log files ? Is it D or J drive ? Should we suggest them
>> >> to put the transaction log file from D to J as well ?
>> >>
>> >> Thank you for your advice
>> >
>> > If you've paid for a SAN, it seems silly not use it for the data and
>> > t-logs. Are you saying that C is a mirrored set and drive D is a
> mirrored
>> > set (so that makes four local hard drives)? You don't have to back up
> the
>> > drives themselves for SQL Server backup and recovery. What you need to
>> > back up are the full, differential, and t-log backups you make
> throughout
>> > the day. Generally, you want to put the backup files on different
> spindles
>> > than the drive on which the data originates to maximize bandwidth. So
>> > if
>> > the data is on the SAN, you can back up to one of the local arrays.
>> >
>> > --
>> > David Gugick
>> > Quest Software
>> > www.imceda.com
>> > www.quest.com
>>
>|||Stepfen
I'd recommend you buy "Inside SQL Server 2000" written by Kalen Delaney (See
topic about RAID solutions)
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:e2dG$6kdFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would like
to
> know the underlying reason to put the log on RAID 1 and data on RAID 5.
> On the other hand, should we put the Database and Transaction log backup
on
> RAID 5 array ?
> Thank you again.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> > Stephen
> > Most of SQL Server DBA/Programmers ( yep if the budget does not permit
to
> > buy RAID 10&1) put the log on
> > RAID1 device and data on RAID-5
> > (this option is appropriate if the writes activites are moderate)
> >
> >
> >
> > Note: The appropriate decision depends on your perfomance
charctiristics
> > and fault tolerance needs.
> >
> >
> >
> >
> > "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> > news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> >> Dear David,
> >>
> >> Thank you for your advice. Yes, both C and D are mirrored set.
> >>
> >> Someone suggest to put the transaction log file in the RAID 1 for
> >> recovery
> >> and data in RAID 5. Is it OK from your point of view ?
> >>
> >> Where should I put the backup file ? RAID 1 or RAID 5 array ?
> >>
> >> Thanks
> >>
> >> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> >> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> >> > Stephen wrote:
> >> >> For a finance application, we use a Windows 2003 Server
> >> >> with C and D are local HD (They are mirrored as RAID 1).
> >> >> On the other hand, it is connected to a SAN with drive
> >> >> letter J.
> >> >>
> >> >> We find that the consultant has installed the SQLS Server
> >> >> 2000 DB MDF and LDF in the D drive. The finance
> >> >> application is installed on the J drive.
> >> >>
> >> >> We will backup both C,D and J drive daily. They haven't
> >> >> setup the database maintenance plan yet.
> >> >>
> >> >> For both performance purpose, we would like to get your
> >> >> advie where is the best place to put the backup files and
> >> >> log files ? Is it D or J drive ? Should we suggest them
> >> >> to put the transaction log file from D to J as well ?
> >> >>
> >> >> Thank you for your advice
> >> >
> >> > If you've paid for a SAN, it seems silly not use it for the data and
> >> > t-logs. Are you saying that C is a mirrored set and drive D is a
> > mirrored
> >> > set (so that makes four local hard drives)? You don't have to back up
> > the
> >> > drives themselves for SQL Server backup and recovery. What you need
to
> >> > back up are the full, differential, and t-log backups you make
> > throughout
> >> > the day. Generally, you want to put the backup files on different
> > spindles
> >> > than the drive on which the data originates to maximize bandwidth. So
> >> > if
> >> > the data is on the SAN, you can back up to one of the local arrays.
> >> >
> >> > --
> >> > David Gugick
> >> > Quest Software
> >> > www.imceda.com
> >> > www.quest.com
> >>
> >>
> >
> >
>|||Stephen wrote:
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would
> like to know the underlying reason to put the log on RAID 1 and data
> on RAID 5.
> On the other hand, should we put the Database and Transaction log
> backup on RAID 5 array ?
>
I didn't think RAID 5 was one of your installed options given you only
had 4 local drives...
In any case, for small and medium sized databases with a low percentage
of writes, RAID 5 is an inexpensive solution. RAID 5 writes data very
slowly, so it's only appropriate for data files. Transaction logs and
tempdb should reside on a mirrored set (which you have). It's best for
performance to keep data and t-logs on different drives, which are also
separate from the OS. In your environment, you have a SAN (J), and 2
local mirrored sets (C and D).
I would recommend you use C for the OS and applications like SQL Server.
Use D for the t-logs and tempdb, and use the SAN for the data.
Everything needs to backed up in some fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is a multi-part message in MIME format.
--030209030201000601050508
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
This website has a good intro to RAID levels and the basic pros & cons
(even if they are basically trying to sell you their products):
http://www.acnc.com/raid.html
(for those that can't afford Kalen's book _Inside SQL Server_, which in
an excellent book that I thoroughly recommend)
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Stephen wrote:
>Dear Uri,
>Thank you for your advice. However, I am new to RAID array. I would like to
>know the underlying reason to put the log on RAID 1 and data on RAID 5.
>On the other hand, should we put the Database and Transaction log backup on
>RAID 5 array ?
>Thank you again.
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>
>>Stephen
>>Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
>>buy RAID 10&1) put the log on
>>RAID1 device and data on RAID-5
>>(this option is appropriate if the writes activites are moderate)
>>
>>Note: The appropriate decision depends on your perfomance charctiristics
>>and fault tolerance needs.
>>
>>
>>"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
>>news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>>
>>Dear David,
>>Thank you for your advice. Yes, both C and D are mirrored set.
>>Someone suggest to put the transaction log file in the RAID 1 for
>>recovery
>>and data in RAID 5. Is it OK from your point of view ?
>>Where should I put the backup file ? RAID 1 or RAID 5 array ?
>>Thanks
>>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>>news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
>>
>>Stephen wrote:
>>
>>For a finance application, we use a Windows 2003 Server
>>with C and D are local HD (They are mirrored as RAID 1).
>>On the other hand, it is connected to a SAN with drive
>>letter J.
>>We find that the consultant has installed the SQLS Server
>>2000 DB MDF and LDF in the D drive. The finance
>>application is installed on the J drive.
>>We will backup both C,D and J drive daily. They haven't
>>setup the database maintenance plan yet.
>>For both performance purpose, we would like to get your
>>advie where is the best place to put the backup files and
>>log files ? Is it D or J drive ? Should we suggest them
>>to put the transaction log file from D to J as well ?
>>Thank you for your advice
>>
>>If you've paid for a SAN, it seems silly not use it for the data and
>>t-logs. Are you saying that C is a mirrored set and drive D is a
>>
>>mirrored
>>
>>set (so that makes four local hard drives)? You don't have to back up
>>
>>the
>>
>>drives themselves for SQL Server backup and recovery. What you need to
>>back up are the full, differential, and t-log backups you make
>>
>>throughout
>>
>>the day. Generally, you want to put the backup files on different
>>
>>spindles
>>
>>than the drive on which the data originates to maximize bandwidth. So
>>if
>>the data is on the SAN, you can back up to one of the local arrays.
>>--
>>David Gugick
>>Quest Software
>>www.imceda.com
>>www.quest.com
>>
>>
>>
>
>
--030209030201000601050508
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>This website has a good intro to RAID levels and the basic pros
& cons (even if they are basically trying to sell you their
products):<br>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.acnc.com/raid.html</a><br>">http://www.acnc.com/raid.html">http://www.acnc.com/raid.html</a><br>
<br>
(for those that can't afford Kalen's book <u>Inside SQL Server</u>,
which in an excellent book that I thoroughly recommend)<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Stephen wrote:
<blockquote cite="mide2dG$6kdFHA.3040@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">Dear Uri,
Thank you for your advice. However, I am new to RAID array. I would like to
know the underlying reason to put the log on RAID 1 and data on RAID 5.
On the other hand, should we put the Database and Transaction log backup on
RAID 5 array ?
Thank you again.
"Uri Dimant" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:urid@.iscar.co.il"><urid@.iscar.co.il></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl">news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Stephen
Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
buy RAID 10&1) put the log on
RAID1 device and data on RAID-5
(this option is appropriate if the writes activites are moderate)
Note: The appropriate decision depends on your perfomance charctiristics
and fault tolerance needs.
"Stephen" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:anonymous@.discussions.microsoft.com"><anonymous@.discussions.microsoft.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl">news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Dear David,
Thank you for your advice. Yes, both C and D are mirrored set.
Someone suggest to put the transaction log file in the RAID 1 for
recovery
and data in RAID 5. Is it OK from your point of view ?
Where should I put the backup file ? RAID 1 or RAID 5 array ?
Thanks
"David Gugick" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:david.gugick-nospam@.quest.com"><david.gugick-nospam@.quest.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl">news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Stephen wrote:
</pre>
<blockquote type="cite">
<pre wrap="">For a finance application, we use a Windows 2003 Server
with C and D are local HD (They are mirrored as RAID 1).
On the other hand, it is connected to a SAN with drive
letter J.
We find that the consultant has installed the SQLS Server
2000 DB MDF and LDF in the D drive. The finance
application is installed on the J drive.
We will backup both C,D and J drive daily. They haven't
setup the database maintenance plan yet.
For both performance purpose, we would like to get your
advie where is the best place to put the backup files and
log files ? Is it D or J drive ? Should we suggest them
to put the transaction log file from D to J as well ?
Thank you for your advice
</pre>
</blockquote>
<pre wrap="">If you've paid for a SAN, it seems silly not use it for the data and
t-logs. Are you saying that C is a mirrored set and drive D is a
</pre>
</blockquote>
</blockquote>
<pre wrap="">mirrored
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">set (so that makes four local hard drives)? You don't have to back up
</pre>
</blockquote>
</blockquote>
<pre wrap="">the
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">drives themselves for SQL Server backup and recovery. What you need to
back up are the full, differential, and t-log backups you make
</pre>
</blockquote>
</blockquote>
<pre wrap="">throughout
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">the day. Generally, you want to put the backup files on different
</pre>
</blockquote>
</blockquote>
<pre wrap="">spindles
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">than the drive on which the data originates to maximize bandwidth. So
if
the data is on the SAN, you can back up to one of the local arrays.
--
David Gugick
Quest Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.quest.com</a>">http://www.quest.com">www.quest.com</a>
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--030209030201000601050508--

Location of MDF / LDF / Backup / Log

For a finance application, we use a Windows 2003 Server
with C and D are local HD (They are mirrored as RAID 1).
On the other hand, it is connected to a SAN with drive
letter J.
We find that the consultant has installed the SQLS Server
2000 DB MDF and LDF in the D drive. The finance
application is installed on the J drive.
We will backup both C,D and J drive daily. They haven't
setup the database maintenance plan yet.
For both performance purpose, we would like to get your
advie where is the best place to put the backup files and
log files ? Is it D or J drive ? Should we suggest them
to put the transaction log file from D to J as well ?
Thank you for your adviceStephen wrote:
> For a finance application, we use a Windows 2003 Server
> with C and D are local HD (They are mirrored as RAID 1).
> On the other hand, it is connected to a SAN with drive
> letter J.
> We find that the consultant has installed the SQLS Server
> 2000 DB MDF and LDF in the D drive. The finance
> application is installed on the J drive.
> We will backup both C,D and J drive daily. They haven't
> setup the database maintenance plan yet.
> For both performance purpose, we would like to get your
> advie where is the best place to put the backup files and
> log files ? Is it D or J drive ? Should we suggest them
> to put the transaction log file from D to J as well ?
> Thank you for your advice
If you've paid for a SAN, it seems silly not use it for the data and
t-logs. Are you saying that C is a mirrored set and drive D is a
mirrored set (so that makes four local hard drives)? You don't have to
back up the drives themselves for SQL Server backup and recovery. What
you need to back up are the full, differential, and t-log backups you
make throughout the day. Generally, you want to put the backup files on
different spindles than the drive on which the data originates to
maximize bandwidth. So if the data is on the SAN, you can back up to one
of the local arrays.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dear David,
Thank you for your advice. Yes, both C and D are mirrored set.
Someone suggest to put the transaction log file in the RAID 1 for recovery
and data in RAID 5. Is it OK from your point of view ?
Where should I put the backup file ? RAID 1 or RAID 5 array ?
Thanks
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> Stephen wrote:
> If you've paid for a SAN, it seems silly not use it for the data and
> t-logs. Are you saying that C is a mirrored set and drive D is a mirrored
> set (so that makes four local hard drives)? You don't have to back up the
> drives themselves for SQL Server backup and recovery. What you need to
> back up are the full, differential, and t-log backups you make throughout
> the day. Generally, you want to put the backup files on different spindles
> than the drive on which the data originates to maximize bandwidth. So if
> the data is on the SAN, you can back up to one of the local arrays.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Stephen
Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
buy RAID 10&1) put the log on
RAID1 device and data on RAID-5
(this option is appropriate if the writes activites are moderate)
Note: The appropriate decision depends on your perfomance charctiristics
and fault tolerance needs.
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Dear David,
> Thank you for your advice. Yes, both C and D are mirrored set.
> Someone suggest to put the transaction log file in the RAID 1 for recovery
> and data in RAID 5. Is it OK from your point of view ?
> Where should I put the backup file ? RAID 1 or RAID 5 array ?
> Thanks
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
mirrored[vbcol=seagreen]
the[vbcol=seagreen]
throughout[vbcol=seagreen]
spindles[vbcol=seagreen]
>|||Dear Uri,
Thank you for your advice. However, I am new to RAID array. I would like to
know the underlying reason to put the log on RAID 1 and data on RAID 5.
On the other hand, should we put the Database and Transaction log backup on
RAID 5 array ?
Thank you again.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Stephen
> Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
> buy RAID 10&1) put the log on
> RAID1 device and data on RAID-5
> (this option is appropriate if the writes activites are moderate)
>
> Note: The appropriate decision depends on your perfomance charctiristics
> and fault tolerance needs.
>
>
> "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> mirrored
> the
> throughout
> spindles
>|||Stepfen
I'd recommend you buy "Inside SQL Server 2000" written by Kalen Delaney (See
topic about RAID solutions)
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:e2dG$6kdFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would like
to
> know the underlying reason to put the log on RAID 1 and data on RAID 5.
> On the other hand, should we put the Database and Transaction log backup
on
> RAID 5 array ?
> Thank you again.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
charctiristics[vbcol=seagreen]
to[vbcol=seagreen]
>|||Stephen wrote:
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would
> like to know the underlying reason to put the log on RAID 1 and data
> on RAID 5.
> On the other hand, should we put the Database and Transaction log
> backup on RAID 5 array ?
>
I didn't think RAID 5 was one of your installed options given you only
had 4 local drives...
In any case, for small and medium sized databases with a low percentage
of writes, RAID 5 is an inexpensive solution. RAID 5 writes data very
slowly, so it's only appropriate for data files. Transaction logs and
tempdb should reside on a mirrored set (which you have). It's best for
performance to keep data and t-logs on different drives, which are also
separate from the OS. In your environment, you have a SAN (J), and 2
local mirrored sets (C and D).
I would recommend you use C for the OS and applications like SQL Server.
Use D for the t-logs and tempdb, and use the SAN for the data.
Everything needs to backed up in some fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This website has a good intro to RAID levels and the basic pros & cons
(even if they are basically trying to sell you their products):
http://www.acnc.com/raid.html
(for those that can't afford Kalen's book _Inside SQL Server_, which in
an excellent book that I thoroughly recommend)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Stephen wrote:

>Dear Uri,
>Thank you for your advice. However, I am new to RAID array. I would like t
o
>know the underlying reason to put the log on RAID 1 and data on RAID 5.
>On the other hand, should we put the Database and Transaction log backup on
>RAID 5 array ?
>Thank you again.
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>
>
>sql