Negative Unused Space

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


ADVERTISEMENT

Sp_spaceused Report Negative Number For Unused

May 20, 2000

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.

Thanks.

View 1 Replies View Related

Unused Space

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

Very Confused - Unused Space

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

Free UNUSED SPACE

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

Sp_spaceused - Too Much Unused Space

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

HELP: Shrinkdatabase Not Freeing Unused Space

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

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 View Related

Release Unused Space Reserved For A Table

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

SQL 2012 :: Reclaim The Unused Table Space?

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

Reclaim Unused Space / Rebuild Table

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

DB Engine :: Release Unused Disk Space

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

Claim Unused Free Space At Table Level?

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

Sp_spaceused Shows Negative Unallocated Space

Mar 1, 2005

Hi All,

When i run sp_spaceused on one of my database, PROD, it gives the following output

database_name: PROD
database_size: 4268.00 MB
unallocated space: -789.82 MB


reserved: 4654920 KB
data: 2929008 KB
index_size: 327272 KB
unused: 1398640 KB

Why is that the unallocated space is negative ?

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

Appreciate your time and help on this.

View 1 Replies View Related

Log File Alert And Negative Free Space!?

Sep 24, 2007

We've got an alert setup on our production database to warn us when the log file(s) exceeds 7 gigs. The Alert is triggering:

"The SQL Server performance counter 'Log File(s) Size (KB)' (instance 'Lexus') of object 'SQLServer:Databases' is now above the threshold of 7000000.00 (the current value is 7057656.00)."

However, according to the file system, the database properties screen files, and properties tabs, the log files combined are at under 5 gigs so this alert should not be going off.

The scary part is, when going to right-click on the database, choosing "tasks" and going to "Shrink > Files", the "Free Space" shows negative numbers for the first log file:

Log 1
Currently Allocated space: 118.69 MB
Available Free Space: -5323.24 MB (-4485%)

Log 2
Currently Allocated space: 4853.13 MB
Available Free Space: 411.20 MB (7%)

Has anybody ever run into this? Should I be worried that there is a bigger issue at hand?

Thanks!

View 20 Replies View Related

How To Replace Empty Space Or White Space In A String In A Stored Procedure

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

Mutilple Space Gets Converted To Single Space In Report Viewer Control

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 €œ&nbsp;€?
2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried &lt;Pre&gt; instead of <pre>)

But in all the cases result is same. The Report Viewer control is showing €œ&nbsp€? 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

Transact SQL :: How To Find Space Available Or Send Space Alerts In Percentage

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

Trans Log-&>space Allocated 27GB, Space Used 100MB

Mar 2, 2005

Hi.. 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?

View 1 Replies View Related

Double Space Replaced With Single Space By Dbms ??!

Jul 20, 2005

This 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 Related

Help Regarding Unused Columns

Aug 23, 2007

Dear 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.

View 2 Replies View Related

Problem With Space Allocated For Transaction Log Space

Dec 5, 2001

Hello,

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.

View 1 Replies View Related

Suppress Multiple Space To Single Space..

Nov 14, 2006

How 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.


View 3 Replies View Related

How To Audit For Unused Login Ids???

Dec 9, 2002

I 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

View 4 Replies View Related

Updates And Unused Fields

Aug 15, 2005

I 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 Related

HELP! How To Reduce The Unused Spaces ..

Aug 9, 2004

HI 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.

View 1 Replies View Related

Get All Unused Numbers In Range

Jul 20, 2005

What 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 Related

Identifying Unused Tables

Jul 20, 2005

I 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 Related

13GB Of Unused Memory

Jul 22, 2007

I'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?

View 1 Replies View Related

How To Prune Old Or Unused Reports?

Mar 14, 2008

Is 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 Related

Unused Connection Managers

Sep 7, 2007

Hi,

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

View 9 Replies View Related

Deleting Old Unused Database Issue

Nov 7, 2007

I 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

View 5 Replies View Related

Identify Unused Indexes On Database

May 12, 2015

There 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] .....

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved