Initial Size For DB Files

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 Replies


SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.

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 Replies View Related

Tempdb Initial Size

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 Replies View Related

File Initial Size

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 Replies View Related

How To Resize Initial Size?

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 Replies View Related

Transaction Log - Initial Size Too Big


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 Replies View Related

Changing The Log Initial Size


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 Replies View Related

Newbie Question About Initial Table Size

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 Replies View Related

Performance - Automatic Expansion Vs Setting Large Initial Size.


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 Replies View Related

Data Files Size And Log Files Size

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

View Replies View Related

Changing Value Of &"Initial Size (MB)&"

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 Replies View Related

Changing Value Of &&"Initial Size (MB)&&"

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 Replies View Related

How To Get The Images Files Size?

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 Replies View Related

MDF And LDF Files Size Command

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 Replies View Related

Different Size Of Data Files~~

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 Replies View Related

RESTORE DB Into Different Size Files

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 Replies View Related

Size Of Backup Files

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 Replies View Related

Deployed Files Reduced Size

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 Replies View Related

Size Limit On Text Files To Import With DTS

We are trying to import an ASCII text file into SQL Server 7.0 using DTS wizard.
The file has fixed-length fields and we are trying to map the fields to the database columns.
When we get to position 4189, there is a black line displayed which we cannot move or delete.
We cannot map any fields after this line either. Is there a size limit on a source file? We have
tried deleting and re-adding characters at this point in case there are special characters in the
file, but that doesn't help. Needless, to say, this is very frustrating, because we also keep losing
the mappings we have already made.

Any ideas? Thanks a lot.


View Replies View Related

Reducing The Log Files Size In SQL Server 2000

Hi all,

I have the following data and log files:
cp_1_Data 1, 024 KB
cp_1_Log 23,149,312KB
cp_date.MDF 512,000KB
cp_Log.LDF 1,024KB

How can I reduce the capdb_1_Logto smallest as possible?

I tried the following command:


use cp
dbcc shrinkfile ('cp_1_Log', {100})

But I got error:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

Thanks for any help:-)


View Replies View Related

Balancing Size Of Data Files In Filegroup

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 Replies View Related

Disaster Recovery: Calculate DB Size Based On .dmp Files

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 Replies View Related

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

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 Replies View Related

Enum Ntfs Cluster Size For Files Used By Databases On Server

Greetings experts!

We are having ourself a datwarehouse with multiple 32GB servers, and disk and network infrastructure that fits with this.
I'm going through the installation and want to make sure ntfs cluster size is appropriate.

(Don't know if we are to SAN or local disks. It may switch at any time. If we are to use SAN, they seem capable of setting it up).

Q: Am i right to assume that 64 KB cluster size is the right choice for volumes containing the databases?
- I guess we'll minimize logging and use TABLOCK for both updates where IO is high.
(Please underline answers with rationale)

Q: How can I (with sql) ensure that files used are on volumes with 64 KB cluster size?
(I can manage the enumerating of databases and their files, it's the querying of ntfs cluster size I can't remember how to do)


View Replies View Related

Help With Initial Set Up


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 Replies View Related

Initial SQL Password

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 Replies View Related

Need Help About Initial Snapshot

i have created a publication whereas i have provided a network path to its snapshop folder e.g ( \serverfolder ) at time of creating. When i try to make a Pull Subscription and follow all steps of wizards, it gives me following error "The initial snapshot for publication '---' is not yet available". can you guide me what are causes of this problem and how may i solve it?

Thanks for help.

View Replies View Related

Need Some Initial Direction

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 Replies View Related

Initial Case Query

I want get get results in sql that are all written in UPPERCASE but I want to receive them in Initial Case format
lowercase is lower
but what is Initial Case(first letter Capital in a word)

View Replies View Related

Initial Snapshot Error

Hi all,

I am Using Sql Server 2000 as publisher and and Sql Server Mobile Edition as Subcriber.......Now my problem is while creating publisher for one Database,in the snapshot location the Snapshot.exe is not created.

when i start synchronization it leaves me error :

Thread=304 RSCB=17 Command=SYNC Hr=80045003 The initial snapshot for publication 'DB_Invetory_imp' is not yet available. -2147201021

My publication name is Any body please help me in resolving these error plzzzzzzzzzzzzzzzzz.


Ayyappa K

View Replies View Related

Getting The Middle Initial From A Name Column

-- I have a first name field that sometimes contains only the first name,
-- sometimes contains the first name and the middle initial, and
-- sometimes contains the first name, a space, followed by NMI (for no middle initial)
-- how do I correctly grab the first letter of the middle initial in all cases?
-- I have been playing with patindex but its harder than I thought. guess I need a case
-- statement in addition to this. Any idea how I can do this?
-- thanks!

create table UHS_t1 (c1 varchar(20))
insert UHS_t1 select 'john a'
insert UHS_t1 select 'jeff b'
insert UHS_t1 select 'sue z'
insert UHS_t1 select 'joe nmi'
insert UHS_t1 select 'jamie'

select *, substring(c1, patindex('%[ ]%', c1)+1, 1) as middle_name
from UHS_t1
drop table UHS_t1

View Replies View Related

Display Of Initial Catalog


I'm not a sql server savvy, so I need assistance on the following two scenarios:

A customer runs a script like this (slightly larger, but I ripped away the meat)
create database test_database

USE [test_database]
exec sp_changedbowner 'sa'

use master;
sp_grantlogin 'server01CUSTOM_ADMIN';

use test_database;

-- lots of table creations, where one of the tables are TEST_TABLE

sp_addrole 'ADMIN_ROLE';
sp_grantdbaccess @loginame = 'server01CUSTOM_ADMIN', @name_in_db = 'USER_ADMIN';
sp_addrolemember @rolename ='ADMIN_ROLE' , @membername = 'USER_ADMIN';


Now, if a person is added to the server01CUSTOM_ADMIN group, he/she should be able to do the following: (let's say it's a he)

- Create a test.udl-file (win xp). Set a provider to sql server.
- On the connection-tab enter hostname of database server in the Data Source-field and use windows NT Integrated security.
- When he now test the connection, it should work, since he has access to the database.
- Also, using the dropdown "3. Enter the initial catalog to use:", he should see SOME datatables. Not ALL, not none. The ones that he has access to.

So, if TEST_DATABASE is the only access that server01CUSTOM_ADMIN has, that database and only that one should show, right?

In my customers scenario, some databases show (irrelevant ones), but not TEST_DATABASE. In my test, I still get ALL databases. Even after I rip the guy out of the Administrators-group and Users-group. He's only a member of the CUSTOM_ADMIN-group on server01. "Test connection" succeeds, and all the databases on the server shows.

What I hope for is following questions like "He's probably sysadmin, check it" etc, so that I can systematically (using your brains) filter out the reasons for these scenarios to happen.

Thanks in advance.

View Replies View Related

Copyrights 2005-15, All rights reserved