January 18, 2010 at 4:17 am
Hi,
I executed the below statement to determine the table size.
This is what i got as output.
sp_spaceused 'SAMPLE'
namerowsreserveddataindex_sizeunused
SAMPLE32000 840 KB832 KB8 KB0 KB
How to calculate the total size of a table? What components can i sum up and say this is the
exact size of the table.
Why i am asking this is because, i want to calculate the size of the logfile(.LDF)?
Scenario,There is a table with 16 crore data and we are increasing the length of the column from
char(5) to char(10). While doing the increase, the before and after image is being logged in .LDf file. Right?
We are running the ALTER TABLE script but at it is running short of free space in disk drive and the operation is falling....
If we can know how much actual/exact amount space occupied in the .LDF file, it would help us!
I can go with SELECT INTO but the script is already there and i have to go with it!
Example scenario, would be great.
Thanks in Advance.
January 19, 2010 at 9:10 am
Here's a little script I found along the way to caculate table size:
Must be ran in Steps:
--Step 1
USE hrs_backup
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
--Step 2
alter table #temp add CreationDate datetime
--Step 3
update b set creationDate = a.crdate
FROM #Temp b
inner join sysobjects a on b.table_name = a.[name]
--Step 4
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size,
a.creationDate
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size, a.creationDate
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
--Step 5
DROP TABLE #temp
February 20, 2010 at 12:29 am
Thanks! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply