Solved

Table and disk usage query

Posted on 2008-10-21
16
1,082 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

820 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