We have a 120GB database. There was table with 60GB of data which is useless and we have truncated it.
Now database size is 120GB with free space 60GB. The database will not grow up to 60GB in at least 3 months. So can we shrink the data file.
Am aware of fragmentation issue. I can rebuild my index since our's is not a 24*7 database.
Please advice on shrinking MDF file
What exactly you are trying to achieve by shrinking the database? You should plan for the database growth and have reserved space for this growth.
So why don't you leave it as it is to have allocated space for new data coming to your DB.
It has been discussed so many times that you do not want to shrink your databases. Check this out and think twice before shrinking:
Stop Shrinking Your Database Files. Seriously. Now. By Brent Ozar
Please read my post on how to shrink a database, which I'll summarize here:
Look at the size of all files in your filegroup. You want all files within the group to be evenly sized, and you want to allow space for growth, index maintenance, etc. It makes more sense to err on the side of leaving the database a little too large than shrinking it to be too small. Personally, I'd account for at least 6 months growth in my target size.
SELECT
LogicalName = dbf.name
,FileType = dbf.type_desc
,FilegroupName = fg.name
,PhysicalFileLocation = dbf.physical_name
,FileSizeMB = CONVERT(DECIMAL(10,2),dbf.size/128.0)
,UsedSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 - ((dbf.size/128.0)
- CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT) /128.0))
,FreeSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0
- CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)
FROM sys.database_files dbf
LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id
ORDER BY dbf.type DESC, dbf.name;
It sounds like you've already done this, and are OK with having to perform index maintenance following your shrink. Make sure you plan enough time to shrink and perform index maintenance during your maintenance window. It might be faster & easier to use the maintenance window to migrate into a fresh database, or rebuild all indexes into a new filegroup, rather than shrinking the existing one.
Always use SHRINKFILE and never SHRINKDATABASE. Use the info you determined in step 1 to build your SHRINKFILE statement(s).
USE [DatabaseName];
DBCC SHRINKFILE(LogicalName, TargetSize);
If you have a regular maintenance job, just kick off that job and have it do it's magic. It's going to take a much longer time than normal, because everything will be really fragmented. You'll generate a lot more transaction log than usual, so you will have larger transaction log backups, and you will also see the effects in any log shipping, Availability Groups, mirroring, etc.
Use this code below. Details here. 7 is in MB.
USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO
