March 15, 2011 at 11:33 am
Hi Experts,
I have a user database which is about 300 GB. I deleted few big tables from it, so there is at least 100 GB of space out of it is available and not used now. This database is in simple recovery mode. The drive where this database resides has only 20 GB left now. I am trying to shrink the database and free up the space,
1. It is taking too much time.
2. When shrink operation starts it fills up the log for that database and in turn fills up the drive space which has only 20GB.
What is the best thing to do here?
Thanks in advance...
March 15, 2011 at 11:36 am
Forgot to mention,
If I shrink in small chunks (releasing 2GB at a time) then it works. But I don't want to keep doing this 50 times..
March 15, 2011 at 4:58 pm
make sure you use shrinkfile not shrink database
try the truncateonly clause first
other than that you have no choice but to do it in chunks, best to do that anyway as this process is intrusive. You could set it up in a sqlagent job.
reindex your database afterwards.
---------------------------------------------------------------------
March 16, 2011 at 11:01 am
Can you add another file to your transaction log on a different drive and make it the Primary? Given enough space, trying to shrink your database or your data file should be easier (though I won't guarantee shorter) once your transaction log can write to a different drive with more space.
Also, a faster way of shrinking would be to not choose the "reorganize files" option in the GUI or the "target size" in the T-SQL command. If you allow the server to choose the amount of space it shrinks, then it works faster, but you don't get as much space back.
March 18, 2011 at 6:54 am
Truncating and shrinking the log file both are same[/url]
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 21, 2011 at 1:43 pm
I was able to shrink successfully.
Here are the steps i followed.
Change the DB back to Simple If it is in Full mode.
Change it back to Full mode.
Use T-sql
DBCC Shrinkfile(logfilename,Size you want to Shrink to)
Should work.
March 22, 2011 at 6:01 am
mail.pnreddy (3/21/2011)
I was able to shrink successfully.Here are the steps i followed.
Change the DB back to Simple If it is in Full mode.
Change it back to Full mode.
Use T-sql
DBCC Shrinkfile(logfilename,Size you want to Shrink to)
Should work.
If you did this, do you know what will happen exactly?
Nope don't do this without knowing the things. It will break the log chain.
Read my link added previous post.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 22, 2011 at 9:34 am
You are correct. But when there is no disk space even to perform a full/diff backup how would you handle that?
March 22, 2011 at 10:19 am
mail.pnreddy (3/22/2011)
You are correct. But when there is no disk space even to perform a full/diff backup how would you handle that?
Do you have another disk drive handy?
Have you gone through the existing drive to remove all unnecessary files?
It would behoove you to remove previous backup files, assuming those files were already archived off to a tape drive or off-site storage. And if they weren't then archive them off now.
If your disk is full, get another. Put backups on a new disk, separate from your dbs, if you don't already have it set up that way. And don't ever switch from Full to Simple to Full unless you're doing it for a very specific reason and you fully understand the consequences. Is Bad DB Habits.
March 22, 2011 at 11:06 am
muthukkumaran (3/22/2011)
mail.pnreddy (3/21/2011)
I was able to shrink successfully.Here are the steps i followed.
Change the DB back to Simple If it is in Full mode.
Change it back to Full mode.
Use T-sql
DBCC Shrinkfile(logfilename,Size you want to Shrink to)
Should work.
If you did this, do you know what will happen exactly?
Nope don't do this without knowing the things. It will break the log chain.
Read my link added previous post.
But didnt the OP mentioned that his recovery mode was already in simple.
Hi Experts,
I have a user database which is about 300 GB. I deleted few big tables from it, so there is at least 100 GB of space out of it is available and not used now. This database is in simple recovery mode.....
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2011 at 11:20 am
Sachin Nandanwar (3/22/2011)
mail.pnreddy (3/21/2011)
I was able to shrink successfully.Here are the steps i followed.
Change the DB back to Simple If it is in Full mode.
Change it back to Full mode.
Use T-sql
DBCC Shrinkfile(logfilename,Size you want to Shrink to)
Should work.
But didnt the OP mentioned that his recovery mode was already in simple.
Yes, Sanchin, (s)he did. Which means changing recovery modes isn't just a bad idea because of log chain breakage, but because it has nothing to do with the existing problem.
mail.pnreddy, I understand that some people think that changing from FULL to SIMPLE will instantly truncate the transaction log (it doesn't quite work that way, BTW), but not only does it take time for the db engine to effect this change, but it takes disk space (which is at a premium), breaks the log chain, and causes a whole host of other problems, especially if this is a production environment during business hours.
March 22, 2011 at 11:44 am
Hey Brandie,
I do agree that changing from FULL to SIMPLE will break the log chain but just wanted to highlight the fact that OP had mentioned that his/her recovery model was already set to SIMPLE.
In between I still wonder even when the recovery model was already SIMPLE then how is it that the log files are bloating if OP is shrinking the database ?
Do shrinking of database logged even though the recovery model is SIMPLE ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2011 at 11:50 am
Sachin Nandanwar (3/22/2011)
Do shrinking of database logged even though the recovery model is SIMPLE ?
All data modifications are logged regardless of the recovery model.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2011 at 11:54 am
GilaMonster (3/22/2011)
Sachin Nandanwar (3/22/2011)
Do shrinking of database logged even though the recovery model is SIMPLE ?All data modifications are logged regardless of the recovery model.
Hence the reason, Sachin, why I advised adding another file to the transaction log to another disk with more space and setting it to Primary. It's really the best way to solve the problem, though there may still be issues if the other log file location isn't also moved to the new disk.
But as the OP has not responded to any of these comments, I'm unsure if the problem is still a problem.
March 22, 2011 at 12:11 pm
GilaMonster (3/22/2011)
Sachin Nandanwar (3/22/2011)
Do shrinking of database logged even though the recovery model is SIMPLE ?All data modifications are logged regardless of the recovery model.
So when a checkpoint occurs the log file should come to its normal size.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 15 posts - 1 through 15 (of 69 total)
You must be logged in to reply to this topic. Login to reply