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