如果驱动器已满，如何缩小 MS SQL Server 中的数据库? [英] How to shrink a database in MS SQL Server if the drive is full?

问题描述

我遇到了 SQL Server 问题，我无法执行任何查询，因为托管数据库的硬盘已满.我尝试使用 MS SMS 中的收缩"功能来收缩数据库，但由于磁盘空间已满，这不起作用.

I am having issue with SQL Server where I Can't execute any queried because the Hard Drive that hosts the databases is full. I tried to shrink the database using the "Shrink" function in MS SMS but that did not work because the disk space is full.

如何在不使数据库崩溃的情况下截断日志并缩小大小?

How can I truncate the logs without crashing the database but also shrink the size?

我也尝试了以下方法

USE myDatabaseName;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE myDatabaseName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (myDatabaseName_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE myDatabaseName
SET RECOVERY FULL;
GO

但出现以下错误

Msg 3023, Level 16, State 3, Line 2
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.

谢谢

推荐答案

SQL Server Pre 2012

BACKUP LOG LogName
  WITH TRUNCATE_ONLY;

SQL Server 2012 及以上

WITH_TRUNCATEONLY 已被删除，因此您应该将恢复模式切换为简单并恢复以执行相同的操作.

SQL Server 2012 onwards

The WITH_TRUNCATEONLY has been removed so you should switch the recovery model to Simple and revert to perform the same action.

ALTER DATABASE DatabaseName
  SET RECOVERY SIMPLE;

之后，不要忘记恢复原始恢复模型！

仅仅因为您截断了日志，这并不意味着磁盘上的文件大小发生了变化.

Just because you've truncated the log this does not mean that the file size on disk has changed.

要减小日志的文件大小，您需要发出以下命令

To reduce the file size of the log you need to issue the following command

DBCC SHRINKFILE (DatabaseLogLogicalName, 1);

这篇关于如果驱动器已满，如何缩小 MS SQL Server 中的数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！