December 29, 2008 at 4:52 pm
Hi all,
I have a large SQL database (About 400gigs) and I'd like a query that will tell me 'where' all that physical space is. I have seen a few that will tell me the size of tables... That doesn't seem to be enough though as I'm sure a large part of it is also indexes, etc. Can anyone point me in the right direction for this?
The end goal is to clean up the DB to bring the size down.
Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
December 29, 2008 at 6:26 pm
Dunno if this will work on 64 bit... but I think you'll like it if it does...
/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used for all user tables at once.
Notes:
1. May be used as a view, stored procedure, or table-valued funtion.
2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.
Revision History:
Rev 00 - 22 Jan 2007 - Jeff Moden
- Initital creation for SQL Server 2000
Rev 01 - 11 Mar 2007 - Jeff Moden
- Add automatic page size determination for future compliance
Rev 02 - 05 Jan 2008 - Jeff Moden
- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name
**********************************************************************************************************************/
--===== Ensure that all row counts, etc is up do snuff
-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should
-- execute the command below prior to retrieving from the view or UDF.
DBCC UPDATEUSAGE(0)
--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more
SELECT DBName = DB_NAME(),
SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
--SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1,
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) <> 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2008 at 10:01 pm
sp_msforeachdb 'dbcc showfilestats'
GO
sp_msforeachdb 'sp_spaceused'
GO
sp_databases
GO
December 30, 2008 at 1:05 am
Had to make a small modification to actually get the different results instead of always the results from the current db.
sp_msforeachdb 'use ? dbcc showfilestats'
GO
sp_msforeachdb 'use ? exec sp_spaceused'
GO
sp_databases
GO
/Kenneth
December 30, 2008 at 11:28 am
Jeff came the closest... his query gave me the sizes of all the tables which was very helpful - however I'd like to be able to also see the size of all the indexes. The other two responses here I appreciate, however I know what size my DB's and their respective files are... I need to know sizes of tables and indexes.
December 30, 2008 at 11:32 am
Assuming you have SP2 installed - right-click on the database, pick reports, and go after the Physical Index STats report.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 30, 2008 at 1:56 pm
Wouldn't that require reporting server to be installed?
December 30, 2008 at 1:58 pm
I don't know about installed - but I could swear I've run them without SSRS running before.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 30, 2008 at 2:00 pm
Actually - let me correct that - it runs on the machine I am on, which has a "default" SQL Express install (no SSRS). So - no, it doesn't require SSRS.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 30, 2008 at 2:02 pm
Yeah unfortunately I don't seem to have that option.
December 30, 2008 at 2:17 pm
It just requires you to have SP2 applied to SQL Server 2005 instance.
MJ
December 30, 2008 at 2:19 pm
MANU (12/30/2008)
It just requires you to have SP2 applied to SQL Server 2005 instance.MJ
*AND* on the machine running SSMS (i.e. the client tools need to be updated too).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 30, 2008 at 2:22 pm
Ahh yes. I don't believe I have it on my box..
Just checked:
Microsoft SQL Server Management Studio9.00.1399.00
That is SP1. My server is on SP2, let me update my client and give that a shot. Thanks folks.
December 30, 2008 at 3:05 pm
Right. Obstacle #2 the database is in 2000(80) compatibility mode and it needs to be in 2005 (90) in order for the report to run. Guess I'll have to do some testing in development to make sure changing that won't break anything.
December 30, 2008 at 4:06 pm
dholland (12/30/2008)
Ahh yes. I don't believe I have it on my box..Just checked:
Microsoft SQL Server Management Studio9.00.1399.00
That is SP1. My server is on SP2, let me update my client and give that a shot. Thanks folks.
that's not even SP1 its RTM, 9.00.2047 is SP1 update it ASAP
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply