I have received a database management procedure on SQL and the database has with more than 440 Gbytes of space and the disk is just for 500 Gbytes so there is almost no free space on disk to run tasks and to have a good space for temporary files. The database has more of 54% of free space internally. I know that it is not a good idea to shrink database or files but, what other solution exists? (not involving a new hardware) Do I have to "shrink" sql databases or files now?
Asked
Active
Viewed 429 times
0
-
You could alternatively get a bigger disk. – Bill Karwin Mar 19 '19 at 14:03
-
MSSQL Enterprise 11.0.3128.0 – backslash17 Mar 19 '19 at 14:05
-
2It's also a good idea to try to find out *why* you have so much unused space. – HoneyBadger Mar 19 '19 at 14:05
-
No option at this moment to add another disk – backslash17 Mar 19 '19 at 14:06
-
@Ryan Wilson my problem is not memory, its disk space. – backslash17 Mar 19 '19 at 14:08
-
@backslash17 Ok, I must have misunderstood your question. If the database itself is 440 GBs then maybe looking into some kind of data warehousing to move some of this data off of this server may be an option, if that isn't then I don't really know what else to do outside of getting more disk space. – Ryan Wilson Mar 19 '19 at 14:11
-
The data file has 436769024 KB of data but the 54% of it is unused. Shrink or not to shrink, that is the question. – backslash17 Mar 19 '19 at 14:19
2 Answers
2
I would not be scared to do so if you already cleaned up particular drive.
Shrinking a database should not be your first option, but it does to the trick if there are no other option to clean up the disk.
I think the warning is mostly done to make sure that no one shrinks a database by maintenance or something like that.
user5152775
- 21
- 2
1
Yes, db shrink is a bad thing bla bla, but Given the situation you are in, I think you are ok to Shrink the database but also keep an eye out what causing the database to bloat like this ? Maybe a process that create some tables and drop them later.
Also, once you have shrunk the database you must do an index rebuild on the database, because shrinking the database fragments the indexes really badly.
In most of the article you read online, they have said that it is a bad practice to shrink databases, yes, if you are shrinking databases as a scheduled task to free up disk space, this is wrong.
But the situation you are in, it makes perfect sense to shrink the db.
M.Ali
- 67,945
- 13
- 101
- 127