모든 데이터베이스의 모든 파일을 빠르게 축소하려면 어떻게합니까? 선택할 수 있지만 더 빠른 것을

SQL Server (2008의 경우)에서 인스턴스의 모든 데이터베이스에 대한 모든 파일 (로그 및 데이터)을 빠르게 축소하려면 어떻게해야합니까? SSMS를 통해 마우스 오른쪽 버튼을 클릭하고 작업-> 축소를 선택할 수 있지만 더 빠른 것을 찾고 있습니다.

나는 “데이터베이스 생성”스크립트를 스크립팅하고 기본값으로 풍선 크기를 가지고 있다는 것을 잊어 버렸고이 프로젝트에서 이러한 파일을 위해 예약 된 공간이 많이 필요하지 않습니다.

답변

GUI에서 “작업-> 축소”를 수행하면 실제로 DBCC SHRINKDATABASE장면 뒤에서 명령이 실행됩니다. 시도 해봐. 대화 상자가 나타나면 “확인”버튼을 클릭하지 마십시오. 대신 “스크립트”버튼을 클릭하십시오. 쿼리 창에 명령이 표시됩니다. 이를 sys.databases (master 및 msdb 제외)의 쿼리와 결합하면 스크립트를 만들어 모든 데이터베이스를 축소 할 수 있습니다.

예를 들어 (jcolebrand의 의견에서 발췌) :

SELECT
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10)
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)'
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM
         sys.master_files mf
    JOIN sys.databases d
        ON mf.database_id = d.database_id
WHERE d.database_id > 4;

해당 쿼리의 출력을 복사하고 실행하여 모든 파일을 축소하십시오.

답변

한 줄의 SQL 문은 어떻습니까?

읽어 보시기 바랍니다 이 다음 SQL 문을 실행하기 전에 매우 흥미로운 블로그 게시물을.

EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

답변

DBCC SHRINKDB (및 사촌 SHRINKFILE)는 해당 코드에서 많은 단일 스레드 실행이 진행되므로 매우 느립니다.

데이터베이스 파일을 축소하는 훨씬 빠른 방법은 다음과 같습니다.

  • 새 파일 그룹을 데이터베이스에 할당
  • 이 파일 그룹을 필요한만큼 크게 만드십시오 ( sp_spaceused얼마나 큰지를 결정 하는 데 사용 )
  • 이 새 파일 그룹에 대한 모든 인덱스를 다시 작성하십시오.
  • 기존 파일 그룹 삭제

인덱스 재 구축은 대규모 병렬이기 때문에이 기술을 사용하면 데이터베이스가 훨씬 더 빨리 축소됩니다. 물론 프로세스가 진행되는 동안 새 파일 그룹을위한 약간의 추가 공간이 필요합니다. 그러나 새 파일 그룹에는 인스턴스에서 가장 큰 파일 그룹을 보유하기에 충분한 공간 만 있으면됩니다 (공간을 확보 할 때마다).

이 기술은 또한 프로세스에서 인덱스 조각 모음을 수행 할 수 있다는 이점이 있습니다.

답변

요청이있을 때 LOG 만 축소하도록 쿼리를 약간 조정했습니다.

set nocount on
SELECT
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10)
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)'
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM
         sys.master_files mf
    JOIN sys.databases d
        ON mf.database_id = d.database_id
WHERE d.database_id > 4 and mf.type_desc = 'LOG'

답변

아래 코드는 비 시스템 데이터베이스 목록을 가져 와서 데이터베이스를 읽기 전용으로 설정 한 다음 파일을 축소합니다. 공간이 항상 문제가되는 SQL 에이전트 작업을 사용하여이 코드를 몇 개의 SQL Server 상자에 보관했습니다. 매주 토 / 일 밤에는 데이터베이스 크기에 따라 몇 시간 내에 모든 데이터베이스가 실행되고 축소됩니다.

declare @db varchar(255)
declare c cursor for
select name from sys.databases where is_read_only=0 and state=0
  and name not in ('master','model','tempdb','msdb')
open c
fetch c into @db
while @@fetch_status=0
begin
  exec SP_dboption @db,'trunc. log on chkpt.','true'
  DBCC shrinkdatabase (@db)
  fetch next from c into @db
end
close c
deallocate c

답변

master, model, msdb를 제외한 모든 로그 파일을 축소하십시오.

EXEC sp_MSforeachdb '
DECLARE @sqlcommand nvarchar (500)
IF ''?'' NOT IN (''master'', ''model'', ''msdb'')
BEGIN
USE [?]
SELECT @sqlcommand = ''DBCC SHRINKFILE (N'''''' +
name
FROM [sys].[database_files]
WHERE type_desc = ''LOG''
SELECT @sqlcommand = @sqlcommand + '''''' , 0)''
EXEC sp_executesql @sqlcommand
END'

답변

이것은 커서를 사용하여 SQL 문을 하나씩 반복하여 위의 대답을 확장합니다. Emrah의 대답만큼 짧지는 않지만 커서 내의 while 루프 내에서 추가 논리를 허용합니다.

SELECT
    'USE ['
    + databases.name + N']'
    + CHAR(13)
    + CHAR(10)
    + 'DBCC SHRINKFILE (N'''
    + masterFiles.name
    + N''' , 0, TRUNCATEONLY)'
    + CHAR(13)
    + CHAR(10)
    + CHAR(13)
    + CHAR(10)                                                                  AS sqlCommand
INTO
    #shrinkCommands
FROM
    [sys].[master_files] masterFiles
    INNER JOIN [sys].[databases] databases ON masterFiles.database_id = databases.database_id
WHERE
    databases.database_id > 4; -- Exclude system DBs


DECLARE iterationCursor CURSOR

FOR
    SELECT
        sqlCommand
    FROM
        #shrinkCommands

OPEN iterationCursor

DECLARE @sqlStatement varchar(max)

FETCH NEXT FROM iterationCursor INTO @sqlStatement

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC(@sqlStatement)
    FETCH NEXT FROM iterationCursor INTO @sqlStatement
END

-- Clean up
CLOSE iterationCursor
DEALLOCATE iterationCursor
DROP TABLE #shrinkCommands