Last post May 19, 2015 03:47 PM by strangermike
May 19, 2015 02:26 PM|StrangerMike|LINK
Recently we ran out of space on the server's C drive. We rebooted the server and got back 13 gig.
We have a database table that we deleted millions of records from.
I even created a new table from the old table and dropped the old table.
We were about to run the Shrink database command, but now I am reading warnings on the internet about Shrinking a database and how it will hurt database efficiency in the future.
I found on the mircosoft site that it does not recommend doing multiple shrinks, but kind of made it sound like in the case of reclaiming space from a truncate or drop it is okay.
So do I or don't I, how else can I reclaim the ghost space from those 4 million records deleted?
May 19, 2015 03:10 PM|limno|LINK
When you run into space issue for your database server, you may need to check your instance to see whether it will serve your database well enough for the near future.
You need to check your database configuration and file size and growth rate. You also need to check your restore mode and your backup plan. There are other things to check as well. Without knowing your particular situation, it is hard to get a full list.
When you don't need the hug log file space, you should be able to claim back some to fit your need (go, shrink your DB shrinkfile to get the space back). You need to test any solution you found from the internet. Good luck.
May 19, 2015 03:25 PM|StrangerMike|LINK
We just took the defaults when creating this particular database: Data (By 1 MB, Unrestricted Growth) Log (By 10% restrict to 2097152 MB)
I noticed the recovery mode is Full, I want to discuss with the user about their recovery requirements. Maybe we could switch to Simple.
If so does this stop log records from being written, which might alleviate his issue with log file becoming full?
And what did you mean by: (go, shrink your DB shrinkfile to get the space back)? Do I pick Shrink Database or Shrink files?
May 19, 2015 03:33 PM|limno|LINK
If the recover mode is FULL and you don't have a log backup plan in place, it will out grow your machine no matter how big is your machine.
Never implement a job or a routine to shrink a database. Do it once or when you must should be the right approach. When you create your database, you need to plan the size by not using the default ones.
First thing first, if your application does not need it (or you don't know), switch to SIMPLE before your log will be out of control again with huge delete option.
May 19, 2015 03:47 PM|StrangerMike|LINK
Ok thanks limno.