Performance - Automatic Expansion Vs Setting Large Initial Size.
Aug 25, 2004
Hi,
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
View 1 Replies
ADVERTISEMENT
Jul 8, 2015
give me the best autogrowth & initial size setting for database
View 7 Replies
View Related
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?
View 1 Replies
View Related
Mar 3, 2000
We 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 Related
Dec 26, 2006
Hi 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.
View 2 Replies
View Related
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
View 3 Replies
View Related
Jan 27, 2007
Folks:
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?!
View 4 Replies
View Related
Jul 23, 2007
Hi,
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.
View 6 Replies
View Related
Feb 12, 2008
Hi, 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
View 4 Replies
View Related
Mar 11, 2008
Via 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
View 9 Replies
View Related
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
View 1 Replies
View Related
Jul 20, 2005
I 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 Related
Dec 16, 2004
Hi 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
View 3 Replies
View Related
Apr 9, 2013
How 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.
View 4 Replies
View Related
Oct 23, 2015
I 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.
View 2 Replies
View Related
Sep 12, 2011
We 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
View 9 Replies
View Related
Jun 19, 2007
We are going to be getting a new system to house our large databases. Some of these databases will need to be encrypted. Ideally we'd like to set up encryption in SQL server rather then have to purchase an appliance to encrypt the data. What is the trade off of using SQL server 2005 to encrypt data? Does performance suffer a great deal with a large database (500g-4tb)? Also, how much more/less sure is the encryption for SQL Server? Some of our users need to be able to read the data as unencrypted while working, but we need to keep the system as secure as possible. Answers to any of these questions would be incredible helpful.
Thanks,
Robb S.
View 4 Replies
View Related
May 24, 2006
Hi
I've been searching this site and the Web for info on an error message I get when importing from Access 2003 into SQL Server 2000.
'Data for Source Column 3('Col3') is too large for the specified buffer size'
A memo field in Access is larger than 255.
I have followed advice about putting the field to the first column. This doesn't work - the error just returns the new column number. In fact, I've tried just importing the first column - no good.
I am wary about making Registry changes as comments on the Web say this doesn't work either.
Does anybody have the solution for this.
Paul
View 6 Replies
View Related
May 9, 2008
I have set up transaction replication between two databases. Data from a table in the first database is replicated to the same table in another database.
The table at the publisher already has some data in it. The table at the subscriber is empty. When the replication is synchronizing, I get the following errors in the replication monitor:
*The process could not bulk copy into table "dbo"."virtualdatalocations_waitingqueues". (Source: MSSQL_REPL, Error number: MSSQL_REPL20037) Get help: http://help/MSSQL_REPL20037
*Field size too large
The table looks like this:
CREATE TABLE virtualdatalocations_waitingqueues (
dataid int ,
personid int ,
queueid int ,
CONSTRAINT FK_vw_dataid
FOREIGN KEY(dataid) REFERENCES datalocations(id) ON DELETE CASCADE ,
CONSTRAINT FK_vw_personid
FOREIGN KEY(personid) REFERENCES persons(id),
CONSTRAINT FK_vw_queueid
FOREIGN KEY(queueid)REFERENCES waitingqueues(id)
);
It used to run fine in the past. I couldn't find any help on google or on forums.
Any help or comments are greatly appreciated.
View 6 Replies
View Related
Feb 24, 2006
I developed a vb app that imports csv data into an sql server db. The original text file is 36.5mb. The db after import is 230mb and the log file is 555mb. Is this normal?
View 8 Replies
View Related
Jan 8, 2001
I recently started using Differential backups. They are working but are growing in size a lot quicker than I expected.
The backups are growing by 2.5GB every day although the total size of all
transaction backups is under 350MB. I would have imagined that the total transaction log backups would be a good indicator of total database changes and therefore the differential backups would approach this figure.
Please explain!
View 2 Replies
View Related
Feb 11, 2015
My log file size is of 5 GB, I just wanna reduce this to some extent without adopting shrinking method. So is there any way to do the same ?
View 1 Replies
View Related
May 24, 2007
Hello,
I have a issue with the drill down. In the report there is drill down in the Amount column. I am trying to pass the customer names in this drill down but there are more than 100 customers for that specific case and drill down is not able to pass all the customers.
Is there any other way to pass the large string in the drill down?
View 2 Replies
View Related
Oct 10, 2006
hi all,
my tlogs at the subscriber are growing very large
irregardless of my recovery mode. any help
using snapshot-push replication
thanks,
joey
View 8 Replies
View Related
May 30, 2006
Hi,
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
View 4 Replies
View Related
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?
View 7 Replies
View Related
Jun 14, 2001
HI There,
Generally speaking, is it better to use a large or small stripe size for a Raid 5 array (4 drives) ? I would appreciate any specifics also.
Thanks in advance.
Charlie
View 1 Replies
View Related
Jul 18, 2006
Hi,
Please could you tell me how big sql tables are when people refer to them as small, medium and large? Preferably in terms of disk space or rows (each row in my table will contain a standard length job advert and 20 additional columns with an average of 8 characters)
Thanks for your help! :-)
Stu
View 3 Replies
View Related
Apr 20, 2007
Hi
I want to store large files like pdf file,Html page,audio file in Sql Server database.How can i do it?
if somebody know then tell me as soon as possible.
Thanks in advance.
Bye
View 1 Replies
View Related
Jul 20, 2005
Hello there,I have and small excel file, which when I try to import into SQlServer will give an error "Data for source column 4 is too large forthe specified buffer size"I have four columns in the excel file, one of the column contains alarge chunk of data so I created a table in SQL Server and changed thetype of the field to text so I could accomodate this field but stillno luck.Any suggestions as to how to go about this.Thanks in advance,Srikanth pai
View 5 Replies
View Related
Jan 25, 2008
Hi gurus, I'm creating a web application where I will have a large number of tables (between 10k and 20k), this is done for the sake of scalability as tables will be moved to different database servers as the application grows and also for performance (smaller indexes). I'm worried though how having a large number of tables could affect the performance of SQL Server as the application will start on one single database server. I tried to find some resources on that on the internet but couldn't find any.
I would really appreciate if you can give me some advice and if you have any good links that would be great...
View 10 Replies
View Related
Jan 25, 2008
Hi gurus, I'm creating a web application where I will have a large number of tables (between 10k and 20k), this is done for the sake of scalability as tables will be moved to different database servers as the application grows and also for performance (smaller indexes). I'm worried though how having a large number of tables could affect the performance of SQL Server as the application will start on one single database server. I tried to find some resources on that on the internet but couldn't find any.
I would really appreciate if you can give me some advice and if you have any good links that would be great...
Waleed Eissa
http://www.waleedeissa.com
View 9 Replies
View Related
Dec 5, 2007
Hi,
I have a table with over 61 million records having a clustered index on an identity column(Primary key). Simple count queries are taking minutes to execute on this table (ex: select count(1) from table1). I have checked the statistics on the primary key which displayed me the histogram having the 39th million record as the Range-hi-key. I updated the statistics on this column and tried requerying, but still it took atleast 5 minutes to give me the count of records in the table. Also, there were no users using the table when I queried. Inserts into this table were working fine. I have other tables in my database with 41 million records having no such issues. Can anyone point me to the problem areas in such scenarios?
Thanks,
Harish
View 6 Replies
View Related
