Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

spaceused Expand / Collapse
Author
Message
Posted Friday, March 17, 2006 9:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 3, 2006 5:20 AM
Points: 40, Visits: 1

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 ?

Post #266619
Posted Friday, March 17, 2006 9:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Post #266628
Posted Friday, March 17, 2006 10:50 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:12 PM
Points: 3,248, Visits: 569
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



Post #266666
Posted Monday, March 20, 2006 9:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 3, 2006 5:20 AM
Points: 40, Visits: 1

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.

Post #267000
Posted Monday, March 20, 2006 12:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:04 AM
Points: 209, Visits: 381

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

Post #267040
Posted Tuesday, March 21, 2006 7:15 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:12 PM
Points: 3,248, Visits: 569
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



Post #267257
Posted Tuesday, March 21, 2006 8:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Post #267282
Posted Wednesday, March 22, 2006 8:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 3, 2006 5:20 AM
Points: 40, Visits: 1

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.

Post #267611
Posted Wednesday, March 22, 2006 8:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Post #267621
Posted Wednesday, March 22, 2006 9:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Post #267623
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse