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.
>>