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.
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
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
I am using a select statement to obtain a result set back with aggregateddata. The problem is that I am seeing column data with 11 to 13 digitsafter the decimal point. I tried using the STR function, but then the OrderBy clause does not sort properly because there are negative numbers in theaggregated data... I tried using Round, but that does no good either - itstill ends up displaying too many digits after the decimal point. Right nowI'm just using Query Analyzer to display the data, so I can live with it fornow. But, in the future, my app will be getting a result set back and Iwould prefer not to have to go through each row and do a round on it fromthe program. Does anyone know how to solve this problem?Thanks for any help,Bob
I am trying to import about 3300 lines of data from excel. Some of the columns in excel have negative, positive and even 0 as numbers.
I have matched those columns in the DB and have tried all the different data types that i thought should work, but on import, all negative and 0 numbers get dropped.
I have played with the excel formatting as well, but nothing seems to work.
Hello, I'm having problems with a column of numeric string data coming from a tab delimited CSV file. When a number in the column is negative, it is expressed this way; 1,240.52-
The negative symbol occurs at the end of the numeric string. The destination column is in a SQL Server 2005 table. I've tried to change the data type of the table column, and three or four different data types in a data conversion task, but nothing has worked so far to bring the data over.
Hi, I am having chart which has positive and negative numbers.I need dark line(gridline) at 0.00th location.So that it differentiates between positive and negative levels.So please help me out in solving this asap.
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
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.
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
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
How is it that the value returned by sp_spaceused is larger than the actual database size and unallocated space be negative? For example sp_spaceused retruns the following for one of our databases.
database_size = 52022.31 MB unallocated space = -16462.47 MB
That does not make much sense to me how the reserved size and even data size can be larger than the database_size.
This database was 85 gigs yesterday but we were running extremely short on disk space so I was forced to do a shrink. I did update stats for the database as well.
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
