准备只读数据库的最佳方法

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.

我们正在使用我们的一个生产数据库并在另一台服务器上创建副本以用于只读目的。只读数据库位于SQL Server 2008上。一旦数据库位于新服务器上,我们希望将其优化为只读使用。

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.

涉及的问题是什么,最好的方法是什么? SQL Server附带的存储过程是否有帮助?我已经尝试运行DBCC SHRINKDATABASE,但是没有释放未使用的空间。

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.

它跑了几个小时。当我使用sp_spaceused检查表空间时,没有释放任何未使用的空间。

4 个解决方案

#1

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

第二 - Idera有很多很酷的SQL Server工具,其中一个工具可以解析数据库。 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).

第三 - 删除并重新创建聚簇索引本质上也会对表进行欺骗 - 并且它会重新创建所有非聚簇索引(也对它们进行碎片整理)。请注意,这可能会扩展数据库的已分配大小(再次,不要担心)并且需要很长时间(聚簇索引重建很昂贵)。

#2

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.

使用DBCC SHRINKDATABASE需要考虑的一些事项,您不能缩小超出数据库的最小大小。

Try issuing the statement in the following form.

尝试以下面的形式发出声明。

DBCC SHRINKDATABASE (DBName, TRUNCATEONLY);

Cheers, John

#3

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.

将表和索引放在不同的物理磁盘上总是有帮助的。当您在另一个文件组上重新创建索引时,将从头开始重建索引,因此不会对其进行分段。

#4

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.

有一个工具可以缩小或截断MSSQL Server中的数据库。我想你选择数据库的属性,你会发现它。这可以在复制备份之前或之后完成。

Certain forms of replication may do what you wish also.

某些形式的复制也可以按照您的意愿进行。

#1