Error executing the DBCC SHRINKFILE command in the Log File of a SQL Server database

Question

Error executing the DBCC SHRINKFILE command in the Log File of a SQL Server database

Navigation

#1 by (0 votes)

1

The following sql code for the compaction of the log:

use My_dabase
go

declare 
@arch_log sysname

set @arch_log =(select name FROM SYSFILES
                where fileid = '2')

select name from sysfiles

ALTER DATABASE My_database
SET RECOVERY SIMPLE
    DBCC SHRINKFILE (@arch_log , 2);
ALTER DATABASE My_database
SET RECOVERY FULL

... I get the following error message:

'Cannot shrink log file 2 (My_database_Log) because total number of logical log files cannot be fewer than 2.'

I have tried to investigate and open an endless number of pages related to the problem but I do not understand very well the concepts of the VLF's, much less I can relate them to the specific problem that I have. How should I handle this error? Is the script wrong?

sql sql-server query sql-server-2008-r2

asked by Fran.J 17.01.2018 в 23:26

source

1 answer

Java - Help creating an object of my type from other classes hello I want to join to one only 5 forms that every time I go filling up it is accumulating [closed]

score 0 · Accepted Answer

Your script is correct, but depending on what the error tells you, the log file contains the minimum number of VLFs, so it can not be reduced any more.

I'll explain, each physical log file is divided internally into smaller parts called Virtual Log File (VLF), the amount and size of these virtual files is determined by the Engine, and are used by sequential and circular way.

        logfile.ldf
+----+----+----+----+----+
|VLF1|VLF2|VLF3|....|VLFN|
+----+----+----+----+----+

The VLFs are used as needed and the ones that are no longer useful are marked as available (Generally when making a backup of the log in BD in fullrecovery mode). When it fills the last VLF it starts to reuse the ones from the beginning if they are available. If not, there is an increase in the size of the physical Log to generate new VLFs.

So you can have a physical log file of say 10MB which internally would have 4 VLF of 2.5MB for example. Assuming that two VLFs still contain useful information and two are available, the physical log file could be reduced by 5MB by eliminating the two "free" VFLs.

In case you need to reduce yes or yes the size of the log you can try what they indicate here , although the log usually grows to the size the engine needs to be able to work:

Create a new log file for temporary use.
Empty the original log file: dbcc shrinkfile(2, emptyfile) --My_database_log tiene id 2 según el mensaje de error when emptying the log with id2 the content goes to the newly created log file.
Achieve the original log already empty.
Set the size of the orignal log to an appropriate one, this would depend on your specific case.
Empty the temporary log, to dump the content to the original log file: dbcc shrinkfile(3, emptyfile) and remove it.
Return to execute shrinkfile