:::: MENU ::::

Increase or shrink file size for SQL Server database files

  • Oct 01 / 2014
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Increase or shrink file size for SQL Server database files

Following handy scripts help to manage the SQL Server database files. You may increase the max size and current size as well.

-- Increase file max size  
 USE [master];  
 ALTER DATABASE DatabaseNameHere MODIFY FILE ( NAME = N'FileNameHere', maxsIZE = 1500MB)  
 GO  
 -- Increase file size  
 USE [master];  
 ALTER DATABASE DatabaseNameHere MODIFY FILE ( NAME = N'FileNameHere', sIZE = 1500MB)  
 GO  

Shrink operation may be up to a specific extent or just shrink by removing the space available ay the end of file without manipulating data in file.If file shrink operation is cancelled during execution the current status is not rolled back. TRUNCATEONLY operation is fast.

-- Shrink to specific limit  
 USE DatabaseNameHere;  
 DBCC SHRINKFILE (N'FileNameHere' , 200)  
 GO  
 -- Shrink to space available at the end of file  
 USE DatabaseNameHere;  
 DBCC SHRINKFILE (N'FileNameHere' , 0, TRUNCATEONLY)  
 GO  
Consult us to explore the Databases. Contact us