SQL Server Performance Forum – Threads Archive
reduce database size?<font size="3">Hi,<br />This is first post for me [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />And i would like to thanks evry bode here, I benefited greatly from your posts [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />].<br /><br />i have question,<br /><br />How i can reduce my sql database size ?<br /><br />there is option at Access database cold ‘Compact and repair database’ , there is something like this in ms sql database?<br /><br />Thank you [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]</font id="size3">
You can set the ALTER DATABASE AUTO_SHRINK ON for SQL to shrink automatically in the background You can use DBCC SHRINKDATABASE or DBCC SHRINKFILE for manual shrinking You can’t shrink a Database to a size smaller than the origional specified size of the DB when created (ie 50GB was specified when the db was created), or smaller than the the used portion of the db (ie there is 70GB of data in the db you cannot shrink below 70GB). There is a resource cost for manual and auto shrinking, and you should check for fragmentation periodically or after the manual process completes. For transaction logs you cannot shrink them below the virtual log file size, SQL maintains virtual log file boundaries.
Welcome to the forum!
First of all I would like to know the reason to shrink or reduce the database size, do you have any disk free spaces lately.
See what kind of jobs and processess are running on the SQL Server, set an optimum size for transaction log in order to take care of such issues. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.