Questions tagged [auto-growth]
The auto-growth tag has no usage guidance.
57
questions
0
votes
0
answers
39
views
Database AutoGrowth For Logging
I have a SQL Server 2019 database that we use for collection of logs from our software from machines on our network. We are required to hold these logs for a about a year. With the logging from our ...
1
vote
1
answer
123
views
Database file reserved space
What are the possible reasons that a SQL Server database file reserved space is 95 percent of the total space? My database file has increased drastically and it shows that the actual space used is 5 ...
0
votes
1
answer
86
views
Why does data file growth fail even with plenty of disk space?
We have a SQL Server 2016 (v13 SP3) Enterprise Edition server hosted in a Windows failover cluster / SQL Availability Group with two nodes (primary & secondary). The two nodes are running inside ...
1
vote
1
answer
889
views
Sql Server - Best Practices for Growing Large Database Files
I know this kind of question has been asked previously but I don't get the answer that I'm looking for.
I'm reconsidering setting up Autogrowth for DB Primary & Data files for Log(partitioned ...
0
votes
1
answer
78
views
What happens to proportinal fill once data files reach their limit?
Taking the example of data files (user db or temp db), assuming initial db size is 1GB and we have 3 data files.
Proportional fill will allow data to enter into the 3 files such that it allow max ...
0
votes
1
answer
57
views
Looking for help understanding what is happening in this SQL Script that shows key info about SQL Server Autogrowth Events
Here is the code below:
DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
SELECT @current_tracefilename = path
FROM sys.traces
WHERE is_default = 1;...
0
votes
0
answers
51
views
Shrinking log files in SQL Server 2014
I ran sp_Blitz in our Production server over the weekend and it output a few results regarding the log file growth. Example:
The database [MYDB] has a 104 GB transaction log file, larger than the ...
-1
votes
1
answer
125
views
Data loss is happening due to auto growth of the MSSQL Database [closed]
I have a database that is 101 GB in size (as of now) and continuing to increase.
How to stop the data loss that is happening due to the unavailability of the SQL server during the auto-growth event?
I ...
1
vote
2
answers
112
views
Tempdb log growth 3x as slow on new server, SP does not perform
We are running into the issue that our stored procedure on our new SQL Server performs a lot slower than on the old server. We looked into the problem and noticed the following on our temp db. The ...
0
votes
1
answer
135
views
DB autogrowth issue
I have a database that will growth in 1 month time the autogrowth was set to 500MB but as I monitored it, the consumption is 1GB+ per day. Should I wait for it to auto grow or is it better to set the ...
2
votes
1
answer
385
views
Do I need to enable Trace Flag 1117 for equally sized data files?
I was reading about fill proportional algorithm in SQL Server and then I recalled TF1117. BOL states:
When a file in the filegroup meets the autogrow threshold, all files
in the filegroup grow. ...
2
votes
1
answer
74
views
Log Autogrowths bigger than log size, but no shrinks to explain how
I have a database with a logfile size of 1GB. I implemented some logging of AutoGrowths to eventually setup some notifications for when a database autogrows.
What I'm experiencing though, is that ...
0
votes
1
answer
108
views
Is there a way to determine what caused the last growth operation of my database (MDF file)? [duplicate]
If I don't already have any logging in place, is there any way to use the DMVs or anything else on SQL Server to determine which query / event caused my database's last grow operation?
0
votes
1
answer
577
views
autogrowth set higher than available disk
First all our autogrow settings are either turned off or the file size is set to the max autogrow size. I guess same difference as I came in after the setup.
Although I've never had this problem, I'...
0
votes
0
answers
140
views
Problem with calculating an approximate space required for online index rebuild
I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes ...
8
votes
3
answers
315
views
What factors do we need to consider in order to enable auto-growth settings for my PROD database
I want to know the factors that we need to consider in case we need to enable auto-growth settings for my PROD database.
I have already checked the database growth history and the database is ...
2
votes
1
answer
2k
views
what could be preventing my transaction log autogrowth to be set to UNLIMITED?
I have been trying to set the autogrowth of one of my databases to unlimited but despite whatever I do it remains the same.
First thing I have checked if something is holding any locks to my database:...
0
votes
1
answer
164
views
Flash storage and auto-grow
Our databases files (data and log) of (a lot of) SQL Servers are hosted on flash storage (SAN), what would you use as auto-grow property for databases having size round 500GB (data) and 150Gb (log)?
...
3
votes
3
answers
517
views
Database Log File Growth change does not reflect on secondary replica sys.master_files
Environment: Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
...
0
votes
1
answer
51
views
Shrinking the database partition if autogrow is turned off?
I want to know:
Are there chances of shrinking the database partition if autogrow is turned off?
Also, are there chances of data fragmentation and increase in seek times if we fully extend size ...
0
votes
1
answer
502
views
What happens when an auto grow event is triggered to the database?
I want to know what happens when an autogrow event is triggered...
Does it hold up all the database processing including reads and writes during that time?
For instance, our database encounters for ...
1
vote
1
answer
285
views
How to make a test Database grow in size by running transactions to test autogrowth factor?
I have created a test Database instance which is currently having zero activity on it. I have set the auto growth parameter for this DB similar to our actual production DB which is 600 MB per day , ...
0
votes
1
answer
226
views
Autogrowth Analysis in PROD DB based on the trace file data
I have used the below query to check our Production Database Autogrowth events so that we can set to a value which is much more realistic than the current value of 200 mb , unrestricted for mdf file ...
1
vote
2
answers
4k
views
Recommended Auto Grow settings suitable for database whose size is 867666.25 MB [closed]
I need recommendations for setting/modifying the auto grow options for our production database.
size of FUNDSDB 867666.25 MB
Also,
I have attached the current autogrowth statistics , looks like ...
1
vote
2
answers
273
views
Set default space free in sql log
We have a database that needs 40% free in the log files at all times or we get an alert.
I cannot find a way to set growth when a limit is reached.
Of course we can set autogrowth to a certain limit ...
3
votes
2
answers
4k
views
What caused the database [.mdf file] to grow suddenly
I am trying to find the cause which have bloated the mdf of a database from 20 GB to 100 GB.
So far i tried checking the autogrow events to find time, but could not find none using standard reports ...
1
vote
1
answer
87
views
How to know exactly when my Database / Log files grew?
Is there a way to see when (history / log records) of .mdf / .ldf files grew?
Ultimately, is it possible to setup alerting and know immediately when my database / log files grow?
7
votes
2
answers
14k
views
what is the best practice for tempdb autogrowth?
I have a client's server with about 15 databases on this instance with a data total of about 100 GB. One of the databases is for a sysaid app.
his temp db files are one mdf one ndf and of course an ...
0
votes
1
answer
151
views
Auto-Growth Events
Do we have a way to prove to somebody that auto-growth events may hurt performance of SQL Server?
Currently I can only know when it occurred through sys.trace and sys.fn_trace_gettable. But of course ...
1
vote
1
answer
490
views
Disk Usage Report SSMS - query to obtain same info
I would like to have a query that obtains the same info as in the Disk Usage report, section data/Log files autogrowth/autoshrink events
I would like to build a custom monitoring report but i could ...
0
votes
1
answer
285
views
Changing Auto-Growth Setting in AOAG for log file
We are on AOAG SQL Server 2014.
I am planning to change Auto-Growth setting from % --> MBs for log file.
Do I need to change the setting in all Replicas or only Primary Replica?
0
votes
3
answers
76
views
Changing Auto-Growth Setting to MBs vs Log Drive Huge Occupancy
These are our PROD Databases current settings and total information about log file utilization.
We are using SQL Server AOAGs 2014
Auto-Growth I am planning to set 512 MB
Recently we have seen huge ...
-1
votes
1
answer
349
views
Auto Growth in % ? What exactly it does? [closed]
I am new to SQL DBA Administration. Please help me understand the below scenario.
As it is really confusing. Please help.
What does it mean keeping the Auto Growth in Percentage.
For example:
My ...
4
votes
2
answers
1k
views
ALTER DATABASE MODIFY FILEGROUP [filegroup] AUTOGROW_ALL_FILES
Does anyone know of a way to accomplish setting a filegroup to AUTOGROW_ALL_FILES without setting the database to single user mode? I frequently add new filegroups/files to a 24/7 production database ...
2
votes
2
answers
719
views
Restore database without overriding Auto growth
I created a database with auto growth 200MB. After restoring database auto growth changed to 1MB. Because my Backup file database auto growth is 1MB.
Is there a way to restore without overriding the ...
1
vote
1
answer
438
views
Is a manual log file grow procedure working different that autogrowth?
I wonder if the internal methods of growing a file using
ALTER DATABASE testdb
MODIFY FILE
(
NAME = testdb_Log
, SIZE = 40960MB --40 GB
)
work different then letting the file ...
3
votes
1
answer
1k
views
Autogrowth in SQL Server
I ran into some trouble with my disk space on server because my DB size increase. Auto Growth is enable on my database, But file growth is in percentage and not in MB. I want to change in to MB.
But ...
2
votes
2
answers
2k
views
SSMS Disk Usage report does not show auto grow events
I have for some time suspected that the Disk Usage report in SSMS have stopped showing autogrow events. To test I created an empty database with a very small auto grow setting. I then inserted a lot ...
0
votes
1
answer
345
views
Is it safe or possible to change SQL Server AutoGrowth when in a SQL Server AlwaysOn Cluster
I am wondering it is possible or advisable to change the AutoGrowth settings of a database while it is part of an AlwaysOn SQL Server 2016 database cluster. I realized I used the default settings when ...
1
vote
1
answer
1k
views
What does autogrow run in the background (Is it a DDL statement?)
I am currently running into the following intermittent error from my applications logs
Commit - ERROR: Code=0x80004005 Source=Microsoft OLE DB Provider for SQL ServerDescription=Insert bulk failed ...
1
vote
1
answer
94
views
Why is Log File Auto Grow sometimes performed by my application user and sometimes by sa?
Many database tasks (including Data File Auto Grow, Log File Auto Shrink, and Data File Auto Shrink) are completed only by the sa user, but Log File Auto Grow seems to be associated with both sa and ...
3
votes
2
answers
685
views
SQL 2012 Autogrow and file extension problem
I had my data and log files set to auto grow by 10%.
This has caused the transaction logfile to grow to an astronomical 600 GB in size and led to the error
Autogrow of file PFMultilinguaklPOO_log in ...
3
votes
2
answers
433
views
SQL Server 2008 R2 DB grows overnight and slow
I am running a SQL Server 2008 R2 SP2 DB. I have a DB that was running around 19 GB in size and three days ago grew to 34 for no good reason. We typically have about 100 MB growth in the DB every ...
2
votes
0
answers
535
views
OrientDB distributed architecture
Technically this question could relate to any database that performs in a similar distribute manner but given that I am specifically working with OrientDB I thought the question should be target as ...
5
votes
2
answers
3k
views
Performance hickups after Postgres 9.4 upgrade
After upgrading our database from 9.3.5 to 9.4.1 last night, the server suffers from high CPU spikes. The upgrade was done with pg_dump. So the database was converted to SQL and then imported into 9....
0
votes
2
answers
698
views
What causes disk space growth from Informatica load onto SQL Server?
We've been dealing with huge disk growth on SQL Server from Informatica. After the load, the database grows to 2.4TB. After the database shrink, it goes to 1.05TB. What could likely cause this to ...
2
votes
3
answers
9k
views
Tempdb growing, however it has a lot of free space
On a sql server dedicated to a Dynamics CRM database, I've been seeing some odd behavior.
While monitoring growth using the following query as basis, I've noticed tempdb has grown quite frequently ...
4
votes
2
answers
7k
views
MS SQL Server autogrow configuration with heavy loaded database
I have a database its size around 200G and it is heavy loaded with many inserts happens every day (it is grow around 3 to 4 GB every day), but the Auto-Grow setting is set to 1 MB.
I think this is a ...
1
vote
2
answers
8k
views
SQL Server 2008 R2 – Track Database Growth (Production Database)
My requirement is to get the database size information collected daily, I did not find any option to get database growth over a period of time.
Someone please advise what the correct method for this ...
1
vote
0
answers
125
views
MDF/TempDB Growth, SQL Server 2005 Enterprise
Here's my scenario:
SQL Server 2005 Enterprise
Records from Database A being added to Database B
Size of Database A MDF file: 3.15 GB
I have separate drives for Data, Logs, and System.
I ran a T-SQL ...