Database Growth

Apr 20, 2007



I would like to know followings:



I want to see every day or weekly Database growth (%) save on table



I have some SP which will give me one time run and see the growth. which is ...



Please advice any other way to find out and save on a location ...



create procedure sp_growth as

set ansi_warnings off

declare @l_db_name varchar(50)
,@l_sql_string varchar(1000)

set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth

create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))


declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases

open db_name_cursor

fetch next from db_name_cursor into
@l_db_name

While (@@fetch_status = 0)
begin
select @l_sql_string = 'select ' + '''' + @l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @l_db_name + '].dbo.sysfiles'

insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@l_sql_string)

fetch next from db_name_cursor into
@l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return


GO


Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/

View 3 Replies


ADVERTISEMENT

Growth Of A Database!!??

Sep 17, 2007

Hi everyone,

I'm a beginner in SQL Server databases, my problem is this:

i'm making a database witch the frontend is an access project, the database has several stored procedures views and user functions (the normal..), but a few data, (only the experimental), last night i've noticed that the file grow from 22 MB to 89 MB, the objects are the same and also the data, the only diference was that i forgot to put in an event procedure code, the ADO method, "MoveNext", to update various records, and the loop was infinit.
Is it possible that SQL statments generated by ADO make the file grow so rapidly!?
If so how can i shrink it, because i've tried and and the results was 4%.

Can you help me!?

Thanks

View 1 Replies View Related

Database Growth

Apr 20, 2007



I would like to know followings:



I want to see every day or weekly Database growth (%) save on table



I have some SP which will give me one time run and see the growth. which is ...



Please advice any other way to find out and save on a location ...



create procedure sp_growth as

set ansi_warnings off

declare @l_db_name varchar(50)
,@l_sql_string varchar(1000)

set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth

create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))


declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases

open db_name_cursor

fetch next from db_name_cursor into
@l_db_name

While (@@fetch_status = 0)
begin
select @l_sql_string = 'select ' + '''' + @l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @l_db_name + '].dbo.sysfiles'

insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@l_sql_string)

fetch next from db_name_cursor into
@l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return


GO


Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/

View 1 Replies View Related

Database Growth

Apr 20, 2007



I would like to know followings:



I want to see every day or weekly Database growth (%) save on table



I have some SP which will give me one time run and see the growth. which is ...



Please advice any other way to find out and save on a location ...



create procedure sp_growth as

set ansi_warnings off

declare @l_db_name varchar(50)
,@l_sql_string varchar(1000)

set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth

create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))


declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases

open db_name_cursor

fetch next from db_name_cursor into
@l_db_name

While (@@fetch_status = 0)
begin
select @l_sql_string = 'select ' + '''' + @l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @l_db_name + '].dbo.sysfiles'

insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@l_sql_string)

fetch next from db_name_cursor into
@l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return


GO


Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/

View 1 Replies View Related

Automatic Database Growth

Aug 21, 2001

I've got a question about the automatic database growth feature of V7. Here's an example:

I have a 1gb db that can grow to max size of 2gb.
I set the auto grow option to 75%
The first time the db grows it will grab 75% of the free space (1gb)

What happens if the database needs to grow again?

Will the db grow using the remaining free space (25%) or
has the database reached its max size because it can't grow any further?

Thanks

View 1 Replies View Related

Database Size Growth

Sep 23, 2002

:eek: I am somewhat confused -- I have a database in production that I restored to a QA environment; upon restore, the size has grown by 200MB.

Both production and QA are running SQL2000 -- the only difference is that QA has the latest security hotfixes installed -- version 8.0.0.665 from KB article at the following link:

Q316333 (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316333&id=Q316333)

View 3 Replies View Related

Tracking Database Growth

Jan 14, 2005

Hi everyone.

I am trying to find a way to calculate everyday my DB Growth, I did find a script on some site but it seems to give me the same information as the taskpad wich is not very specific. Basicaly i would like to know the size of a table in MB or in whatevever conversion possible, so that i will be able to do some forcasting.

Any help here would be greatly apreciated.

View 1 Replies View Related

Is There A Way To Find Growth In Log (ldf) And Database(mdf) ?

Sep 22, 2005

Hello,I need to monitor every 15 minutes growth in data file and log file .Since mdf and intial file sizes are set to high value,measuring these values at 15 min interval will not provide the changein size .My intention is to measure the log file size growth which helps tocalculate the disk space and bandwidth required to setup log shipping .We need to set up this infrastructure based on this calculationThanksM A Srinivas

View 6 Replies View Related

Database && File Growth Monitoring

Mar 8, 2004

Can someone point me to examples of database & file growth monitoring.

I specificially want to monitor a number of separate SQL servers (2000, 7.0)

I want to end of with statistics of any size changes on any of these over time.

Help is greatly appericated..

thanks

View 5 Replies View Related

Why The Database Size Growth Too Fast ?

May 31, 2005

My DB size was from 500MB to 10GB since 8/1998 to 12/2004. But now is 16GB (from 1/2005 - 5/2005), I don't why the data size growth too fast (as double) ?

View 4 Replies View Related

Performance Degradation W/Database Growth

Mar 27, 1999

At approximately what db size is sql 6.5 known to degrade in performance?

Also, what is the maximum db size 6.5 can handle?

thanks in advance...

View 1 Replies View Related

Rapid Growth Of SQL Express Database

Oct 30, 2007

I have a client running RMS, since moving to SQL express his database size has jumped 2 from 2G to 4G in 8 months. Previiuosly it took 2 years to reach the 2G size. has anyone else experienced this rapid growth of their database?

View 5 Replies View Related

Distribution Database Log File Growth

Jan 11, 2007

SQL Server 2000 | Transactional Replication

Suspected Problem: Distribution Database Transaction Log Not Checkpointing

I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.

I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.

SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.

Any thoughts?

Jeff

View 1 Replies View Related

Large Database Growth Out Of Control

Oct 23, 2007

Hopefully I'm posting in the right area. There is a database that has grown to about
41-42 GB in size in about a 2 month period. The previous database had grown to about
22 GB before it was purged out. I'm running this on SQL 2000, and I've tried running all
the DBCC SHINKFILE and SHRINKDATABASE commands to no avail. In this case,
the MDF file is the one that has grown out of control as opposed to the log file (LDF file).

Does anyone have any suggestions on what could be done to control the size?

View 17 Replies View Related

Database Size Not Growing Despite Unlimited Growth

Mar 11, 2004

Hi All,

Database size is not increasing automatically ,however I have set it as unlimited growth. Any idea about this ?

thanks for in advance,


Sedat Duztas

Probil

View 1 Replies View Related

SQL Server 2008 :: How To Monitor Database Growth

May 5, 2015

I need to monitor my database growth, as few of databases are growing rapidly. My client wants the growth list of my databases. have report of database growth of specific databases, at least of one month.

View 3 Replies View Related

DB Design :: Control Growth Of Database File

Oct 7, 2015

 I currently have a DB that is growing at a rate of 10gb per month. It is set to 1mb unrestricted growth and the log file is set to 400mb restricted growth. I take regular transaction log backups so the log file is well under under without any issue. This DB's recovery model is set to FULL as it has to be mirrored to a backup site.  Any recommendations on how to control the growth. - Is it advisable to take create a new DB with data older than 2 years and transfer that file to an external drive and if i do this, can i "attach" it back to the main server if and when required ?

View 7 Replies View Related

DB Engine :: How To Find Database Growth Rate

Apr 22, 2015

Wanted to do the forecasting of disk growth for one year. How to find the database growth rate?

View 4 Replies View Related

Can't Change The Auto Growth Option On My Database

Aug 3, 2007



I'm currently using SQL Server 2005. Before I have set my database on unrestricted auto growth. But today, I have noticed that the Log file has been set to limit its growth to 2,097,152 MB. I have 160GB space for my log files, I just want to maximize the space for logs in my hard drive.

When I try to change the settings back to auto growth it still keeps on returning to its previous setting it is still set on 2,097,152 MB. What I did was :
Right Click on the Database - Properties - Files - Click the (...) - set the auto growth option to unrestricted - Click Ok
But when I checked log file, it is still set on 2,097,152MB.


Can some one help me change the settings of my Database.

View 6 Replies View Related

Database And Logfile Growth When Attaching SQL2000 DB To SQL2005

Jun 25, 2007

Can anyone tell me why my SQL2000 database has grown aprox 15 % and my Log file 20,000 % when I attach it to SQL2005 .I've Thousands of Databases to Upgrade, but with the log file increasing to more than the size of the Database Its going to be a struggle !

It also takes a fair ammount of time to attach,

I suspect there is some reindexing going on , as when I try to reattach to SQL 2000 I get index errors ?

Is the re anything I can do in advance to reduce the database growth ?

I know I can truncate the log afterward but the peak diskspace consumed during my Migration may be an issue !



Thanks for any help

View 2 Replies View Related

DB Engine :: How To Track Growth Rate Of Server Database

Aug 25, 2015

I am only DBA in my company and client want to know the growth rate of his SQL server DataBase which is in production. How can I get the growth rate per day?

View 3 Replies View Related

Database Growth Excessive When Changing Varchar Length From 50 To 100.

Aug 9, 2007



Hi all,

I'm trying to get an understanding of a serious problem I have with a large DB in production. This is going to be obvious to someone (everyone probably) <bg>

I have a table which consists of numerous varchars and ints but also a Text type field. This table resides in a SQL 2000 Database. This DB currently has a data file size of 16Gb and a Transaction Log size of 17Gb. When I edit the table and increase the size of a Varchar field from 50 to 100 these files grow to more than double their size!

Why is this happening and how can I prevent this?

TIA

NozFx

View 1 Replies View Related

SQL Server 2008 :: Monitor Database Growth And If Any DB Grown By 20% Send Mail Alerts

Apr 30, 2015

Is there any automated script available to - "Monitor Database Growth and if any DB is grown by 20%, sending mail alerts"? If not, what is the approach to write the T SQL script ?

View 5 Replies View Related

DB Engine :: Recommended Size And File Growth For A Database And Log File?

Sep 22, 2015

What is the recommended size and file growth for a database and log file? We will be storing approx 10000 records a day.Currently we have the following:

CREATE DATABASE Dummy
ON 
PRIMARY
( NAME = Dummy_data,
    FILENAME = 'D:....DATADummy.mdf',
    SIZE = 250MB,
    FILEGROWTH = 25MB )
LOG ON
( NAME = Dummy_log,
    FILENAME = 'D:....DATADummy_log.ldf',
    SIZE = 50MB,
    FILEGROWTH = 5MB ) ;
GO

View 3 Replies View Related

Tran Log Growth

Aug 14, 2001

Does this seem right? We have our transaction logs set to "Truncate Log on Checkpoint" and they still grow over 1GB. Is it possible that one transaction (to a checkpoint) generates this much logged information? Will transaction log backups every 5-10 minutes help me out better or is this just a poorly written application?

Thanks!

View 4 Replies View Related

Tempdb Growth Help

Mar 6, 2001

I am having a problem with the growth of the tempdb on my SQL 7.0 box. I have over 300+ stored procedures that are running (many with group by and order by in them). This is causing the size of my tempdb to grow to over 30gigs in size. If i reset the services of the DB it shrinks back down to the managable 6 gigs that i expect. Is there a way to have the services restart automatically on a nightly basis or is there a way to have the tempdb deallocate resources once they are used without restarting services?
I apreciate any help you can provide,
Nathan

View 3 Replies View Related

Tansaction Log Growth

Oct 9, 2002

Hi,

I am moving table(14 million rows) data from one server to another, The transactional log file growth during the process is 3 folds compared to actual data file size.

Could you please tell why?

Thanks
John Jayaseelan

View 4 Replies View Related

Analyzing Db Growth Per Day

Feb 14, 2005

Well my question is how do i analyze db growth per day. is there a tool i can use or a method. I mean i do take a look at the task view and the files but per day it doesnt move in MB wich is weird since this is a warehouse and their are nightly loads to it inserting maybe 30000 record a night on avg.

Any help would be grately aprreciated.

View 3 Replies View Related

SQL Automatic Growth

Jul 23, 2005

We use SQL 2000 and our database is configured to grow automatically by10%. Currently 96% of our database is used. At what point will thedatabase expand - what is the trigger point?

View 2 Replies View Related

DB Growth Issue

Jul 20, 2005

I have done this experiment on one of the tables. There is table calledbuild havinf nvText Field with large no of records. I want to drop thatcolumn and recover space. These are the results I got.SP_SPACEUSED ‘BUILD’ Resultsname rows reserved data index_size unused1. Before Deleting nvText FieldBuild 663211 341440 KB 339464 KB 1944 KB 32 KB2. After Deleting nvText FieldBuild 663211 341440 KB 339464 KB 1944 KB 32 KB3. After Executing the Shrink Database from Enterprise Manager.Build 663211 608280 KB 604744 KB 3456 KB 80 K4. After Executing DBCC DBReindex (build,'',70)Build 663211 124096 KB 123392 KB 712 KB -8 KBCan anyone please explain me after executing step 3 i.e shrink datacolumn as well as index_size shows an increased figure whereas logicallyit should be a reduced figure.Regards,Harcharan*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Monitoring DB Growth With Sp_spaceused

Jun 19, 2000

Hello,
I started to write a stored procedure to insert into a temp table from sp_spaceused but couldn't get the format right. I did a search in the swynk archive and saw that some people have solved this but I couldn't see any example code. Does anyone have scripts they've written to gather db size info using sp_spaceused or some other mechanism?
Thanks in advance,
Dan

View 2 Replies View Related

Restore/Log Growth Question:

Oct 6, 2003

Restore/Log Growth Question:

We have an SQL2K SP3 Decision Support Server which will be receiving a nightly data dump from the offsite production system. We’ve decided on a backup/restore process to move the data. We’re currently testing the process and have come across a problem. The test backup file is only 500MB, the real DB will be approx. 20GB, and when it is restore the DB is fine but the log file has ballooned to 8 GB.

When the DB is restored the properties of the production DB are forced over our settings. These include no truncation of log on checkpoint and a full recovery model (the destination DB will have simple recovery). We’ve been through the books online and search online forums, but have not found information on this particular problem. We can, of course, shrink the log file after restoring but are concerned about the amount of dead space needed when the real DB is delivered.

Any help would be greatly appreciated.

View 8 Replies View Related

DB Growth Overtime Script

Nov 8, 2006

Hi All,

I am researching the iformation on how to a percentage of the database growth over time. I have looked on the net, but I didn't like anything I saw. Do you have any ideas?

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved