Initial Size For DB Files

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.


View 2 Replies


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€?


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

Tempdb Initial Size

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

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?



View 3 Replies View Related

Changing The Log Initial Size

Jan 27, 2007


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

Transaction Log - Initial Size Too Big

Jul 23, 2007


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

How To Resize Initial Size?

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
(NAME = 'mydb_data',SIZE= 50000)

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?

View 4 Replies View Related

File Initial Size

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

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?



View 1 Replies View Related

Newbie Question About Initial Table Size

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

T-SQL (SS2K8) :: Initial Size Of Database File

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

Autogrowth And Initial Size Setting For Database

Jul 8, 2015

give me the best autogrowth & initial size setting for database

View 7 Replies View Related

How To Change Initial Log File Size On Mirrored Database

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

Performance - Automatic Expansion Vs Setting Large Initial Size.

Aug 25, 2004


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.


View 1 Replies View Related

SQL Server 2008 :: Initial Size For TempDB Data And Log File?

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

Data Files Size And Log Files Size

Sep 22, 2004

I'm using SQL Server 2000!
How to know then Data files size and Log files size by Store Procedures or Sql query?

View 1 Replies View Related

LDF Files Size Gone To 200 GB

Jul 5, 2013

My database .mdf file size is around 6 GB however .ldf file size is 200 GB.

Though I don't know exactly what .ldf file is, I believe maybe that is the reason why some of the stored procedures taking long time to run which were running good before.

Can I delete the .ldf file? If not, can I do something to reduce the file size?

The database (tables) are also replicated to another sql server in another computer.

View 7 Replies View Related

How To Get The Images Files Size?

Jul 21, 2004

I want to retrive the images from SQL database and store into microsoft access database.
But only images file which is <than 500mb is allowed to retireve.How can i get the images files size if i just store the image file name into my sql database.(there is another folder to store images)


View 3 Replies View Related

Size Of Backup Files

Jun 25, 1999

I need to know how big SQL Server 7 backup files are in comparison to the database size. For example if I have a database that is 300 Mb and I do a complete backup to disk with SQL Server 7 will the backup file be about 300 Mb?



View 2 Replies View Related

RESTORE DB Into Different Size Files

Aug 11, 1999

I have a 24gig DB that has 1 data file.
Is it possible to RESTORE the backup to a DB that has 2 files
(1 = 10gig 2 = 14gig).
Does one of the files on the the DB have to be at least 24gig to RESTORE?

View 1 Replies View Related

Different Size Of Data Files~~

Aug 14, 2006

just wondering if any expert out there can answer my question.

i got a database separate into 3 datafiles in 3 different drives.
i will name it A, B, C.
A datafile size 30G, B datafile size 15G, C datafile size 15G.
and the drive for datafile A is about full. so is there anyway i can more some of data from A datafile to other data file? or since A+B+C =60G can i make it all 20G for each one of them by any command? thanks!!! :)

View 4 Replies View Related

MDF And LDF Files Size Command

Jul 23, 2005

Hi All,I want to shrink a transaction log using :USE MasterBACKUP Log test WITH truncate_onlyUSE testDBCC shrinkfile(test_log, 0)These commands will be executed from a workstation.After shrinking, I want to see the size of thetransaction log and database files. But, I do notknow what command should I use.Could anyone help me ?Thanks in advanceJohn S.*** Sent via Developersdex ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Deployed Files Reduced Size

Apr 20, 2006

I am wondering why deployed packages are so much smaller than built packages. What is removed and why isn't it removed when the packages are built?

I have a package that goes from 3Mb to 1.5Mb

View 5 Replies View Related

T-SQL (SS2K8) :: Getting Files Size For Each Database File?

Oct 28, 2014

I would like to add to it the actual file size in mb or gb of each file to the results.

select, as logical_name,mf.physical_name,
when dm.mirroring_state is null then 'No'
else 'Yes'
end as Mirrored
from sys.sysdatabases sd JOIN
sys.master_files mf on sd.dbid = mf.database_id
join sys.database_mirroring dm on sd.dbid = dm.database_id

The sp_spaceused procedure does a nice job on it's own giving me what I want (only one db though), plus a bonus allocated space column. How can I combine this sp with my other query, or is there a better way to ad this information?

View 2 Replies View Related

Balancing Size Of Data Files In Filegroup

Apr 26, 2008

Due to SQL's proportional fill algorithm I'd like to have the amount of data in my data files as close to equal as possible.

File sizes before:
dat 482,895 MB 0% free
2dat 436,927 MB 3% free

I made the max size of each file 600,000 MB and added a third file 3dat also 600,000 MB. I rebuilt all the clustered indexes (and nonclustered for good measure) and unfortunately the re-balancing wasn't quite right.

File sizes after:
dat 464,761 MB 77% free
2dat 443,234 MB 73% free
3dat 309,568 MB 51% free

I only have a handful of heap tables that take up <100MB total so they're not the issue. I did do an ONLINE index rebuild. I'm not sure if an offline rebuild would have been better. I will not be able to try and offline for a few weeks though as it's time consuming and I have other tasks I need to run on this test server now.

I did a FULLSCAN rebuild on any column statistics not updated by the index rebuild but that didn't help either.

View 3 Replies View Related

Transact SQL :: How To Calculate Size Of Files That Have UNC Paths For

Mar 27, 2014

I am on SQL Server 2008 R2. I have a table that contains a field called [Location]. In that field is a UNC path to the physical file on the repository. Is there a way in SQL Server that I can say give me the select sum([Location] UNC file) where criteria? I saw some posts about xp_filesize or xp_GetFileDetails, but I do not see them in master. I am unable to add anything and wondering if there is any native functionality that would allow me to accomplish this!?

View 4 Replies View Related

Disaster Recovery: Calculate DB Size Based On .dmp Files

Feb 7, 2000

I have a client with no backup of MASTERdb or Help_rev_devices, all I have is a couple of .dmp's of the production data. In order to run DISK REINIT I need to know the size of the database, is there any way of finding out the size of the database device, if all you have to go with is a backup dump file?

I know it won't be pretty.


View 1 Replies View Related

SQL Server 2008 :: Virtual Log Files And Determining Right Growth Size

Oct 14, 2015

Any good starting point to understand for a specific db, how many max VLFs are good to have so that it does not cause long startup or backup times?

Also, I need some calculation so that I can identify a best growth parameter I will setup for each database ?

I'm seeing the below msg in errorlog and curious to know the changes (right sizing/growth) to be done? As of now 100 MB of log file growth value is set (refer: [URL] ....)

Database BizTalkMsgBoxDb has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

View 3 Replies View Related

SQL Express-solving 4GB Size Limit By Multiple Database Files?

Nov 19, 2006

Hi,I am new to SQL express and try to solve the 4GB size limitation.Is there a possibility to create a new database file every time I getto the limit?How can I do that with C#? how can I create new database file everytime it gets full?Can I be connected to two database files at the same time (the full dband the new db)?thanks in advance,oren

View 1 Replies View Related

Transact SQL :: How To Rollover Trace Files Once It Reaches Max File Size

Aug 18, 2015

I am trying to trace few events using profiler Via T SQL like below,  the below scripts works fine, but once the file reaches 1GB , it is not rolling over to another file and start trace again.I tried using URL...once it reaches max file size provided between the time interval..

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
set @DateTime = '2015-08-18  16:00:00.000'------Stop Time 4 PM 
set @maxfilesize = 1024  --1GB max File size 


View 2 Replies View Related

Help With Initial Set Up

Jul 25, 2007


I have been using a licensed copy of Visual Studio 2005 and MS SQLServer 2005 for some time but am only now trying out the Reporting Services functionality.

I have attempted to follow the instructions from url:

However despite confirming that the report services are running and also checking the configuration by following the information in the above web page I still get the following problems.

1. When attempting to create a project via the wizard I get the following error: Exception has been thrown by the target of an invocation. If an attempt is made to add a new data source I am unable to choose the data source type (eg: Microsoft SQL Server). I am not given a choice for the type, in fact the relevant drop down is blank.

2. A project can be created without the wizard but if I right click the Shared Data Source folder I do not see the next Pop up to set the data source parameters.

I assume I am missing something quite fundamental - however so far I cannot see what.

Thanks in advance for any advice...

Regards Andrew

View 5 Replies View Related

Initial SQL Password

Jun 25, 2007

Hi, trying to use SQL server for the first time, and it's asking for a password that I don't know. Any guidance on that? Thanks.

View 5 Replies View Related

Need Some Initial Direction

Jan 23, 2007

Hi all. The company I work for is looking for a new SQL server. Where can I find information and or a tool for sizing information? By sizing information I mean how big a pile of hardware am I going to need to run MS SQL for x number of connected users with x size database, etc. I've been tooling around the internet and MS' site but can't find any info on this.

Can anyone shed some light for me?

View 4 Replies View Related

Copyrights 2005-15, All rights reserved