[AccessD] Table size

jwcolby jwcolby at colbyconsulting.com
Wed Sep 30 21:47:32 CDT 2009


Well, what the client told me is that the be is approaching 2 gigs and he needed to split it.  So he 
was trying to discover which tables had how much stuff so he could decide what to put where.

I basically told him that there are no tools that I know of but I would ask the experts.  ;)

I also told him to find the tables with the most records for starters, but also tables with lots of 
heavily used memo fields.  Then just export them to a new BE and see how big the new be is.  That's 
what I did when I was facing something similar.

John W. Colby
www.ColbyConsulting.com


Max Wanadoo wrote:
> Yes, but (from memory) what JC actually asked for was TABLE size.
> 
> Bit more problematical, eh?
> 
> Now, does he mean ACTUAL size, ie bytes taken or  DECLARED size?
> 
> Max
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jack and Pat
> Sent: 01 October 2009 01:30
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Table size
> 
> Max,
> 
> Totally agree on the need for compact/repair to get  meaningful info. There
> could be all kinds of  junk from a variety of tests etc that would skew the
> "size"  upwards.
> 
> Jack
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
> Sent: Wednesday, September 30, 2009 8:21 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Table size
> 
> Also, when I ran my version it came up with > 4Gb but disk space is 1.3 Gb
> so counting the field Size is not very accurate because clearly MS does
> sparse saving of actual data and not declared field sizes.  So, one option
> would be 1) do you want maximum possible data size assuming a memo field of
> 64Kb, or 2) actual data stored working with len() function for memo fields
> etc.
> 
> Loads of options and ways of looking.  Probably the best way of looking
> overall at it is to compact/repair and take the disk size.  Even then it is
> 1) block size or 2) actual byte size..
> 
> Ooh, my head...
> 
> Max
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: 01 October 2009 01:06
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Table size
> 
> ROTFL.  Don't worry about it Max.  If it was easy MS would hand it off to
> the same summer interns 
> that design North Wind DB and they would screw it up.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Max Wanadoo wrote:
>> Sometime, you guys are just impossible...
>>
>> Max
>>
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
>> Sent: 30 September 2009 23:36
>> To: 'Access Developers discussion and problem solving'
>> Subject: Re: [AccessD] Table size
>>
>>
>>   Below is some code that may come a bit closer, but even so field size
>> measures characters, not storage space.  Plus your not taking into account
>> variable length fields, any of the database overhead (page and DB), or
>> indexes associated with a table.
>>
>>   So even at best, all this will be off by quite a bit.
>>
>> Jim.
>>
>> Public Sub showSizes()
>> Dim sql As String
>> Dim td As TableDef
>> Dim recordSize As Long
>> Dim numRecords As Long
>> Dim inMemos As Long
>> Dim CurrentDb As Database
>>
>> Set CurrentDb = gTL1DB
>>
>>                 ' Create a temporary table for results:
>>
>>                         ' delete the table first, in case it's still there
>> On Error Resume Next
>> CurrentDb.Execute "DROP TABLE T___tmp"
>> On Error GoTo 0
>>                 
>> sql = "CREATE TABLE T___tmp(name TEXT (100), size INTEGER,"
>> sql = sql & " recordSize INTEGER, numRecords INTEGER, inMemos INTEGER)"
>> CurrentDb.Execute sql
>>
>> Dim rs As Recordset
>>
>> Set rs = CurrentDb.OpenRecordset("T___tmp")
>>             
>>             ' for each table, add a record in T___tmp with its size info.
>>
>> For Each td In CurrentDb.TableDefs
>>     recordSize = 0
>>     numRecords = 0
>>     inMemos = 0
>>
>>     rs.AddNew
>>     rs!name = td.name
>>     rs!size = sizeEstimate(td.name, recordSize, numRecords, inMemos)
>>     rs!recordSize = recordSize
>>     rs!numRecords = numRecords
>>     rs!inMemos = inMemos
>>     rs.Update
>>     Next td
>> rs.Close
>>
>>                     ' print table size info in decreasing order of size
>> Set rs = CurrentDb.OpenRecordset("SELECT * FROM T___tmp ORDER BY size
> DESC")
>> Do Until rs.EOF
>>     If rs!size > 0 Then
>>         Debug.Print rs!name; Tab(20); rs!size; Tab(30); " = "; rs!inMemos;
>>         Debug.Print Tab(45); "+ ("; rs!recordSize; Tab(55); " * ";
>> rs!numRecords; ")"
>>     Else
>>         Exit Do
>>         End If
>>     rs.MoveNext
>>     Loop
>> rs.Close
>>
>> End Sub
>>
>> Public Function sizeEstimate(tableName As String, recordSize As Long,
>> numRecords As Long, inMemos As Long) As Long
>> Dim rs As Recordset
>> Dim fld As field
>> Dim msg As Integer
>> Dim CurrentDb As Database
>> Dim colMemos As New Collection
>>
>>
>> Set CurrentDb = gTL1DB
>>
>> On Error Resume Next
>> Set rs = CurrentDb.OpenRecordset(tableName)
>> If Err Then
>>     sizeEstimate = 0
>>     Exit Function
>>     End If
>> If rs.EOF Then
>>     sizeEstimate = 0
>>     Exit Function
>>     End If
>> rs.MoveLast
>> numRecords = rs.RecordCount
>> 'Debug.Print
>> 'Debug.Print tableName
>>                 ' get the size of the fixed-size fields
>> For Each fld In rs.Fields
>>     recordSize = recordSize + fld.size
>>     If fld.Type = dbMemo Or fld.Type = dbLongBinary Then
>>         colMemos.Add fld.name
>>         End If
>> '    Debug.Print fld.name; " size "; fld.size
>> Next fld
>>
>> sizeEstimate = (recordSize * numRecords) 'Bytes
>>
>> Dim ff As Variant
>>                     ' add up all the memo and long binary fields in
>>                     ' all the records.
>> If colMemos.count > 0 Then
>>     rs.MoveFirst
>>     Do Until rs.EOF
>>         For Each ff In colMemos
>>             inMemos = inMemos + rs.Fields(ff).FieldSize
>>             Next ff
>>         rs.MoveNext
>>         Loop
>>     End If
>>
>> sizeEstimate = sizeEstimate + inMemos
>>
>> 'If SizeEstimate > 1073741824 Then '1073741824 Bytes per GigaByte
>> '    msg = MsgBox("This view may still be larger than 1 GB.  Do you wish
> to
>> continue?", vbOKCancel + vbCritical, "View Size Warning")
>> '    If msg = 2 Then GoTo SizeEstimate_Exit
>> 'End If
>>
>> sizeEstimate_Exit:
>> rs.Close
>> End Function
>>
>>  
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
>> Sent: Wednesday, September 30, 2009 6:13 PM
>> To: 'Access Developers discussion and problem solving'
>> Subject: Re: [AccessD] Table size
>>
>> You could try something  like this (below).
>> Knowing how many records you deal with, I have made the variable DOUBLES
>>
>> Not entirely sure it is accurate as I have only just written it and never
>> tested it in real life, but it may help.
>>
>> Max
>>
>> Sub sGetTableSizes()
>>     Dim dbs As DAO.Database, tbl As DAO.TableDef
>>     Dim sql As String, fld As Field, dblTotSize As Double
>>     Dim dblFldSize As Double, dblRecCount As Double, dblTableSize As
> Double
>>     Set dbs = CurrentDb
>>     For Each tbl In CurrentDb.TableDefs
>>       If Not Left(tbl.Name, 1) = "~" And Not Left(tbl.Name, 4) = "Msys"
> Then
>>         dblRecCount = DCount("*", tbl.Name)
>>         dblFldSize = 0
>>         For Each fld In tbl.Fields
>>             dblFldSize = dblFldSize + fld.Size
>>         Next fld
>>         dblTableSize = dblFldSize * dblRecCount
>>         Debug.Print tbl.Name, dblTableSize
>>       End If
>>       dblTotSize = dblTotSize + dblTableSize
>>     Next tbl
>>     MsgBox "Total Size: " & Format(dblTotSize, "##,##")
>> exithere:
>>     Set dbs = Nothing: Set tbl = Nothing
>>     Exit Sub
>> End Sub
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
>> Sent: 30 September 2009 20:21
>> To: Access Developers discussion and problem solving
>> Subject: [AccessD] Table size
>>
>> Is there any direct way to obtain the total storage used for each table.
> A
>> client is approaching 
>> the 2 gb limit and needs to split the BE.
>>



More information about the AccessD mailing list