Amicus Database

Posted on 2011-10-05
Last Modified: 2012-05-12
I have a six user law office that is running Amicus Attorney 2008 Premium.  The office has been running under the Amicus platform for many years.

We attempted to migrate to Amicus Attorney 2011 Premium in June, however we were told that there was in issue in converting the database as it was 22GB.  Amicus is using SQL Server Express 2005 as it's backend.

However, since June the database has grown to over 28GB.

Any ideas on why the database would have grown so much larger so quickly as well as to this size overall?

Is there any way to run a report showing the size of each table within the database?
Question by:TechGuy_007
    LVL 17

    Expert Comment

    I've attached a query i use to see the size of each table in a database.
    Declare @include_system_tables bit
    Set @include_system_tables = 0
    SELECT Table_Name
    , (SELECT rowcnt FROM sysindexes s WHERE s.indid < 2 AND = OBJECT_ID(a.Table_Name)) AS Row_Count
    , Total_Space_Used_MB 
    Into #T1
    , CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) 
    * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024)/1024)) AS Total_Space_Used_MB 
       FROM sysindexes i (NOLOCK)
       INNER JOIN sysobjects o (NOLOCK) ON = 
       AND ((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U') 
       AND (@include_system_tables = 1 OR OBJECTPROPERTY(, 'IsMSShipped') = 0)
       WHERE indid IN (0, 1, 255) --AND Total_Space_Used_MB = 0
    --ORDER BY Total_Space_Used_MB DESC
    ORDER BY Row_Count DESC
    -- Get the column counts
    Select myInner.Table_Name, Count( As Number_Columns
    Into #T2
    From dbo.syscolumns sc
    Inner Join 
     FROM sysindexes i (NOLOCK)
     INNER JOIN sysobjects o (NOLOCK) ON = AND ((o.type IN ('U', 'S')) OR o.type = 'U') 
     AND ((OBJECTPROPERTY(, 'IsMSShipped') = 0))
     WHERE indid IN (0, 1, 255)) myInner On =
    Group By myInner.Table_Name
    Select #T1.*, #T2.Number_Columns
    From #T1
    Inner Join #T2 On #T1.Table_Name = #T2.Table_Name
    Drop Table #T1
    Drop Table #T2

    Open in new window

    LVL 15

    Expert Comment

    Also Check your Log files may be you need to shrink it. To shrink the log file backup transaction log command and then DBCC SHRINFILE('LogFileName', <required Size>).
    LVL 39

    Accepted Solution

    To check T-log space you cna use the following command:

    dbcc sqlperf(LOGSPACE )

    To check space used/available in all DB files for one DB use below:

    use database_name
    SELECT name,size/128.0 as size ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
    FROM sys.database_files;

    Before you can shrink a database T-log you must issue a Log backup command like below:

    backup log database_name with truncate_only
    LVL 17

    Expert Comment

    Wow...I gave you exactly what you asked for, a query to show the size of every table in the database...  :(

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Read about achieving the basic levels of HRIS security in the workplace.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now