Avatar of TechGuy_007
TechGuy_007
Flag for United States of America asked on

Amicus Database

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?
Microsoft SQL ServerDatabasesMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Chris Mangus

8/22/2022 - Mon
Chris Mangus

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 s.id = OBJECT_ID(a.Table_Name)) AS Row_Count
, Total_Space_Used_MB 
Into #T1
FROM (SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS Table_Name
, 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 i.id = o.id 
   AND ((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U') 
   AND (@include_system_tables = 1 OR OBJECTPROPERTY(i.id, 'IsMSShipped') = 0)
   WHERE indid IN (0, 1, 255) --AND Total_Space_Used_MB = 0
   GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))) as a
--ORDER BY Total_Space_Used_MB DESC
ORDER BY Row_Count DESC

-- Get the column counts
Select myInner.Table_Name, Count(sc.id) As Number_Columns
Into #T2
From dbo.syscolumns sc
Inner Join 
(SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS Table_Name, i.id
 FROM sysindexes i (NOLOCK)
 INNER JOIN sysobjects o (NOLOCK) ON i.id = o.id AND ((o.type IN ('U', 'S')) OR o.type = 'U') 
 AND ((OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
 WHERE indid IN (0, 1, 255)) myInner On sc.id = myInner.id
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

Anuj

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>).
ASKER CERTIFIED SOLUTION
lcohan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Chris Mangus

Wow...I gave you exactly what you asked for, a query to show the size of every table in the database...  :(
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes