Initial 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.
ADVERTISEMENT
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?
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 RelatedChanging 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
Newbie 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
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.
Autogrowth And Initial Size Setting For Database
Jul 8, 2015give me the best autogrowth & initial size setting for database
View 7 Replies View RelatedHow 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.
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 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
Data Files Size And Log Files Size
Sep 22, 2004Hi!
I'm using SQL Server 2000!
How to know then Data files size and Log files size by Store Procedures or Sql query?
Thanks!
LDF Files Size Gone To 200 GB
Jul 5, 2013My 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.
How To Get The Images Files Size?
Jul 21, 2004Hi,
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)
p@ywen.
Size Of Backup Files
Jun 25, 1999I 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?
Thanks,
Mike
RESTORE DB Into Different Size Files
Aug 11, 1999I 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?
Different Size Of Data Files~~
Aug 14, 2006hi
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!!! :)
MDF And LDF Files Size Command
Jul 23, 2005Hi 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 http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedDeployed Files Reduced Size
Apr 20, 2006I 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
T-SQL (SS2K8) :: Getting Files Size For Each Database File?
Oct 28, 2014I would like to add to it the actual file size in mb or gb of each file to the results.
select sd.name,mf.name as logical_name,mf.physical_name,
case
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?
Balancing Size Of Data Files In Filegroup
Apr 26, 2008Due 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.
Transact SQL :: How To Calculate Size Of Files That Have UNC Paths For
Mar 27, 2014I 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 RelatedDisaster Recovery: Calculate DB Size Based On .dmp Files
Feb 7, 2000I 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.
`Ken
SQL Server 2008 :: Virtual Log Files And Determining Right Growth Size
Oct 14, 2015Any 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.
SQL Express-solving 4GB Size Limit By Multiple Database Files?
Nov 19, 2006Hi,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 RelatedTransact SQL :: How To Rollover Trace Files Once It Reaches Max File Size
Aug 18, 2015I 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Â
[code]...
Help With Initial Set Up
Jul 25, 2007Hi,
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:
http://www.microsoft.com/technet/prodtechnol/sql/2005/usingssrswithsqlexpress.mspx
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
Initial SQL Password
Jun 25, 2007Hi, 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 RelatedNeed Some Initial Direction
Jan 23, 2007Hi 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?