?
Solved

sp_spaceused

Posted on 2013-06-10
6
Medium Priority
?
273 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 400 total points
ID: 39236146
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 400 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 400 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
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!

 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 400 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 400 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

831 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