SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 DBCC SHRINKFILE not working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dimepop
Starting Member

33 Posts

Posted - 07/16/2013 :  10:04:15  Show Profile  Reply with Quote
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

USA
15681 Posts

Posted - 07/16/2013 :  11:49:05  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000