Sql-server – How to recover 350 Gigabytes of unallocated space

database-designperformanceshrinksql serversql server 2014

I was left with a scenario where my Database Initial size is 370GB.

When I checked the EXEC sp_spaceused, I can see almost 360GB of unallocated space .

Does unallocated space means unused space? If it is unused space, why this is causing the issue for storage?

What options do I have apart from shrinking the .mdf file with 360GB of unallocated space?

Also, as this is a huge database, what level of increments I can mention in the place of 'x' below – can I start from 10,000 MB every time I shrink?

USE Demo
GO
DBCC SHRINKFILE (N ‘Demo’, x)
GO

Is there anything else I should consider in this type of situations?

Would adding a new data file help?

Best Answer

Do you know everything that goes on with this app / database?

Imagine this scenario:

There's a Very Important Person at your company that needs to get (daily | monthly | quarterly | annual) financial reports
These reports are generated by a scheduled job
This job generates hundreds of GBs of data in the database - creating work tables, crunching numbers, etc
Afterwards, the job "cleans up" after itself by dropping (or truncating + rebuilding) the work tables
- This results in a lot of unused space in the database - but the size of the files doesn't change
One day, a DBA comes along and shrinks this database
Some day in the future, this reporting job tries to run - but it grinds to a halt as the file has to grow over and over and over
Maybe the job even fails, or is delayed so long that it starts to affect performance during normal business hours
Very Important Person is mad, because they don't have their important reports, and everyone is complaining to them about the system being slow

You don't want that to happen, do you?

All that being said, if you're sure that the database:

really doesn't need this space, and
will never use this space because it doesn't gain data that quickly, and
you have an actual need for the space on this drive (paying by the GB to a cloud vendor, expensive SAN that other apps need space on, etc),

then by all means, shrink it.

Maybe there was a typo in the filesize when this database was first created (someone tacked a 0 onto the end of "37" - poof, 370 GB database file).

Maybe there was a one-time data load / deletion that will never happen again that caused the DB to grow to this size.

Make sure to give some thought to the "target size" you use with SHRINKFILE if you do this. You say it's only 10 GB of used space, but I wouldn't immediately set it to 10 GB - you are putting some data into this database, right?

Related Solutions

Sql-server – Shrinking the log file does not reduce size

Here is the answer to my own question.

Run the below query to get information about the log file's reuse wait:

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DBName'

I got the following output:

log_reuse_wait_desc
-------------------
REPLICATION

There were some replication-related objects remaining in the database, even after removing the replication.

To remove the replication from the database, sp_removedbreplication can be used. But it didn't work for us as replication was not active at the time and actually replication had been removed long before.

The solution was to import the database contents to another database using the import option of SQL Server.

Sql-server – SQL Server Database grew too much too fast

The database is being running on SQL Server 2000 Standard for the last 6 years in Full Recovery mode.

This is fin as long as you are doing regular full and log backups to keep the log file size under control. There are plenty of articles and questions on here regarding log management.

In the beginning of this summer the database was about 5GB. Since then, the only thing we did beyond normal usage is some extensive deletions.

Large deletions are fine but understand that the space that data used was not released back to the operating system, unless you did a shrink of the data file.

This week, there was a problem with the PC and I was forced to do a backup and restore it on an SQL Server 2008R2 Express and set the database in 2008 mode.

I hope you tested your code! T-SQL syntax changed a good bit from SQL 2000 to SQL 2008 R2. I have supported many databases that broke due to some of these changes. Also note that Express has a limitation on how large a database can grow, 10GB with SQL 2008 R2

The backup file was about 1GB. When I restored it, the MDF was 9GB of size! I checked the old MDF and it was of the same size. I checked the size of the tables and they cannot reach the 9GB reported!

When you do a backup of the database, the backup is only going to grab the data itself along with some configuration information of the database. One of those configuration items is the initial size of the data and log file. I would expect if you checked your database properties you might find the initial size of your data file (MDF) is set to 9GB in size.

I did a shrink but the size did not change.

A database shrink is likely what you performed. Although ill-advised, if you need to regain disk space or just bring the data file (MDF) of your database under control you will need to perform a data file shrink. Again there are plenty of articles on MSDN and blog post that provide the way of doing this.

Is there a chance that the Full Recovery, can affect the size of the MDF files?

Nope.

I am thinking of settinng the recovery model to Simple, back-it-up and restore it. Is it going to make a difference?

With the size of your data file, nope.

Best Answer

Related Solutions

Sql-server – Shrinking the log file does not reduce size

Sql-server – SQL Server Database grew too much too fast

Related Question