|
|
You can gain significant performance gains if you have the luxury of placing your database in read-only mode. Since you don't risk reading 'dirty' data, SQL Server can skip much of the locking it would normally perform. This is handy in a Decision Support System (DSS) where you may only be updating the data nightly from a large extract, and then running reports throughout the day on the system. In this scenario, you could take the database out of read-only mode before the data load and then place it back in the mode after the load occurs.
Tip | Consider placing aged data into a read-only database. For example, place data for invoices over a year old in a separate database, because the data is not updated. You can still merge that data with current data by using the UNION ALL clause. |
To place your database in read-only mode, use the ALTER DATABASE command as follows:
ALTER Database pubs SET READ_ONLY
Anyone who attempts to update data thereafter receives the following error:
Server: Msg 3906, Level 16, State 1, Line 1 Could not run BEGIN TRANSACTION in database 'pubs' because the database is read-only.
The error is also returned to users who attempt to modify the schema while the database is in read-only mode. You can take the database out of this mode by using the READ_WRITE keyword as shown in the following syntax:
ALTER Database pubs SET READ_WRITE
Any users who attempt to connect while the database is being switched between modes receive the following error:
Server: Msg 952, Level 16, State 1, Line 1 Database 'pubs' is in transition. Try the statement later.
When taking a database in and out of read-only mode, you may experience the following error:
Server: Msg 5061, Level 16, State 1, Line 1 ALTER DATABASE failed because a lock could not be placed on database 'pubs'. Try again later. Server: Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.
Usually, the error is generated by one of the following causes:
Another process may be trying to place the database in read-only mode at the same time.
Someone is viewing the database in Enterprise Manager or in the Object Browser.
When you're going to place a database in read-only mode, consider using the script I covered in the 'Disconnecting Users' section of this chapter. This avoids problems by killing all the connections before you change the database status.
|
|