Read Only Database Performance Question

Jul 23, 2005

In a nutshell, I have a stored proc that takes appx 15 minutes when it uses
a read-only database, and about 2 minutes when the database is not
read-only.

Details:

SQL Server 7

The stored proc exists on a writable database on the same server. It stuffs
a subset of about 20000 rows into a temp table and repeatedly updates the
temp table while doing selects and joins on the read only database, for
example,

Select *
into #LocalTempTable
from SAMESERVER.dbo.ReadOnlyDatabaseTable

Update tmp set tmp.SomeColumn = 1
from #LocalTempTable tmp
inner join SAMESERVER.dbo.ReadOnlyDatabaseTable x on
tmp.ID = x.ID
where SomeCriteria = SomeValue, etc, etc.

If I change the read-only database to writable it works fine. There are no
explicit transactions used in the stored proc. On the same server, a
production database exists and is constantly using transactions and temp
tables, but I wouldn't think this is an overall server performance issue
because it handles the same load when the suspect database is not read-only.

Any thoughts will be greatly appreciated. I'm searching all over and can't
seem to find reference to this type of issue.

View 1 Replies


ADVERTISEMENT

Transact SQL :: Making Server Database Read / Write From Read Only

Jan 12, 2012

i attached adventure works in sql server 2008 and it showing as read only ,make it read write or remove read only tag from database.

View 11 Replies View Related

Reset Database Files From Read-only To Read-write

Jan 18, 2008

I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message:

Server: Msg 3415, Level 16, State 2, Line 1
Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded.

What command(s) are needed to make these files read_write?

thanks

View 7 Replies View Related

Read/Write Performance

Apr 21, 2006

Hello,

We currently run sql 2005 server and also sql express in our dev environments. We use sql express as an offline store (smart client). We have a similar/exact schema on the sql 2005 server and also the express.

We use the auto attach feature to connect to the express version of the database. Both the developer machines and the one that is running the sql 2005 server have exactly the same hardware configuration. The only difference may be that the server box is not running the VS.Net environment. The disk space etc is pretty much the same. Actually we run another database server(DB2) on the 2005 server machine.

We have observed that sql express is much slower and queries execute much slower aswell. For example, this may not be a totally scientific way of checking but a long running query on the server took only 2 minutes while on express it took longer than 9 minutes. The schema and data etc are the same.

Is there something we need to look into as far as read write speed/performance goes ?

TIA,

Avinash

View 6 Replies View Related

2000 Performance Issues...(MVP & SQL Mag, Plz Read)

Mar 26, 2001

I am hoping that an extremely experienced SQL Server DBA can assist in this critical matter.

We are in a 6.5 to 2000 upgrade process. Our current app runs in 6.5 with acceptable performance times. However, in our testing 2000 has performed miserably (4-10 times longer for identical processes with identical hardware configurations.) The SQL Server and Windows 2000 server has been configured, according to all documented books by MSPress, for optimal performance. We have reviewed all code that Profiler has helped us identify as potiental bottlenecks in the duration of our largest nightly process. The identified code has been scrutinized by both the development team and dba team using all known techniques in determining indexing, better logical and physical reads, optimal query executation plans and ect.

We are perplexed by having found out that identical, optimalized, indexed code runs the same or in most cases worse in 2000 then in 6.5. Our largest performing benchmark sp's has shown that a process in 2000 runs 2-3 times slower than 6.5.

Are there any undocumented tuning, optimizing or 6.5 to 2000 upgrade issues that can be shared as to why 2000 appears to run slower then 6.5? At this point, it would be greatly appreciated if a highly expereinced DBA can provide ANY insight. If no suggestion can be provided, perhaps a contact or names of VERY skilled SQL DBA's that can be contacted in assisting this problem, whether it is a third party or someone from MS.

TIA

View 9 Replies View Related

SQL 2012 :: Read Committed For Read Only Database?

Jun 27, 2014

i have a database which get refreshed every day from client's data . and we need to pull heavy data from them every day as reports . so only selects happens on that database.

we do daily population of some table in some other databases from this daily refreshed DB.

will read uncommitted or NOLOCK with select queries to retrieve data faster.

there will be no dirty read as there are NO DML operation in that database so for SELECT which happens concurrently on these tables , will NOLOCK work?

