[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-12-28
11
Medium Priority
?
650 Views
Last Modified: 2012-05-08
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.
0
Comment
Question by:robjay
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26134832
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26134918
0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 1000 total points
ID: 26136150
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:robjay
ID: 26138612
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
 

Author Comment

by:robjay
ID: 26138624
I'm using SQL 2008, I'm not sure there is a separate log file.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26138757
this will list you the filesizes

sp_helpDb 'databasename'
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26138775
+ 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
 
LVL 13

Assisted Solution

by:MikeWalsh
MikeWalsh earned 1000 total points
ID: 26138793
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
 

Author Comment

by:robjay
ID: 26187282
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
 
LVL 22

Accepted Solution

by:
dportas earned 1000 total points
ID: 26188546
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
 
LVL 13

Assisted Solution

by:MikeWalsh
MikeWalsh earned 1000 total points
ID: 26190651
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question