파일사이즈 확장 테스트
카테고리 없음 / 2025. 7. 2. 11:02
declare @dbName nvarchar(1000) ='storageTest'
declare @incSizeGigaBytes int = 10
-- internal variable
declare @currentGigaBytes bigint
, @tobeGigaBytes bigint
, @message nvarchar(4000) = N''
, @mdfFileName nvarchar(1000) = N''
, @fileName nvarchar(1000) = N''
, @sql nvarchar(max)
select @currentGigaBytes = (size * 8) / power(2,20) , @fileName = name
from master.dbo.sysaltfiles
where dbid = db_id(@dbName)
and fileid = 1
select @tobeGigaBytes = @currentGigaBytes + @incSizeGigaBytes
select @message = N'currentGigaBytes : ' + cast (@currentGigaBytes as nvarchar(4000)) + ', tobeGigaBytes : ' + cast (@tobeGigaBytes as nvarchar(4000))
select @message
-- log
RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
set @sql = 'ALTER DATABASE '+@dbName+' MODIFY FILE ( NAME = N'''+@fileName+''', SIZE = '+cast(@tobeGigaBytes as nvarchar(100))+'GB, FILEGROWTH = 512MB)'
print @sql
-- cmd
exec (@sql)
