Why does my disk space size go down when I delete rows?

Why does my disk space size go down when I delete rows? I believe the nightly backup comes along and frees up the space again, but I'm not sure.
robjayAsked:
Who is Participating?
 
dportasConnect With a Mentor Commented:
It's genereally better to avoid auto-growth because it causes file fragmentation and impacts performance and availability. So the answer is yes, it may be a problem because it probably indicates that you aren't managing space as effectively as you could be.

Best practice is to put log on a different drive to data files.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
thats normal; as you delete the data, sql server will make an entry in the sql log and thus the log file will increase. in order to shrink this
you may have to shrink the database log file after the delete operation

DBCC SHRINKFILE(logfile,10)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
dportasConnect With a Mentor Commented:
It sounds like your log file is auto-growing because you or your DBA didn't allocate any free space to it. If your overnight processes actually shrink the log file on disk then that is what probably caused it to be short of space in the first place.

Please don't use DBCC SHRINKFILE unless and until you understand what happened. Shrinking data or log files frequently is a very bad idea. It is also totally unnecessary and ineffective unless the size of your data is genuinely reducing all the time (unlikely I think).

It's much better to pre-allocate the data and log files to the right size. Don't shrink them and don't allow them to auto-grow.








0
 
robjayAuthor Commented:
Is there a way to view the log file size to know if it is the problem?  I see the mdf database file, but not any log files.
0
 
robjayAuthor Commented:
I'm using SQL 2008, I'm not sure there is a separate log file.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
this will list you the filesizes

sp_helpDb 'databasename'
0
 
MikeWalshCommented:
+ 1 on Dportas comments. Never a good idea to shrink files in production, it will likely grow again. Not knowing which file grew (presuming it is your log) I wonder if you are in FULL Recvoery mode and not taking log backups.

I posted a series on transaction log growth and the impact of shrinking here. This may provide assistance to this issue: http://www.straightpathsql.com/archives/category/shrinking-transactions/
0
 
MikeWalshConnect With a Mentor Commented:
And yes in SQL Server 2008 there is still a separate .LDF file. I wouldn't expect the database .MDF file to grow substantially after a delete but I would expect the space to not be reclaimed. Can you search for the .LDF files and see if they are in this drive or a different on?

Also SP_HELPFILE run from within the context of that database will tell you where your log file is physically located.
0
 
robjayAuthor Commented:
So the .ldf file could possibly be increasing in size significantly when deleting rows?  I did look for it and it is on the same drive.   Is this a problem?
0
 
MikeWalshConnect With a Mentor Commented:
Yes the .LDF file would be increasing because you are logging the deletes. Look at your recovery model --> Is it full? If so are you taking regular log backups? If not you should revisit your recovery model. Choose simple if you are fine restoring to your last good full or differential backup only (and not a point in time). If you need the point in time recovery then stay in Full but start taking log backups.

Also consider breaking the delete up into batches. Don't delete all rows at once but delete maybe 10,000 at a time, maybe less, maybe more. And pause a bit in the middle for a log backup (if in full) or for SQL to potentially issue a Checkpoint, thus clearing the log file.

And yes, to echo dportas --> Log file on a separate drive is good for performance since the log file must be written before your user transaction can be considered complete so separating it onto proper storage optimized for sequential writes (RAID 10 or even just RAID 1 vs. RAID 5 or 6, for example). It is also a good idea from recovery in case you are in full and you have a failure on your data volume, you could potentially still take advantage of your .ldf as part of that plan in some situations.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.