View 2 Replies View Related

Read Only Database Restore Bringing Database In Single User Mode?

May 9, 2012

I'm taking a database(read-only) backup from one server and restoring it on other server. As soon as restore is done it is bringing database into single-user read-only mode.

why it is bringing the database into single user mode ?

View 1 Replies View Related

Failed To Update Database C:INETPUBWWWROOTAPP_DATAASPNETDB.MDF Because The Database Is Read-only.

Mar 4, 2006

I'm new to ASP.NET (I normally work with Windows Forms) and have installed the Club Website Starter Kit. I created an Administrators role and a user called "admin". When I run the project locally, everything works as it should - I can log in as admin and I can create new users.
I then copied the site to localhost (C:Inetpubwwwroot), but when I run the site from localhost, I can't log in (as admin or any other user); instead I get a message that says, "Your login attempt was not successful. Please try again."
Also, I can't create a new user. When I try, I get the following error:
Server Error in '/' Application.


Failed to update database "C:INETPUBWWWROOTAPP_DATAASPNETDB.MDF" because the database is read-only.
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: Failed to update database "C:INETPUBWWWROOTAPP_DATAASPNETDB.MDF" because the database is read-only.Source Error:



An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:



[SqlException (0x80131904): Failed to update database "C:INETPUBWWWROOTAPP_DATAASPNETDB.MDF" because the database is read-only.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857242
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734854
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.Security.SqlMembershipProvider.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) +3612
System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +305
System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105
System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453
System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149
System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
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.42
I figure there must be a simple(?) explanation and solution for this problem, and ideally, some sort of step-by-step procedure that new users to ASP.NET can follow to configure a site - such as one of the starter kits - to work correctly. I can honestly say that trying to get this working has been an extremely frustrating experience.
If someone could help me out, I would really appreciate it. I've been working on this for two days and feeling I have hit the wall.
Thanks in advance.
 

View 1 Replies View Related

Failed To Update Database C:INETPUB... Because The Database Is Read-only.

Mar 28, 2006

Hi there,I am getting a real problem with ASP.NET 2.0, all i am trying to do is run a recovert password (using the login controls) and i got the following error.Does anybody know how to fix this? I have tried the SSEUTIL answer here and it didn't work. http://forums.asp.net/909168/ShowPost.aspxI also ensured that Network Services user has READ/WRITE/EXECUTE premissions on the directory below and that each file isn't read only and also that it inherits ACL from the directory which it seems to do..Bit of strange one this, it isn't created (and i am not using) a beta version of VS 2005 Pro... but a full retail version.The PC is the local PC with IIS 5.1 and VS installed... it is the one i am using for developing.. I don't need to copy my files to the IIS directory below as that is where i have created it and open it in VS 2005 from there..I did read that if you use IIS 5.1 then the user should be ASPNET ... but i don't seem to have that use but a Network Services user...Any help or guidance would be really appreciated... i have come to a stop stage and can't get any further..Thanksian
Server Error in '/igdotcom' Application.


Failed to update database
"C:INETPUBWWWROOTIGDOTCOMAPP_DATAASPNETDB.MDF" because the database
is read-only. 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: Failed to update database
"C:INETPUBWWWROOTIGDOTCOMAPP_DATAASPNETDB.MDF" because the database
is read-only.Source Error:



An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.

View 3 Replies View Related

Failed To Update Database Because The Database Is Read-only

Mar 31, 2008

hi
i am currently testing my website on IIS7, and first error is that:
Server Error in '/' Application.


Failed to update database "C:INETPUBWWWROOTAPP_DATAASPNETDB.MDF" because the database is read-only.
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: Failed to update database "C:INETPUBWWWROOTAPP_DATAASPNETDB.MDF" because the database is read-only.Source Error:



Line 109:
Line 110: conn.Open();
Line 111: SqlDataReader reader = command.ExecuteReader();
Line 112: reader.Close();
Line 113: command.Connection.Close();
i cheked on aspnet_Data folder to see if is read-only AND IT IS, but if i change it, the read-only property is setting up again. why?
thanks in advance

View 1 Replies View Related

Could Not Run BEGIN TRANSACTION In Database 'whatever' Because The Database Is Read-only.

