April 5, 2006 at 3:37 pm
I need find the growth rate of the datafiles for all the databases in the server. Is there way I also need to find growth of datafile for all the databases in a server from Jan15th to Feb 15th. How can I do this?
April 6, 2006 at 7:53 am
Well, I found the link below very helpful for evaluating the size increase of a database. It will work if you are backing up to a backup set.
He also mentions looking at sysfiles (pretty easy) or tracing (takes some effort).
The other way to do it is to create a #temp table, drop to xp_cmdshell and 'dir' the folder contents to the table and then parse it out... It takes a little more work.
Take care.
April 7, 2006 at 8:43 am
If you're looking for the growth of the data file itself, rather than of the data within the file, you can look through the SQL error log for file growths... assuming that yours goes back far enough.
John
April 7, 2006 at 8:51 am
Really you'd need to track your database size on a daily or weekly period and then use that. Error logs work as well, but if you are having regular growths, you have not properly sized the database.
April 7, 2006 at 8:54 am
Properly sized the database ,what is the meaning of that? How can we do that?
April 9, 2006 at 8:25 am
Plz check this link. You have a downloadable ppt with arthimetic calculations on planning.
http://www.sql-server-performance.com/joe_chang.asp
April 10, 2006 at 9:50 pm
Hi Arul,
Just try this
-------------------------------
create proc usp_databases
as
set nocount on
declare @name sysname
declare @SQL nvarchar(600)
/* Use temporary table to sum up database size w/o using group by */
create table #databases (
DATABASE_NAME sysname NOT NULL,
size int NOT NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases
where has_dbaccess(name) = 1 -- Only look at databases to which we have access
open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
select @SQL = 'insert into #databases
select N'''+ @name + ''', sum(size) from '
+ QuoteName(@name) + '.dbo.sysfiles'
/* Insert row for each database */
execute (@SQL)
fetch c1 into @name
end
deallocate c1
select
DATABASE_NAME,
DATABASE_SIZE = size*8,/* Convert from 8192 byte pages to K */
RUN_DT=GETDATE()
from #databases
order by 1
-----------------------------
Jey
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply