CODEWITHSUNDEEP
sqlsql-server-2016
fr0zt
fr0zt

Reputation: 861

SQL Server MDF file growing too much

I am working with SQL Server and I've noticed my .mdf file is growing to much.

I mean, in my logs I changed te recovery model to simple so I've fixed it and the logs are not growing as much as they were. But now I'd like to shrink my .mdf file. Doing a shrink, using management studio, to shrink this file will result in data loss?

How can I prevent this file from growing so much?

Upvotes: 0

Views: 2312

Answers (1)

anon
anon

Reputation:

The data file grows because you have put data there. If you put data there temporarily (or ran a lot of updates or deletes) you can reclaim free space within the file by rebuilding and reorganizing indexes. You can determine the biggest indexes in terms of rows using:

SELECT TOP (10) OBJECT_SCHEMA_NAME(object_id),
  OBJECT_NAME(object_id), rows = SUM(rows)
FROM sys.partitions
WHERE index_id IN (0,1)
AND OBJECTPROPERTY(object_id, 'IsMsShipped') = 0
GROUP BY object_id
ORDER BY rows DESC;

And in terms of size using:

SELECT TOP (10) 
  OBJECT_SCHEMA_NAME(i.object_id),
  OBJECT_NAME(i.object_id),
  i.name, 
  numpages = COUNT(p.allocated_page_page_id) 
FROM sys.indexes AS i
CROSS APPLY sys.dm_db_database_page_allocations
  (DB_ID(), i.object_id, i.index_id, NULL, 'LIMITED') AS p
GROUP BY i.object_id, i.name
ORDER BY numpages DESC;

It is rarely the case that you'll want to shrink the database. The MDF file got big because of your workload. If your workload means the data file will get big and then the contents will get smaller again, then making the container smaller only to have it grow again over your next workload cycle accomplishes nothing. What will you do with the free space that will only be free temporarily?

If you do decide to shrink your MDF file, please use DBCC SHRINKFILE, not the UI or DBCC SHRINKDATABASE. Expect any shrink operation to increase fragmentation and make query performance worse and, if your workload cycles as I described, expect to have to repeat this process often. And if you rebuild indexes to remove the fragmentation and improve performance, that operation will grow the file again. It's like a hole in the bucket, Dear Liza, Dear Liza.

OR - just let the file stay large and let your workload reuse the space within it, instead of reclaiming the space temporarily.

Unless you stumble across a yet-undiscovered bug, shrinking does not introduce any risk of data loss.

Upvotes: 1

Related Questions