BACKUP LOG TO DISK 후에도 로그 파일의 DBCC SHRINKFILE 크기가 줄어들지 않음

BACKUP LOG TO DISK 후에도 로그 파일의 DBCC SHRINKFILE 크기가 줄어들지 않음

---

다음 정보가있는 데이터베이스 [My DB]가 있습니다.
SQL Server 2008
MDF 크기 : 30GB
LDF 크기 : 67GB

가능한 한 로그 파일을 축소하고 싶었 기 때문에이 작업을 수행하는 방법을 알아 내기위한 탐구를 시작했습니다. 주의 사항 : 저는 DBA가 아니며 (또는 DBA에게 접근하기까지)이 퀘스트를 통해 느낌으로 발전해 왔습니다.

먼저 SSMS, DB 속성, 파일로 이동하여 초기 크기 (MB) 값을 10으로 편집했습니다. 그러면 로그 파일이 62GB (입력 한 정확히 10MB가 아님)로 줄어 듭니다. 그래서 SQL Profiler를 연결하고 DBCC SHRINKFILE이 호출되는 것을 확인했습니다. 그런 다음 해당 명령을 쿼리 편집기에 입력했으며 결과는 다음과 같습니다.

DBCC SHRINKFILE (N'My DB_Log' , 10)

결과는 다음과 같습니다.

Cannot shrink log file 2 (My DB_Log) because the logical log file located at the end of the file is in use.
DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
8      2           8044104     12800       8044104     12800

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

그런 다음 그것에 대해 몇 가지 조사를했고 이것을 발견했습니다.

http://support.microsoft.com/kb/907511

즉, 가상 로그 파일이 해제되고 shrinkfile이 작업을 수행 할 수 있도록 shrinkfile 전에 로그 파일을 백업해야한다고 말합니다. 그게 무슨 뜻인지 모르겠습니다.

그래서 로그 파일을 백업 한 다음 DBCC SHRINKFILE을 수행 할 것이라고 생각했습니다 (이전 DBCC SHRINKFILE 명령의 출력에서 ​​식별 된 MinimumSize이므로 새 로그 파일 크기를 12800으로 변경했습니다).

BACKUP LOG [My DB] TO DISK = 'D:\SQLBackup\20110824-MyDB-Log.bak'
GO
DBCC SHRINKFILE (N'My DB_Log' , 12800)
GO

결과는 첫 번째 이동과 동일했습니다. 로그 파일을 62GB까지만 줄일 수 있습니다.

내가 뭘 잘못하고 있는지, 다음에 무엇을 시도해야하는지 잘 모르겠습니다.

---

이미 수행 한 단계 외에도 로그를 축소하기 전에 복구 모드를 단순으로 설정해야합니다.

이것은 프로덕션 시스템에 권장되는 관행이 아닙니다 . 이전 백업 / 로그 파일에서 특정 시점으로 복구 할 수있는 능력을 잃게됩니다.

예제 및 설명 은이 DBCC SHRINKFILE (Transact-SQL) msdn 페이지의 예제 B를 참조하십시오 .

---

좋아요, 여기 에 복구 모드를 단순으로 변경 하지 않고 트랜잭션 파일의 물리적 크기를 줄이는 솔루션이 있습니다 .

데이터베이스 내에서 다음 쿼리를 사용하여 로그 파일의 file_id를 찾습니다.

SELECT * FROM sys.database_files;

제 경우에 로그 파일은 file_id 2입니다. 이제 사용중인 가상 로그를 찾고 다음 명령을 사용하여이 작업을 수행합니다.

DBCC LOGINFO;

여기에서 상태가 2 (사용 중) 또는 0 (사용 가능)인지 확인하여 사용중인 가상 로그가 있는지 확인할 수 있습니다. 파일을 축소 할 때 처음 사용 된 상태에 도달 할 때까지 파일의 끝에서 시작하여 빈 가상 로그가 물리적으로 제거됩니다. 이것이 트랜잭션 로그 파일을 축소하면 때때로 부분적으로 축소되지만 모든 사용 가능한 가상 로그가 제거되지는 않습니다.

0 이후에 발생하는 상태 2를 발견하면 축소가 파일을 완전히 축소하는 것을 차단하는 것입니다. 이 문제를 해결하려면 다른 트랜잭션 로그 백업을 수행하고 즉시이 명령을 실행하여 위에서 찾은 file_id와 로그 파일을 줄일 크기를 제공하십시오.

-- DBCC SHRINKFILE (file_id, LogSize_MB)
DBCC SHRINKFILE (2, 100);
DBCC LOGINFO;

그러면 가상 로그 파일 할당이 표시되며 다소 감소 된 것을 알 수 있습니다. 가상 로그 파일이 항상 순서대로 할당되는 것은 아니기 때문에 트랜잭션 로그를 두 번 백업하고이 마지막 쿼리를 다시 실행해야 할 수 있습니다 . 그러나 일반적으로 백업 한두 개 내에서 축소 할 수 있습니다.

---

SQL Server 2008 R2에서이 스크립트를 사용합니다.

USE [db_name]

ALTER DATABASE [db_name] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE([log_file_name]/log_file_number, wanted_size)

ALTER DATABASE [db_name] SET RECOVERY FULL WITH NO_WAIT

---

이 시도

ALTER DATABASE XXXX  SET RECOVERY SIMPLE

use XXXX

declare @log_File_Name varchar(200) 

select @log_File_Name  = name from sysfiles where filename like '%LDF'

declare @i int = FILE_IDEX ( @log_File_Name)

dbcc shrinkfile ( @i , 50) 

---

Paul Randal은 블로그에서이 문제에 대한 훌륭한 토론을했습니다. http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to -stop-it.aspx

---

로그 파일 축소

로그 파일의 경우 데이터베이스 엔진은 target_size를 사용하여 전체 로그의 대상 크기를 계산합니다. 따라서 target_size는 축소 작업 후 로그의 여유 공간입니다. 그러면 전체 로그의 대상 크기가 각 로그 파일의 대상 크기로 변환됩니다. DBCC SHRINKFILE은 각 실제 로그 파일을 대상 크기로 즉시 축소하려고합니다.

그러나 논리 로그의 일부가 대상 크기를 초과하는 가상 로그에 있으면 데이터베이스 엔진은 가능한 한 많은 공간을 확보 한 다음 정보 메시지를 발행합니다.

이 메시지는 파일 끝에서 가상 로그에서 논리적 로그를 이동하는 데 필요한 조치를 설명합니다. 작업을 수행 한 후 DBCC SHRINKFILE을 사용하여 남은 공간을 확보 할 수 있습니다.

로그 파일은 가상 로그 파일 경계로만 축소 될 수 있기 때문에 로그 파일을 사용하지 않는 경우에도 가상 로그 파일의 크기보다 작은 크기로 축소하는 것은 불가능할 수 있습니다 . 가상 로그 파일의 크기는 로그 파일을 만들거나 확장 할 때 데이터베이스 엔진에서 동적으로 선택합니다.

• 문제 해결 : 파일이 축소되지 않음

축소 작업이 오류없이 실행되지만 파일의 크기가 변경되지 않은 것 같으면 다음 작업 중 하나를 수행하여 파일에 제거 할 충분한 여유 공간이 있는지 확인합니다.

다음 쿼리를 실행하십시오.

SELECT name, size / 128.0-CAST (FILEPROPERTY (name, 'SpaceUsed') AS int) /128.0 AS AvailableSpaceInMB FROM sys.database_files;

DBCC SQLPERF 명령을 실행하여 트랜잭션 로그에 사용 된 공간을 반환합니다.

• If insufficient free space is available, the shrink operation cannot reduce the file size any further.

• Typically it is the log file that appears not to shrink. This is usually the result of a log file that has not been truncated.

• You can truncate the log by setting the database recovery model to SIMPLE, or by backing up the log and then running the DBCC SHRINKFILE operation again.

Example :

Shrinking a log file to a specified target size

The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

Transact-SQL

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

When you use DBCC SHRINKFILE(Logfile, size) it only truncates from the end of the log file back as far as it can go. When it reaches the highest virtual log still in use, it cannot shrink further. This is described in the SQL Server Books Online at:

http://technet.microsoft.com/en-us/library/ms189493.aspx

So, once the high end of the log is clear, it can be shrunk down in size. Again, that will depend on how much of the log is still in use. The log can be cleared by backups, but the backups will not clear incomplete transactions, so the log can remain in a high-end VLF even after repeated backups.

With regard to the increase and decrease of VLFs, how big was the log file created to be originally and what is the setting for log file growth? If it grows by only a small amount it will create more VLFs than anyone desires.

A common pattern for shrinking a log file is CHECKPOINT, BACKUP, SHRINKFILE, CHECKPOINT, BACKUP, SHRINKFILE, etc until you get results. There are many reasons that the log may not be shrinkable, including a very large rollback.

Switching from Simple to Full has a Problem:

There are rules and exceptions here. We'll talk about long running transactions in depth below.

But one caveat to keep in mind for Full Recovery Mode is this: If you just switch into Full Recovery mode, but never take an initial Full Backup, SQL Server will not honor your request to be in Full Recovery model. Your transaction log will continue to operate as it has in Simpleuntil you switch to Full Recovery Model AND take your first Full Backup.

Full Recovery Model without log backups is bad:

So, that's the most common reason for uncontrolled log growth? Answer: Being in Full Recovery mode without having any log backups.

This happens all the time to people.

Why is this such a common mistake?

Why does it happen all the time? Because each new database gets its initial recovery model setting by looking at the model database.

Model's initial recovery model setting is always Full Recovery Model - until and unless someone changes that. So you could say the "default Recovery Model" is Full. Many people are not aware of this and have their databases running in Full Recovery Model with no log backups, and therefore a transaction log file much larger than necessary. This is why it is important to change defaults when they don't work for your organization and its needs)

---

I tried many ways but this works.

Sample code is availalbe in DBCC SHRINKFILE

USE DBName;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE DBName  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (DBName_log, 1);  --File name SELECT * FROM sys.database_files; query to get the file name
GO  
-- Reset the database recovery model.  
ALTER DATABASE DBName  
SET RECOVERY FULL;  
GO

---

Thanks to @user2630576 and @Ed.S.

the following worked a treat:

BACKUP LOG [database] TO DISK = 'D:\database.bak'
GO

ALTER DATABASE [database] SET RECOVERY SIMPLE

use [database]

declare @log_File_Name varchar(200)

select @log_File_Name = name from sysfiles where filename like '%LDF'

declare @i int = FILE_IDEX ( @log_File_Name)

dbcc shrinkfile ( @i , 50)

ALTER DATABASE [database] SET RECOVERY FULL

참고URL : https://stackoverflow.com/questions/7193445/dbcc-shrinkfile-on-log-file-not-reducing-size-even-after-backup-log-to-disk