SQL 2012 :: MDF File Growth - No Inserts

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


ADVERTISEMENT

SQL 2012 :: Cannot Stop File Growth On Data File

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

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

SQL 2012 :: Log Growth On AlwaysOn Availability Databases On Primary Server?

Nov 29, 2014

Secondary server is offline due to a hardware issue and the log files are growing on Primary sever for availability databases. The log drive is running out of space. How can I stop the log growth for the primary databases?

View 4 Replies View Related

Unexplained Log File Growth

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

Restricted File Growth

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

File Size Growth

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

Mirroring And Log File Growth

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

Log File Growth Concern

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

How To Minimize A Log File Growth?

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

SQL Server 7.0 'File Growth' Algorithm

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

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

Monitor Data File Growth

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

Stop Growth In .mdf And Continue On .ndf File?

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

TempDB Growth And File Size

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

Backups And Log File Growth Issues

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

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

SQL 2012 :: Blocking During Bulk Inserts?

Dec 9, 2014

We are noticing a lot of blocking when performing bulk inserts.

Sometimes the lead blocker(s) are blocking inserts into the same tables, but most of the time, it's insert to other tables. So, while bulk insert into table X is running, bulk insert into table Y is blocked from the insert into X.

- We turned off transactions from the application performing the bulk inserts - no change.

- We enabled table locking from the application - no change

- We enabled lock_on_bulk_load on the tables - this seems to have worked; however still see some blocking.

According to the lock_on_bulk article - When you specify table locking for a bulk import operation, a bulk update (BU) lock is taken on the table for the duration of the bulk-import operation, shouldn't we be seeing this BU lock? Instead, we see nothing but LCK_M_X (exclusive table locks).

Just found out that in order get a BU lock, no indexing can exist on the table ... sure be nice if that was in the article!

Also, we saw the exclusive locks happening before we made these changes, meaning it wasn't using the row locks that it should be by default. Assuming we're missing something here with lock escalation. I am profiling just for lock escalation, and it never happens ...

So I guess my pending question at this time is that why when inserting into table X do inserts into table Y get blocked?

View 3 Replies View Related

Where Is The Retricted File Growth Value From System Tables?

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

Surprising Backup File Growth - Puzzle!!!

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

SQL Server 2005 File Growth Issue

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

SQL Server 2008 :: MDF File Growth Datewise

Jun 30, 2015

Script to find DATEWISE MDF FILE GROWTH Like

30/6/2015 10%
1/7/2015 8%

View 2 Replies View Related

SQL Server Admin 2014 :: Log File Growth

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

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

Monitoring Data File Growth Of Databases In Table

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

Make Unristrictable Growth Of Data Base Log File

Jun 10, 2008

Hi friends,

I am working on a application in which,at once i have to make a huge amount of data to insert and delete in my data base.At this time of some transaction my data base log file,shows data base error like the log file of this data base has been full.plese see the sys.databsesd view...like..

Please help me.If you need the exception then i will send you later.

Cheers..

Thanking you
Sadik Ali

View 1 Replies View Related

Data File Growth Afer Restore On 2005

Oct 15, 2007

Hi at all,
first post....first help :P .
I hope to help another one (basing on my knowledge) in the next future :)

I'm a beginner dbadmin and I'm looking for help regarding a strange fact relative SQL Server 2005 :)

I migrated a medium(3,5GB) database from sql server 2000 to sql server 2005.
I made a backup in sql server 2000 (it has generated a 3,5GB BAK FILE) and I restored it in sql server 2005 on another server. (I didn't checked data file size).

I configured (with wizard) a maintenance plan on new sql server 2005 with these steps (in this order):
(tellme if the order of the steps is wrong :))

1) check db
2) rebuild index
3) reorganize indexes
4) update statistics (all table and all views)

I planned the maintenance plan on 3a.m. and I went home.

Next day I found that data file (MDF) on sql server 2005 was 22GB large!!
I made a shrink DB but there wasn't free space to erase. It seems there are
22GB of data.
(the data inside is the same of the sql 2000 server..same records same
table...identical, non change of data in the meanwhile)

How is it possible? What am I doing wrong?
I don't understand what can caused the growth of the file, the maintenance plan or the restore? (unfortunately....I didn't checked size after restore...I checked it only the next day).
May be the statistics? (there wasn'int update statistics job on old maintenance plan on sql server 2000)

Thanks in advance :)
Bob

View 20 Replies View Related

Recovery :: AlwaysOn - Changing Log File Growth Setting

Aug 5, 2015

I was looking to change the file growth setting in our AlwaysOn environment databases.We have a single availability group, one primary and one secondary replica. I learned that when changing the file growth setting on the primary databases (data file), the change flows though to the database on the secondary replica.However after doing the same with the log files, the file growth setting changed on the primary but the change did NOT propagate to the secondary.

Is the solution to apply the change directly to the secondary?here's the T-SQL code I used:

ALTER DATABASE myDB
MODIFY FILE ( NAME = N'myDB_log', FILEGROWTH = 512MB );
GO
SQL Server 2012 (11.0.5532)

View 9 Replies View Related

Logging Inserts From Flat File

Apr 25, 2007

Can someone tell me if SQL server logs individual inserts when copying from Flat File to OLE DB Destination? If so, can this be turned off?



ULTIMATELY, I would like to use a Bulk Insert task, but it looks like it is pretty specific to a single DB and a single table in the DB. I would like the Bulk Insert task to be generic enough to where I can specify (via Parameters) the DB and table for the Bulk Insert because this task will be performed in an iteration of many different flat files.



Sorry I never seem to have a "simple" question. My DBA is breathing down my neck here..


View 18 Replies View Related

Would A TLOG File Size Change When Inserts/updates/deletes Are Not Involved?

Jan 3, 2008

I have a 14GB database whose data content is legacy and is described as static. The log file is significantly large and continues to change size mostly increasing by 2-5GB a day (~60GB now) I have observed over the past two days; it shrank once unexpectly by a few GB. The instance is hosting other databases such as: EnterpriseVaultDirectory, EnterpriseVaultMonitoring, EnterpriseVaultStore, and NetPerfMon - might these seemingly unrelated data sources be involved?


I am trying to a trace to find traffic against the tables, no such luck.


Web applications are playing against it for queries but there should be no UPDATEs beign applied. I can only suspect that other unknown applications are performing operations but have yet to find unexplained connections.

Are there any other reasons why this type of log file activity would happen merely due to queries or stored procedure calls?

Lets also state, "mirroring, indexing, replication" are not at play. I know logging "Full" is not necessary as "Simple" should suffice but I am still hunting down why UPDATEs might be getting through. I realize I might adjust the migrated SQL 2000 security model to deny updates to find what breaks but would rather not take that iniative yet.


The installation is a fresh SQL 2005 Standard setup with SP2 applied; the databases were upgraded.

View 6 Replies View Related

SQL Server 2012 :: Write A Process To Get File Size In Kb And Record Count In A File?

Jul 31, 2014

I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?

I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.

View 1 Replies View Related

SQL 2012 :: Find Out Number Of Columns In Flat File Before Process That Particular File

Apr 14, 2014

I need find out the number of columns in flat file before i process that particular file.I have file name in @filename variable and file path is @filepath variable.But do not not that how i will check the column name in before i will process that file.

@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles
And i am using for each loop container to read the file one by one and put the file name in @filename variable.and my file name like

Product_20120607060930.txt
Product_20130708060930.txt

[code]....

Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file.if it is not there then i have to send mail to client saying that file is not valid.I need to also calculate the size of flat file.

View 4 Replies View Related

SQL 2012 :: TempDB Log File Usage Constantly Rising And File Keeps Growing?

Jun 16, 2014

The TEMPDB transaction log file keeps growing.The database server is new and the transaction log was presized to 1 GB on installation. After installing a number of databases, the log file grew over a day to 38GB. Issuing a manual checkpoint was the only way to free some space to allow it to be shrunk back to a usable size. The usage of the file is still going up.

I am struggling to find what process is causing the log to be used so heavily. Looking at the log reuse wait desc for tempdb returns "Nothing" and tempdb itself isn't being used very much or growing in size.

View 9 Replies View Related







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