Unused Space
Mar 1, 2006How 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.
ADVERTISEMENT
Very Confused - Unused Space
Mar 29, 2001Hello,
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
Negative Unused Space
Mar 12, 2004I 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)?
Free UNUSED SPACE
Oct 17, 2005I 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,
Sp_spaceused - Too Much Unused Space
Jul 20, 2005On 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 RelatedHELP: Shrinkdatabase Not Freeing Unused Space
Feb 7, 2002I 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
Reclaiming Unused Space In A Table
Mar 13, 2008I'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
Release Unused Space Reserved For A Table
Sep 22, 2005Hey 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?
SQL 2012 :: Reclaim The Unused Table Space?
Sep 15, 2014currently 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?
Reclaim Unused Space / Rebuild Table
Dec 30, 2014best 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.
DB Engine :: Release Unused Disk Space
May 26, 2015OS: 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.
Claim Unused Free Space At Table Level?
Feb 23, 2012how 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 RelatedHow To Replace Empty Space Or White Space In A String In A Stored Procedure
Nov 14, 2007Hi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
Mutilple Space Gets Converted To Single Space In Report Viewer Control
Feb 23, 2007I 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.
Transact SQL :: How To Find Space Available Or Send Space Alerts In Percentage
Nov 26, 2015I 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] ....
Trans Log-&>space Allocated 27GB, Space Used 100MB
Mar 2, 2005Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate
Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
Double Space Replaced With Single Space By Dbms ??!
Jul 20, 2005This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
View 2 Replies View RelatedHelp Regarding Unused Columns
Aug 23, 2007Dear Experts,
I'm working for an ERP company, for our product, there are around 2000 tables. in each table, we are using 10 extra columns for our future use. all these are using nvarchar(50) datatype.
almost 95% of the columns in these are not in use almost. those are having null value and in some columns, the values are like 0,1...
is this affects the performance?
please spare your valuble time
thank you very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
Problem With Space Allocated For Transaction Log Space
Dec 5, 2001Hello,
Somebody know how to reduce the space allocated for the transaction log space for my SQL_DB ?
3700 MB allocated but only 100 MB used and 3600 MB are free !
Transaction log properties :
Automatically grow file are filled
file growth by percent = 5%
maximum file size - restrict filegrowth = 3700 MB (we can't reduce it !)
Thank you for your precious help !
Khaix from Brussel.
Suppress Multiple Space To Single Space..
Nov 14, 2006How do we suppress multiple spaces to a single space in T-SQL
E.G.
Field: FullName
e.g.
WOMENS HEALTH RIVER VALLEY
JOHN FAMILY MED GROUP
HERSH STWEART P.
PARK HEIGHTS MEDICAL CENTER
KOPP WHITEFIELD E
The o/p wanted is
HERSH STWEART P.
Thank you.
How To Audit For Unused Login Ids???
Dec 9, 2002I would like to verify that we do not have any unused login ids. I know I can turn on auditing for successful login attempta and either manually check the logs or write some type of findstring code to capture successful login attempts and then for each success, remove the id from a master list, but I was wondering if there may be a simpler approach. Any thoughts?
Thanks, Dave
Updates And Unused Fields
Aug 15, 2005I have a 3rd party developed app which has over 42000 data fields in 1200 tables. It is a heavy transaction type app. Of these fields over 10,000 are null for every record. SOme of their homegrown references these fields (and this can be removed), and in many instances many of the fields are not referenced at all. My opinion is that these extra fields are adding a heavy overhead to updates, inserts etc to the server. Any disagreements?
View 2 Replies View RelatedHELP! How To Reduce The Unused Spaces ..
Aug 9, 2004HI all,
I have a database that allocated:
Data: 7300 MB
Log: 2000 MB
But only used
Data: 5500MB
Log: 50MB
How can I free the unused space in the transaction log because
the database is getting too big.
Thank you for your suggestion.
Get All Unused Numbers In Range
Jul 20, 2005What I am trying to do is get all of the records in a table that are out ofsequence so I know which account numbers I can reuse. I have a rangeof account numbers from 50100 to 70100. I need to know which accountnumbers are not stored in the table (not currently used) so I can use.For instance say I have the following data in table:Account Name--------- --------50100 Test150105 Test250106 Test4......I should see the results:50101501025010350104because 50101-50104 are available account numbers since not currently intable.Thanks.
View 2 Replies View RelatedIdentifying Unused Tables
Jul 20, 2005I have inherited support for a database with many used tables. Thereisn't any documentation on what is used or dead. I was hoping to runtraces and capture the objectid but the property doesn't work thatway.Any good ideas would be appreciated.
View 3 Replies View Related13GB Of Unused Memory
Jul 22, 2007I'm working on an app that contains a series of 100 very compute-intensive queries that altogether take 30 minutes on my laptop machine. The app may be atypical in that there is just this series of expensive queries, and not lots of simultaneous simple queries.
To speed up testing, I bought a Dell PE2900 Quad-Core Xeon with 16GB of RAM and four 15000rpm hard drives. It runs 64-bit Windows 2003 server and 64-bit SQL Server 2005 dev edition.
Unfortunately performance is worse than on my laptop. Task manager reports (almost constant) 13GB of available physical memory, while processor usage hovers around 30%. The page file usage is constant at 3 GB.
Where do I start trouble-shooting this problem?
How To Prune Old Or Unused Reports?
Mar 14, 2008Is there a way to see the the last date a report was accessed? I'm wanting to clean up our report server and get rid of old or unused reports.
View 1 Replies View RelatedUnused Connection Managers
Sep 7, 2007Hi,
I am unsure if i am managing my connections in the correct way... but as it stands we have them in a .dtsConfig file that each package references in order to get the relevant details..
This is working fine, except that in total our ETL project uses 6 different connections, and none of the packages utilise all 6.
This leads to an error message upon opening up EVERY package stating that there are connection strings in the .dtsConfig that are unused, yeah, thats the idea!
The way i have gotten around this, is to just include all 6 connection managers in every package, but my question is, is there a better way to achieve this same result??
are we going about this in a bit of a cackhanded way?
any incite would be useful..
Best Regards
Chris
Deleting Old Unused Database Issue
Nov 7, 2007I have an old DATABASE that is not more in use , so I want to drop that database.
But before that I want to check that is there any application running on it or not.
can any one suggest me how to do this?
thanks
Identify Unused Indexes On Database
May 12, 2015There are too many indexes built on DB. As per the naming convention it seems the indexes are built as per the suggestions provided from execution plan. I presume most of the indexes are used only once in a month for the reports but are hampering the performance of daily running queries. These are also occupying a lot of space.
To confirm on this I have used the below query to know & identify the unused indexes. I have recorded the counters before and after the huge operations and I observed NO CHANGE in any of the values.
What the below values exactly indicate and when do they change? Is it good to delete the indexes having low USER_SEEKS, USER_SCANS, USER_LOOKUPS?
Query:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
[Code] .....
How To Identify Unused DB Objects For Cleanup
Aug 28, 2013I need to setup a Server Audit in sql 2012 standard edition. The goal is to identify all procs, views, tables, functions that are not being used anymore. What is the best way to setup a server audit to accomplish this? What events need to be captured at a minimum so I can delete all the unused crap in this database. This is a smaller, growing company.
View 2 Replies View RelatedSQL Query Return Unused Values
Oct 17, 2006I have a table. In the table I have different types of mailingaddresses. The addresses are linked by and ID. ex,addresses per ID 1ID address1 add21 add31 add4distinct Addresses in tableID address1 add21 add31 add4What I am trying to figure out is if this is possible. Is it possibleto figure out some how with a query that in the above example ID 1is missing add1. I am trying to populate a dropdown list in my codewith just the addresses that are left from the distinct list. I trieddoing an intersect something like this but that did not seem to work.SELECT typeFROM address_demoINTERSECTSELECT typeFROM address_demo AS address_demo_1WHERE progid = '12954892'I am wondering if I am moving in the right direction or if this is evenpossible......Thanks
View 1 Replies View RelatedSp_spaceused Reports Over 40 GB Unused For A Table
Jul 20, 2005I 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