SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 To check Database File Growth
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/12/2001 :  22:27:41  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Prabhakanth writes "I am using SQLServer 2000.
I created a new database and ported data into it.At this stage the size of the database was 35MB. The initial growth percent was set at default(10%). There was no max size specified. Then the database went live and after two weeks the database size grew to 941MB. This is pretty strange as initial data that was ported was data for two months, and approximating the datasize/day entry to be around 2.5MB it would mean a max. size of around 70MB at the end of two weeks. The questions that i have in mind are:
Q1) Could you tell me as to why it has grown to 941MB?
Q2)The possible solutions for the above ?
Q3)Is it advisable to shrink it by a large percentage and see what happens?
Q4)Possible measures to keep the file growth in check,assuming a 2-4MB/DAY data entry.


PS: Pls reply at the earliest."

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 06/12/2001 :  22:27:41  Show Profile  Visit graz's Homepage  Reply with Quote
I'd do a little research on the transaction log. My guess is that it's growing.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/13/2001 :  08:47:13  Show Profile  Visit nr's Homepage  Reply with Quote
I keep meaning to post this somewhere - it's something that the live servers email to me every day so that I have a recored of how databases and tables are growing - runs on v7 but shouldn't be difficult to get working on v2000. Note the first one has the xp_sendmail in the sp - the second is better as the xp_sendmail is in the job but haven't changed the first yet.

create procedure spReportDiscSpace
as
create table ##DiscSpace (s varchar(100), id int identity (1,1))
insert ##DiscSpace
exec master..xp_cmdshell 'dir d:\mssql7\data'

insert ##DiscSpace
exec master..xp_cmdshell 'dir d:\mssql7\backups\'

declare @s varchar(100) ,
@freespace varchar(100)

select @freespace = (
select top 1 ltrim(rtrim(s))
from ##DiscSpace
where s like '%bytes free%'
)

select @s = rtrim(srvname) + ' disk space report at ' + convert(varchar(20),getdate(),113) + ' ' + @freespace
from master..sysservers
where srvid = 0

exec master..xp_sendmail
@recipients = 'me@me.com' ,
@width = 2000 ,
@subject = @s ,
@query = 'select s from ##DiscSpace order by id'

drop table ##DiscSpace

go

create procedure spReportTableSpace
as

set nocount on
create table #Report (s varchar(1000), id int identity (1,1))
create table #a (name varchar(128), size int, id int identity)

insert #a
select name ,
0
from dbname..sysobjects
where type = 'U'

declare @name varchar(128)


create table #b (name varchar(128), rows varchar(128), reserved varchar(128) , data varchar(128), index_size varchar(128), unused varchar(128))

declare @id int
select @id = 0
while @id < (select max(id) from #a)
begin
select @id = min(id) from #a where id > @id
select @name = name from #a where id = @id

delete #b
insert #b
exec dbname..sp_spaceused @name

update #a
set size = convert(int,left(reserved,datalength(reserved) - 3))
from #b
where #a.id = @id
end

insert #Report
select 'total used space by tables = ' + convert(varchar(10),sum(size)) + ' kb'
from #a

insert #Report select ''

insert #report (s)
select right(space(15) + convert(varchar(10),size) + ' kb', 15) + ' ' + name from #a
order by size desc

drop table #a
drop table #b

select s
from #Report
order by id

go


Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 06/13/2001 :  09:10:14  Show Profile  Visit Merkin's Homepage  Reply with Quote
Thats really cool.

You should send it to Graz to publish as an article so it doesn't get lost in the forum archive.



Damian
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/13/2001 :  12:53:52  Show Profile  Visit nr's Homepage  Reply with Quote
I would probably want to make it a bit prettier first.


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000