How to reduce the size of .mdf file in sql

Any records or indices you delete are not immediately freed to the file system, they're just made available to other tables and indices. That's a good thing, leave it there!

Improve this answer. Scott Hodgins brought up compression which is an excellent point, and which reminds me: well-chosen data types can take up less space. This server is not a production server,so can i just take a backup of the database and then delete the original database and restore it again with less initial size.? You could take a backup, restore it somewhere else, make deletions, back it up again, and restore it again on the original server.

I don't know why, though; you'd have to make the original server read-only during this process, whereas it could remain online if you delete and defrag in situ.

Add a comment. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. This email is in use. Do you need your password? Submit your solution!

When answering a question please: Read the question carefully. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem.

Insults are not welcome. Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question. Let's work to help developers, not make them feel stupid. Related Questions. By default, the tempdb database is configured to autogrow as needed. Therefore, this database may unexpectedly grow in time to a size larger than the desired size.

A simple restart of SQL Server resets the size of tempdb to its last configured size. In SQL Server and later versions, you can use any of the following methods to alter the size of tempdb. The "Currently allocated space" value is always pulled from sys. To find the correct size of tempdb files after a shrink operation, execute the following statement in SQL Server Management Studio:. The first three methods are discussed here.

Also, the DBCC commands will require you to put the database in single-user mode. You can use the following three methods to shrink tempdb to a size that is smaller than its configured size. At a command prompt, start the instance in minimum configuration mode. To do this, follow these steps:.

Stop SQL Server. A limitation of this method is that it operates only on the default tempdb logical files tempdev and templog. If more files are added to tempdb, you can shrink them after you restart SQL Server as a service. All tempdb files are re-created during startup. However, they are empty and can be removed. This is the desired percentage of free space left in the database file after the database is shrunk. This calculation is based on the desired database size.

Basically my requirement is that, it should not use the remaining 3GB space and leave that free space in the drive. If you set the maximum size of the MDF file to it's current size, it will stop further growth and force new data to be written to the NDF on the other drive. Just be sure that the NDF is large enough to accommodate further expected growth. You must be logged in to reply to this topic.

Login to reply. Post reply. February 3, at am Also I would like to reduce the size of MDF file, what's the best approch to do the same?