patternMinor
Free up disk space on sql server
Viewed 0 times
spacediskfreesqlserver
Problem
Assuming I have a sql database server with disk space as follows:
D Drive: 10GB (For the MDF file)
E Drive: 10GB (For the LDF file)
And I have only one database on the server with the following details:
Disk space occupied:
9GB MDf file and
9GB LDF file.
10 tables, Only one Schema (dbo), No indexes at all on any of the tables.
What I do:
I drop all the tables on the database, but the drive space remains the same(9gb) and I am left with just 1GB on both the drives.
How would I reclaim the space on D drive and what is the best way to do this without shrinking the mdf and without dropping the database?
Obviously without adding any disk space.
Shrinking log file would free up space but I am more concerned about getting the space back on the D drive which holds the MDF file.
D Drive: 10GB (For the MDF file)
E Drive: 10GB (For the LDF file)
And I have only one database on the server with the following details:
Disk space occupied:
9GB MDf file and
9GB LDF file.
10 tables, Only one Schema (dbo), No indexes at all on any of the tables.
What I do:
I drop all the tables on the database, but the drive space remains the same(9gb) and I am left with just 1GB on both the drives.
How would I reclaim the space on D drive and what is the best way to do this without shrinking the mdf and without dropping the database?
Obviously without adding any disk space.
Shrinking log file would free up space but I am more concerned about getting the space back on the D drive which holds the MDF file.
Solution
In SQL server the data and log files only grow automatically. If you deleted a lot of data there will be empty space inside the file but SQL server will not shrink the file automatically because it expects more data to be added, and growing the files is expensive.
Shrinking the file is also generally bad for indexes and stats, but since you dropped all the tables that is not a concern. You can shrink the file by using the DBCC SHRINKFILE command. I'm on my phone so I'll suggest you look up the syntax on the Microsoft knowledge base.
Shrinking the file is also generally bad for indexes and stats, but since you dropped all the tables that is not a concern. You can shrink the file by using the DBCC SHRINKFILE command. I'm on my phone so I'll suggest you look up the syntax on the Microsoft knowledge base.
Context
StackExchange Database Administrators Q#62074, answer score: 2
Revisions (0)
No revisions yet.