Hi, i use this script that show me the size of each table and do the sum of all the table size.
SELECT X.[name], REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows], REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved], REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data], REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size], REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused] FROM (SELECT CAST(object_name(id) AS varchar(50)) AS [name], SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows], SUM(CONVERT(bigint, reserved)) * 8 AS reserved, SUM(CONVERT(bigint, dpages)) * 8 AS data, SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size, SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused FROM sysindexes WITH (NOLOCK) WHERE sysindexes.indid IN (0, 1, 255) AND sysindexes.id > 100 AND object_name(sysindexes.id) <> 'dtproperties' GROUP BY sysindexes.id WITH ROLLUP) AS X ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup. example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
The largest table in our database eats up above 4G . we do "sp_spaceused" for this table.The length of all columns of this table ( just int, char, varchar, money ,numeric fields types) is about 200 bytes, and the table has around 1,300,000 rows, but the reserved spaced for this table is 4,800,000kb and the data space is around 4,600,00kb.
How can average each row take 3.7kb ( the total size of all columns just 200 bytes)? Any other things I need check? Any one can give any suggestion what cause this problem? or it is normal?
I am wondering if there is the limitation of maximum table size in SQL 6.5. I have a table with 2.6GB and 12,000,000 rows in SQL 6.5 database. Is this a problem?
Is there a practical size limit, in MB's, of a table in SQL Server 6.5?
Is there a size, that once exceeded, degrades performance signifigantly?
I am speaking of raw megabytes. The table in question will consist of only 3 int columns but has the possiblity of becoming VERY LARGE (+1,000,000 rows). I am still in the design phase and can change my strategy if this will prove to be a problem.
I am trying to solve this problem for quite some time.. I was wondering if I can get some help..
These questions are all abt. MSSQL 6.5
1. Is there a limit on the size of the table ? 2. Does it make sense to have more tables if the size of the row size is more that the limit set by 6.5 or i should let have more rows in a different table with duplicate entries for a particular field. 3. What is the number of rows before the performance of a query starts getting affected..
Is there a maximum or optimum number of rows I should have in a table so that I can have fastest search queries. I am a novice programmer just developed something for my work place. The database has a table created by converting data from excel spreadsheets. There were 24 spreadsheets for 12 months each having approximately 500 rows. Designed this way the table will have approximately 24 * 500 = 12000 records. Should I consider redesigning the database to make searches faster
Hi. I am trying to get a row count of each row of each table in the database. Is that possible? Using a SP or UDFS? I dont want the column size of each table but the total datasize of each row.So for example if I have 5 rows each in 3 tables I need a query that will return 15 rows with the size of each row(size of all coumn data summed together). Thanks.
How do I find the average size of a row in a table? I need to calculate a row size in a number of tables, then sum those to find the average size of one record ( a hotel guest in this case), which includes entries in a dozen tables.
I have inherited a system where several of the tables have WAY TOO MANY fields (over 255) and performance, well, you know.
I've been thinking about breaking the tables out into multiple tables and I know a view linking all these tables together can provide me with backward compatibility.
I'm wondering if a view will truely be a greater gain than leaving it alone while we redesign. hhhmmmm
Hi, I am getting this error when I try to run a script to create a table. The error reads as below
The total row size (12488) for table exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.
Can somebody tell me what I have done wrong? thanks zoey
Anybody know any method/script I can use to see a list of table name and table size (in bytes, not rows) within a database? I know I can see the size of any particular table in "Table info" tab or using "sp_spaceused" procedure. But I can't generate a report with a list of table name and table size using these methods. Any idea? Thanks.
Hi all, Is it possible to get the name and size of each index in a table? Please let me know. In sql 7, we could do this using EM but, in sql 2000, I'm not sure how to do this.
How can I calculate the combined table size for the following: The Destination and the ShipmentWages tables on a database of a courier service have approximately 10,000 rows and 15,000 rows, respectively. The average row size of the Destination table is 4KB and that of the ShipmentWages table is 3KB.
If I have a database with a list of tables is there a way to calculate the size of each table individually and then calculate the size all the tables. If you have 1 table with say 10 rows and 3 columns and the width of the columns are of variable length you could do something like