SQL Server log shrinking Issue

undefined undefined


I have a 300 GB database log in our SQL Server. I want to shrink all the logs but it is not allowing me to do so. I tried by using query and UI in SQL Server 2008 R2 . I used


USE  [sample];
GO
DBCC SHRINKFILE (sample_log, 1);
GO

If you are shrinking a log file, you won't be able to shrink it any less than the active log portion. In other words, if you are in Full Recovery Model, and you haven't done any transaction log backups (which is why it could have grown that large), then running a DBCC SHRINKFILE will do absolutely nothing.

Solution:


USE  [sample];
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE [sample]
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (sample_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE  [sample]
SET RECOVERY FULL;
GO

3:42 AM | |

 

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Your Location