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
ADVERTISEMENT
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
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
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
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
Oct 5, 2015
I am trying to create sql script which will check all databases file size and then depend on the size set up auto growth different sizes with MB.
for example, sql server has 50 databases with different sizes, i would like to change auto growth all databases with different autogrowth size MB.
 File size      Data Autogrowth  Log AutogrowthÂ
 0MB - 250MB          25MB            25MB
250MB - Â Â 1GBÂ Â Â Â Â Â Â Â Â 250MBÂ Â Â Â Â Â Â Â Â Â Â 250MB
...........................
View 2 Replies
View Related
Oct 2, 2006
I am trying to upload a file in ASP.net 2.0 to a SQL database using the FileUpload control. I am doing this by way of a typed dataset.Here is my code.Dim rta As New ResponseTableAdapterDim rdt As ResponseDataTable = rta.GetResponseByID(1)Dim rr As ResponseRow = rdt(0)Dim fs As New FileStream(fu.PostedFile.FileName, FileMode.Open, FileAccess.Read)Dim br As New BinaryReader(fs)Dim image() As Byte = br.ReadBytes(fs.Length)br.Close()fs.Close()rr.UploadFile = imagerr.NameFile = fs.Namerta.Update(rdt)The code runs without an error but afterwards I find that nothing has been stored in the UploadFile cell in the database but the file name has been stored in the NameFile cell in the database. Any ideas?Your help is much appreciated. James
View 1 Replies
View Related
Apr 22, 2014
We have a large 'History' database that is currently about 4.5TB, with most of that in a datafile that is 4.2TB. We wanted to stop growth on the one large data file and have SQL Server allocate new data to the other data files, but this throws an error when we attempt to change the MAXSIZE settings:
ALTER failed for Database 'History'
MODIFY FILE failed. Specified size is less than or equal to current size.
The SQL Server is saying we can have a max size of 2TB, and anything over that is blocked. Since this is being blocked, the file continues to grow.
Is there any way to cap the growth of the 4.2TB file and not allow any more data to be written to it?
View 1 Replies
View Related
Dec 1, 2015
I have a SQL script folder on my HDD. The folder contains SQL Scripts and subfolders with Constraints and Extended properties. I need a BAT file that builds a database on my SQL Server instance.
I have the following structure:
 Tables (folder)
 dbo.Customers.sql
 dbo.OrderLines.sql
  dbo.Orders.sql
 dbo.Products.sql
  Constraints (folder)
      Foreign Keys(folder)
dbo.OrderLines.FK.sql
dbo.Orders.FK.sql
  Extended Properties(folder)
   dbo.Customers.EXTPRP.sql
   dbo.Products.EXTPRP.sql
