어영's

USE DB명

------------------------------------------------------------------------------

-- Don't change anything below this line.

------------------------------------------------------------------------------

GO

​

-- Declare variables

DECLARE @SqlStatement as nvarchar(max)

DECLARE @LogFileLogicalName as sysname

​

-- Alter the database to simple recovery

SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY SIMPLE'

EXEC ( @SqlStatement )

​

-- Make sure it has been altered

SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()

​

-- Set the log file name variable

SELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1

​

-- Shrink the logfile

DBCC Shrinkfile(@LogFileLogicalName, 1)

​

-- Alter the database back to FULL

SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY FULL'

EXEC ( @SqlStatement )

​

-- Make sure it has been changed back to full

SET @SqlStatement = 'SELECT [name], [recovery_model_desc] FROM ' + DB_NAME() + '.sys.databases WHERE [name] = ''' + DB_NAME() + ''''

EXEC ( @SqlStatement )

​

​

SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' MODIFY file ( name = ' + @LogFileLogicalName + ', maxsize = 2000mb ) '

EXEC ( @SqlStatement )

​

​

SET @SqlStatement = 'dbcc shrinkdatabase(' + DB_NAME() + ',2,truncateonly)'

EXEC ( @SqlStatement )

​

------------------------------------------------------------------------------

맨 첫줄의 DB명에 실제 적용할 DB의 이름을 넣으면 됩니다.

그리고 ldf 의 용량을 수정하려면 maxsize 를 수정하시면 됩니다. 위 예제에서는 2000mb 로 되어 있습니다.

실제 현업에서는 10000mb 이상으로 하셔야 될 겁니다.

그리고 에이전트에 등록해서 정기적으로 정리를 하시지 않으면 금방 꽉차버려서 에러가 날 겁니다.