On a production database, there is a 2GB database, when I run
sp_spaceused it indicates a very high quanity of unused space. The
database has been shrunk & free space sent to the OS. Why is this
value so high, what can I do to reclaim the space?
database_name database_size unallocated space
------------------------------------------------------------------------------
DB_00001 2004.13 MB 49.64 MB
I have a large table that I recently purged a year of data from.However, the table size in sp_spaceused hasn't decreased as much as Iwould expect. (there are no text or large object columns on thistable, BTW) Running sp_spaceused on the table shows the following.rows reserved data index size unusedTABLE_NAME23470880 67790808 KB 18116312 KB 3211616 KB 46462880 KBI ran a dbcc indexdefrag on all indexes on the table last night,including the clustered index, and I'm still seeing pretty much thesame amount of space reported as unused.The one thing that I haven't done yet is to run the sp_spaceused onthis table with the @updateusage flag set to true as I need to do thisafterhours to reduce contention. I will attempt this tonight andreport on the results.Here's DBCC SHOWCONTIG OUTPUT for the table:DBCC SHOWCONTIG scanning 'TABLE_NAME' table...Table: 'TABLE_NAME' (917578307); index ID: 1, database ID: 7TABLE level scan performed.- Pages Scanned................................: 2264447- Extents Scanned..............................: 285484- Extent Switches..............................: 287092- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 98.59%[283056:287093]- Logical Scan Fragmentation ..................: 0.04%- Extent Scan Fragmentation ...................: 3.19%- Avg. Bytes Free per Page.....................: 2418.9- Avg. Page Density (full).....................: 70.12%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.Looking through the history of the group, the one thing that seems toremedy this problem consistantly is to bcp out all the data, trunc thetable and bcp all the data back in. This is not really a possibilityfor me due to the size of the table and the availability expectationsof my customers. Is there anything that I forgot to check? Do youthink that it's just a matter of incorrect statistics in sp_spaceused?Thanks in advance:Matt
Could some one help me to explain what does it means when I ran sp_spaceused on the database it reported ex. -15430kb for unused. Does this mean that the index ran out off space. Do I need to increase space for the database. I will try to run the dbcc check hope it will fix the problem.
How can I determine how much unused space to remove from database files? I am setting up a maintenance plan and I am trying figure that out. Also, is there a rule on how far to shrink TL?
On the drive where the datafiles resides there is enough free space . Also, the datafile and transaction are set to auto grow with unlimited file growth
FYI - the OS is Windows 2000 and the DB is SQL Server 2000 with SP3
Hello, I have a table which resides on a diff.filegroup. The space allocated is 7700MB. Now ehen I use sp_spaceused 'table_name' to see how much space is left it's giving me data as rows reserved data index Unused at 1000hrs 629879 6777904 5068344 8 1709552 After an hour 637537 6780336 5070800 8 1709528 After 2hours 643883 6782560 5072904 0 1709656 After 2.5hrs 646887 6783584 5073920 0 KB 1709664 kb after 3hrs 647239 6783712 5074056 0 KB 1709656 K If you note rows are increasing,so is the reserved place and data but unused is also increasing.I think it should come down. What behavior is this?How can I find out how much space is left in this table residing on diff.file group? TIA
I wrote simple script to check space used by tables:
CREATE TABLE #SpaceUsed( TableName NVARCHAR(128), NoOfRows INT, Reserved NVARCHAR(18), Data NVARCHAR(18), Index_Size NVARCHAR(18), Unused NVARCHAR(18) ) GO sp_msforeachtable "INSERT INTO #SpaceUsed EXEC sp_spaceused '?'"
SELECT * FROM #SpaceUsed
SELECT CAST(Sum(CAST(Replace(Reserved,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalReserved, CAST(Sum(CAST(Replace(Data,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalData, CAST(Sum(CAST(Replace(Index_Size,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalIndex_Size, CAST(Sum(CAST(Replace(Unused,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalUnused FROM #SpaceUsed
I am working with a large database that has its tables stored on a secondary filegroup. I'm trying to shrink the size of the files but I can't seem to get the system to free up the unused space. I've tried shrinkdatabase and shrinkfile both with and without the truncateonly option. Has anyone else had this problem? Is there a workaround? Any help would be greatly appreciated.
I'm trying to figure out how to reclaim unused space in a huge table (tbl) after setting one of the text columns to be the empty string. Other tables have foreign key columns associated with tbl.
After doing something like this,
update tbl set col = ''
the table doesn't automatically reclaim that space. If I do a direct insert into a new table like this,
insert into tbl2 (cola, colb, colc) select * from tbl
the new table is smaller as expected (about half the size in my case). Any ideas?
I have a table which has 6 text columns (tblA).. I no longer require 1 of those text columns and want to reclaim the space that it is currently taking up..
Is the only way to BCP out all the data (except the 1 column i no longer require) drop the column and BCP the data back into the table?
currently stuck with an issue where I need to reclaim the unused table space in SQL server.
ISSUE: Due to database size issue we have moved all the images from database to filesystem and want to reclaim the unused space now....I have tried shrinking the database and rebuild the indexes but didn't see any difference in the table or database size.
Similarly I have identified many Non clustered indexes on big transactional tables(~ 4 million records) that where not used since GO live 1 year back, so I wanted to drop these indexes to cut down the performance and maintenance overhead , so my question is will dropping these indexes reduces the database size?
best method to my issue regarding unused space. SQL SERVER 2014 BI EDITTION..I have a table that showed 62% Data, 7% unallocated, and 29% unused space.I ran the ALTER TABLE <table name> REBUILD, which changed it to 32% data and 67% unused.What I do with this table monthly and what I believe is causing this unused space issue is this:Every 1st of the month I get a csv file that holds around 3.2 million rows/15 columns of data. About 3 gbs every time.It's 13 months of data. I remove the last 12 months from the current table and import the new 13 month data.I do this twice a month and have no choice because the data is constantly updated and why we get it twice a month.So I am deleting around 6 gbs of data a month as well as adding around 6 gbs of data a month.
I believe this is why I end up with so much unused space, but just found the REBUILD command, which worked but now a small hit on performance and have too much space in the current table. I have read several times that shrinking the file is no good, but what other way to get rid of the extra space in this table? Also, the table doesn't have any indexes or primary key because of duplicates.
OS: Windows Server 2008 R2 Standard SQL Server: 2008 SP1 Standard
We have a database with about 500 GB of free disk space on data file and the database is being set to read only mode for the fore-see-able future. We would like to release this unused disk space. We know that we could shrink the data file and then work on re-indexing to remove fragmentation.we wanted to check if some other method like backup and restore of the database could free up unused disk space in the data file. if someone was able to release unused disk space on data file by implementing a backup and restore.
how to claim unused free space at table level. The database size is of 4.6TB, recently I deleted some data which is of almost 1.5TB but my unused space has grown upto 2.5TB leaving me short of space on my drives. How do I claim this usused on to OS Disk space?
I am using the sp_spaceused system stored procedure to identify tables in my database that are large. I have two questions. Can anyone help me sort this list. I assume this is returned in clustered order, but I want to order the list by rows. Also, when I run this sp_spaceused stored proc, on some of my tables I get a negative value returned under the 'index_size' and 'unused' columns in my result set. How can this be.
on some of our sql 6.5 databases the space available is shown as 0MB. I realise that I must run sp_spaceused to get an accurate result, but the results I then get are slightly confusing. All I want to know is the space left in both the database and the log - can I get a reading for both?. Sometimes the unused space is shown as a negative number. Can anyone advise since books online is fairly unspecific.
(the total script that i run incidentaly is:
exec sp_spaceused go DBCC checktable (sysindexes) go DBCC checktable (syslogs) go exec sp_spaceused @updateusage=true go)
hi, I used sp_spaceused 'owner.tablename' and it shows index size as 24kb whereas there is no index on this table. I thought may be it's not showing the right statistics so I update the statistics but it is still showing the same . what is happening? I am using SQl 2k/Win2k. TIA.
Hi,In our environment sp-spaceused returns:Allocated: 500Unallocated: -100Enterprise Database Taskpad shows that our total database size = 400 MB(320 used; 80 unused)Windows Explorer shows also that MDF file is more or less 400 MB.What does sp_spaceused exactly ???How do I SELECT the 320;80 of the taskpad with a query ???Greetings,Arno de Jong, The Netherlands.
I have ran sp_spaceused (SQL 6.5 SP4) on my database (4000MB Data, 500 Log) and received the following output for "Reserved" : 4412390 KB. Note that this is more than 4000MB. I checked and made sure my data and log were truly separated...
Just to make sure, I added 2000MB more data space. Sp_spaceused now returned the following for reserved: 3493220 KB.
I am wondering why the allocation dropped, while no user activity was taking place during the process of growing the database....sp_spaceused reported data size first at 2725532, then at 1806314. Any ideas? A bug? Did the database fill up and begin using log pages...?
Hi, I ran sp_spaceused against a DB table, and got -160K in the field "unused". If anybody can explain what this negative number means? We have a problem with performance. It's extremly slow. The table contains 600,000 records. Even simple select * from <table> (table scan) takes 6 minutes. This is SQL Server2000. Any help is appreciated.
CREATE PROCEDURE sp_space @sortbyrows bit=0 AS SET NOCOUNT ON select cast(object_name(id) as varchar(50)) AS name, sum(CASE WHEN indid<2 THEN rows END) AS rows, sum(reserved)*8 AS reserved, sum(dpages)*8 AS data, sum(used-dpages)*8 AS index_size, sum(reserved-used)*8 AS unused from sysindexes with (nolock) where indid in(0,1,255) and id>100 GROUP BY id with rollup ORDER BY CASE WHEN @sortbyrows=1 THEN sum(CASE WHEN indid<2 THEN rows END) ELSE sum(reserved)*8 END desc
Usage:
EXEC sp_space --show stats sorted by reserved space size EXEC sp_space 1 --show stats sorted by row count
It basically displays the results of sp_spaceused, but broken out by each table, and it rolls it up to a grand total. Naturally, for the most accurate results you should run DBCC UPDATEUSAGE before running this sproc.
Hello, I started to write a stored procedure to insert into a temp table from sp_spaceused but couldn't get the format right. I did a search in the swynk archive and saw that some people have solved this but I couldn't see any example code. Does anyone have scripts they've written to gather db size info using sp_spaceused or some other mechanism? Thanks in advance, Dan
