robjay
asked on
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
I'm using SQL 2008, I'm not sure there is a separate log file.
this will list you the filesizes
sp_helpDb 'databasename'
sp_helpDb 'databasename'
+ 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/
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/
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you may have to shrink the database log file after the delete operation
DBCC SHRINKFILE(logfile,10)