Jul 20, 2005

I had a databases running on SQL Server (EVALUATION VERSION).I moved it to another system running:-Windows 2000 Server-IIS 5.0-BlueDragon Server-MSDEI applied all of the latest service packs, security updates, etc...Intalled SQL Server Web Data Administrator and .NET framework.I attached the database using sp_attach_db:[color=blue]>osql –U sa>password>Sp_attach_db ‘whatever',>@filename1 = ‘C:Program FilesMicrosoft SQL[/color]ServerMSSQLDataCollaborationTools.mdf',[color=blue]>@filename2 = ‘C:Program FilesMicrosoft SQL[/color]ServerMSSQLDataCollaborationTools.ldf'[color=blue]>go[/color]I added my datasource using the ODBC Admin Tool...Verified it using the BlueDragon Admin Datasources page....But now I'm getting the error message:'Could not run BEGIN TRANSACTION in database 'whatever' because thedatabase is read-only.'Any help/guidance would be appreciated.Thanks very much.Shaun

View 3 Replies View Related

Failed To Update Database Because The Database Is Read-only.

Dec 19, 2006

I am building a Windows Forms application in VS 2005, using C# and SQL Server 2005 Express as the backend.



When I try to accessd data from the db, it works with no problems.



When I try to insert/update/delete, I get the following error message



Failed to update database "DBNAME" because the database is read-only.



I am using a connection string with the following syntax:

"Data
Source=.SQLEXPRESS;AttachDbFilename=c:PATH_TO_DBdb.mdf;Database=MyDB;Integrated
Security=False;User Instance=False;User ID=USR;Password=PWD;"



I have made sure that the permissions on the DB are set (in Windows) so
that all users can modify. The user that is logging in to the DB has
status set to DBOWNER.



I have seen others experience the same problem - with ASP.net sites,
not Windows Forms sites (thus most of their solutions dont apply to me)



Any ideas on what is wrong and how I can get this to work?

View 9 Replies View Related

Database Is Read-only

Oct 2, 2007

getting this error after publishing my site to local IIS, then trying my signup page.
Failed to update database "C:INETPUBWWWROOTAPP_DATAASPNETDB.MDF" because the database is read-only.
have tryed to change the attributes, still not working. have given write access to the APP_DATA folder from IIS, still not working.
and i cannot delete the APP_DATA folder from IIS, it says it is being used by another process or program. how to fix this? and or delete the folder?
 

View 1 Replies View Related

The Database Is Read-only.

May 31, 2005

I use MS Sql DataBase file (SQL 2005 )as the conneciton in my project.When I use GridView to update my database, it fail:The error message is :Failed to update database "D:DATAASPNETHEALTH8APP_DATADBHEALTH.MDF" because the database is read-only. What is the problem with it?

View 12 Replies View Related

Read Only Database

May 8, 2000

Hello,

I'm optimizing a database running under SQL 7.0. Have anyone any knowledge wether setting the database to "read only" can enhance performance? The database is used only for reading, one table is updated via DTS on a hourly basis, the rest of the tables on a daily basis.

Brgds

Jonas Hilmersson

View 3 Replies View Related

Database Is Read-only.

Aug 5, 2005

Hi
I have been learning on how to use the new features of the .NET 2.0, and Well I using VS2005 and everything is pretty straight foward. I did a Membership webform where you log in and create users and roles etc. I installed the SQL Server 2005 express as my Data provider and thats where my Tables are. I used the aspnetsql_reg comand to integrate the server to this application. Anyway my problem started when i decided to copy my project to my IIS server and well when I run the app from the Browser tells me that he Database It's set to Read-Omly and therefore it cant update it. This happens right after log in. but then I open the App. on hte VS and it runs find.
This is the error:

View 58 Replies View Related

Read Only Database

Mar 8, 2007

hi

I'm having a problem while attaching the database to the server through server management studio. The database is getting attached as read only database.

But when i'm attaching it through command prompt or using a batch file it is getting attached properly

Can any one suggest the rason and a reamedy for avoiding this problem

View 3 Replies View Related

Read-Only Database

Jun 25, 2007

Hi,



I've reatored a database into my SQL Server 2005. It has been restored as "Read Only" Database. So I couldn't make any changes in my database. I don't know how to make the "Read Only" database as a Writable one. Can you help me out?



Thanks

View 3 Replies View Related

Read Only Database

Aug 3, 2007

Hi guys,
I'm using SQL Server 2005 Express. When i'm attaching My project database through "SQl Server Management Studio" The database is getting attached as a 'Read only' database. But when i'm attaching the same database from command prompt or using a batch file it is getting attached normally.
The database is from another system. It is behaving the same on the parent system on which it is developed.

View 4 Replies View Related

C:INETPUBWWWROOTSLEPCHAAPP_DATAASPNETDB.MDF Because The Database Is Read-only.

Jul 29, 2005

i have created a appl and deployed it in IIS .
 
When i login from the login page i get a error :Server Error in '/slepcha' Application.--------------------------------------------------------------------------------
Failed to update database "C:INETPUBWWWROOTSLEPCHAAPP_DATAASPNETDB.MDF" because the database is read-only. 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: Failed to update database "C:INETPUBWWWROOTSLEPCHAAPP_DATAASPNETDB.MDF" because the database is read-only.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 
Stack Trace:

View 1 Replies View Related

Read DataBase File

Jan 17, 2002

Hi,
I received a SQL Server 6.5 database (.dat and .Log files). I'm currently running 7.0. Is there any way to read/import this data?
I tried to do this:
I have installed SQL Server 6.5. And After install, I have created a database with the same names (.dat an .log) and make it bigger than the first .dat and .log files.
Then, I have stoped the SQL server service, have deleted the .DAT and .LOG files that i have created, and have copied the first .DAT and .LOG in their place. I have Started the SQL server service and the database was beeing suspected. I stil unable to read data.
Can you help me?

View 1 Replies View Related

MS SQL Read MS Access Database

Aug 29, 2006

I have an existing MS SQL 2005 server which is used to maintain data that has been collected from various sources around a plant. A new system has been added on our network where the data is provided in a daily MS Access database. The name of the MS Access database changes every day.

I would like to use the job manager to run a Stored Proc every night to extract the daily data from the MS Access database. Is there any way to do this.

Regards, Eric

View 1 Replies View Related

Restore Database By Read-only

Jan 10, 2008

Hello, everyone:

I have two questions about database restorsation.

1. From Enterprise Manager, I can choose Read-only model (able to install another transaction log). How to execute same model restore by T-SQL? This script is used by Loading model:

RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE 'MyNwind' TO 'c:Program FilesMicrosoft SQL ServerMSSQLDataNewNwind.mdf',
MOVE 'MyNwindLog1' TO 'c:Program FilesMicrosoft SQL ServerMSSQLDataNewNwind.ldf'
RESTORE LOG MyNwind
FROM MyNwindLog1
WITH NORECOVERY


2. If a database is in Read-only Warm Stand-by because restore trans log files, how to turn of read-only model?

Thanks

ZYT

View 2 Replies View Related

Read-Only Database Transaction Log

Apr 12, 2007

I still have transactions in use mainly to set isolation levels. There are obviously no update/delete statements in them. I'm pretty sure this will still add rows to the transaction log even though there won't be any writes to the db data files. Is it still a critical best practice to use RAID 1/0 instead of RAID 5 for the logs? Looking to re-use some existing storage rather than buy new ones. Thoughts?

View 1 Replies View Related

SQL DataBase MDF File Read Only

Nov 22, 2007



Dear All

I have some problem, I User Visual Web Developer 2005 Express to Create DateBase Files, when I copy the code and Database file to Server then user remote client to insert records and update, but it alert the error :

'/telecom' 應用程�中發生伺�器錯誤。


Failed to update database "D:TELECOMWORKSAPP_DATAPRODUCT.MDF" because the database is read-only.
æ??è¿°: 在執行目å‰? Web è¦?求的é?Žç¨‹ä¸­ç™¼ç”Ÿæœªè™•ç?†çš„ä¾‹å¤–æƒ…å½¢ã€‚è«‹æª¢é–±å †ç–Šè¿½è¹¤ä»¥å?–得錯誤的詳細資訊,以å?Šåœ¨ç¨‹å¼?碼中產生的ä½?置。

例外詳細資訊: System.Data.SqlClient.SqlException: Failed to update database "D:TELECOMWORKSAPP_DATAPRODUCT.MDF" because the database is read-only.



How to do this care. thanks

View 1 Replies View Related

Read-Only Database Problem

Jun 25, 2006

Hello

Problem with Read-Only (gray color) database

I attached my database (shortly db) to sql server, after somedays without deattaching db i changed db path in my hard. Then when i loged in to sql server express I can't open db, So I deleted db and then attached it again. Now sql server shows my db as "Read-Ony" !!! I can't delete it or do anything and working on it causes sql server hanging up (Not work and stop).

What should i do? I can't attach my db.

View 3 Replies View Related

SQL Express Database Read-only Problem

Oct 10, 2006

Hi,I have a site and I am using SQL Express to store my data. Since I need to install the site on many different machines, I need to have an installer. I have added a visual studio "Web Setup Project"() to my solution which is handling the installation and it is doing it fine. The only problem I have is that after each installation, my database becomes read-only and as far as I have found, tat is related to user's rights on App_Data folder. How can I solve this problem or is there a way for installer to set permissions too? Is there any other better solution at all?Thanks for your help,Cheers

View 1 Replies View Related

Cannot Update. Database Or Object Are Read-only

Aug 16, 2004

I have to build application Excel (interface) - SQL Server (backend)

I've created Data Source File "test.dsn" and tested succesfully.
SQL Server database in on the other machine.
This is a code that I am using.

And error message I am geting is "Cannot update. Database or object are read-only".

Dim cn As ADODB.Connection
Dim strSQL As String

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Documents and SettingspdDesktopDevelopment est.xls;" & _
"Extended Properties=Excel 8.0"


strSQL = "INSERT INTO [DSN = test.dsn].Region Values(5, 'ab');"


cn.Execute strSQL

cn.Close
Set cn = Nothing


Read-only of databse properties is not selected.

View 2 Replies View Related

Adding A User To A Read-Only Database

Aug 1, 2007

Hey guys,

I currently have a sql server 2000 sp4 instance with a read-only db on it. We get tlogs shipped from an outside vendor and keep a copy of the production database in house for reporting purposes. The database is in read-only mode because we apply new tlogs daily. Recently we did a full recover and applied 2 tlogs.

The issue:
There is a user YYY in the database and at the server level, but they aren't linked. So I do what I usually do and run the following command:

exec sp_change_users_login 'Update_One', 'YYY','YYY'

Except for I get the following error:

Server: Msg 3906, Level 16, State 1, Procedure sp_change_users_login, Line 109
Could not run BEGIN TRANSACTION in database 'ZZZ' because the database is read-only.

Now this error makes sense and I understand why, so my question is how do I fix the users not being linked?

View 1 Replies View Related

SQL 2012 :: Read Only Copy Of Particular Database?

Aug 28, 2014

We are using 208r2. We used to generate simple reports against to prod. The production is running slow. Just we need the production fresh copy all the time. We are using mainly one database. So I need to have read only copy of that particular database which is sync with the production.

View 3 Replies View Related

SQL 2012 :: Read Only Database Transfer

Dec 4, 2014

I have an inter-company problem.

The one company gathers data into a SQL database hosted within their secure network and now one of the companies (that they are contracted to gather the data for) wants a backup of the data or a replicated database to be able to be placed onto a external HDD or different SQL server on their own network and server.

The catch is that to maintain their data security the original company doesn't want the company that is receiving the data to be able to edit the data. They are concerned that someone might change the data and claim that the data that they gathered is incorrect.

Is there a way to ensure the SQL database remains entirely read only?

View 1 Replies View Related

SQL 2012 :: Read Only Database Copy?

Aug 24, 2015

we would like something like Mirroring with a Snapshot database so that the "replicated" site can read the database locally.Unfortunately, this requires SQL Enterprise edition on the reading site if I get this right.Is there a cheaper way to achieve this sort of functionality?

View 9 Replies View Related

Attached Database Showing Read-only

Feb 27, 2008

I attached my database to SQL 2005 but it's showing up as read-only for some reason. I've attached this before just fine in another instance of the Vista OS. Why would it attach differently this time compared to all other times and show read only?

The .mdb and .ldf files are not read only if you right-click and check out their properties.

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved