• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

DBCC SHRINKEFILE has no effect

Hi Experts,

     I am trying to create a script to shrink the log file of a database to 1 MB.  The below script runs without errors but does not change the size of the log file!  Any thoughts as to why?  I am working with SQL Server 2008R2 (EXPRESS)!

DECLARE @LogFileTable AS TABLE (logicalName nvarchar(max), fileID int, DBFileName nvarchar(max), DBFileGroup nvarchar(max),
								size nvarchar(max), maxsize nvarchar(max), growth nvarchar(max), usage nvarchar(max))
 
INSERT INTO @LogFileTable EXEC sp_helpfile
 
DECLARE @DB_Name AS nvarchar(max),
		@DB_Log_FileName AS nvarchar(max);
 
SET @DB_Name='MyTestDB'

SET @DB_Log_FileName = (SELECT logicalName FROM @LogFileTable WHERE [usage] = 'log only')

EXEC  
('USE [' + @DB_Name + ']; ' + 
'DBCC SHRINKFILE( '''+@DB_Log_FileName+''', 1)' 
) 

Open in new window


Thanks!
0
axnst2
Asked:
axnst2
1 Solution
 
axnst2Author Commented:
OK, I got it!  You have to switch the DB into RECOVERY SIMPLE first!

DECLARE @LogFileTable AS TABLE (logicalName nvarchar(max), fileID int, DBFileName nvarchar(max), DBFileGroup nvarchar(max),
                                                size nvarchar(max), maxsize nvarchar(max), growth nvarchar(max), usage nvarchar(max))
 
INSERT INTO @LogFileTable EXEC sp_helpfile
 
DECLARE @DB_Name AS nvarchar(max),
            @DB_Log_FileName AS nvarchar(max);
 
SET @DB_Name='MyDataBase'

SET @DB_Log_FileName = (SELECT logicalName FROM @LogFileTable WHERE [usage] = 'log only')

EXEC  
('ALTER DATABASE ' + @DB_Name + ' SET RECOVERY SIMPLE ' +
 'ALTER DATABASE ' + @DB_Name + ' SET RECOVERY FULL ' +
 'USE [' + @DB_Name + ']; ' +
'DBCC SHRINKFILE( '''+@DB_Log_FileName+''', 1)' +
'ALTER DATABASE ' + @DB_Name + ' SET RECOVERY FULL '
)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now