Reclaiming Unused Space In A Table
Mar 13, 2008
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?
Rob
View 3 Replies
ADVERTISEMENT
Aug 5, 2015
I have many large tables with millions of records in a SQL Server database. They all use an Identity column which is the clustered index. We haven't been deleting any records until recently because disk space is now becoming a problem.
Assuming I delete a lot of old records, I'm thinking that the freed up space in the data pages won't be reused. New records will be added at the end of the table because of the Identity column being the clustered index. So the table will keep getting bigger even though there is lots of free space.
Assuming I'm right, then how do I recapture this unused space? Is an alter table rebuild the best way? Or rebuilding the clustered index?
View 2 Replies
View Related
Sep 22, 2005
Hey guys,
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?
View 12 Replies
View Related
Sep 15, 2014
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?
View 5 Replies
View Related
Dec 30, 2014
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.
View 5 Replies
View Related
Feb 23, 2012
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?
View 12 Replies
View Related
Nov 27, 2012
I'm running a security application that generates a lot of events. I have gone in over the past couple days and deleted large numbers of old events in an effort to reclaim space on the D: drive where the SQL DB for this app resides. I did this throught the app itself... through its GUI front end.
I'm not having any impact. There is not a file on the same drive where the deleted events are being directed to. If all these events are going away, why does my used space on the drive not shrink? I came at it from another tack, creating sql queries to delete old events from the same app. I did this through queries in the SQL mgmt studio. The tasks (multiple) completed successfully, and the report on the # of rows 'affected' showed that it was a substantial number. No decrease in the windows drive space being used. Any tips?
View 1 Replies
View Related
Jan 23, 2008
Dear Friends,I'm running a SQL 2005 database. Lately (in the last 4 months) my database size has grown exponentially and now totals 2.5 gig (it used to be about 100mb). When I run sp_spaceused i get the following info:
Reserved Space 2522MB
Data 450MB
Indexes 215MB
Unused 1856MB
<!--[if !supportLineBreakNewLine]-->
The unused space is reserved by my two largest tables, one being 542mb (which is a log I create and the size seems fine for the amount of data) reserving 1033mb and the other being 121mb (which is another log and also seems fine) reserving most of the rest.
I have tried running DBCC SHRINKFILE and DBCC CLEANTABLE commands on a backup copy of the database and cannot seem to free the unused space.
I have also tried copying the database in an attempt to reduce the unused reserved space and have tried creating a new database and importing the tables with no luck
Any help would be greatly appreciated, noting that I'm honestly not that technical and would probably need step by step instructions, but if you only have time to point me in the right direction that would also be of great help!
Jano
View 9 Replies
View Related
Jul 11, 2014
Having just archived quite a bit of data from the main Production DB, I now have around 15% free, reclaimable space sat in the data file.
I'm reluctant to run DBCC SHRINKFILE as that apparently causes a lot of Index fragmentation which will cause issues for performance - how else can the space be allocated back to the OS?
View 4 Replies
View Related
Mar 1, 2006
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?
Please help. Thanks.
View 3 Replies
View Related
Mar 29, 2001
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
View 3 Replies
View Related
Mar 12, 2004
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
DROP TABLE #SpaceUsed
and one of results looks strange to me:
TableName NoOfRows Reserved Data Index_Size Unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
T_TableXX 50081 38024 KB 37432 KB 640 KB -48 KB
Anyone know reason of such result (negative value of unused space)?
View 3 Replies
View Related
Oct 17, 2005
I was just reading a doc on SQL , that said that to free up the unused space from the DB , you should use the shrinkdatabase DBCC commnad. ....
I want to knw how to determine how much free unused space is there in a DB
Thanks,
View 2 Replies
View Related
Jul 20, 2005
On a production database, there is a 2GB database, when I runsp_spaceused it indicates a very high quanity of unused space. Thedatabase has been shrunk & free space sent to the OS. Why is thisvalue so high, what can I do to reclaim the space?database_name database_size unallocated space------------------------------------------------------------------------------DB_00001 2004.13 MB 49.64 MBreserved data index_size unused------------------ ------------------ --------------------------------1531248 KB 412720 KB 165168 KB 953360 KB
View 5 Replies
View Related
Feb 7, 2002
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.
Thank you.
Cathy
View 2 Replies
View Related
May 26, 2015
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.
View 2 Replies
View Related
Jul 20, 2005
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
View 4 Replies
View Related
Jan 15, 2015
How to find if there is a query that can be written on DMV's which will be able to retrieve the indexes that are not being used in a table.
View 2 Replies
View Related
Jul 20, 2005
I have a database that has grown larger than I had expected. I thinkthere is one table that is at the root of the problem. The table isdefined as follows:CREATE TABLE [dbo].[UserAudit] ([UserAudit_id] [int] IDENTITY (1, 1) NOT NULL ,[UserAuditAction_id] [int] NOT NULL ,[Dataset_id] [int] NOT NULL ,[UserName] [char] (64) NOT NULL ,[TableName] [char] (64) NOT NULL ,[Detail] [varchar] (4000) NOT NULL ,[DateRecorded] [smalldatetime] NOT NULL ,[Dsc] [char] (256) NULL)There are 14919 records in this table. When I do the calculations iapproximate that space used by this table should be in the region of10mb (4+4+4+64+64+290(average length of Detail column)+4+256) * 14919.When I execute "sp_spaceused 'UserAudit'" i see that 119MB are beingused by this table.name rows reserved data index_size unusedUserAudit 14919 119808 KB 119352 KB 400 KB 56 KBEven if I use 4000 for the Detail column in my calculations I stillcome up with about only 64mb.Any ideas on whats going on here?Thanks in advance.
View 6 Replies
View Related
Nov 16, 2007
I currently have 8,000 rows in the orders table and it is estimated that it will have in average 50 orders daily. The orders need to be kept for 6 months, before it is archived and deleted from the database. I calculated the amount of space that needs to be reserved for the table but unsure if I am on the right track in calculating the table size.
would there be anything wrong in my calcultation that i missed?
int
datetime
tinyint
nvarchar(15)
int
int
int
int
money
money
money
money
datetime
4
4
2
15
4
4
4
4
4
4
4
4
4
4
= 65 bytes
+ rowoverhead = 7
total = 72
View 12 Replies
View Related
Jul 23, 2005
I have a table name in SQL Server 2000 that has a space in itex: aim internationalI had trouble just in the query analyzer with this..I had to place thename in brackets [] for it to work. But now I'm in Visual Studio .Net2003 and it gives me another problem. I get the table name from a dropdown list selection and send it to a query string. But is gives me thiserror:***************Line 1: Incorrect syntax near 'AIM international'.Exception Details: System.Data.SqlClient.SqlException: Line 1:Incorrect syntax near 'AIM international'.******************Here is the string:****************Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM '" & PubName &"' WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'ORDER BY Last_Name "**********************And the variable PubName is the string AIM international .I tried placing it in brackets like in the query analyzer :****************Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM ['" & PubName &"'] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'ORDER BY Last_Name "*******************and I get this:*******************Invalid object name ''AIIM international''.Exception Details: System.Data.SqlClient.SqlException: Invalid objectname ''AIIM international''.*******************Any idea what I have to do for it to work ??? Can I use table nameswith spaces or it's just not a good idea???Thanks for the help guys!!JMT
View 6 Replies
View Related
Jul 8, 2002
I'm trying to determine how much space some tables use (SQL2000), and I found 2 suggestions posted earlier, but can't get them to work for me.
The first was
"Right click on the DB in Enterprise Manager, select view then taskpad."
When I try that, I get some of the tables displayed with the info I want, but I can't see them all and can't scroll down beyond the first 22 tables in the database.
The second suggestion:
exec sp_msforeachtable @command1="print '?'",@command2="sp_spaceused '?'"
It says "command copleted successfully, but where does the output to this go ?? Is there something other than "print" I should use ?
The grid pane is empty.
View 2 Replies
View Related
Jul 1, 2004
Hey all, first time poster here.
Cheers!
Enterprise Edition SQL2k sp3a on Win2k3 Enterprise Edition server.
Using BCP to load ~4 million records.
Largest record width is ~350 bytes.
When finished i have over 6 million pages reserved and only about 800 thousand actually used.
Which puts me 'round about 3 rows per page and WAY too much empty space.
YIKES!
Any thoughts as to why this is happening?
Or insight into how BCP handles page allocations that may be different than the typical extent based page allocations for table data?
Thanks!
View 14 Replies
View Related
Dec 4, 2007
I have a stored proc that processes large amounts of data. I have used temp tables in this stored proc. After I drop these temp tables, it doesn't release the disc space and my hard drive runs out of space before the process ends. When I stop the sql service and restarts it, it releases the disc space. How can I force the release of disc space inside of my stored procedure?
View 6 Replies
View Related
Feb 6, 2007
The default behaviour for pressing SHIFT + SPACE when editing text inside a cell in the Table Editor view in Microsoft SQL Server Management Studio Express is to change focus to select all cells comprising the current row. This behaviour lends itself to the default behaviour for pressing SHIFT + {LEFT | RIGHT | UP | DOWN } in changing the focus to select adjacent cells.
However, I find this is quite annoying as the SHIFT + SPACE combination is quite a common miss-type when entering text in UpperCaps or when entering text with spaces while holding the SHIFT key.
Anyone else have this gripe with MSSMSE? Anyone know how to disable this behaviour?
Simon
View 2 Replies
View Related
Jul 21, 2015
Using lot intermediate table in SP, at end of the SP, those table are getting truncating.but those pages are still unallocated and unused , How to unallocated the space and add to OS drive.
Because of above scenario, we are wasting lot of space. i am seeing lot of unallocated space.
View 5 Replies
View Related
Nov 14, 2007
Hi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
View 1 Replies
View Related
Feb 23, 2007
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005.
The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions
1) Replacing spaces with €œ €?
2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
View 1 Replies
View Related
Nov 26, 2015
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList  VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
View 3 Replies
View Related
Jan 31, 2007
My client's website database is hosted by a third party. I need to alter one of the column definitions for the largest table in the database. Unfortunately, the transaction log fills up if I try to alter the table. I've done all the usual stuff like truncating the log, etc., but the simple fact is that the operation requires more log space than we have available. Therefore, we need to purchase additional disk space for the database.
What I'm looking for is a way to roughly estimate how much log space will be required to alter this table so that we purchase enough but not too much additional space. The table has an identity primary key and 4 other single column indexes: one int, one datetime and two varchar(30) columns.
Any suggestions? Thanks in advance.
View 4 Replies
View Related
May 12, 2007
In our SQL Server database we will have a table that will be populated with about 2000 recordsper day. That is 2000 records per day for 5 days per week. Currently the computer we are using has about 50 gigabytesof available hard drive space on it. We are concerned that maybe we will need a bigger hard drive,based solely on the number of records entered into this table per day. The problem is I don'tknow how to calculate how much hard drive space we need. I think I read that using varchar,sql server 2005 really optimizes a database. Here is a typical example of data in ourdatabase. I put dots on three lines between the first and last sample record to justillustrate that there are many records in between.
Basically we only need 8 months of data at a time in the table and then we can purgerecords older than 8 months.Can someone help me approximate how much hard drive space I might need for 8 months of data,given the following sample record in the database?
Sample: -->34.5 4.08 10.6 .0012
Sample Table in my DB just for illustration:
(PPsquare inch) (Diameter) (Weight gm) (coeffOfSatFriction)
34.5 4.08 10.6 .0012...21.7 3.54 6.22 .019
View 4 Replies
View Related
Mar 22, 2000
The sp_spaceused proc seems to have been removed in 7.0?
Dbcc sqlperf also seems to have been removed?
Surely there must be a counterpart - which is?
I like to have a script which shows the space usage
especially on a table basis for a periodic report.
View 3 Replies
View Related
Apr 12, 2008
I hv dropped the useless columns for a table but the table size is remain unchanged by executing sp_spaceused. pls advise how to release table spaces.
thx
View 6 Replies
View Related