Solved

Table and disk usage query

Posted on 2008-10-21
16
1,083 Views
Last Modified: 2008-10-23
I need a query that will generate disk utilisation details for each table in a database (SS 2000) and the below script accomplishes this for the smaller non-production databases. However as soon as i attempt to run it for the larger ones it breaks, usually to a network error.

Can anyone help me get this running on the larger databses?

Here is the script:

CREATE PROCEDURE dbo.allTables_SpaceUsed
AS
BEGIN
    SET NOCOUNT ON      
 
    DBCC UPDATEUSAGE(0)
 
    CREATE TABLE #t
    (
        id INT,
        TableName VARCHAR(32),
        NRows INT,
        Reserved FLOAT,
        TableSize FLOAT,
        IndexSize FLOAT,
        FreeSpace FLOAT
    )
 
    INSERT #t EXEC sp_msForEachTable 'SELECT
        OBJECT_ID(PARSENAME(''?'',1)),
        PARSENAME(''?'',1),
        COUNT(*),0,0,0,0 FROM ?'
 
    DECLARE @low INT
 
    SELECT @low = [low] FROM master.dbo.spt_values
        WHERE number = 1
        AND type = 'E'
 
    UPDATE #t SET Reserved = x.r, IndexSize = x.i FROM
        (SELECT id, r = SUM(si.reserved), i = SUM(si.used)
        FROM sysindexes si
        WHERE si.indid IN (0, 1, 255)
        GROUP BY id) x
        WHERE x.id = #t.id
 
    UPDATE #t SET TableSize = (SELECT SUM(si.dpages)
        FROM sysindexes si
        WHERE si.indid < 2
        AND si.id = #t.id)
 
    UPDATE #t SET TableSize = TableSize +
        (SELECT COALESCE(SUM(used), 0)
        FROM sysindexes si
        WHERE si.indid = 255
        AND si.id = #t.id)
 
    UPDATE #t SET FreeSpace = Reserved - IndexSize
 
    UPDATE #t SET IndexSize = IndexSize - TableSize
 
    SELECT
        tablename,
        nrows,
        Reserved = LTRIM(STR(
            reserved * @low / 1024.,15,0) +
            ' ' + 'KB'),
        DataSize = LTRIM(STR(
            tablesize * @low / 1024.,15,0) +
            ' ' + 'KB'),
        IndexSize = LTRIM(STR(
            indexSize * @low / 1024.,15,0) +
            ' ' + 'KB'),
        FreeSpace = LTRIM(STR(
            freeSpace * @low / 1024.,15,0) +
            ' ' + 'KB')
        FROM #t
        ORDER BY 1
 
    DROP TABLE #t
END
0
Comment
Question by:SigmundFraud
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 11

Expert Comment

by:MacNuttin
ID: 22766931
COUNT(*) for big tables is a resource hog. I use a script that works better Would you like try it?
0
 
LVL 23

Expert Comment

by:adathelad
ID: 22766933
TBH, if you're getting network errors I don't think it's the query that needs to be looked at. At a glance, I can't see anything wrong with the query.
Have you tried running the sproc directly on the SQL Server machine? Does it always succeed there?
What is the error you get?
0
 
LVL 11

Expert Comment

by:MacNuttin
ID: 22767039
There's a setting for dealing with long running querys basically a time out are you hitting that?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:SigmundFraud
ID: 22767043
Thanks for the prompt responses.

Macnuttin, yes I would like to try the script.

Adathelad, the first time i ran the query it brought back multiple responses of tables being updated then suddenly broke due to a network error - however the correct response would obviously be a table. I have ran the script directly on the server and i get the folllowing:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Server: Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.

Connection Broken
0
 

Author Comment

by:SigmundFraud
ID: 22767052
I forgot to add that i get the error message almost instantly.
0
 
LVL 11

Accepted Solution

by:
MacNuttin earned 400 total points
ID: 22767093
0
 
LVL 11

Expert Comment

by:MacNuttin
ID: 22767243
In SQL Server Management Studio -->server properties -->Connections make sure allow remote connections is checked and remote query timeout is 0 for no timeout or atleast 600
0
 

Author Comment

by:SigmundFraud
ID: 22767272
MacNuttin I like that script but i will just have to make some modifications in order to get the index size.

Thanks.
0
 
LVL 11

Expert Comment

by:MacNuttin
ID: 22767470
ok well good luck
0
 
LVL 5

Expert Comment

by:jose_juan
ID: 22767594
Hi,

use my code.

Regards!


-- JJBM, 04/05/2005, dimensionado BD
--/
 
CREATE TABLE #TMP (
	Name		sysname,
	Rows		char(20),
	reserved	varchar(30),
	Data		varchar(30),
	index_size	varchar(30),
	Unused		varchar(30)
)
 
DECLARE	@name sysname
 
DECLARE	C CURSOR READ_ONLY FOR
SELECT	name
FROM	sysobjects
WHERE	xtype	= 'U'
 
OPEN C
FETCH NEXT FROM C INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO #TMP
	EXEC sp_spaceused @name
FETCH NEXT FROM C INTO @name
END
CLOSE C
DEALLOCATE C
 
SELECT	*
FROM	#TMP
ORDER
BY	Convert( int, rows ) DESC
 
DROP
TABLE	#TMP

Open in new window

0
 

Author Comment

by:SigmundFraud
ID: 22768143
jose_juan that code works fine on the smaller databses but on the production one i am getting the following errors:

'the object <object name> does not exist in database <db name>'

There are hundreds of missing objects it seems - any way around this?
0
 
LVL 5

Expert Comment

by:jose_juan
ID: 22768231
Hi,

uhm... your problem is not speed... is consistence!

i test my code for 403 user tables and run in 2 seconds.

check your database integrity...

   DBCC CHECKDB

Good luck!
0
 

Author Comment

by:SigmundFraud
ID: 22784613
CHECKDB produces many similar results along the following lines:

There are 146 rows in 4 pages for object <object>.
DBCC results for <object>

Also no errors are returned so am i right in assuming the DB is consistent or am i missing something?
0
 
LVL 5

Expert Comment

by:jose_juan
ID: 22784689
Uhm...

recapitulate,

you have a database myDB, with hundred of tables (and objects like views, procs, ...), you db egine are MS SQL Server 2000, you run my script and return a error

'the object <object name> does not exist in database <db name>'

....mmmm.... (thinking...)

do you running EXACTLY my code on myDB?
who client do you using?
try with "SQL Query Analyzer, SQL Server 2000 version" and select myDB

who user do you using? this user, have permission for all objects?

this is extraneous... (it should can run ok)

Regards.


0
 

Author Comment

by:SigmundFraud
ID: 22784824
Thanks for the responses guys, I think Mcnuttin had the best solution for me.
0
 
LVL 5

Expert Comment

by:jose_juan
ID: 22784883
Hi SigmundFraud,

ok no problem, but, what's problem with 'the object <object name> does not exist in database <db name>' error?

Regards.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question