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
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 bitSet @include_system_tables = 0SELECT 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 #T1FROM (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 DESCORDER BY Row_Count DESC-- Get the column countsSelect myInner.Table_Name, Count(sc.id) As Number_ColumnsInto #T2From dbo.syscolumns scInner 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.idGroup By myInner.Table_NameSelect #T1.*, #T2.Number_ColumnsFrom #T1Inner Join #T2 On #T1.Table_Name = #T2.Table_NameDrop Table #T1Drop Table #T2
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>).
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.
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!
Open in new window