Calling All SQL DBAs - I need help, pronto!

Domo · 23 Nov 2012 at 14:19

Summary

- SQL database at 60GB with no disk space left.

- The related application had written 6 million records which are not needed so we used some T-SQL to delete them out which should have free up 40+ GB of disk space (each record contains large text fields).

- SQL is not willing to give up the disk space. Even running sp_spaceused suggests there's only 200MB free so I'm assuming the shrinkdatabase command will be ineffective.

As said - we're out of disk space and this is a production environment! Your help will be very much appreciated.

platypus · 23 Nov 2012 at 14:33

SQL won't free up the space unless/until page rebuilds are needed. Shrinking the db file will help, as I'm sure you know the purpose is to return free space. It might take an awful long time with only 200MB free though, or as you say it might fail.

You've dropped 6 million rows. Whilst I don't know the structure of your database, have you tried DBCC CLEANTABLE? SP_CLEAN_DB_FREE_SPACE?

And remember to update statistics once you've actually recovered the space.

Run

Code:

SELECT name as 'Database Name' ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'
FROM sys.database_files;

Should tell you how much a shrink operation can return.

Domo · 23 Nov 2012 at 14:45

Thanks for the response platypus. I looked into shrinking the DB but since SQL thinks the 60GB has been allocated (as determined using sp_spaceused), I didn't think it would do anything.

I've not tried any of those console commands either but I am about to. I'll run the code you provided first. I've just been told there's 196KB free on the 65GB volume. So running anything is going to be difficult if not impossible. Guess we could detach the DB and move it to another volume while maintenance takes place.

platypus · 23 Nov 2012 at 14:51

Then it sounds like somethings gone awry when you dropped 6 million rows.

What command did you use?

skyripper · 23 Nov 2012 at 15:14

Disclaimer - I am not a SQL Server DBA, or a DBA of any kind... saying that...

If is is SQL Server, and you have deleted a ton of rows from a record, then two things generally happen - the logfile records the delete operation and the datafile doesnt release the space until the logfile is marked as backed-up.

In order to shrink databases effectively after doing this, I needed to do something similar to the following:

BACKUP LOG blah_logname WITH NO_LOG;

Which then allowed the Shrink Database option in the management tool to actually do its thing and shrink stuff.

Domo · 23 Nov 2012 at 16:43

Thanks, sounds interesting. The Recovery Model is set to SIMPLE for the DB but I did notice the log file growing while deleting the records - only to a certain amount, approx. 1.5GB. At this point in time there was 5GB free.

platypus - I used a regular delete.. from.. while.. it did include a join. When I run a select statement using the criteria I deleted with - zero records are returned.

After each delete, I did get a message saying n number of records affected.