DBCC SHRINKFILE not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

DBCC SHRINKFILE not working

Author

Topic

dimepop
Starting Member

33 Posts

Posted - 2013-07-16 : 10:04:15

Hi, i have a SQL Server 2005 with a Dtabase in Full recovery mode, with a 80GB log
I try to shrink it with the command:
USE MYDB
GO
ALTER DATABASE MYDB
SET RECOVERY SIMPLE;
GO
BACKUP LOG MYDBWITH TRUNCATE_ONLY
DBCC SHRINKFILE(MYDB_log, 1)
GO

ALTER DATABASE MYDB
SET RECOVERY FULL;
GO

the command runs fine but it is not shrinking which command can i use?
Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-07-16 : 11:49:05

You need to issue a CHECKPOINT to clear the log in SIMPLE recovery before shrinking.

If that doesn't fix it run the following in that database:

dbcc loginfo

Check the Status column, look for the last row (Virtual Log file/VLF) where Status=2. That is as far back in the log file that you can shrink to (active portion of log). In order to shrink further you need to cycle the active log back to an earlier VLF. You can do that with:

USE MyDB;
CREATE TABLE testtable(i int not null default(0)
DECLARE @c int;
SET @c=1;
WHILE @c<1000 BEGIN
INSERT testtable DEFAULT VALUES;
SET @c=@c+1;
END
DROP TABLE testtable

Once that completes you would repeat the process to shrink the file, including the CHECKPOINT.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources