Reclaiming lost space

Question

Reclaiming lost space

DhelMheds

SSC Journeyman

Points: 82
More actions
October 6, 2004 at 12:07 am

#163429

Hi!
Im new to MS SQL and I would like to ask what command do I use in reclaiming space in MS SQL 2000?

Thanks in advance!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76001 More actions · Answer 1

I am assuming here that you have performed some form of large delete operation and want to shrink your database file?

Firstly, do a DBCC DBREINDEX on that tables from which you have deleted records. This will reclaim the internal space within the database file. This requires exclusive access so if you can't kick your users off the database use DBCC INDEXDEFRAG.

Secondly use DBCC SHRINKFILE, however if disk space isn't critical I would simply leave the empty space alone because it will soon fill up again and you there will be a performance hit when the database grows.

LinkedIn Profile

Richard Rice SSC Eights! Points: 879 More actions · Answer 2

We had an issue with the transaction logs growing out of control and resolved it by running an SQL script from MS that works to reorganize the data in the log file to be sequential, and then performing a backup to purge the log file. I have seen log files go from 20G to 10M in size (I set the script to create a 10M log file, this is variable).

To keep the log files under control, I do backups of the transaction logs every 8 hours. This purges the logs of data and keep growth under control. If you don't do something like this, the log files can grow out of control.

Also, doing a tape backup using anything other than MS-SQL's backup utility does NOTHING for the transaction log size. To be affective, only use the SQL backup utilities.

Try this (from Microsoft):

SET NOCOUNT ON

DECLARE @LogicalFileName sysname,

@MaxMinutes INT,

@NewSize INT

-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***

USE -> DATABASE NAME <- -- This is the name of the database

-- for which the log will be shrunk.

SELECT @LogicalFileName = -> LOG FILE NAME <-, -- Use sp_helpfile to

-- identify the logical file

-- name that you want to shrink.

@MaxMinutes = 30, -- Limit on time allowed to wrap log.

@NewSize = 10 -- in MB

-- Setup / initialize

DECLARE @OriginalSize int

SELECT @OriginalSize = size -- in 8K pages

FROM sysfiles

WHERE name = @LogicalFileName

SELECT 'Original Size of ' + db_name() + ' LOG is ' +

CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +

CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'

FROM sysfiles

WHERE name = @LogicalFileName

if exists(select table_name from INFORMATION_SCHEMA.TABLES

where table_name = 'DummyTrans')

begin

print ' DummyTrans table found, deleting now...'

drop table DummyTrans

end

else

begin

print 'DummyTrans table not found, continuing with the process...'

end

CREATE TABLE DummyTrans

(DummyColumn char (8000) not null)

-- Wrap log and truncate it.

DECLARE @Counter INT,

@StartTime DATETIME,

@TruncLog VARCHAR(255)

SELECT @StartTime = GETDATE(),

@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'

-- Try an initial shrink.

DBCC SHRINKFILE (@LogicalFileName, @NewSize)

EXEC (@TruncLog)

-- Wrap the log if necessary.

WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired

AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk

AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.

BEGIN -- Outer loop.

SELECT @Counter = 0

WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))

BEGIN -- update

INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.

DELETE DummyTrans

SELECT @Counter = @Counter + 1

END -- update

EXEC (@TruncLog) -- See if a trunc of the log shrinks it.

END -- outer loop

SELECT 'Final Size of ' + db_name() + ' LOG is ' +

CONVERT(VARCHAR(30),size) + ' 8K pages or ' +

CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'

FROM sysfiles

WHERE name = @LogicalFileName

DROP TABLE DummyTrans

PRINT '*** Perform a full database backup ***'

SET NOCOUNT OFF

Also reference:http://support.microsoft.com/default.aspx?scid=kb;en-us;873235

DhelMheds SSC Journeyman Points: 82 More actions · Answer 3

No... I did not perform any deletion in our data... The situation is this. I executed sp_spaceused table_name and below is the result.

tablename rows reserved data index_size unused

table_name 3708631 3032360 KB 585192 KB 1034176 KB 1412992 KB

I would like to know how am I gonna reclaim the 1412992 KB unused space? Is it possible?

Thanks!

DhelMheds SSC Journeyman Points: 82 More actions · Answer 4

I was able to reclaim the space by using dbcc updateusage()....

Thanks to all!

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 5

Why reclaim it .... if it will be used as needed or is this unusable?

sa24 SSCrazy Eights Points: 8027 More actions · Answer 6

FYI -

Below is the definition for DBCC UPDATEUSAGE from books online.

Reports and corrects inaccuracies in the sysindexes table, which may result in incorrect space usage reports by the sp_spaceused system stored procedure.

Please note that it does not release space and it only updates the reports. The unused space did not exist on the system when sp_spaceused was run.

DhelMheds SSC Journeyman Points: 82 More actions · Answer 7

Thank you very much for all your info! It will really help me a lot!

alfred-169739 SSC Journeyman Points: 93 More actions · Answer 8

Your message has been very helpful.

Useful posting

alfred