I was trying to explain to my boss that the SQL database we’re using for the intranet was somewhere in the 400 MB range when he asked ‘What’s taking up all the space?’ \u00a0I was a little taken aback, I mean databases collect data and that takes up space right? \u00a0Well it got me thinking what the hell is taking up all the space. \u00a0I found this great query that tells all:)<\/p>\n
<\/pre>\r\nSELECT\r\n t.NAME AS TableName,\r\n i.name AS indexName,\r\n SUM(p.rows) AS RowCounts,\r\n SUM(a.total_pages) AS TotalPages,\r\n SUM(a.used_pages) AS UsedPages,\r\n SUM(a.data_pages) AS DataPages,\r\n (SUM(a.total_pages) * 8) \/ 1024 AS TotalSpaceMB,\r\n (SUM(a.used_pages) * 8) \/ 1024 AS UsedSpaceMB,\r\n (SUM(a.data_pages) * 8) \/ 1024 AS DataSpaceMB\r\nFROM\r\n sys.tables t\r\nINNER JOIN\r\n sys.indexes i ON t.OBJECT_ID = i.object_id\r\nINNER JOIN\r\n sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id\r\nINNER JOIN\r\n sys.allocation_units a ON p.partition_id = a.container_id\r\nWHERE\r\n t.NAME NOT LIKE 'dt%' AND\r\n i.OBJECT_ID > 255 AND\r\n i.index_id <= 1\r\nGROUP BY\r\n t.NAME, i.object_id, i.index_id, i.name\r\nORDER BY\r\n OBJECT_NAME(i.object_id)\r\n<pre>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"I was trying to explain to my boss that the SQL database we’re using for the intranet was somewhere in the 400 MB range when he asked ‘What’s taking up all the space?’ \u00a0I was a little taken aback, I mean databases collect data and that takes up space right? \u00a0Well it got me thinking […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[10],"tags":[],"class_list":["post-106","post","type-post","status-publish","format-standard","hentry","category-mssql","author-aron"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p4bBkH-1I","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/asberry.org\/blog_tech\/index.php?rest_route=\/wp\/v2\/posts\/106","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/asberry.org\/blog_tech\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/asberry.org\/blog_tech\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/asberry.org\/blog_tech\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/asberry.org\/blog_tech\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=106"}],"version-history":[{"count":6,"href":"https:\/\/asberry.org\/blog_tech\/index.php?rest_route=\/wp\/v2\/posts\/106\/revisions"}],"predecessor-version":[{"id":111,"href":"https:\/\/asberry.org\/blog_tech\/index.php?rest_route=\/wp\/v2\/posts\/106\/revisions\/111"}],"wp:attachment":[{"href":"https:\/\/asberry.org\/blog_tech\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=106"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/asberry.org\/blog_tech\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=106"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/asberry.org\/blog_tech\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}