database table size

I have an MSDB database that is 256 MB in size. I ran the below script from
another group to show reserved and used data by table. The sum of reserved
sized is about 10 MB. THe script doesn't include system tables. Is there a
way to get the sp_MSForEachTable function to include system tables?

Thanks.
DECLARE @SQL VARCHAR(255)

SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'

EXEC(@SQL)


CREATE TABLE #foo

(

name VARCHAR(255),

rows INT ,

reserved varchar(255),

data varchar(255),

index_size varchar(255),

unused varchar(255)

)


INSERT into #foo

EXEC sp_MSForEachtable 'sp_spaceused ''?'''


SELECT *

FROM #foo


DROP TABLE #foo

Most likely your size is due to the rows int he backup tables. Each time you
issue a backup there is a row written to 3 tables to record that event. By
default here is no process to delete or clean these up. You can run
sp_delete_backuphistory specifying the date that you want to remove all rows
from that point back. The first time you run it there is a good possibility
it will appear stuck but this is a very slow process with lots of data so be
patient. From then on it is quick. Create a SQL Agent job that runs this
once a week or so and you should be all set.

--
Andrew J. Kelly SQL MVP

"brian shannon" wrote in message
news:eI0Zm52tHHA.4612-at-TK2MSFTNGP04.phx.gbl...
>I have an MSDB database that is 256 MB in size. I ran the below script
>from another group to show reserved and used data by table. The sum of
>reserved sized is about 10 MB. THe script doesn't include system tables.
>Is there a way to get the sp_MSForEachTable function to include system
>tables?
>
> Thanks.
> DECLARE @SQL VARCHAR(255)
>
> SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
>
> EXEC(@SQL)
>
>
> CREATE TABLE #foo
>
> (
>
> name VARCHAR(255),
>
> rows INT ,
>
> reserved varchar(255),
>
> data varchar(255),
>
> index_size varchar(255),
>
> unused varchar(255)
>
> )
>
>
> INSERT into #foo
>
> EXEC sp_MSForEachtable 'sp_spaceused ''?'''
>
>
> SELECT *
>
> FROM #foo
>
>
> DROP TABLE #foo
>
>

Thanks.

I noticed my sysdtspackages table is 605 rows but I only have about 40 DTS
packages. It appears each time I save a DTS package it appends it to the
table.

Is there a way to delete old records without deleting the current package?

Thanks.
"Andrew J. Kelly" wrote in message
news:u20OMD3tHHA.4972-at-TK2MSFTNGP05.phx.gbl...
> Most likely your size is due to the rows int he backup tables. Each time
> you issue a backup there is a row written to 3 tables to record that
> event. By default here is no process to delete or clean these up. You can
> run sp_delete_backuphistory specifying the date that you want to remove
> all rows from that point back. The first time you run it there is a good
> possibility it will appear stuck but this is a very slow process with lots
> of data so be patient. From then on it is quick. Create a SQL Agent job
> that runs this once a week or so and you should be all set.
>
> --
> Andrew J. Kelly SQL MVP
>
> "brian shannon" wrote in message
> news:eI0Zm52tHHA.4612-at-TK2MSFTNGP04.phx.gbl...
>>I have an MSDB database that is 256 MB in size. I ran the below script
>>from another group to show reserved and used data by table. The sum of
>>reserved sized is about 10 MB. THe script doesn't include system tables.
>>Is there a way to get the sp_MSForEachTable function to include system
>>tables?
>>
>> Thanks.
>> DECLARE @SQL VARCHAR(255)
>>
>> SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
>>
>> EXEC(@SQL)
>>
>>
>> CREATE TABLE #foo
>>
>> (
>>
>> name VARCHAR(255),
>>
>> rows INT ,
>>
>> reserved varchar(255),
>>
>> data varchar(255),
>>
>> index_size varchar(255),
>>
>> unused varchar(255)
>>
>> )
>>
>>
>> INSERT into #foo
>>
>> EXEC sp_MSForEachtable 'sp_spaceused ''?'''
>>
>>
>> SELECT *
>>
>> FROM #foo
>>
>>
>> DROP TABLE #foo
>>
>>
>
>

brian shannon (brian.shannon-at-diamondjo.com) writes:
> I have an MSDB database that is 256 MB in size. I ran the below script
> from another group to show reserved and used data by table. The sum of
> reserved sized is about 10 MB. THe script doesn't include system
> tables. Is there a way to get the sp_MSForEachTable function to include
> system tables

You could run

SELECT object_name(id), reserved
FROM sysindexes
WHERE indid IN (0, 1)
ORDER BY reserved DESC

to see which are the biggest tables, including system tables.

--
Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Jun 25, 2:18 pm, "brian shannon"
wrote:
> I have an MSDB database that is 256 MB in size. I ran the below script from
> another group to show reserved and used data by table. The sum of reserved
> sized is about 10 MB. THe script doesn't include system tables. Is there a
> way to get the sp_MSForEachTable function to include system tables?

use databasexyz;
go

SELECT CAST(OBJECT_NAME(id) AS VARCHAR(12)) AS 'Table',
CAST(rowcnt as varchar(6)) AS '#Rows',
reserved * 8 AS 'Disk - Kb',
dpages * 8 AS 'Data - Kb',
(sum(used) * 8) - (dpages * 8) AS 'Index - Kb'
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
GROUP BY id, rowcnt, reserved, dpages
ORDER BY 'Table';
go

See http://www.sqlhacks.com/index.php/Ad...dsForAllTables

for samples and explanations

New this week:

Which operator to use for better performance when using Microsoft SQL
Server
How to improve the union queries on Microsoft SQL Server
How many duplicates do I have in the database on an MS SQL Server
How to summarize data with Microsoft SQL Server
How to optimize Microsoft SQL Server
Web pages related to the administration of Microsoft SQL Server
How to retrieve data with Microsoft SQL Server
How to drop the time portion of a DateTime column in MS SQL Server
How to get both the details and the subtotals with Microsoft SQL
Server

Unfortunately I don't know how that works. You might try posting that
question in the DTS or SSIS newsgroup.


--
Andrew J. Kelly SQL MVP

"brian shannon" wrote in message
news:e493DO3tHHA.668-at-TK2MSFTNGP05.phx.gbl...
> Thanks.
>
> I noticed my sysdtspackages table is 605 rows but I only have about 40 DTS
> packages. It appears each time I save a DTS package it appends it to the
> table.
>
> Is there a way to delete old records without deleting the current package?
>
> Thanks.
> "Andrew J. Kelly" wrote in message
> news:u20OMD3tHHA.4972-at-TK2MSFTNGP05.phx.gbl...
>> Most likely your size is due to the rows int he backup tables. Each time
>> you issue a backup there is a row written to 3 tables to record that
>> event. By default here is no process to delete or clean these up. You can
>> run sp_delete_backuphistory specifying the date that you want to remove
>> all rows from that point back. The first time you run it there is a good
>> possibility it will appear stuck but this is a very slow process with
>> lots of data so be patient. From then on it is quick. Create a SQL Agent
>> job that runs this once a week or so and you should be all set.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "brian shannon" wrote in message
>> news:eI0Zm52tHHA.4612-at-TK2MSFTNGP04.phx.gbl...
>>>I have an MSDB database that is 256 MB in size. I ran the below script
>>>from another group to show reserved and used data by table. The sum of
>>>reserved sized is about 10 MB. THe script doesn't include system tables.
>>>Is there a way to get the sp_MSForEachTable function to include system
>>>tables?
>>>
>>> Thanks.
>>> DECLARE @SQL VARCHAR(255)
>>>
>>> SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
>>>
>>> EXEC(@SQL)
>>>
>>>
>>> CREATE TABLE #foo
>>>
>>> (
>>>
>>> name VARCHAR(255),
>>>
>>> rows INT ,
>>>
>>> reserved varchar(255),
>>>
>>> data varchar(255),
>>>
>>> index_size varchar(255),
>>>
>>> unused varchar(255)
>>>
>>> )
>>>
>>>
>>> INSERT into #foo
>>>
>>> EXEC sp_MSForEachtable 'sp_spaceused ''?'''
>>>
>>>
>>> SELECT *
>>>
>>> FROM #foo
>>>
>>>
>>> DROP TABLE #foo
>>>
>>>
>>
>>
>
>