Best Way To Prepare A Read-Only Database

Question

We're taking one of our production databases and creating a copy on another server for read-only purposes. The read-only database is on SQL Server 2008. Once the database is on the new server we'd like to optimize it for read-only use.

One problem is that there are large amounts of allocated space for some of the tables that are unused. Another problem I would anticipate would be fragmentation of the indexes. I'm not sure if table fragmentation is an issue.

What are the issues involved and what's the best way to go about this? Are there stored procedures included with SQL Server that will help? I've tried running DBCC SHRINKDATABASE, but that didn't deallocate the unused space.

EDIT: The exact command I used to shrink the database was

DBCC SHRINKDATABASE (dbname, 0)
GO

It ran for a couple hours. When I checked the table space using sp_spaceused, none of the unused space had been deallocated.

Answer 1

I think it will be OK to just recreate it from the backup.

Putting tables and indexes on separate physical disks is always of help too. Indexes will be rebuilt from scratch when you recreate them on another filegroup, and therefore won't be fragmented.

Answer 2

Hi,

One thing you may wish to consider is to change the recovery model of the database to simple. If you do not intend to perform any write activity to the database then you may as well benefit from automatic truncation of the transaction log, and eliminate the administrative overhead of using the other recovery models. You can always perform ad-hoc backups should you make any significant structural changes i.e. to indexes.

You may also wish to place the tables that are unused in a separate Filegroup away from the data files that will be accessed. Perhaps consider placing the unused tables on lower grade disk storage to benefit from cost savings.

Some things to consider with DBCC SHRINKDATABASE, you cannot shrink beyond the minimum size of your database.

Try issuing the statement in the following form.

DBCC SHRINKDATABASE (DBName, TRUNCATEONLY);

Cheers, John

Answer 3

There is a tool for shrinking or truncating a database in MSSQL Server. I think you select the properties of the database and you'll find it. This can be done before or after you copy the backup.

Certain forms of replication may do what you wish also.

Answer 4

There are a couple of things you can do:

First -- don't worry about absolute allocated DB size unless you're running short on disk.

Second -- Idera has a lot of cool SQL Server tools, one of them defrags the DB. http://www.idera.com/Content/Show27.aspx

Third -- dropping and re-creating the clustered index essentially defrags the tables, too -- and it re-creates all of the non-clustered indexes (defragging them as well). Note that this will probably EXPAND the allocated size of your database (again, don't worry about it) and take a long time (clustered index rebuilds are expensive).