File shrinkage and removal

Shrink the data file (to make room, no way can only shrink files) during the process of encountering log backup, error

 Use [dba_monitor] GO DBCC Shrinkfile (N'dba_monitor'276000)GO

View Code

Wait for log backup to finish, shrink data file again, error

The query learns that the initial size of the file can be modified

-- Modify the initial size of the file, the current size of the base plus 1M  Use [master] GO ALTER DATABASE [dba_monitor] FILE = N'dba_monitor'=  283649024KB)GO

View Code

The contraction is normal again.
A database is found to have two log files, and SQL Server writes the log records in strict order. So although there are two log files here, SQL Server writes only one of them at a point in time. SQL Server will write to the other one only if the file is full. So joining multiple log files is not going to help much in performance.
How do I confirm which log file is currently in use?
You can use the DBCC LOGINFO command to view the Fileid of the current status>0

Create Table#loginfo (FileIdint, FileSizeint, Startoffsetbigint, fseqnobigint, Statusint, Parityint, CREATELSNvarchar( +))Insert  into#loginfoexec('DBCC LOGINFO')Select *  from#loginfowhereStatus>0Drop Table#loginfo

View Code

How to delete redundant log files
First, empty the extra log file, and then delete the empty log file

--Step1 emptying files by migrating data to other files in the same filegroup Use [Dba_monitor]GODBCCShrinkfile (N'dba_monitor_log2', Emptyfile)GO--Step2 Remove the emptied log file Use [Dba_monitor]GOALTER DATABASE [Dba_monitor]REMOVEFILE [dba_monitor_log2]GO

View Code

File shrinkage and removal