SQL Server 2005 收缩和重建索引
我们有一个每周维护计划来缩小所有用户数据库并重建其索引。在我们创建只读数据库之前，这一切都工作正常，现在每次计划运行时，由于其只读状态，它在开始处理该数据库时都会失败。
据我所知，我们有两个选项从数据库中删除只读标志，这是可能的，但由于数据库仅每季度更新一次，从性能的角度来看，使用只读功能是有意义的。或者手动选择计划应运行的数据库，即除只读数据库之外的所有用户数据库，这要求人们记住将任何新数据库添加到计划中。
有人对更好的方法有什么建议吗？
谢谢尼尔
We have a weekly maintenance plan to shrink all user databases and rebuild their indexes. This has working fine until we created a read-only database, now each time the plan runs it fails when it starts processing this database due to its read only state.
As far as I can see we have two options remove the read only flag from the database, this is possible but as the database is only updated once a quarter it makes sense from a performance point of view to make use of the read-only feature. Or manually select the database that the plan should run for i.e. all the users databases apart from the read only one, this then requires people to remember to add any new databases into the plan.
Does anyone have any suggestions of a better way of doing this?
Thanks
Neil
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你为什么首先要缩小数据库？
也没有必要像这样维护只读数据库。
why are you shrinking the database in the first place?
also there's no need to maintain read opnly db's like that.
如果您不想自定义维护计划，我会删除只读标志。
为什么还要缩小数据库？如果数据库增长到给定大小，那么这可能就是它的自然当前大小。
另请记住，索引重建（经验法则）需要目标表大小 120% 的可用空间。例如，500 MB 的表需要 600 MB 的可用空间。
缩小然后重建是没有意义的......而且你也会遇到可怕的文件碎片
I'd remove the read only flag if you don't want to customise the maint plan.
Why are you shrinking DBs too? If the database grows to a given size, then this is probably it's natural current size.
Also remember that an index rebuild (rule of thumb) require free space of 120% of target table size. Eg 500 MB table needs 600 MB free space.
It's pointless to shrink then rebuild... and you'll have horrendous file fragmentation too
我想可以修改维护计划，以“执行 T-SQL 语句”步骤开始，该步骤删除只读标志（
ALTER DATABASE database-name SET READ_WRITE）并添加最后一步来重置它：ALTER DATABASE 数据库名称 SET READ_ONLY
I suppose could modify the maintenance plan to start with a 'Execute T-SQL Statement' step, which removes the readonly flag (
ALTER DATABASE database-name SET READ_WRITE) and add a final step to reset it:ALTER DATABASE database-name SET READ_ONLY