New NT/SQL Install, Drive Partitioning
Dec 23, 1999
We have purchased a new Server at my company. We previously had two servers. One was the NT file server and then we had another SQL Server. Well, we've bought one box to replace both old boxes. I have 3 18.2 GB SCSI drives running on a RAID-5 configuration. I've partitioned the C: drive as 2GB and it will only be used for NT files and NT Utility apps.
On the old system I had all my data files on one physical drive and all of my logs on a seperate physical drive. Now, with RAID, the three drives act like one big 36GB drive. Finally, my question...
How should I partition this drive? Should I put the SQL Server App, my data files and my logs all on one partition? Should they each have their own partition? Should it be a mix (App & Log, Data & Log, etc.). Also, how much space do I need just for the application if I put it in its own partition?
I've never installed SQL Server before, so if anyone has any other suggestions, tips, tricks, etc., I'd love to hear it. Either post here or e-mail me responses.
Thank you all very much
Merry Christmas!
View 1 Replies
ADVERTISEMENT
Oct 25, 2001
Hi,
Just got a new box and want to fully optimize the server. I was contemplating where to put the transaction log? I have 2 drives:
Drive C: 30 Gig(Operating System)
Drive E: 180 Gig(Database file)
I'm planning to build the database in Drive E:, but am leaning to put the transaction log in drive C:. Do you think it's a good move? I want to separate the log and data files, but Drive C: is host the operating system. Do you think it would be wise to partition drive C: and put the operating system on one partition and the Transaction log on the other? Does it enhance performance in anyway?
Thank You!
Joe
View 6 Replies
View Related
Aug 7, 2007
We are currently having a "lively" discussion between our DBAs and Server Builders and I am interested in what others are doing. Our DBAs want to take the default SQL install into the C: drive, making sure that NO logs are written to C:. That way there is no chance for the C: drive to become full, crashing the box. Server guys are insisting that we install to a drive other than C:.
I would appreciate any discussion re: what others consider best practice , industry standard, if such a things esist.
Thanks.
View 2 Replies
View Related
Mar 19, 2007
Is there a way to install SQL 2005 Express on a different drive? The server that I want to use has 700MB availible on the C drive and 80 GB availible on the D drive. can I install SQL and all tools on the D drive?
View 3 Replies
View Related
Oct 7, 2004
When I ran the install for MSDE, it installed the DB server and the databases onto my C: drive and under program files...
The instructions I followed were listed here:
http://www.asp.net/msde/default.aspx?tabindex=0&tabid=1
I'd like to be able to install it to my D: drive. I've looked through all of the docs but I can't find any information on how to change the install path. Would anyone know how to do this?
Thanks,
LD.....
View 2 Replies
View Related
Jun 11, 2008
Hello Everyone,
I wasn't able to find the answer to my question by searching the forum. I would very much appreciate if anyone would help me.
I need to install the SQL Server Workgroup trial. I wasn't able to find the workgroup trial so I was trying to install the enterprise instead. (If anyone knows how to get ahold of the workgroup trial that would be a help too)
I keep getting the following error when running the SQLEval.exe:
There is not enough space on drive C: to extract this package
I did an internet search on this error and it seems there is a bug that requires the amount of free space not be a multiple of 4. I currently have 13.2 GB free space and I have tried adding and removing files, to no avail. I have tried downloading the file in a few different versions in case the file was corrupted, and rebooting etc, but still I keep getting the same message. I only have one drive!!!
Can anyone help me with this?
View 8 Replies
View Related
Oct 22, 2007
I created two partitions for my hard drive. The C drive which holds all of the Windows 2003 server operating system files and E drive which will be just for data and applications. The C drive is only 30GB while the E drive is 119GB. I need to be able to install in the application on the E drive. I already tried creating a folder on the E partition named program files with the sql2005 folder in it when I go through the installer package. SQL server still wants to be installed on the C drive. Is there anyway I can change this so it gets installed on the E partition. Thank You
View 1 Replies
View Related
Oct 29, 2007
I am trying to install the SQL Sever 2005 Workgroup Edition software that came with our Small Business Server 2003 R2 system. I would like to install to the d: drive because space on the c: drive is limited. Can I do that? If so, how?
View 1 Replies
View Related
Oct 22, 2007
I created two partitions for my hard drive. The C drive which holds all of the Windows 2003 server operating system files and E drive which will be just for data and applications. The C drive is only 30GB while the E drive is 119GB. I need to be able to install in the application on the E drive. I already tried creating a folder on the E partition named program files with the sql2005 folder in it when I go through the installer package. SQL server still wants to be installed on the C drive. Is there anyway I can change this so it gets installed on the E partition. Thank You
View 3 Replies
View Related
Jan 12, 2005
On the drive that SQL server is install on which is the C drive the amount of space is 495mb, can the amount of space be a potential problem, I am so used to looking at the drives that contains the data and log files and the backup.
So My question is should I also concern myself with the drive the sql server itself is installed on?
Thanks
Lystra
View 5 Replies
View Related
Aug 20, 2007
I am trying to install SQL Server 2005 Express Edition onto my PC on the D: drive. I do not have write privelleges to the C Drive. All I want to do is point the executable to install to another drive like D: or E: etc. Is this possible with SQL Server 2005 Express Edition setup executable file.
Just to let you know I have changed the template.ini file in the source folder to reflect the following (I tried both with and without quotes)
INSTALLSQLDIR="D:Program FilesMicrosoft SQL Server"
And then using command prompt I did the following:
"start /wait setup.exe /qn myini.ini"
and it still doesn't work.
View 1 Replies
View Related
Apr 4, 2008
Hi
I have been trying to use openrowset with a shared drive, and even though the share has "full control" permissions granted to "everyone" and the accout that SQL runs under has been granted explicit full control permissions I am unable to open the file which itself has no security on it.
Can I not use a \ path and only use mapped drives?
Thanks
below works...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:5People.xls', [Sheet1$])
below doesn't work...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\cluster02FileManager5People.xls', [Sheet1$])
View 3 Replies
View Related
Nov 9, 2000
I am trying to move a log file from one drive to another.
What I have done is add another file to my file group. So now my log has a file on the 'e' drive and one on the 'f' drive. I now want to remove the file on the 'e' drive. I have emptied the file on the 'e' drive. When doing the command:
ALTER DATABASE Uniprodruntime
REMOVE FILE m_rk_runtime_log
I get the following error message..
Server: Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.
I have also gone into enterprise manager and tried to delete the file and it does nothing.
Has anyone run into this?
View 2 Replies
View Related
Feb 12, 2007
How do you compare SAN drive vs local drive on a 32 bit server?
Is it good idea to move my DB files to a SAN instead of local?
Canada DBA
View 4 Replies
View Related
Nov 28, 2015
Server: SQL 2008 R2
1: TempDB keeps getting filled. Restart of the server has not fixed it. I shrink it, but the space gets filled again. Now I can't even shrink it anymore
2: TempDB is at the wrong location. Its current location is this :C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLPROD6MSSQLDATA empdb
How do I change its location?
C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLPROD6MSSQLDATA empdb
Correct location of TempDB should be: TempDB(T:) But its not there
View 13 Replies
View Related
Jan 31, 2008
Being a very novice SQL Server administrator, I need to ask the experts a question.
How do I go about moving a database from 1 drive to another? The source drive (C is local to the server, but the target drive (E is on a Storage Area Network (SAN), although it is still a local drive for the server. I want to move the database from C: to E:. Can someone provide me with instructions?
Thanks,
Rick
View 4 Replies
View Related
Jan 16, 2015
How to backup half of dbs from a server on C drive and the other half on D drive and vice versa, first half on D drive and other half On C drive using only one job and one stored procedure??
Using scheduling from job add 2 schedules to the job so first schedule backup first half to C and second half to D , the second schedule backup first half to D and second half to D.
View 1 Replies
View Related
Sep 10, 2007
Hello
I have a table containing 100,000 record for each year, and every year a new 100,000 record are inserted,I need to know how to make partion this table by year
I need to know the syntax
Thank you
View 5 Replies
View Related
Jul 25, 2006
I have begun my quest to become familiar with SQL 2000 table partitioning and have had some success.
I've partitioned tables according to the "rules" and guidlines of other papers and the show plans look good for all cases.
But when I went to create another demo series, I got stuck.
I've created two tables, with the appropriate CHECK constraints, and with a "union all" view.
When inserting data, it goes to the right tables.
When reading data with a where clause like:
col1=10,
the show plan shows the query getting the data from the ONE correct table.
BUT
When the where clause looks like:
where col1 between 1 and 10:
the showplan shows the query getting data from both tables, instead of the table that 1 to 10 belongs to.
Help!
View 2 Replies
View Related
Apr 22, 2008
Hi guys,
1) I'm about to implement SQL Table/Index Partitioning for the first time. It seems really good, no downside. Is there an "Gotcha's" I gotta be weary of?
2) My tests show a clear performance gain. Is there a performance loss in other areas?
In "Designing Partitions to Improve Query Performance" it says this:
"In this way, the SQL Server query optimizer can process the join faster, because the partitions themselves can be joined. If a query joins two tables that are not collocated or are not partitioned on the join field, the presence of partitions may actually slow down query processing instead of accelerate it."
What does that mean?
If I have:
Select A.Col1, B.Col3
from A
join B on A.Col5=B.Col6
Does that mean I have to partition A.Col5 & B.Col6 the same way else my queries will get slower?
3) Does it take up more space? Do restores take longer?
4) How much maintenance is involved?
Is there a way of automating the creation of new partitions? Or do you just create loads that you'll need in the future e.g. always have a few months' partition created ahead of time?
The "Designing Partitions to Manage Subsets of Data" article in BOL seems to indicate some manual work every month (if you have a monthly partitioning strategy).
5) Why not partition every table that has over say a million rows?
6) If I create a Yearly Partition on a table that has been around for a long time, will it automatically partition all the indexes on that table? Or do you have to re-create the indexes for that to happen?
7) Neither table can have a full-text index in a partition move. Does that mean you can't ever partition tables with full-text indexes?
8) Partitioning doesn't seem to fit well with replication. Do you have to end up choosing between the two?
View 7 Replies
View Related
Mar 27, 2008
i have a table named "user" in which user which are located at different places within a city are recorded.
i want to group user with respect to there location like users of northern region are recorded first then users of western region and so on.
tell me from horizontal and vertical partitioning wh technique is better or i should use some other technique.
thanks for ur consideration.
View 5 Replies
View Related
Jan 24, 2002
Hello all. My company's in the process of configuring our new Dell server boxes. We're contemplating whether to partition the drive (where DB will reside) into 1 large partition or several smaller partitions.
What are the pros and cons of each option? Any suggestion will be greatly appreciated.
View 1 Replies
View Related
Nov 14, 2000
Hi,
I want to know more on table partitioning.I do not know where to get the right info.from.
I have a doubt - if a table is partitioned horizontally how does a query identifies where to pick up the data from i.e. from which part of partitioned table?
View 2 Replies
View Related
Oct 9, 2004
I'm faced with a project that requires the caching of vacations.
Each vacation has a departure date & a price.
The amount of different vacations that will need to be cached is probably near 1 million per day.
I will then need to select the price(s) of vacations for either a single day or a date range (based on the vacation criteria).
I was considering creating a new partition (table with a date on it) every day.
This would allow me to jump into the needed table(s) based on the vacation search criteria. This would also allow me to drop tables with past dates.
I was considering running this all on 1 sql server. I was hoping I could create multiple threads for a datespan search and hit all the tables in the daterange at the same time.
Can you guys enlighten the noob on where I really need some help on this?
View 1 Replies
View Related
Apr 3, 2006
Hi,
I am trying to implement partitioning on a table
depending upon the fiscal_month value...
The current values are from 1-6...
Create partition function LoadDataPartitionFunction ( smallint)
as
Range for values (1,2,3,4,5,6)
-- drop partition scheme LoadDataPartitionScheme
create partition scheme LoadDataPartitionScheme
as
Partition LoadDataPartitionFunction ALL to ([PRIMARY])
CREATE TABLE Load_Data_Partition (
[RowID] [int] NOT NULL,
[Fiscal_Month] [smallint] NOT NULL,
[Fiscal_Year] [smallint] NOT NULL,
....
[Service] [nvarchar](100) COLLATE
) ON LoadDataPartitionScheme (Fiscal_Month)
truncate table Load_Data_old --- same schema as load_data_partition
Alter table load_data_partition switch partition 1 to Load_Data_old
-- which month's data to be moved out
alter partition function LoadDataPartitionFunction () merge range (1)
Alter partition scheme LoadDataPartitionScheme next used [primary]
-- which months data to be moved in
alter partition function LoadDataPartitionFunction () split range(7)
Select * from sys.partition_range_values
function_id boundary_id parameter_id value
----------- ----------- ------------ -----
65545 1 1 2
65545 2 1 3
65545 3 1 4
65545 4 1 5
65545 5 1 6
65545 6 1 7
Alter table [Load_Data_new] switch to [Load_Data_partition] partition 6
ALTER TABLE SWITCH statement failed. Check constraints of source table Load_Data_new' allow values that are not allowed by range defined by partition 6 on target table 'Load_Data_partition'.
Values in Load_Data_new for fiscal_month is 7
But when i try
Insert into [Load_Data_partition]
Select * from [Load_Data_new]
where fiscal_month = 7
it works fine...
reference used : http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20I I.htm
View 1 Replies
View Related
Nov 21, 2006
hi all,
before i had a big database more than 90GB and it's growing very sharp so what i did is to create a summary database that gonna hold some aggregation and i create also three tables that gonna hold the data, on will hold the data for the last three months (very detailed information) one between 3 months and a year(less detailed) and the last one older than a year(less less detailed) these are the company requirement i'm working with.
then i transfer the data to the new tables with stored procedures.
now my question is: do i need to create a view for these three tables? and if yes how should i do it?!! cus i do not have the same columns in the three tables.
thanks experts.
View 1 Replies
View Related
Jun 16, 2008
i want to partition a table containing about 3 million rows. The partition column will be of datetime type.
following is the partition function i have used
create partition function MyPartFun
(datetime) as range left for values ('07/30/2007','09/30/2007','11/30/2007','01/30/2008','04/30/2008')
following is the partition scheme i have used
create partition scheme PartScheme as
partition MyPartFun all to ([primary])
i know how to add partition column while creating the table
But dont know how to add above partition scheme to an already populated table
Plz help...
View 2 Replies
View Related
Jan 4, 2007
Snehalata writes "does view for vertical partitioning improves the performance, since the view will have all the columns which exist in the original table(without partitioning?"
View 1 Replies
View Related
Apr 24, 2007
Hi,
I have a database created using Enterprise Manager Wizard.
For example datafile db1_data.mdf and log file db1_log file exists.
All the tables are created in datafile db1_data.mdf.
Now to improve performance I want to implement table partitioning.
Can anybody tell me howto implement it with existing strutcure.
Suppose there is table Mytable in which all update and delete actions are performed regularly.And it contains about 10,0000 records.
I want to partition the table so that it contains 5000 records.
Solution with example highly appreciated.
Satish
View 7 Replies
View Related
May 4, 2007
How to partition tables in dynamic way?
I want to partion a table based on the client specific id and I want these values (client id's) to be passed dynamically to the create partition function.
I am not familiar with partitioning so it will be great if someone guides me (I am also reading some articles on partitioning, but it will be easier with some help)
The table I am trying to partition has like 80 million rows with four client's data as of now and will be more once we implement new clients.
I also think Partition will help, because before we load a client's data, we remove the data that is already out there (we flush previous qtr data before we insert this qtr data)
Any help will be appreciated.
Thanks
Raj
View 4 Replies
View Related
Jun 6, 2006
two questions:
Im using Snapshot replication;
1. how can I replicate database1.table1 to database2.tablex1
by default replication will replicate it to database2.table1
2. If i have database2.table1 sitting on 10 partitions
where table is partitioned on PK_x where x is divided by 10
how can i make tableX1 to be a member of database2.table1
sitting on the 7th partition. that is pk_x = 70-79
partition schemes and function are already set
thanks
View 4 Replies
View Related
Dec 13, 2007
Hi Experts,
I am new to Table Partitioning, Can any body guide me how to do table partitioning?
any way here is my scenario, we are having one database called "DATA" in SQL 2000 server and we have migrated to SQL 2005 by using backup and restore. and "DATA" is having about 15 tables and they are very very very big in size. and they dont have any index on a coulum name "DATETIME", but i want make table partition according to that perticular field "DATETIME" and right present we are having 6 months of data.
So, how to proceed further?
Your help will be appreciable..
View 1 Replies
View Related