The Server name for the SQL CMD is PCMSSQL2014? user: sa pass is empty.
View 3 Replies
View Related
Oct 24, 2006
Hi everyone,
Is Microsoft thinking over the possibility to implement Control+Z in order to avoid drawbacks when you're writing a package? I mean, when you drop a container then you aren't able to retrieve again
.
That same behaviour happened with Sql2k-dts.
Thanks in advance and regards,
View 3 Replies
View Related
Jun 3, 2015
We have database when trying to make read only throwing below error: with stack dump
Location:Â Â Â Â Â Â Â Â Â Â Â Â Â recovery.cpp:4517
Expression:Â Â Â Â Â Â Â Â m_recoveryUnit->IsIntendedUpdateable ()
SPID:Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 51
Process ID:Â Â Â Â Â Â Â Â Â 6448
Msg 926, Level 14, State 1, Line 1
Database 'XXXX' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server errorlog for more information.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database 'XXXX', an error occurred at log record ID (0:0:0). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 3414, Level 21, State 1, Line 1
An error occurred during recovery, preventing the database 'XXXX' (database ID 7) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Investigation DONE:
- DBcc checkdb shown CleanÂ
- database is online and able to access
-Detached database and attached with rebuild log, still could not bring database read_only
SQL version : SQL server 2008 SP4.Â
View 15 Replies
View Related
Feb 4, 2008
I was just wondering what would be a preferred approach in designing the tables against checkboxlist?
For example I have 3 checkboxlist:
chklVendor
chklModel
chklColor
and each will have lets say 4 - 5 list items.
Here are the different types of approaches that I've seen when designing database tables:
1. Creating tables for each checkboxlist.
2. Creating a single table and storing the values in a comma delimited format.
3. Using XML
Also these attributes will be used in the search form as one of the fields.
View 1 Replies
View Related
Oct 28, 2007
Hi,
I am new to SSRS.
I have created simple report using reporting services, In which i am using one List element and inside that 8 text boxes and a table, in which 4 texboxes is used as lable and remain 4 textbos is value from dataset and table displays details as below for each unquie call number with table header marked in red color. When i click preview i get below report(Report 1) for call number 4 and 10 records in table which is under list. But for call number 4 still 6 records as to be display in next page with text boxe element which marked in Green color in Report 1. Now i am getting report with out List element i.e (Report 2 given below )
Report generated for Call numnber 4 (on click of preview ) (Report 1)
Generated Report
Call No
4
CallDate
2/2/2007
Customer Name
ZINDAL STEEL P . LTD
Address
Chennai
SL Number
PartNumber
PartName
Qty
Rate
Amount
1
12345
SCREWS
100
10
1000
2
47555
BOLTS
200
25
5000
3
125453
RIVETS
300
40
12000
4
15786
RATCHETS
500
23
11500
5
15566
TORQUE WRENCH
600
45
27000
10
5456
HARMONIZATION TOOL
700
45
31500
(Report 2)
Report generated for Same Call number 4 in next page with out List element, In this page i want display the same textboes and values which are marked in green color.(This 6 records alos belongs to same call Number 4 )
SL Number
PartNumber
PartName
Qty
Rate
Amount
11
12345
SCREWS 6"
100
10
1000
12
47555
BOLTS 5"
200
25
5000
13
125453
RIVETS 5"
300
40
12000
14
15786
RATCHETS Big
500
23
11500
15
15566
TORQUE WRENCH Small
600
45
27000
16
5456
HARMONIZATION TOOL
700
45
31500
Can any one please guide me how to do this kind of desing.
Thanks and regards
Manju
View 2 Replies
View Related
Jun 13, 2006
hi all of you,
I haven€™t idea if the following description is a issue or not, anyway:
I begin from Control Flow layer.
I€™ve created a sequence container and inside I€™ve got two groups, one own a sql task and another one own a Data Flow task. Both are linked for a completion conector. Up to here everything is fine. But when I collapse my sequence container the arrow remains there for these tasks and you can see the sequence container €œclosed€? and the arrow lonely.
Not very esthetic, not practical.
Any clarification or though will be as usual welcomed
View 6 Replies
View Related
Jul 1, 2004
We have a SQL 2000 database here. As of 3 days ago, it was about 75 megs ( 73 Data / 2 log ). As of today, it is 73 Meg Data / 15 GIG log. The log file went from 2 MEG to 15 GIG in a matter of days, yet the data file size has not changed. also, there are NO users to this database - it's new and has not been placed into production yet. I currently don't have any idea as to what happened - I'm looking into Lumigent's log explorer software now. Does anyone have an explanation as to why the log file would have increased 6000X in size, yet the data is not any larger ?
Thanks,
JK
View 6 Replies
View Related
Jan 15, 2008
Hello Everyone,
I appear to be doing something wrong. When I
set my Table for Unrestricted File Growth and
save it, When I go back and look at the settings
it is set back to restricted file growth.
It seems to me if you set any settings and apply
and/or save it. Should it not keep that setting.
Any advice would be great.
Thanks
View 1 Replies
View Related
Mar 3, 2008
i have a database which has a log file size of 10 Mb. it goes into single user mode automatically . i tried to increase the size of file size of log file from 10 mb to 50 mb... but i want to make it only 20 mb ... i am unable to change since it gives a message .cannot decrease the size of the file .. is there another way to decrease the size of log file .....
View 2 Replies
View Related
Jun 12, 2007
We currently have a 10GB database that is functioning properly mirrored. The only issue we have is that the log files grow very quickly during the early morning hours when a large number of transactions hit our DB from scheduled jobs. We have transaction log backup and shrink job that runs every 3 hours to backup the log and shrink the logical file to 10GB. In most cases this will shrink the log back down to it's desired size of 10GB. However, on some mornings it takes more repetitions of the log backup/shrink job to return the log size back to "normal". During this these times when it does not shrink the DB effectively, I get a report of the job process by email that states "Cannot shrink log file 2 (e_Log) because all logical log files are in use." I run a DBCC OPENTRAN command, and there are no open transactions. Eventually, the log file will return to it's normal size of 10GB through the log file backup job. I have a few questions though.
1. Is this normal behavior during moderate database use?
2. Does mirroring have any effect on the SIZE of the log file?
3. Is it normal for the size of the data file to be smaller than the size of the log file on a heavily used database?
4. Does anyone have any suggestions to better maintain the log file size?
Thank you,
Greg
View 1 Replies
View Related
May 18, 2007
I am getting a bit concerned with the size of my log file and my understanding of backups and how the log file should be getting reduced in size. I have a production database that is 12 GB and the log file is 275 GB. The database file is set to autogrow at 1 MB and unrestricted file growth. The log file is set to 10% file growth and restricted to 2,097,152 MB file growth. I perform a full database backup each night. I had thought that all transactions in the log file would be rolled into the database file and then the log file auto-truncated in size during the backup process. I have never seen a log file stay larger than the database file. Please advise how I may keep the log file size (growth) down. Thanks!
View 10 Replies
View Related
Nov 27, 2007
SQL Server 2005
We need to set up a database with minimal log file growth or none at all. This database is used as an intermediate step in the data extraction process, i.e. ( daily inserts and truncations) - data recovery is not an issue, but the size of the log file is. How should I set this database options?
Can I set log file growth to 0 ( none)? Will it affect inserts?
Thank you
View 6 Replies
View Related
Aug 15, 2015
I was putting a database together (its a C# application MSSQL)  the application will handle a few hundred customers records and save  maybe a couple of thousand  word docs/images/other doc files wondered if the way to go was blob or filestream, I see the medium and larger databases seem to go for filestream but just wondered as not much mentioned about smaller dbs.I do not think  security/disk space/super fast access  will be a big issue.
View 4 Replies
View Related
Nov 16, 2000
We are running SQL Server 7.0 SP2, and are experiencing the following out-of-
space error message:
"Could not allocate new page for database 'FooBar'.
There are no more pages available in filegroup SECONDARY.
Space can be created by dropping objects, adding additional files,
or allowing file growth."
Needless to say, but the the database is set for 10% unlimited autogrowth and there
IS available space in the partition where the filegroup resides.
Any ideas as to why this is happening? What is SQL Server's algorithm for allocating
space when growing a database? Must it satisfy the request in one 'extent' and the
cause of our problem is that our disk is fragmented?
Thanks in advance.
Bill Zimmer - zim@ibx.com
View 1 Replies
View Related
Apr 7, 2014
We have a database called "ih".
On Saturday the mdf file was 41.8 GIG. Today it is currently 79.1 GIG.
I've restore the DB to a new db from Saturday to compare to see where the growth is happening. That db is called "ih_restore".
I've found the following script which reports the size of each table of the database -
SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
-- DB size.
EXEC sp_spaceused
-- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
[Code] ....
I've run it for ih and ih_restore and can see that the "reserved" and "data" fields are growing but no extra rows - so no inserts are happening in the database?? What or why will this be happening.
Example of csv file of a table that I've exported -
From ih -
name,rows,reserved,data,index_size,unused
em_comm_costing,384191,1011704 KB,512424 KB,498648 KB,632 KB
From ih_restore
name,rows,reserved,data,index_size,unused
em_comm_costing,384191,119808 KB,62960 KB,56088 KB,760 KB
So the em_comm_costing rows are 384191 in both but the data field has increased to 512424 from 62960.
The database is being mirrored as well, but not sure if that would be effecting the size?
View 6 Replies
View Related
Sep 19, 2007
Hi everyone,
Once our company encoutered database disk full issue, so we cannot insert any more data. So is there way I can monitor the data file size? or is there a monitor tool to sending out alert?
for example,
It can show me how many percentage of data file has been used e.g. 90%. so I know it's critical and I need to increase more space for database or disk drive.
thank you in advance
View 1 Replies
View Related
Jan 4, 2008
I have 50GB datafile (.mdf) and have 650 mb lift on the hard drive. I have another drive (on the same box) which has about 30 GB left.
My question is can i create a .ndf file in that 30GB drive and continue the database growth on the new .ndf file with out any furthur growth on the .mdf file? please help!
Thanks in advance!
THE LADDERS (The Most $100k+ Jobs.)
www.TheLadders.com
View 3 Replies
View Related
Aug 10, 2007
We have a problem with the size of the tempdb.mdf file. The tempdb had grown to 25Gb and consumed all the available disk space. SQL server was restarted and the tempdb was reset back to the default size. The following day the tempdb suddenly increased in size from 200mb to 25GB within a very short space of time. There were a couple of event log entries from sqlservger regarding the lack of disk. Since then the server is running without any problems but the level of free space is virtually zero on the drive with tempdb.mdf file.
What would cause the tempdb to grow suddenly and to this size?
Can I control the size the tempdb can grow to?
SQL 2005 (x64) sp1
W2K3 R2 SP1
View 4 Replies
View Related
Apr 16, 2007
I've been looking through this forum and can only guess that SQL2005 has changed in how backups and log file growth issues are handled. I tried the All Tasks feature from the GUI to perform a backup, however, the Truncate the Transaction log feature is greyed out so I can not select it. In SQL2000 I used the following to backup my databases and the log files were never a problem:
BACKUP DATABASE [DSS] TO DISK =
N'G:MSSQLBackupDSSbackup.BAK'
WITH INIT , NOUNLOAD , NAME = N'DSS backup',
SKIP , STATS = 10, NOFORMAT
Could someone assist me with a proper T-SQL to place in a job to backup my database and keep the log file under control and / or explain why the Truncate transaction log feature is greyed out?
Thanks!
View 5 Replies
View Related
Apr 1, 2005
Does anyone know where I can find the "retricted file growth" value (for the data file) from the system tables? We setup a limit for the file growth for all the databases. We would like to write a script to alert us when the database size approachs the limit.
View 3 Replies
View Related
Mar 29, 2007
Guys,
I am a bit puzzled. Our database backup grew from the usual size of ~27GB to ~40GB, all of a sudden. Nothing special happened in the last few days - nothing major to cause such increase.
I found out about this, because we suddenly had the backups failing, and when I explored, I saw that this was due to the lack of space on the hard-disk.
I do know that we need additional hard disk space. In the meantime, however, I'd like to be able to identify what exactly could cause such growth.
As far as I understand, for the backup to grow, the database needs to grow in a similar proportion. My only theory is that when the backup failed a few times, each time, somehow, it resulted in the database growth. Does this make any sense?
Another clue is that the backup job, which usually runs ~ 30 minutes has been running for 6 hours already, the file has grown to 40GB, and the backup job is still running ...
What is the best way for me to explore what exactly happened? Are there some system tables containing history of table counts or something - so that I can see who grew when and by how much?
I ran a query to see which objects were added in the past few days - that did not give me any clues - all looks normal.
Any ideas/suggestions?
Thanks a lot
View 5 Replies
View Related
Jun 20, 2007
I have a SQL Server 2005 SP2 instance and I detach/attach a database to the system and changed the file growth parameter from 10% to 100MB. Now when I look at the file growth parameter, it is showing 12800% instead of 100MB. I did some research and I found out this is an issue with SQL Server since MSSQL 2000. Is there a way to fix this issue using ALTER DATABASE or some other SQL command?
Thanks in advance for the help.
View 2 Replies
View Related
Jun 30, 2015
Script to find DATEWISE MDF FILE GROWTH Like
30/6/2015 10%
1/7/2015 8%
View 2 Replies
View Related
Nov 7, 2015
A log file size of a production database has been increase from 4gb to 150 gb initial size.Now i want to find when it will grow & how much it grow & which transaction is responsible for this.
View 6 Replies
View Related
Mar 27, 2015
I am monitoring the data file growth of the databases in a table. Every week I review to see how much space is left on each database. I am thinking of writing a query where the current free space left is less than 20% of the file size, it sends out an alert to me, so that I can manually resize the file . Is it a good practice to resize the data file manually? If so I believe this need to be done at the time when the server is least busy since it can slow down the database. Also do I need to re-index the tables once the data file is resized?
View 5 Replies
View Related
