April 17, 2009 at 10:54 am
Recently we removed over 8000 stored procedures from a database. Does someone know how I can recover the space from the table that was using it? Normally if a significant amount of data is removed from a table I rebuild the clustered index so that there is not a lot of wasted, fragmented space. I am not sure which tables are affected when a stored procedure is deleted.
Thank you in advance for any clues.
April 17, 2009 at 3:38 pm
Run this line of Sql and post the results.
Use your_database_name_here
go
exec sp_spaceused
go
April 21, 2009 at 9:18 am
The results are:
database_size unallocated space
64835.94 MB 38206.49 MB
reserved data index_size unused
25160008 KB 19028528 KB 5718520 KB 412960 KB
Now to figure out what to do. 🙂
Thank you
April 21, 2009 at 10:19 am
USE master
go
DBCC SHRINKDATABASE('your_database_name_here', NOTRUNCATE)
go
DBCC SHRINKDATABASE('your_database_name_here', TRUNCATEONLY)
go
This command will shrink the database and should be run off hours. As always when making a change to adatabase have a backup you are confident of.
I'd expect this to shrink the database down to very little excess space. You'll need to then expand both the data and log files to a size appropriate to your environment.
David
April 21, 2009 at 12:36 pm
David O (4/21/2009)
USE mastergo
DBCC SHRINKDATABASE('your_database_name_here', NOTRUNCATE)
go
DBCC SHRINKDATABASE('your_database_name_here', TRUNCATEONLY)
go
This command will shrink the database and should be run off hours. As always when making a change to adatabase have a backup you are confident of.
I'd expect this to shrink the database down to very little excess space. You'll need to then expand both the data and log files to a size appropriate to your environment.
David
Note that if you do this, all of your indexes are going to be fragmented. After increasing the database size - you are going to need to rebuild all of your indexes.
I would not recommend shrinking the database - if needed, you should just shrink the file using DBCC SHRINKFILE(logical_name, size). You can look up the specifics in books online. I don't think this is what you want to do. You want to keep the space available in the database unless you don't think it will ever be used again.
The tables you would be interested in are:
dbo.syscomments
dbo.sysdepends
dbo.sysobjects
dbo.sysindexes
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 21, 2009 at 12:49 pm
I don't want to use shrink database. I think that SQL Server is similar to Oracle and Informix in that an object is allocated space and that the only way to release the space from the object is to rebuild it; just deleting data does not reduce the size of the object. Which ever table once held all of the code for the 8000 procedures would now be quite empty and will never again need to be nearly that large. I want that space to be available to other objects.
I will check the tables that you indicated.
Thank you.
April 21, 2009 at 12:59 pm
Right - I was not recommending shrinking the database. Only, if absolutely required - because you are never going to grow the datafile again, ever - then you can shrink the file. Otherwise, the data file is going to end up growing again.
Also, if you ever do shrink a database or file - that process will fragment the indexes as it moves the data around to allow for the shrink process. Then, you would have to rebuild all indexes to remove the fragmentation. Not really a good idea...
I think you are on the right track - rebuilding the indexes for those tables should free up that space. However, I am not sure it really is going to be that significant - but it is not going to hurt to rebuild them, other than the momentary unavailability of the tables during the rebuild process.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 21, 2009 at 1:01 pm
Hmmm, I must have mis-interpeted this post. Space isn't allocated to tables in Sql Server it is allocated to the database. If you deleted 8000 objects that space is then just available as unused space.
You don't need to do anything to the recovery space from system tables. They are best left alone.
Your database is almost half empty, so I thought you were interested in recovery any available space. If your system doesn't have any storage pressure then you can just leave it as is.
Sorry if I lead you in the wrong direction.
David
April 21, 2009 at 1:02 pm
I had (still have on certain servers) this same issue. I documented the process I used to address this issue at http://www.simple-talk.com/sql/database-administration/on-the-trail-of-the-expanding-databases/ I should note that if you were using SQL 2005 the Alter INDEX.. REBUILD command is very helpful with this issue. Of course if you are running SQL 2000 then see the above link for an alternate approach.
HTH
Francis
April 21, 2009 at 1:13 pm
David O (4/21/2009)
Hmmm, I must have mis-interpeted this post. Space isn't allocated to tables in Sql Server it is allocated to the database. If you deleted 8000 objects that space is then just available as unused space.You don't need to do anything to the recovery space from system tables. They are best left alone.
Your database is almost half empty, so I thought you were interested in recovery any available space. If your system doesn't have any storage pressure then you can just leave it as is.
Sorry if I lead you in the wrong direction.
David
Not quite true - tables do reserve space and sometimes that space is not released. Performing an index rebuild or reorganize can release the unused space and reduce the reserved amount.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 21, 2009 at 1:17 pm
SQL Server allocates extents to an object. It keeps track of how much space is free or used but it does not release the free space in the extent for other objects. I think the only way to release unused space in the an objects allocated extents is to rebuild the clustered index.
April 21, 2009 at 1:31 pm
Thank you. I read the article and will use the script to track down my problem tables. Thank you much.
April 21, 2009 at 2:55 pm
Jeff
I stand corrected on the clustered index point but the space used by the fragmentation versus the data deleted shouldn't be that significant?
David
April 21, 2009 at 2:59 pm
David, I wouldn't think so either - but, it really depends on how large the stored procedures were. They are looking at 8000 procedures that were removed, which could be quite a bit of space if the procedures were of any significant size at all.
Either way - it doesn't hurt to rebuild the indexes.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply