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 |
|
pkkalra
Starting Member
5 Posts |
 Posted - 2014-03-24 : 00:27:47
|
I am trying to shrink the log file which has grown to 21 GB. I can see that only 25% of it is being used and rest is free space sitting there. I wanted to reclaim this space.
Our recovery model is FULL and cannot be changed to SIMPLE as it is being synchronized to a backup server.
I have tried the following:-
Taken full backup, transaction log backup and then doing shrink log file. I am mainly doing it via SSMS (Management studio) as I am not an expert in DBA stuff ( I am a developer).
File size is same and not reduced at all. |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
pkkalra
Starting Member
5 Posts |
Posted - 2014-03-27 : 01:27:02
|
ALTER DATABASE [DB] MODIFY FILE command didn't work. It seems like you can increase it but can't reduce it.
Error:
MODIFY FILE failed. Specified size is less than current size.
ALTER DATABASE Docprep MODIFY FILE (NAME = Docprep_log, SIZE = 1000MB) |
 |
|
|
pkkalra
Starting Member
5 Posts |
Posted - 2014-03-27 : 01:59:03
|
Finally resolved by following instruction from another forum. Basically you have to do twice in a row.
1. Make sure no open connections - DBCC OPENTRAN
2. Take Full backup
3. Take Transaction log backup with truncate
4. Shrink log file using DBCC SHRINKFILE
5. Repeat 3 & 4 again.
Voila! Log file is 10 MB now. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-04-01 : 01:49:01
|
In the link I sent you there is a clear reference to first commit a shrinkfile , in step 3 , before you commit the ALTER DATABASE statement
If you attempt a ALTER DATABASE with MODIFY FILE you'll get this error
Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than or equal to current size.
To decrease the transaction log file size log , truncation must occur to free disk space. To reduce the log file physical size shrink to eliminate incative virtual log files
-- check there are no active transactions
USE sizetest
GO
dbcc shrinkfile(sizetest_log,TRUNCATEONLY)
GO
ALTER DATABASE sizetest
MODIFY FILE
(NAME = sizetest_log,
SIZE = 1MB);
GO
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
|
|
|
Shrink log file