Autogrowth And Initial Size Setting For Database
Jul 8, 2015give me the best autogrowth & initial size setting for database
View 7 Replies
ADVERTISEMENT
Performance - Automatic Expansion Vs Setting Large Initial Size.
Aug 25, 2004Hi,
We currently have a fairly new SQL server 2000 db (currently about 18mb is size) as a backend to an application (Navision). Performance seems to be below what it should be.
The db is increasing quite rapidly in size, with a lot of data scheduled to be loaded onto the db and also more and more shops and users coming onto the system with alot more transactions going onto the db.
The initial setup of the db has the database File properties set to "Automatically grow file" by "30%" and has an unrestricted file growth.
The server that the db sits on is high spec and very large disk space.
Because the database will be expanding alot and thus reaching its maximum space allocation and then performing a 30% increase in size (which I guess affects performance quite a bit??) quite regularly.
Is it best to set the intitial size of the db to a alot bigger size in the first place as we have large disk space availiable and also set the % increase bigger also.
any advice on best performance would be much appreicated.
Regards,
David
SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.
Sep 4, 2007
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
T-SQL (SS2K8) :: Initial Size Of Database File
Apr 9, 2013How to get the initial size of the database file using T-SQL.
sys.master_files, sys.database_files, sysfiles, sysaltfiles --> gives only the current size and not the initial size.
How To Change Initial Log File Size On Mirrored Database
Oct 23, 2015I have a database I need to copy from a Prod server to a Dev server. There is not enough space on the Dev server. In looking at the size of the files on the Prod server, the Initial Size property for the transaction log on the Prod server is set to 100,000 MB though the log is using nowhere near that.
This is a mirrored database so the recovery model is "full". I know that to change the initial log size, I have to put the database in 'simple" recovery model. Is this possible? Can I just:
1. Pause the mirror
2. Switch recovery model to simple
3. Change the initial size property to something smaller.
4. Shrink the transaction log
5. Change the recovery model back to full and resume the mirror?
I honestly don't know if the transaction log is needed on the Dev server. Meaning I may just be able to restore the transaction log to a different location on the server and delete it so that new one is created.
Find Autogrowth Setting Of All Databases
Jul 1, 2015give me query or script to find autogrowth setting of all databases in instance
View 5 Replies View RelatedWhat Should I Set The Autogrowth Size To?
Jul 13, 2007Hi Guys,
Should I keep all my databases to have a standard growth size for the data and log files? What would be good practice?
Also should be be shrinking the log file every so often?
Regards,
Af
Tempdb Initial Size
Mar 3, 2000We had a runaway query which built the size of tempdb to 24000mb. Then someone changed the unrestricted file growth property to restricted growth while the size was 24000mb. Now I can not reduce the initial size. I have set the property back to unrestricted file growth. I have shrunk the tempdb and available space is almost 24000mb. I have stopped sqlserver. I even deleted the existing tempdb.mdf & tempdb.ldf files. But when SQL server is restarted, the initial size is set to 24000mb. It will not let me reduce the size. Is there anything short of manipulating the system tables to reduce the size back to 500mb?
View 5 Replies View RelatedInitial Size For DB Files
Dec 26, 2006Hi All,
I need to create a brand new db in prod and I was wondering if anybody has any recomendations for initial .mdf, .ndf, and .ldf file sizes.
Thanks.
Changing Value Of Initial Size (MB)
Dec 5, 2007I would like to increase the initial size of a SQL 2005 DB from 150 to 250 GB to prevent automatic autogrowth; would this have any impact in production if you do it on the fly?
Thanks,
Carlos
Changing The Log Initial Size
Jan 27, 2007Folks:
Is there a way to decrease the initial size of a database/log file? I've noticed you can increase it, but if you decrease it, after you confirm the change and go checking again, you will see nothing happened.
Is there a way? Am I missing anything?!
Transaction Log - Initial Size Too Big
Jul 23, 2007Hi,
Currently my db size is only 6 GB but the transaction log file initial size was set to 20 GB and has grown much way beyond the db size with the autogrowth feature turn on. The database was originally a test/development DB and was migrated to a production server including the log file. This probably caused the accumulation of transactions on the log.
We run backup everyday and tried to shrinkfile and file size did not change.
Can I change the "initial size" setting of the transaction log without causing any problems? Do I need to stop the service before I made the change assuming I made the change after the backup run? Or can I change it on the fly?
Thanks in advance.
How To Resize Initial Size?
Feb 12, 2008Hi, I wish resize the allocated space of my DB.
It's 245GB, but the DB is 50GB.
I tryed this query:
use master
GO
ALTER DATABASE mydb
MODIFY FILE
(NAME = 'mydb_data',SIZE= 50000)
GO
but I receive an error:
Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than current size.
Can you help me?
Thanks
File Initial Size
Mar 11, 2008Via t-sql, how can I query for a file's initial size?
I want the same thing one finds by :
Start SQL Server Management Studio, view, object explorer, right click [dbname], properties, files, Database files: Initial Size (MB).
Many thanks.
Anything will give up its secrets if you love it enough. -- George Washington Carver
Changing Value Of Initial Size (MB)
Dec 5, 2007
I would like to increase the initial size of a SQL 2005 DB from 150 to 250 GB to prevent automatic autogrowth; would this have any impact in production if you do it on the fly?
Thanks,
Carlos
Not Applying The Initial Snapshot When Setting Up Tran Repl.
Jul 20, 2005I am setting up a combination of transactional and merge replicationon a very large database and do not want to apply the initial snapshotsince this would take to long. I am doing this with scripts that werepreviosly created when the database was not this big. I want to copythe publisher DB over to the Subscriber Server. Can I use theparameter @sync_type to 'none' when running the sp_addsubscription?Does anyone have any experience in doing this?Thanks in Advance for any help on this.Alan
View 2 Replies View RelatedNewbie Question About Initial Table Size
Dec 16, 2004Hi all,
I've worked with informix for a very long time and this is my first aproach to sql server. I have an extremely simple design for a "small" database and at this moment I'm creating the tables, in informix I can assign a first extent and next extent size to the creation of the table so if your volume and growth analisys is good you can basically be sure that you will allways have contigous space on disk for your table. I'm readin BOL to see if I have that feature here but can't seem to find anything similar. Does that mean that my table data will be "fragmented" all over the primary and secondary files every time I load into them? Would it be a good practice to simulate the extents by creating a secondary file for each table with the size I require?
Any coments will be greatly appreciated :)
Luis Torres
SQL Server 2008 :: Initial Size For TempDB Data And Log File?
Sep 12, 2011We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.
We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them.
What are the best values to start with?
U ->Tempdbdata having tempdb.mdf file
V->Tempdblog having templog.ldf file
Exponential Database Autogrowth
Dec 7, 2007Whenever we restart the services on this one SQL server 2005 instance, the database autogrowth changes to grow by 2500%. We have to manually change the autogrowth of data file to some sane number. Has anyone faced this issue? We tried to put SP2 on but even that doesn't help. Any help would be appreciated
View 1 Replies View RelatedSetting Paper Size To Be Default A4
May 30, 2006Hi,
I have been trying to configure the Paper Size to be default "A4" instead of "Letter".
My Report is configured to 21cm x 29,7cm and margins 1,5cm.
The Body is configured to 18cm x 26,7cm.
Everything looks fine in the Preview but the Size is always "Letter". The printers are all configured for A4 printing.
Is there a way to set these default values in the Page Setup Toolbar or is it supposed to figure it out?
Thanks,
steinar
Creating DB And Setting Size With Code
Apr 29, 2008
How to you create a database and set it size usings code?
I know CREATE DATABASE SUPPLIES but how do I set it to a max of 100 mb with no autogrowth using code?
How To Change The Transaction Log File Size To Less Than The Intial Setting.
Sep 4, 2006I have H Drive with 12 GB size.
The Transaction log file of my Database is given in H drive.
i.e H:Krishna_log.LDF.
My database initial setting for the Krishna_log.LDF is 10 GB.
so currently I can see 10 gb file occupied in the H Drive.
very soon my database Krishna_log.LDF is going to increase 10%.
Even though If do Backup/Shrink, it will reduce to the initial to 10 GB.
So all I need is, change the initial size of the
Krishna_log.LDF from 10 GB to 5 GB.
Pls give your suggetions...(I dont mind to shutdown,Restart the Sql server do these changes to achive this Task).
Thanx in advance.
Regards,
Krishna
Want Initial Raws Of Database Table
Apr 10, 2004HI all...
I am using MS SQL 2000 DB...from last 4 months....I want to take backup of the data from 1st january to 29th Feb 2004 and then trim this data to freeup some space in my dtabase.
Is it possible?if yes hOW?
Thanks in advance,
Chandresh here
How To Set Autogrowth Off / On (T-SQL)
Oct 2, 2007I am testing a msde 2000 sp4 database. (would like to use it against sqlserver 2005 later)
I need to turn the property for autogrowth on and check that an application alterts the user that it is on
and then off and verify the user is altered that it was set off.
I am not sure if this will work for mydb and mydblog
--turn off autogrowth
USE master
GO
ALTER DATABASE mydb
MODIFY FILE
(NAME = mydb,
FILEGROWTH = 0MB)
GO
--turn on autogrowth
USE master
GO
ALTER DATABASE mydb
MODIFY FILE
(NAME = mydb,
FILEGROWTH = 10MB)
GO
USE master
GO
ALTER DATABASE mydblog
MODIFY FILE
(NAME = mydblog,
FILEGROWTH = 0MB)
GO
--turn on autogrowth
USE master
GO
ALTER DATABASE mydblog
MODIFY FILE
(NAME = mydblog,
FILEGROWTH = 10MB)
GO
Also I need to know the t-sql for setting unrestricted growth on and off but I can not seem to find that.
Any help would be great.
We can not use enterprise manager so I'm planning to use osql thru a cmd prompt.
thanks
ced
Autogrowth For MDF And LDF Files
Nov 29, 2007I have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?
Thanks,
Carlos
Autogrowth For MDF And LDF Files
Nov 29, 2007
I have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?
Thanks,
Carlos
Autogrowth Values Keep Changing
Sep 21, 2006Hi!
We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.
What am I missing here?
BR John
Can I Change The AutoGrowth Option?
Jul 13, 2007Hi,
Is it possible to change the Autogrowth option of a database is none is set? I recieved an alert saying that one of the databases has 39.9% of freespace. Having checked the properties of the database, I noticed that the Autogrowth option had not been used.
The database is SQL 2005.
Thanks.
Af.
Table Size And Database Size
Mar 2, 2008Hi,
i use this script that show me the size of each table and do the sum of all the table size.
SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
THX
SQL 2012 :: How To Find Autogrowth OFF In DMVs
Feb 25, 2015So I need to write a script to look for available free space percentage in my databases, but I only want it to look at capped files. We consider a file with autogrowth off as capped for our purposes.
This is my problem: in sys.database_files and sys.master_files, if I have autogrowth off then max_size is -1, which is the same value as unlimited growth.
I cannot find another setting anywhere to determine how SQL Server recognizes that a particular file is set to not allow autogrowth. Any setting in a DMV anywhere where I can see whether autogrowth is disabled or not?
Bug: Data File Autogrowth Set To 12800%
May 11, 2006SQL2K5 SP1the autogrowth setting in one of my database's primary filegroup datafile keeps having the value of 12800%, which was originally set to100MB, everytime the service is restarted. the same occurs whenever irestore a backup of this database in our development environment.WTF? this issue does not happen to other filegroups. only on theprimary data file. whenever this happens the 4GB data file grows tomore than 70GB (even the math is incorrect) with about 95% of UNUSEDspace.has anyone else come accross this BS or anyone knows how to preventthis from happening? is MS aware of this not-so-funny joke?thanks
View 2 Replies View RelatedDB Engine :: DB Autogrowth Turned On In Secondary Data File Still 0% Internal Free Space
Oct 30, 2015Today we received an issue on an application database on internal free space on the DB is 0% that was designed with as below
name   fileid   filename   filegroup   size   maxsize   growth   usage
XX   1   I:DataMSSQL.1MSSQLDataNew XX.mdf   PRIMARY   68140032 KB   Unlimited   0 KB   data only
XX_log   2   I:DataMSSQL.1MSSQLDataNew XX_log.LDF   NULL   1050112 KB   2147483648 KB   102400 KB   log only
XX_2   3   I:DataMSSQL.1MSSQLDataNew XX_2.ndf   PRIMARY   15458304 KB   Unlimited   0 KB   data only
XX_3   4   I:DataMSSQL.1MSSQLDataNew XX_3.ndf   PRIMARY   13186048 KB   Unlimited   0 KB   data only
XX_4   5   I:DataMSSQL.1MSSQLDataNew XX_4.ndf   PRIMARY   19570688 KB   Unlimited   204800 KB   data only
XX_5   6   I:DataMSSQL.1MSSQLDataNew XX_5.ndf   PRIMARY   19591168 KB   Unlimited   204800 KB   data only
2 of the secondary data files had its autogrowth enabled to unrestricted with 200MB and 3 of the data files including primary had its Autogowth turned OFF. Application use is complaining that there is no internal freespace on the DB.
What fails to understand us is that when the Auto growth was already TURNED OFF on 3 data files ( 1 primary and 2 secondary ) still why was the application trying to increase the space on the .mdf and .ndf files; as well when the Autogrowth is TURNED ON on 2 of the secondary data files, why was the DB not able to expand these file groups when the autogrowth is already turned off on 3 of its  other files.
What more data i need to ensure i submit an analysis to this.
Setting SQL Database So That Cold Fusion Will Go To New Database Instead Of Master
Apr 17, 2001whenenver i run cold fusion, it automatically gets my tables out of the master database instead of the new one i have created. i've added a user that has a default to the new database instead of the master, but i need to know how to use that new user correctly, and what i will need to do once i start adding more databases so that cold fusion will go to the appropriate database and not to the master.
View 3 Replies View Related