-
Reporting database growth
I have two procedures
one sp_spaceused
And the other.
CREATE TABLE #filestats (Fileid int, FileGroup int, TotalExtents dec(15,2),
UsedExtents dec(15,2), Name nchar(128), FileName nchar(260))
INSERT INTO #filestats
EXEC ('DBCC SHOWFILESTATS')
declare @pagesperMB dec(15,2)
declare @totalExtents dec(15,2)
declare @usedExtents dec(15,2)
declare @dbsize dec(15,2)
declare @dbspaceavail dec(15,2)
SELECT @totalExtents = sum(TotalExtents) FROM #filestats
SELECT @usedExtents = sum(UsedExtents) FROM #filestats
select @pagesperMB = 1048576.00 / (select low from master.dbo.spt_values
where number = 1 and type = 'E')
set @dbsize = (@totalExtents * 8.00 ) / @pagesperMB
set @dbspaceavail = @dbsize - ((@usedExtents * 8.00 ) / @pagesperMB)
DROP TABLE #filestats
CREATE TABLE #logstats (DatabaseName nchar(128), LogSize dec(15,2),
LogSpaceUsed dec(15,2), Status int)
INSERT INTO #logstats
EXEC ('DBCC SQLPERF(LOGSPACE)')
declare @logsize dec(15,2)
declare @logsizeused dec(15,2)
declare @logspaceavail dec(15,2)
select @logsize = sum(LogSize) FROM #logstats WHERE DatabaseName = db_name()
select @logsizeused = sum(LogSpaceUsed) FROM #logstats WHERE DatabaseName =
db_name()
DROP TABLE #logstats
set @logspaceavail = @logsize - (@logsize * (@logsizeused / 100.00))
select @dbsize + @logsize AS DatabaseSize, @logspaceavail + @dbspaceavail AS
SpaceAvailable
Which one is best to use to monitor when the actual database will grow by x percent
I need to recall each week the size of the database and how much physical hard drive disk space i may need.
Thanks
Similar Threads
-
By RaeK in forum Database
Replies: 5
Last Post: 02-22-2012, 07:40 AM
-
By Russ in forum Database
Replies: 0
Last Post: 06-04-2002, 12:57 AM
-
By Michael Tzoanos in forum Database
Replies: 0
Last Post: 04-12-2002, 11:19 AM
-
By Dave W in forum VB Classic
Replies: 5
Last Post: 10-26-2001, 11:29 AM
-
By Marshal in forum authorevents.appleman
Replies: 2
Last Post: 04-10-2000, 03:36 AM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
Forum Rules
|
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL
|
Bookmarks