{"id":8615,"date":"2014-09-04T09:11:19","date_gmt":"2014-09-04T09:11:19","guid":{"rendered":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/?p=8615"},"modified":"2014-09-04T09:11:19","modified_gmt":"2014-09-04T09:11:19","slug":"get-size-of-all-tables-in-crm-database","status":"publish","type":"post","link":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/","title":{"rendered":"Get Size of all Tables in CRM Database"},"content":{"rendered":"

For any database driven application, it is very important that the database must be optimized to the best so as to deliver higher performance. As the data volume in database increases, load on the system also increases which affects the performance on the system which in turn affects the client\u2019s business. For business application like Sage CRM, this must be paid attention on.
\nNew Stuff:<\/strong><\/em><\/span> Migration of Act! Groups in Sage CRM<\/a>
\nRecently, we got a support email from one of our client saying that CRM is slow and users are not able to work on it. We quickly made some preliminary checks and everything seemed proper, no SQL error, nothing suspicious in System logs\u2026.Huh..!!! But we noticed that drive of CRM instance directory was almost full. We investigated some folders which generally consume more space of the drive and we got to know that database size was considerably large compared to the volume of data. So decided to check size of each table.
\nNow, how to get size of each table? Below is the SQL Script to get size of each table in database.
\nCREATE PROCEDURE GetAllTableSizes<\/em><\/span>
\nAS<\/em><\/span>
\n\/*<\/em><\/span>
\n\u00a0\u00a0 Obtains space used data for ALL user tables in the database<\/em><\/span>
\n*\/<\/em><\/span>
\nDECLARE @TableName VARCHAR(100)\u00a0\u00a0 –For storing values in the cursor<\/em><\/span>
\n\u00a0<\/em><\/span>
\n–Cursor to get the name of all user tables from the sysobjects listing<\/em><\/span>
\nDECLARE tableCursor CURSOR<\/em><\/span>
\nFOR<\/em><\/span>
\nselect [name]<\/em><\/span>
\nfrom dbo.sysobjects<\/em><\/span>
\nwhere OBJECTPROPERTY(id, N’IsUserTable’) = 1<\/em><\/span>
\nFOR READ ONLY<\/em><\/span>
\n\u00a0<\/em><\/span>
\n–A procedure level temp table to store the results<\/em><\/span>
\nCREATE TABLE #TempTable<\/em><\/span>
\n(<\/em><\/span>
\n\u00a0\u00a0 tableName varchar(100),<\/em><\/span>
\n\u00a0\u00a0 numberofRows varchar(100),<\/em><\/span>
\n\u00a0\u00a0 reservedSize varchar(50),<\/em><\/span>
\n\u00a0\u00a0 dataSize varchar(50),<\/em><\/span>
\n\u00a0\u00a0 indexSize varchar(50),<\/em><\/span>
\n\u00a0\u00a0 unusedSize varchar(50)<\/em><\/span>
\n)<\/em><\/span>
\n\u00a0<\/em><\/span>
\n–Open the cursor<\/em><\/span>
\nOPEN tableCursor<\/em><\/span>
\n\u00a0<\/em><\/span>
\n–Get the first table name from the cursor<\/em><\/span>
\nFETCH NEXT FROM tableCursor INTO @TableName<\/em><\/span>
\n\u00a0<\/em><\/span>
\n–Loop until the cursor was not able to fetch<\/em><\/span>
\nWHILE (@@Fetch_Status >= 0)<\/em><\/span>
\nBEGIN<\/em><\/span>
\n\u00a0\u00a0 –Dump the results of the sp_spaceused query to the temp table<\/em><\/span>
\n\u00a0\u00a0 INSERT #TempTable<\/em><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXEC sp_spaceused@TableName<\/em><\/span>
\n\u00a0<\/em><\/span>
\n\u00a0\u00a0 –Get the next table name<\/em><\/span>
\n\u00a0\u00a0 FETCH NEXT FROM tableCursor INTO @TableName<\/em><\/span>
\nEND<\/em><\/span>
\n\u00a0<\/em><\/span>
\n–Get rid of the cursor<\/em><\/span>
\nCLOSE tableCursor<\/em><\/span>
\nDEALLOCATE tableCursor<\/em><\/span>
\n\u00a0<\/em><\/span>
\n–Select all records so we can use the reults<\/em><\/span>
\nSELECT *<\/em><\/span>
\nFROM #TempTable order by tableName<\/em><\/span>
\n\u00a0<\/em><\/span>
\n–Final cleanup!<\/em><\/span>
\nDROP TABLE #TempTable<\/em><\/span>
\n\u00a0<\/em><\/span>
\nGO<\/em><\/span>
\nExecute above sript against CRM database. Executing this procedure will return output as shown below
\n\"Image-1\"<\/a>
\nWhen we executed above procedure against the database, we found that size of TableScriptLogs<\/strong> table was much larger. This table is nothing but the Table Level Scripts logs. Looking at the logs we found an issue at TLS level and surprisingly neither any exception were thrown on the screen nor any screen crash happened but log was getting written each time which resulted into larger data size of table. Truncating this table resolved the issue being faced. We resolved that issue too\u2026:)
\nThis is how we can use above procedure to troubleshoot database level issue.
\nAlso Read:<\/strong><\/em><\/span>
\n1) Avoid scrolling with Collapse Screen Feature in Sage CRM7.2<\/a>
\n2) CRM database auto-backup on SQL express Editions<\/a>
\n3) Sage 100 user mapping with Sage CRM User<\/a>
\n4) Additional Information in Entity Top Content Screens \u2013 Sage CRM v7.2<\/a>
\n5) User specific data on Interactive dashboards<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

For any database driven application, it is very important that the database must be optimized to the best so as to deliver higher performance. As the data volume in database increases, load on the system also increases which affects the performance on the system which in turn affects the client\u2019s business. For business application like\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[102,332,367,380,393,1,423],"tags":[921,1952,2296,2325,2405,2560],"class_list":["post-8615","post","type-post","status-publish","format-standard","hentry","category-database-sage-crm","category-sage-crm","category-size-sage-crm","category-sql","category-tables","category-uncategorized","category-view","tag-database","tag-rows","tag-size","tag-sql","tag-tables","tag-view"],"yoast_head":"\nGet Size of all Tables in CRM Database - Sage CRM \u2013 Tips, Tricks and Components<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Get Size of all Tables in CRM Database - Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"og:description\" content=\"For any database driven application, it is very important that the database must be optimized to the best so as to deliver higher performance. As the data volume in database increases, load on the system also increases which affects the performance on the system which in turn affects the client\u2019s business. For business application like\u2026 Read More »\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/\" \/>\n<meta property=\"og:site_name\" content=\"Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"article:published_time\" content=\"2014-09-04T09:11:19+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/09\/Image-1.jpg\" \/>\n<meta name=\"author\" content=\"greysagecrm\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"greysagecrm\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/\",\"name\":\"Get Size of all Tables in CRM Database - Sage CRM \u2013 Tips, Tricks and Components\",\"isPartOf\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/09\/Image-1.jpg\",\"datePublished\":\"2014-09-04T09:11:19+00:00\",\"author\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/#primaryimage\",\"url\":\"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/09\/Image-1.jpg\",\"contentUrl\":\"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/09\/Image-1.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Get Size of all Tables in CRM Database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/\",\"name\":\"Sage CRM \u2013 Tips, Tricks and Components\",\"description\":\"Explore the possibilities with Sage CRM insights through our comprehensive blogs. As a leading Sage partner, Greytrix helps businesses maximize their Sage CRM potential with its rich expertise and immense knowledge. Here, you will find blogs that feature expert advice, tips & tricks, best practices, and comprehensive guides on customizing and configuring Sage CRM for your business. Stay informed with our regular updates and expert insights!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f\",\"name\":\"greysagecrm\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"caption\":\"greysagecrm\"},\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/author\/greysagecrm\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Get Size of all Tables in CRM Database - Sage CRM \u2013 Tips, Tricks and Components","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/","og_locale":"en_US","og_type":"article","og_title":"Get Size of all Tables in CRM Database - Sage CRM \u2013 Tips, Tricks and Components","og_description":"For any database driven application, it is very important that the database must be optimized to the best so as to deliver higher performance. As the data volume in database increases, load on the system also increases which affects the performance on the system which in turn affects the client\u2019s business. For business application like\u2026 Read More »","og_url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/","og_site_name":"Sage CRM \u2013 Tips, Tricks and Components","article_published_time":"2014-09-04T09:11:19+00:00","og_image":[{"url":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/09\/Image-1.jpg","type":"","width":"","height":""}],"author":"greysagecrm","twitter_card":"summary_large_image","twitter_misc":{"Written by":"greysagecrm","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/","url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/","name":"Get Size of all Tables in CRM Database - Sage CRM \u2013 Tips, Tricks and Components","isPartOf":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/#primaryimage"},"image":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/#primaryimage"},"thumbnailUrl":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/09\/Image-1.jpg","datePublished":"2014-09-04T09:11:19+00:00","author":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f"},"breadcrumb":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/#primaryimage","url":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/09\/Image-1.jpg","contentUrl":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/09\/Image-1.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/09\/04\/get-size-of-all-tables-in-crm-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/"},{"@type":"ListItem","position":2,"name":"Get Size of all Tables in CRM Database"}]},{"@type":"WebSite","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website","url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/","name":"Sage CRM \u2013 Tips, Tricks and Components","description":"Explore the possibilities with Sage CRM insights through our comprehensive blogs. As a leading Sage partner, Greytrix helps businesses maximize their Sage CRM potential with its rich expertise and immense knowledge. Here, you will find blogs that feature expert advice, tips & tricks, best practices, and comprehensive guides on customizing and configuring Sage CRM for your business. Stay informed with our regular updates and expert insights!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f","name":"greysagecrm","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","caption":"greysagecrm"},"url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/author\/greysagecrm\/"}]}},"_links":{"self":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts\/8615","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/comments?post=8615"}],"version-history":[{"count":0,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts\/8615\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/media?parent=8615"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/categories?post=8615"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/tags?post=8615"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}