Solved

Table and disk usage query

Posted on 2008-10-21
16
1,080 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now