jwcolby
jwcolby at colbyconsulting.com
Thu Oct 1 07:53:56 CDT 2009
Jim,
> In fact I'm left wondering why someone hasn't done that already...
Because we all work for a living and no one wants to pay us to develop that?
;)
But you are correct, it wouldn't be that tough to do.
John W. Colby
www.ColbyConsulting.com
Jim Dettman wrote:
> Max,
>
> <<Oh no, I didn't mean it that way. What I meant was the guys (and gals) on
> this site are amazing with their knowledge and skills. Impossible to keep
> up with. I think I have done something "clever" and the say "yeah, watch
> this bud".>>
>
> Been there many times. Sometime I feel like I live in the stone age when
> I see a lot of the stuff on EE.
>
> <<Hope I didn't offend anybody. Emails sometimes can be difficult.>>
>
> Not here and I hope the reverse is true as well. I really was just trying
> to point out that it's a thornier problem then it first appears. To really
> do it right, you'd need to scan every page in the DB assigned to the table
> or do a compact and then just add up all the pages assigned to the table.
> And while the data page layouts for Jet 3.0 and 4.0 are pretty well known,
> what's known about the index pages and page usage maps are fuzzy at best.
>
> And then there are details such as page padding (leaving free space for
> new data); does JET do it or not? So even scanning all the pages and
> getting a very accurate storage count of the data still might not tell you
> how much space a table would occupy. If the fill factor was 50%, you could
> be off by quite a bit. The best approach I guess would be a compact, then
> just count the pages assigned to the tables and indexes.
>
> Without going through all that, the only realistic approach I've found is
> to import a single table into a fresh MDB and then check the size. That is
> accurate, but it's a lot of manual work. And now that I say that, I think
> between all of us we could come up with some code to do that automatically
> fairly quickly for a given DB. In fact I'll see if I can squeeze out some
> time latter today to do that. Would not be all that hard; read the
> tabledefs, create a scratch DB, transfer the table, get the on disk size of
> the scratch DB, save info into a table, delete scratch DB, and repeat till
> done.
>
> In fact I'm left wondering why someone hasn't done that already...
>
> Jim.
>
>
>
> -----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:13 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Table size
>
> Oh no, I didn't mean it that way. What I meant was the guys (and gals) on
> this site are amazing with their knowledge and skills. Impossible to keep
> up with. I think I have done something "clever" and the say "yeah, watch
> this bud".
>
> Hope I didn't offend anybody. Emails sometimes can be difficult.
>
> Anyway, John. Hope you got something out of it all - LOL.
>
>
> But, hey, tell you what, this might develop into a useful item. I never
> even considered Indexes. What about Reference overheads. Tons of stuff.
>
>
> 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.
>>