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.
| 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. |
 |
|
|
|
|
|
DBCC SHRINKFILE not working