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


ADVERTISEMENT

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

ReportViewer Control Throws OutOfMemory Exception For Large Data

Nov 28, 2007

I have a dataset with 500,000 records and I'm getting the following error with ReportViewer control for local report. "An error has occurred during local report processing. An error has occurred during report processing. Exception of type System.OutOfMemoryException was thrown. any help with this would be highly appreciated.

View 1 Replies View Related

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

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 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

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 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

Question About Move Large Amount Of Data From Database To Database

Apr 23, 2007

guys,I have a project need to move more than 100,000 records from onedatabase table to another database table every week. Currently, usersinput date range from web UI, my store procedure will take those dateranges to INSERT records to a table in another database, then deletethe records, but it will take really long time to finish this action(up to 1 or 2 hours).My question is if there is some other way I should do to speed up theaction, I am thinking about use bcp to copy those records to datafileand then use bcp to insert it into SQL Server table. Is this the rightway to do it or should I consider other solution (then, what is thesolution.)Thanks a lot!

View 8 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

Large DataBase

Jul 23, 2007

What happens if you have a website and the hard drive on your server is say 250GB. Then the database exceeds that and the database is 300GB in size.How would you spread your database into two different hard drives?Thanks

View 2 Replies View Related

Large Database

Apr 17, 2003

Hello,

I would like to know whether MS SQL Server 2000 Can handle Large Database size 28 gig, currently we are using Sybase but we want migrate to SQL Server 2000. Are there any performance issues?

Any help is appreciated.

Thanks

Sejal

View 1 Replies View Related

Database Is Is Too Large Need Help

Aug 17, 2006

We have many installations of our shopping cart database. One specific database is huge, now about 25 GIG, compared to the others that range from 20 to 75 MEG. The server this one resides on has three other instances of the same database that are normal size.

In a particular table in the large database there are 9700 rows taking 380MB, the same table in a normal db has 162000 rows and takes 6.MB. The tables are identical and the indexes are the same.

Any ideas out there?

View 6 Replies View Related

Database Has Gotten Relatively Large

Sep 12, 2006

I upgraded a database from SQL2000 to SQL2005 and it went pretty smooth. After the transition was made, I backed up the DB. The size of the database was as expected, about 5 GB. About 5 hours later, a maintenance plan executed a few optimization jobs in the following order: Reorganize Index, Rebuild Index, Shrink Database, Update Statistics. Soon after that, another job backed up the database and it was then 32 GB. During the time after the first backup, no one was really using the database.

I've been trying to track this down for several days. Does anyone have any ideas for me?

Thanks,
Bobby Crawford

View 1 Replies View Related

Why Is Database So Large?

Dec 4, 2006

Hello!
I have a database with one table inside. The table has six columns with the following datatypes:
col1 -->smallint (2byte)
col2 -->int (4byte)
col3 -->smallint (2byte)
col4 -->smallint (2byte)
col5 -->smallint (2byte)
col6 -->int (4byte)
I have insert 1.359.320 rows with data and the size of the sdf file is 40.116.224 byte.
According to my calculation:
1.359.320 * 2byte + 1.359.320 * 4byte + 1.359.320 * 2byte + 1.359.320 * 2byte +1.359.320 * 2byte + 1.359.320 * 4byte = 21.749.120 byte
I hope somebody can explain me, why the database is so large.

Thank you
Sascha

View 4 Replies View Related

Issue With SSRS Report Exporting To Excel With The Matrix Control Inside The Table Control

Jan 27, 2008

Hi All,
I am placing a Matrix inside the table control for grouping requirements,but when we export the report to the Excel, the contents inside the table cell are ignored. Is there any way to get the full report exported, as per the Requirement.Please help me with this issue.

With Thanks
M.Mahendra

View 5 Replies View Related

Database Growing Out Of Control

May 28, 2002

I have a database that seems to have grown out of control. I have tried deleting tables, but that has not really reduced the size. What could have caused the database to grow this big and what can I do to reduce it's size. I have backed up, truncated the logs, ran the shrink database command, all to no avail. Pleas help.

View 5 Replies View Related







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