Solved

sp_spaceused

Posted on 2013-06-10
6
261 Views
Last Modified: 2013-06-12
I am monitoring my table size using this stored procedure:

sp_spaceused 'tableName'


But, I don't have enough experience to know how to put it into a table in sql so I can monitor trends.  Can you help please?
0
Comment
Question by:mathburg
6 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 39236146
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 39236286
here's a simple script:
http://www.cryer.co.uk/brian/sqlserver/howto_list_table_sizes.htm

in that example the result table is dropped - but that can be amended to suit
0
 
LVL 13

Assisted Solution

by:sameer2010
sameer2010 earned 100 total points
ID: 39237035
Here is what you can do. Create the table to hold the data and remove drop and create from subsequent executions.
drop table #t
create table #t(name varchar(100),rows varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100),currTime datetime default getdate())

insert into #t (name,rows,reserved,data,index_size,unused)
exec sp_spaceused N'avgtable'

select * from #t

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 100 total points
ID: 39237379
Sorry, Before submit my code someone has already posted.



create table #temp
(
name varchar(max),
rows varchar(max),
reserverd varchar(max),
data varchar(max),
index_size varchar(max),
unused varchar(max)
)
GO
sp_msforeachtable 'insert into #temp (name,rows,reserverd, data, index_size, unused)  exec sp_spaceused ''?'''
GO
select * from #temp
GO
0
 
LVL 2

Assisted Solution

by:vivekkumarSharma
vivekkumarSharma earned 100 total points
ID: 39238152
create table test1_spaceused(name varchar(20),rows int,reserved varchar(20),data varchar(20),index_size varchar(20),unused varchar(20))

insert into test1_spaceused
exec sp_spaceused 'test1'

select * from test1_spaceused
0
 

Author Closing Comment

by:mathburg
ID: 39238512
The last three answered my question well.  
Thank you so much.  aneeshattingal , The link did not work for me.  I'm sure it was probably the same thing sorry.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

16 Experts available now in Live!

Get 1:1 Help Now