Can someone help me to optimise this create db?
CREATE DATABASE EVSTORE ON PRIMARY
(
NAME=evstore_data,
FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL\Data
\db1.mdf',
SIZE = 10 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 20000 KB
)
LOG ON
(
NAME=evstore_log,
FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL\Data
\db1.ldf',
SIZE = 10 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 20000 KB
)
I googled this to put the database on the E: drive which is a SAN.
This db will grow and have lots if rows increasing every day, so I
want to avoid bad practice, and have fast queries event when it has
got big after a year.

And suggestions to make this better, please?

Syd

You need to do some capacity planning. Figure out how much growth your DB
will go through during the lifetime of the machine and allocate that plus
some contingency. This way, you avoid disk fragmentation.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"syd_p" <sydneypuente (AT) yahoo (DOT) com> wrote

Can someone help me to optimise this create db?
CREATE DATABASE EVSTORE ON PRIMARY
(
NAME=evstore_data,
FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL\Data
\db1.mdf',
SIZE = 10 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 20000 KB
)
LOG ON
(
NAME=evstore_log,
FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL\Data
\db1.ldf',
SIZE = 10 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 20000 KB
)
I googled this to put the database on the E: drive which is a SAN.
This db will grow and have lots if rows increasing every day, so I
want to avoid bad practice, and have fast queries event when it has
got big after a year.

And suggestions to make this better, please?

Syd

syd_p (sydneypuente (AT) yahoo (DOT) com) writes:
To achieve that, you will need to get more things right than just the
CREATE DATABASE statement. But at least it's a start. But 10 MB is not
a good start, if you execpted it to grow. If you exepct it to be 10 GB
in a year, create it as 10 GB now. As Tom said, you need some capacity
planning.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 22 Jan, 22:41, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Can someone help me to optimise this create db?
CREATE DATABASE EVSTORE ON PRIMARY
(
NAME=evstore_data,
FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL\Data
\db1.mdf',
SIZE = 10 GB, MAXSIZE = UNLIMITED, FILEGROWTH = 20000 KB
)
LOG ON
(
NAME=evstore_log,
FILENAME='E:\Program Files\Microsoft SQL Server\MSSQL\Data
\db1.ldf',
SIZE = 10 GB, MAXSIZE = UNLIMITED, FILEGROWTH = 20000 KB
)
Good point - I reckon it might be 10 GB in a 6 months/year
How does it look now?

syd_p (sydneypuente (AT) yahoo (DOT) com) writes:
I'm afraid that your question is not very meaningful. Getting good
performance is more than getting the CREATE DATABASE statemetn right.

Here you have set the autogrow to 20 MB, which is not very much. This
means that when the database starts to grow, it will grow in small
pieces, which leads to fragmentation on disk.

Then again, if you set autogrow to 1 GB, an autogrow can cause a
noticeable stall when it happens. In such case 20 MB may be a better
choice.

The true answer is that autogrow should never occur, but the database
should be grown under planned circumstances in a maintenance window.
But since I don't know anything how you plan to monitor this database,
I cannot really give any advice.

You have also put both data and log on the same disk. This is certainly
not a good idea at all, but they should be on different disks, and on
different LUNs in the SAN.

Then again, I know nothing what you will use this database for, and
without any information about the context all advices could very well
be misguided.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

I reckon the advice above is real good - Thanks.
I was wondering how big the log file might get - I have made it the
same size as db file.

syd_p (sydneypuente (AT) yahoo (DOT) com) writes:
That's very difficult to tell with knowledge about what the system does.
But if you never back up your transaction log, the log will become very
big!



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx