we have a database that only serves Select requests (no OLTP) for our very high traffic websites.
what are the benefits of turning on read-only setting for the database if there are no transactions occuring in the database?
does sql server put locks on tables for SELECT statements?
is it the same as using HINT (NOLOCK) ?
thanks
stephane
I copied over a small website to 2003 Web Server and got: Exception Details: System.Data.SqlClient.SqlException:
Failed to update database
"D:INETPUBWWWROOTLUNCHCOUNTAPP_DATALUNCHCOUNT.MDF" because the
database is read-only. It works just fine in VS - what to do? Thanks
Using SQL Express in this case, I have a database on one machine using one instance of SQLExpress that I detach. I then copy the database to a USB drive and take it to a different machine and attach it to a different SQL Express instance and it comes in as ReadOnly and I know of no way to change it.
One difference is that I have different users on the two systems, is that the cause? I wouldn't think that this would cause the database to show as ReadOnly, thoughI would just think that I may not be able to access it aside from using sa.
What is the likely cause of this?
Thanks
We have database when trying to make read only throwing below error: with stack dump
Location:Â Â Â Â Â Â Â Â Â Â Â Â Â recovery.cpp:4517
Expression:Â Â Â Â Â Â Â Â m_recoveryUnit->IsIntendedUpdateable ()
SPID:Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 51
Process ID:Â Â Â Â Â Â Â Â Â 6448
Msg 926, Level 14, State 1, Line 1
Database 'XXXX' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server errorlog for more information.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database 'XXXX', an error occurred at log record ID (0:0:0). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'XXXX' (database ID 7) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Investigation DONE:
- DBcc checkdb shown CleanÂ
- database is online and able to access
-Detached database and attached with rebuild log, still could not bring database read_only
SQL version : SQL server 2008 SP4.Â
How to run the profiler on always on read only database to troubleshoot an ssrs issue ?
View 0 Replies View RelatedI have downloaded and installed ASP.NET Ajax Sample applications from
http://ajax.asp.net/default.aspx?tabid=47&subtabid=471
I am trying to run the AJAX TaskList example under C:ProgramMicrosoft ASP.NETASP.NET AJAX Sample Applicationsv1.0.61025TaskList
First I moved the content of the TaskList folder to a virtual IIS directory, making it possible to debug the website on my local IIS server.
When I run the example I get prompted to Login or register as a new user. When I submit the registration form I receive the following error message:
Failed to update database "C:INETPUBWWWROOTTASKLISTAPP_DATAASPNETDB.MDF" because the database is read-only.
How can I modify the permissions for ASPNETDB.MDF in order to run the TaskList example.
Thanks
Hi,
I have sciprt that assign object
permissions to all databases.
I want to exclude all read only and dbo use only databases
Thank you
Alex
Hi,
We have a SQL server database on remote server. We are using ODBC connection to read and write into the table. But we have another user wants to retrieve(readonly access) our data on his pages. How can we create a user on our database and grant readonly access to that user?
Thanks..
Dear All,
I need experienced advice on Security in SQL Server 2005. All I need specific advice on restrcting users to access SQL Server database to have only READ access for certain tables.
Thanks,
I am having a SSIS package which imports data from Excel (2003) files on the netoprk.
Its so happenning that one of those files are opened by someone else & package is giving AcquireConnection error.
As Package is only reading data from the files, i want to open the files in ReadOnly mode
How can remove this error ?
Is there any way to provide a readonly custom property in the ProvideComponentProperties() method of a custom PipelineComponent implementation?
View 7 Replies View Relatedwhenenver i run cold fusion, it automatically gets my tables out of the master database instead of the new one i have created. i've added a user that has a default to the new database instead of the master, but i need to know how to use that new user correctly, and what i will need to do once i start adding more databases so that cold fusion will go to the appropriate database and not to the master.
View 3 Replies View RelatedI'm having trouble with a script component in which I'm trying to use two ReadOnlyVariables. If I use only one of the two variables, everything works without issue. If I use both of the variables (as part of a comma-delimited list) I get the following:
The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.
I don't believe the variables themselves are the problem. Both are scoped to the package level and I can use either of them if I have it as the only variable. Seems bug-like, but thought I'd get some ideas before pursuing that route.
I have two replicated databases i.e.
1. Database 1 is a live database where all live applications point
2. Database 2 is a replica of Database 1 and all reporting / BI applications point to this db. This is read-only.
Is it possible for reporting applications, pointing to Database 2, to create temporary tables in the read-only database?
Thanks
Hi experts,
Pls let me know , how to make normal db to readonly + accept tran logs
Rgds
Srinivas Varanasi.
Hi,I have a prod database (main bread & bread DB) and have a small accessdatabase that sales team is using... I created a login inside SQL 2000and gave db_read permission and SELECT permissions... and public.Role of public is EXEC store procs and view some systables...I linked those tables that I gave permissions through ODBC link intoaccess db with the user that I created inside SQL as readonly user...but to my surprise when I ran a insert query from access on SQLtables... I was able to update and insert data... if my ODBC link isonly for readonly.. why was I able to unpdate & insert data into SQLtable?I don't want to give write/update/insert permissions for SQL tables tosales team... or anyone outside SQL 2000 server DB.Is there any easy way to create viewonly users inside SQL?I created users like this: security -logins -new login...select none serverrole.. db access (bread & butter DB)Thank you,hj******Pardon my English...
View 1 Replies View RelatedAny idea to copy a standby/readonly database. We a have a standby/readonly DB from logshipping for hot standby and we would like to make a copy of this standby/readonly DB into a read/write DB in the same server for daily testing. Thanks in advance!
View 6 Replies View Related
Hi,
I have a database running on sqlserver 2000. This database (let's call it TestDatabase) has 2 filegroups called 'PRIMARY' and 'SECONDARY', and is bulk-logged. The 'SECONDARY' filegroup is set to readonly since no data gets changed there by the applications, it is read-only data. Sometimes this read-only data needs an update, so I need to update the data in the 'SECONDARY' filegroup by running some long data-operations. These data-operations are executed and checked on a another database (which is a backup of TestDatabase) called TestDatabaseDemo. When everything is correct in TestDatabaseDemo we can copy all objects from the 'SECONDARY'-filegroup of TestDatabaseDemo to the 'SECONDARY'-filegroup of TestDatabase using DTS. This takes a long time and I want to try if I could speed up things by using the following strategy:
Take a full backup of TestDatabase
Restore full backup as TestDatabaseDemo
Run long-running data-operations on TestDatabaseDemo
Take a backup of TestDatabaseDemo
Restore only the 'SECONDARY'-filegroup from TestDatabaseDemo to TestDatabase, and keeping the 'PRIMARY' filegroup from TestDatabase as it was at that moment.
Suppose we arrived at step 5, I am executing the following commands:
Code Snippet
--make full backup of TestDatabase
backup database TestDatabase to DISK='G: emp estdatabase.bak' with init
--make full backup of TestDatabaseDemo
backup database TestDatabaseDemo to DISK='G: emp estdatabasedemo.bak' with init
backup log TestDatabase to DISK='g: emp estdatabase.log'
--restore secondary filegroup from TestDatabasedemo-backup
restore database TestDatabase FILEGROUP='SECONDARY' FROM DISK='G: emp estdatabasedemo.bak'
with move 'TestDatabase_Data_Secondary' to 'G: empTestDatabase_Data_secondary.ndf', NORECOVERY
--restore primary filegroup from Testdatabase-backup
restore database TestDatabase FILEGROUP='PRIMARY' FROM DISK='G: emp estdatabase.bak'
with move 'TestDatabase_Data' to 'G: empTestDatabase_Data.mdf', NORECOVERY
--restore log and try to get db onlin
restore log TestDatabase FROM DISK='g: emp estdatabase.log' with recovery
I get the following error:
The log in this backup set terminates at LSN 6000000021500001, which is too early to apply to the database. A more recent log backup that includes LSN 6000000022400003 can be restored.
When trying to execute "RESTORE DATABASE TestDatabase WITH RECOVERY; " as last statement
I get error:
The database cannot be recovered because the files have been restored to inconsistent points in time.
How i can restore the read-only filegroup correctly?
Hi,
I am trying to implement a log shipping scenario in sql 2005 where the secondary server is in standby mode with the ability to roll change during failover.
With the help of BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2d7cc40a-47e8-4419-9b2b-7c69f700e806.htm) I can implement my scenario in Recovery mode, but not in standby mode. I use the following sql to put my primary in standby
BACKUP LOG [database]
TO DISK = @filename
WITH STANDBY = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupROLLBACK_UNDO_database.BAK'
GO
which works, but then my restore job fails on the last step. How can I put my primary db in standby mode in such a way that the log shipping restore will work?
Thanks
Andy
I have been using MySQL as the back end of my .Net 2.0 applications for a while using a custom membership provider without any problems.Having now switched to a new webhost which supports both MySQL and MS SQL Server 2005 and also creating a new site thought I should set up the new site to use MS SQL..... I am having trouble however setting up the SQL server to act as the membership provider. Following the advice on here I tried using aspnet_regsql.exe... Firstly, when trying to get the list of databasses on the remote server I get the following error: Failed to query a list of database names from the SQL Server. Invalid object name 'Sysdatabases' If I then just type in the name of my database and go through to the end I get the following error:Exception:An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 8152 and the SqlException message is: String or binary data would be truncated. I can connect to my MS SQL server remotely without any problem using the likes of MS SQL Server Management Studio Express. Is there any other way I can setup the databases needed for user and roles access control or is the usage of MS SQL much more complicated than what MySQL was?
View 4 Replies View Related. The BPA recommend that the model database setting for the items below be set to on. I can accomplish this task through the query analyzer and run the set command. (Set ANSI_NULLS on). The response is positive but when I re-run the report the setting are back off.
Why???
•QUOTED_IDENTIFIER
•ANSI_NULLS
•ANSI_WARNINGS
•ANSI_PADDING
•ANSI_NULL_DFLT_ON
•CONCAT_NULL_YIELDS_NULL
Hi people,
I'm in the process of trying to set up my first SQL Server database and things aren't going as per the book! (surprise, surprise).
The 'Identity Specification' option during Table creation, is 'greyed out' and won't let me set (Is Identity) to 'Yes' or anything else for that matter.
I guess that there's a simple answer to this one, but as a newbie to SQL Server ( and VB 2005) I'm curently at a loss.
Can anyone point me in the right direction?
Niknek
Hi,
I was just wondering if anybody came across this behaviour where closing a Fast Forward Read only cursor takes abnormally long time to close. I am running SQL Server 2005 standard edition.
Thanks
Nand
I'm having trouble changing data in my database. It is actually a VB cuestion, but you see, i just finished lesson 9 of the begginers Visual Basic 2005 video series and in my last run i can't save the data i've changed into the database. no errors are shown, it just does't change the values.
i know for a fact that the code is well written and there is no problem with the configuration of buttons and such, because i folowed the steps 50 times so I believe it must be a SQL configuration problem.
does anybony have any insight on what the problem might be.
THX malcolm
Hi,
There has been a lot of discussion about moving single databases or devices in same server. Can someone suggest the most simplest method to set up a identical server with all the logins, permissions and password etc?
Building a new server with sp_help_revdatabase and dumps/loads is relatively easy but moving the master is confusing to me. I should run the new server and databases for a little testing period (with diffrent server name, of course) and then go live by dropping the old and by adding the new. I remember someone said that this would be as easy as copying master.dat to new machine (when servers are identical), any experience or other suggestions?
TIA,
Al
Hi all.
Does anyone know how to set a database priority? I have a SQL Server that contains multipule databases. One of the databases is very high priority. I need to make sure that no matter what is running on the server that this database's processes take priority and nothing slows it down.
Thanks.
Is there any practical downside to setting ANSI_NULLS and QUOTED_IDENTIFIER to ON at the database level? Almost all devs will simply leave these default settings at the top of a stored proc script anyway.
View 2 Replies View RelatedI want to query my databases to find out if Auto Shrink is set. I know the status column in sysdatabases holds this value as a bit setting but I don't know how can I check this value using a query.
Canada DBA
Hello everyone, I am fairly new to SQL Server Express and I have a question I was hoping someone could help with. I know this will sound very wacky but please entertain the possibility of such scenario taking place...
Is it possible to connect to an instance of an SQL Server Express 2005, using asp, without having a database created and without configuring anything such as permits or users first ?
The SQL Server Express was installed on a PC that runs a web server as well but nothing else was done, I only have the instance name and I know the SQL Server is configured for local access only.
I was hoping to create a database and begin using it with an asp file that runs SQL commands I can input on a text window, but I can't find connection strings that work without specifying a database name, and also I don't know which user and password to use because it is set to windows authentication.
I thought the code would go something like this:
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Driver={SQL Server};Server=SQLinstance;Address=localhost,port;Database=;Uid=;Pwd="
To top things out the web server is configured to send out a generic error message, so I can't see what the error I'm getting actually is...
Any thoughts ?
Thanks.
Hi,When I create a new database from Enterprise Manager (right click ondatabases > New Database) I noticed that I am not prompted to create apassword or user name. Can I take it then that all databases under thesame local group will have the same password and user name?I have tied to access the newly created database using the samepassword and username but haven't had any luck.Thanks
View 3 Replies View RelatedI have created a snapshot
CREATE DATABASE AdventureWorks_Snapshot1200 ON
(NAME = N'AdventureWorks_Data', FILENAME=
N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAW_1200.ss')
AS SNAPSHOT Of AdventureWorks
I changed the records
AddressLine1
from 1970 Napa Ct. to 1970 Napa Ct. Apt 1
When i run
select * from Person.Address
AventureWorks_Snapshot1200 where addressid = '1'
select * from Person.Address
AventureWorks where addressid = '1'
Both appear as 1
When i change Apt to 2 and run it both appear 2
Changed again to 3 both appeared as 3 too.
I was under the impression that the snapshot would remain as 1 when the new update 2 has been performed.
Any light be appreciated.
Hi all :-)
I am new to Sql server and hope that someone in here might be able to help me out with a little advice on how to go about how to achive the following.
My boss wants me to set up a copy of one of our production databases, the copy is supposed to be used for running questions against thru businessobjects. There will be aprox 10 simultanious users, the original and the copy must never be further apart in time then 15 min. The database is today about 25 gigs and to make it even more complicated the copy must be available at all time during business hours (8-17). The solution is also supposed not to influence the production databases perfommance to much (really low impact to the performance is a key issue).
The options I have thought of is:
1. Use mirroring and then take a snapshot of the mirror for the users to connect to.
2. Replicate only the data that is needed.
3. Log shipping in a combination with the online restore option in sql 2005 server.
4. Log shipping and then take a snapshot of the copy for the users to connect to.
I have also thought about if the need for current data is that high it might even be better to let them run their questions directly against the production db instead since there will not be so many simultanious users.
Any of you guys that have any ideas, all tips will be very appreciated!
Thanks in advance :-)
/Wandy
Hey All,
I'm very new to ASP.NET and have recently begun using the databinding functionality. So far, all I've had to do is drag and drop the various controls and then use the data source wizard to set up an appropriate query. However, now I've got a bit of a different need. In the VB codebehind, i need to be able to ask whether something is true in the database, and then if it is, carry out functions. So, for example, what I need to do is:
if (SQLStatement "Select charttype FROM charts WHERE datasource = " & dropdownlist1.selecteditem.tostring()) Then
'do whatever
end if
Trouble is, I don't know how I would go about binding the data so that I could access it in this way. Any guidance would be very much appreciated.
Thanks,J Rankin