February 14, 2013 at 9:10 am
I have a problem to solve. I have a bunch of tables across few databases.
I have to group the records by date each day for a month. I have to find the size of all records for each day for a month on all tables in question.
I have put sample sql script for you all to understand my problem and provide me some ideas.
From the sample, you can see there are 5 records if i group it by UserID, i want to know the record size in MB. In the sample I have used the UserID in place of a datetime.
CREATE TABLE dbo.ABC (AuditABCID BIGINT, UserID BIGINT, OrderID BIGINT, LastUpdatedBy VARCHAR(256), LastUpdatedDateTime DATETIME, AuditDateTime DATETIME)
INSERT INTO dbo.ABC
SELECT 1, 10, 100, 'User1', '2009-07-16 10:57:42.523', '2009-08-16 10:57:42.523'
UNION ALL
SELECT 2, 11, 101, 'User2', '2010-07-16 10:57:42.523', '2010-08-16 10:57:42.523'
UNION ALL
SELECT 3, 12, 102, 'User3', '2011-07-16 10:57:42.523', '2011-08-16 10:57:42.523'
UNION ALL
SELECT 4, 13, 103, 'User4', '2012-07-16 10:57:42.523', '2012-08-16 10:57:42.523'
UNION ALL
SELECT 5, 14, 104, 'User5', '2013-07-16 10:57:42.523', '2013-08-16 10:57:42.523'
UNION ALL
SELECT 6, 14, 105, 'User5', '2013-08-16 10:57:42.523', '2013-09-16 10:57:42.523'
SELECT UserID, SIZE(*) AS RecordSizeInMB FROM dbo.ABC GROUP BY UserID
February 14, 2013 at 9:45 am
I think about the only thing you would be able to do here is to find the average size of a row in the table and then multiply that by the count of rows for each grouping. I don't think there is any way possible to get it exact. What really is the point of calculating storage size like this?
You can find the physical stats of a table using the dmv sys.dm_db_index_physical_stats.
http://msdn.microsoft.com/en-us/library/ms188917.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 14, 2013 at 9:54 am
In our organization there is a team that gets our audit data each day and uses it to evaluate any security related issues. The security team needs it. They wanted to know the history of data in our audit tables by each day and every table for whatever period we have it available.
They will further do some estimates in regards to pricing, security, etc.,
I am trying to get data out of the audit tables by date and size of it by each day.
February 14, 2013 at 10:49 am
another possibility that seems fast and easy would be to select one days data into a new table, do sp_spaceused , and then drop that table.
then you could get the exact sizes of the data;
soemthing like this as a prototype:
select *
into temp
from SomeTable
Where SomeDatecolumn >= DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
AND SomeDatecolumn <= dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
exec sp_spaceused temp
drop table temp
Lowell
February 14, 2013 at 10:55 am
Lowell,
Yes thats my alternate plan. I am also thinking about taking a chunk of data (say 1 month) and then put it in the temp table. Use space used to find the size for a month. Then group it by, use the group by counts to find the percentage of records per day to the size of a month.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply