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?
TechGuy_007Asked:
Who is Participating?
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.

Chris MangusDatabase AdministratorCommented:
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

0
AnujSQL Server DBACommented:
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>).
0
lcohanDatabase AnalystCommented:
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
0

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:
Wow...I gave you exactly what you asked for, a query to show the size of every table in the database...  :(
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.