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