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?

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

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

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

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

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 17

Chris MangusDatabase AdministratorCommented: 2011-10-05

LVL 15

AnujSQL Server DBACommented: 2011-10-07

LVL 40

lcohanDatabase AnalystCommented: 2011-10-07

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Chris MangusDatabase AdministratorCommented: 2011-11-08

Amicus Database

Who is Participating?

Already a member? Login.