CODEWITHSUNDEEP
sql-server
Jaylen
Jaylen

Reputation: 40381

How to shrink a database in MS SQL Server if the drive is full?

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?

I tried the following also

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

but got the following error

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.

Thanks

Upvotes: 5

Views: 13887

Answers (2)

gvee
gvee

Reputation: 17171

SQL Server Pre 2012

BACKUP LOG LogName
  WITH TRUNCATE_ONLY;

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;

After which, don't forget to re-instate your original recovery model!

Shrinking the log

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);

Upvotes: 4

jean
jean

Reputation: 4350

Short answer: BACKUP LOG WITH TRUNCATE_ONLY

Long answer: Backup your DB and read this first:

Managing Transaction Logs

Upvotes: 0

Related Questions