March 17, 2006 at 9:17 am
Hi guys!
My database is getting kind of big, so I have run sp_spaceused in my database to check the space allocated…
The results are as follows:
Database name database size Unallocated space
inquierosb 467.44 MB -136.90 MB
reserved data index size unused
617816 KB 326520 KB 49616 KB 241680 KB
How can i free up the 241680kb unused space?
What is the meaning of the unallocated space?
Is there a way to view which table are allocating more space in my database ?
March 17, 2006 at 9:32 am
firstly it might be wise to run the following
use mydatabase
go
dbcc updateusage
go
that should make sure you get accurate sizing information
then run sp_spaceused again
to view which tables are using the most space you can do a few things
sp_spaceused 'tablename' should sho the individual table size.
there is also an undocumented command (i think it's called) sp_msforeachtable which can run the sp_spaceused command against each table
based on your questions i'd also say can you post the results of the command DBCC SQLPERF(LOGSPACE)
just in case...
MVDBA
March 17, 2006 at 10:50 am
You can shrink the database file size by using DBCC SHRINKFILE command. Check the BOL for the syntax. You have to use the actual file name. To check what it is, use Enterprise Manager, right click on the database. Select Properties. Go to the Data tab and you'll find the file name.
-SQLBill
March 20, 2006 at 9:28 am
Thanks for your replies.
Bill,
Should i use DBCC SHRINKFILE with the TRUNCATEONLY argument to release the unused space ?
Mike,
About that interesting command (sp_MSforeachtable), it's not working, am i missing something ? :
Server: Msg 201, Level 16, State 4, Procedure sp_MSforeachtable, Line 0
Procedure 'sp_MSforeachtable' expects parameter '@command1', which was not supplied.
March 20, 2006 at 12:34 pm
Pedro,
For the sp_MSforeachtable suggestion, use this:
exec sp_MSforeachtable 'exec sp_spaceused ''?'''
For the DBCC SHRINKFILE suggestion, it would be a good idea to do a ReIndex or IndexDefrag of your tables after your done. Some of your tables will get fragmented after running the SHRINKFILE.
HTH,
Mark
March 21, 2006 at 7:15 am
To use Truncate or not........
If you use truncate (with DBCC SHRINKFILE or with BACKUP LOG dbname WITH TRUNCATE_ONLY), you 'break' the backup chain and must start a new chain. That means that you must do a Full backup after you issue the truncate command. Truncate means that log and any after it cannot be applied to the FULL backup done before the truncate.
I have used truncate at times.....but I always do a full backup right after it.
-SQLBill
March 21, 2006 at 8:00 am
DBCC SHRINKFILE WITH TRUNCATE_ONLY doesn't break the LSN (log sequence number) and cause you to lose your backup chain - all it does is remove the empty space from the file. If your tables have allocated space near the end of the file then you may not be able to release the space as you can only release file space down to the last active page in the file - but there may be free space in the file before this
BACKUP LOG WITH TRUNCATE_ONLY does break you LSN though, however this performs something completely different and won't claim space back - it will just flush the inactive portion of the log.
BACKUP LOG WITH TRUNCATE_ONLY has been deprecated and shouldn't be used - if your log files are growing set up a t-log backup job or set your databases to simple mode
a shrinkfile will not
MVDBA
March 22, 2006 at 8:44 am
Thanks for you replies.
I have ran the update usage command and now the values are as folows:
Databasename database size unallocated space
InquieroSB 467.44 MB 43.65 MB
Reserved data index_size unused
432936 KB 365776 KB 53896 KB 13264 KB
I have some questions, can you can help me understand this:
- What is the diference between unallocated space and unused space
- In the event of running DBCC SHRINKFILE TRUNCATEONLY commend what is the space recovered (unallocated space or unused space ?)
- can I ran DBCC SHRINKFILE TRUNCATEONLY in multi-user mode , I mean can I ran it if there is someone using the database ?
- Can i recover some space by simple doing a reindex? What is the command that I can use - DBCC DBREINDEX or DBCC INDEXDEFRAG ?
Thanks.
March 22, 2006 at 8:58 am
What is the diference between unallocated space and unused space
not sure if this is entirely accurate but here goes - unused is allocated space that has not been used by the objects yet - this can't be shrunk using a shrinkfile.
- In the event of running DBCC SHRINKFILE TRUNCATEONLY commend what is the space recovered (unallocated space or unused space ?)
unallocated - but not necessarilly all of it (in some cases none) - if you have allocated space (might be unused) at then end of the file then you won't reclaim any space back
the most you can reclaim from your database is 43Mb - but the next time you rdatabase grows due to autogrow 10% (if thats what you have it at) then it wuill grow by 46Mb - so don't run the shrink!!!
- can I ran DBCC SHRINKFILE TRUNCATEONLY in multi-user mode , I mean can I ran it if there is someone using the database ?
you can run it if other users are operating.
- Can i recover some space by simple doing a reindex? What is the command that I can use - DBCC DBREINDEX or DBCC INDEXDEFRAG ?
the re-index or defrag operations will consume more space during the excercise and then de-allocate the space (but your file may have grown during the operation)
INDEXDEFRAG can be used while users are on the system (sort of) and won't really claim you any space back - it'll just make the indexes run better - t-log useage is pretty small as it's many operations not one big drop and rebuild
REINDEX will create new indexes and drop the old ones - (beware you need space to hold 2 copies of the index + working space + space in your t-log file to do this) but you should reduce the index size (depending on the fill factor you specify)
MVDBA
March 22, 2006 at 9:01 am
if i could suggest something-
can you run the command DBCC SQLPERF(LOGSPACE) and paste the results back
if you need to claim space back it might be wise to claim it back from the log and not from the data file which looks correctly set up.
MVDBA
March 22, 2006 at 10:56 am
Ok, i understand J
Mike,
Here are the results DBCC SQLPERF(LOGSPACE) :
DB name Log size (MB) Log space used(%) Status
master 2.2421875 36.694252 0
tempdb 0.7421875 58.552631 0
model 0.7421875 60.065788 0
msdb 2.2421875 38.567074 0
InquieroSB 0.9921875 54.281498 0
i2 9.9296875 96.449646 0
July 4, 2007 at 8:27 am
All in one stored procedure....
Cleaning and shrinking and reindexing and such..
Easily expandable.
CREATE
PROCEDURE sp_updateDB_65421190912
@dbase varchar(50)
AS
DECLARE @SQLcommand nvarchar(500)
Set @SQLcommand='ALTER DATABASE '+@dbase+' SET RECOVERY SIMPLE'
Exec sp_executesql @SQLcommand
DBCC SHRINKDATABASE (@dbase, 20)
Set @SQLcommand='exec sp_dboption '+@dbase+', ''trunc. log on chkpt.'', ''True'''
Exec sp_executesql @SQLcommand
Set @SQLcommand='DUMP TRANSACTION '+@dbase+' WITH NO_LOG'
Exec sp_executesql @SQLcommand
Set @SQLcommand='DBCC SHRINKDATABASE ('+@dbase+', 15)'
Exec sp_executesql @SQLcommand
Set @SQLcommand='Exec '+@dbase+'..sp_msforeachtable ''select ''''?'''' as ''''indexing ?'''' dbcc dbreindex(''''?'''')'' '
Exec sp_executesql @SQLcommand
go
exec
sp_updateDB_65421190912 'YourDatabase'
go
Drop
procedure sp_updateDB_65421190912
November 5, 2007 at 2:46 am
SQLBill (3/21/2006)
To use Truncate or not........If you use truncate (with DBCC SHRINKFILE or with BACKUP LOG dbname WITH TRUNCATE_ONLY), you 'break' the backup chain and must start a new chain. That means that you must do a Full backup after you issue the truncate command. Truncate means that log and any after it cannot be applied to the FULL backup done before the truncate.
I have used truncate at times.....but I always do a full backup right after it.
-SQLBill
DBCC SHRINKFILE and DBCC SHRINKDATABASE don't break the backup chain, whilst BACKUP LOG ... TRUNCATE_ONLY does, just tested as a matter of interest. One more remark: it's not always possible to do a full backup whenever you want as it might affect the performance of your disks, especially when size of your DB is well above 100Gb.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply