additional data files not filing

We have a quad sql server that runs OLTP transactions at the rate of
100's per second (read & Write).

We used to have all the tables on 1 file but started to notice high contention on this file. We added 3 more files to match the processor number. The problem is that the 3 additional files are not filling with data. Does anyone know why this happens or can reccommend a fix?
--
willVerify that the newly added files are in the appropriate file group (probably PRIMARY) and are of the appropriate file type (ie, they were created as data files, not log files). Also, your new file(s) may not get data written to them if the old file still has space available.

Your logic for reducing contention by adding files to match the number of processors does not make sense. Contention is caused by physical IO going to the same physical disk and getting backed up because the pipe to the physical disk is too small. You would add files to a database filegroup to ease contention by creating the files on separate physical disks. It has no relation the number of CPUs.

Regards,

hmscott|||Yes they are all the same file type (data files).

Microsoft claims that you should have a seperate file per processor for high contention OLTP sql servers. Parallelism is the goal here. Sql server can only access 1 file per cpu at any one time. If it was a dual processor machine It would have less impact considering the OS & tempdb usually have something going on in parallel.

Considering we have a quad sql server = 4 files per file group.

As an FYI we are running a raid 10 with 14 disks.|||Hmm, not doubting you, but could you send a link with the MS info? I had not seen that before.

As for your issue, what's the state of the original data file? Full, nearly full or not even close? Also, can you verify that the new files you added were updateable (ie, not read-only)? Beyond that, I would try testing some scenarios out on a development server somewhere.

Regards,

hmscott

adding secondary database file

Question: When you add secondary files to an already
existing database does SQL automatically stripe existing
data across the new files or only when new data is created?I believe only if you add new data, or do things like rebuild a clustered
index (assuming both files, and the table, are in the same filegroup).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"John Gossett" <jgossett@.nwedge.com> wrote in message
news:008e01c3c666$b3fbe6e0$a601280a@.phx.gbl...
> Question: When you add secondary files to an already
> existing database does SQL automatically stripe existing
> data across the new files or only when new data is created?|||SQL uses a process called proportional fill. It tries to keep each of the
data file the same percentage full. It does this by writing all new data to
the new file until it is as full as the existing data file..
It does NOT re-balance the existing data. You must do this by drop/re-create
the clustered index or export/import the data..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"John Gossett" <jgossett@.nwedge.com> wrote in message
news:008e01c3c666$b3fbe6e0$a601280a@.phx.gbl...
> Question: When you add secondary files to an already
> existing database does SQL automatically stripe existing
> data across the new files or only when new data is created?

adding precedence to multiple files

hi all,

i have a package here which updates a DB from a flat file source.now the problem is i may get multiple files.i have used a for each loop to handle this. it takes files based on the files name9(file names has a timestamp in it).but i want to give files in order of its Creation time.

Please help me on this.i have written a script task before the for each loop and i have got the minimum creation date from all the files,i am not able to going forward from here.

does any body has an idea!!

ASAIK, the files are show in creation order in the for each loop. However, I am not sure this is gospel.

You could use a script task to load a list of files and sort the list by the file attributes. using this sorted list, you could then loop over the list using the for each loop container.

If you wanted to get clever, modify the For Each Directory example in the MS SQL 2005 example. Create your own file enumerator, ensuring the files are in the order you want.|||Generally, it does sort by filename - but there are no guarantees of this. If you want to ensure the sort order is correct, follow Crispin's advice to modify the For Each Directory sample, or use the sample posted here (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1439218&SiteID=1) by jaegd. Or read your directory, save each filename to a temp table, and use SQL sorting.

Adding PDF indexing with Sql Server 2005

I am trying to use the full-text indexes with Sql Server 2005, and wanted to
index pdf files. I have downloaded and installed the Adobe PDF iFilter from
Adobe, and have tested it using the iFiltTst on a directory with PDF files.
It does index the pdf files.
I then moved the files into a sql server table into a varbinary(max) field.
I re-popualted the index, but it is not being indexed. Also, when I use the
"sp_help_fulltext_extensions" command, the ".pdf" extension does not appear.
Any suggestions on how to get this to work?
Steve
issue the following
sp_fulltext_service 'verify_signature', 0
sp_fulltext_service 'load_os_resources',1
bounce sql server and it should work (provided you have the PDF iFilter
installed).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Steven Paplanus" <sapaplanus@.hotmail.com> wrote in message
news:u0j55Zc8EHA.208@.TK2MSFTNGP12.phx.gbl...
> I am trying to use the full-text indexes with Sql Server 2005, and wanted
to
> index pdf files. I have downloaded and installed the Adobe PDF iFilter
from
> Adobe, and have tested it using the iFiltTst on a directory with PDF
files.
> It does index the pdf files.
> I then moved the files into a sql server table into a varbinary(max)
field.
> I re-popualted the index, but it is not being indexed. Also, when I use
the
> "sp_help_fulltext_extensions" command, the ".pdf" extension does not
appear.
> Any suggestions on how to get this to work?
> Steve
>
|||This will work, but beware that making this change makes your SQL instance a
little less secure.
Be sure to read the documentation for these flags so you understand the
risk.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:efcPGQt8EHA.3700@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> issue the following
> sp_fulltext_service 'verify_signature', 0
> sp_fulltext_service 'load_os_resources',1
> bounce sql server and it should work (provided you have the PDF iFilter
> installed).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Steven Paplanus" <sapaplanus@.hotmail.com> wrote in message
> news:u0j55Zc8EHA.208@.TK2MSFTNGP12.phx.gbl...
wanted
> to
> from
> files.
> field.
> the
> appear.
>

Adding ndf files

Hi I have prod DB about 16GB and only single file we plan to move to enterprise artitecture and will be more used in future so planning to add more data files for parallelism and better performance. My idead is to have additional 20GB ndf files but ho wdo i configure the existing datafile and addiotnal ndf files

Do i restore the DB with a single file and add other files but how do I make sure that the data is spreading across evenly. Can i limit the original file to 20GB and add the other files as 20GB

PLS can anyone give me som ehelp

AdminAnup

restore the database and make sure it's working correctly. Then use 'alter database' statement to add the ndf file to a different filegroup. Then you can re/create a clustered index with drop_existing to move the table(s) to the new datafile.

Take a look at 'alter database' and 'create index' in book online for additional details.

|||yes I understand that part my question was more on the autogrow properties and how data is spread across. All these are in single filegroup

say restoring the DB there is one file 16GB then what are the options for this I mean do I limit it to be 20GB

then I add 2 more ndf files each with 20GB what are the options like do I set autogrowth by 1GB or 10%

I am not sure how data is written in each of these and for each the settings of autogrowth to make sure I have a cnsistent 20GB 3 files

Adding Misc files to Solution Explorer

I assume this is the best forum for this quesiton; if not, please direct me.

I have noticed that I can add (for example) a text file to a misc folder in a SQL Server project by dragging and dropping the file from Windows Explorer onto the Misc folder inside the SQL Server project. Is this the only way to add a file?

I noticed, for example, I could not copy and past to the Misc file folder.
Is there another way? If so, what is it? what is the preferred way?

I noticed that there have bee 55 views of this thread. It was posted Monday morning. would someone take a stab at it?|||The other way to add a file is to right click on the Project, select Add Existing Item, then Browse to the file in question (you need to change the File Type at the bottom to "All" in order to see text files, etc.).

Thanks, MJ

Adding files and filegroups

Does SQL Server keep track of when new files and
filegroups are added or removed from a database, e.g., in
the form of a timestamp in the catalog? and how can a user
access this information?
Thank-you, Howardsp_helpdb will tell you more about the databases
sp_helpfilegroups returns the names and attributes of filegroups associated
with the current database
sysfiles : Contains one row for each file in a database. This system table
is a virtual table; it cannot be updated or modified directly.
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"Howard" <anonymous@.discussions.microsoft.com> wrote in message
news:07bc01c3d14e$04b438c0$a601280a@.phx.gbl...
> Does SQL Server keep track of when new files and
> filegroups are added or removed from a database, e.g., in
> the form of a timestamp in the catalog? and how can a user
> access this information?
> Thank-you, Howard|||I would assume this info would be in sysfiles or sysfilegroups. It's not
there, so I don't think it's possible to get from SQL Server.
But...
you CAN find the time a file was added.
Create date is kept by the OS for a file. Something like this would work:
xp_getfiledetails 'c:\temp\authors.txt'
But that would be the create date from an OS perspective. You would end up
with wrong info if for example you restored the database and overwrote
files, since they would then have new create dates...
or you could create your own polling program that periodically looks at the
relevant system tables mentioned above...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Howard" <anonymous@.discussions.microsoft.com> wrote in message
news:07bc01c3d14e$04b438c0$a601280a@.phx.gbl...
> Does SQL Server keep track of when new files and
> filegroups are added or removed from a database, e.g., in
> the form of a timestamp in the catalog? and how can a user
> access this information?
> Thank-you, Howard|||This is essentially what I found; but it falls short of
what I need. Basically, I would like some sort of
notification (short of polling) since changes in the
file/filegroup structure would impact the usefullness of
component backup images that I may be keeping.
Thanks, Howard
>--Original Message--
>I would assume this info would be in sysfiles or
sysfilegroups. It's not
>there, so I don't think it's possible to get from SQL
Server.
>But...
>you CAN find the time a file was added.
>Create date is kept by the OS for a file. Something like
this would work:
>xp_getfiledetails 'c:\temp\authors.txt'
>But that would be the create date from an OS perspective.
You would end up
>with wrong info if for example you restored the database
and overwrote
>files, since they would then have new create dates...
>or you could create your own polling program that
periodically looks at the
>relevant system tables mentioned above...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Howard" <anonymous@.discussions.microsoft.com> wrote in
message
>news:07bc01c3d14e$04b438c0$a601280a@.phx.gbl...
>> Does SQL Server keep track of when new files and
>> filegroups are added or removed from a database, e.g.,
in
>> the form of a timestamp in the catalog? and how can a
user
>> access this information?
>> Thank-you, Howard
>
>.
>

adding DB from another computer

Hi!
I have copied from other computer, from location "C:\Program Files\Microsoft
SQL Server\MSSQL.2\MSSQL\Data" files my_db.mdf and md_db_log.ldf to other
computer to the same location, and I can't see this DB. How can I import DB
using files above?
Regards,
AreqYOu will either have to attach the database using the GUI commands for that
(Right Click on the server, either in EM or SSMS) and select attach
database, then select the appropiate database files, or use the TSQL command
sp_attachdb to attach the files. See more details about the TSQL command in
the BOL (SQL Server Help files).
Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--
"Areq" <areq@.op.com> wrote in message
news:eu1h3n$kqe$1@.nemesis.news.tpi.pl...
> Hi!
> I have copied from other computer, from location "C:\Program
> Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data" files my_db.mdf and
> md_db_log.ldf to other computer to the same location, and I can't see this
> DB. How can I import DB using files above?
> Regards,
> Areq
>|||Areq,
Check BOL for sp_attach_db. That is probably what you need.
-- Bill
"Areq" <areq@.op.com> wrote in message
news:eu1h3n$kqe$1@.nemesis.news.tpi.pl...
> Hi!
> I have copied from other computer, from location "C:\Program
> Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data" files my_db.mdf and
> md_db_log.ldf to other computer to the same location, and I can't see this
> DB. How can I import DB using files above?
> Regards,
> Areq
>|||Use sp_attach_db
Here is an example from BOL
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"Areq" <areq@.op.com> wrote in message
news:eu1h3n$kqe$1@.nemesis.news.tpi.pl...
> Hi!
> I have copied from other computer, from location "C:\Program
> Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data" files my_db.mdf and
> md_db_log.ldf to other computer to the same location, and I can't see this
> DB. How can I import DB using files above?
> Regards,
> Areq
>

adding DB from another computer

Hi!
I have copied from other computer, from location "C:\Program Files\Microsoft
SQL Server\MSSQL.2\MSSQL\Data" files my_db.mdf and md_db_log.ldf to other
computer to the same location, and I can't see this DB. How can I import DB
using files above?
Regards,
AreqYOu will either have to attach the database using the GUI commands for that
(Right Click on the server, either in EM or SSMS) and select attach
database, then select the appropiate database files, or use the TSQL command
sp_attachdb to attach the files. See more details about the TSQL command in
the BOL (SQL Server Help files).
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"Areq" <areq@.op.com> wrote in message
news:eu1h3n$kqe$1@.nemesis.news.tpi.pl...
> Hi!
> I have copied from other computer, from location "C:\Program
> Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data" files my_db.mdf and
> md_db_log.ldf to other computer to the same location, and I can't see this
> DB. How can I import DB using files above?
> Regards,
> Areq
>|||Areq,
Check BOL for sp_attach_db. That is probably what you need.
-- Bill
"Areq" <areq@.op.com> wrote in message
news:eu1h3n$kqe$1@.nemesis.news.tpi.pl...
> Hi!
> I have copied from other computer, from location "C:\Program
> Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data" files my_db.mdf and
> md_db_log.ldf to other computer to the same location, and I can't see this
> DB. How can I import DB using files above?
> Regards,
> Areq
>|||Use sp_attach_db
Here is an example from BOL
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"Areq" <areq@.op.com> wrote in message
news:eu1h3n$kqe$1@.nemesis.news.tpi.pl...
> Hi!
> I have copied from other computer, from location "C:\Program
> Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data" files my_db.mdf and
> md_db_log.ldf to other computer to the same location, and I can't see this
> DB. How can I import DB using files above?
> Regards,
> Areq
>

Adding Data Files

I inherited a server with a database that has 3 data files in the primary
filegroup, but SQL Server is only writing to the first one. It looks like
the 2nd and 3rd files were not created when the database was created, but
were added on later. The initial data file is 164 Gb in size and the 2nd an
d
3rd are still 1 Mb each. Any suggestions on why SQL Server is only writing
to the 1st data file?
Thanks,
HariSQL Server writes the data in each file using a proportional fill algorithm.
This algorithm determines the amount of free space in each file and splits
the data based on the % of free space in each file. If the file is only 1MB
it has no free space compared to the 164GB file. This means all or most of
the data goes to that one. Make the files much larger and you will start to
see data migrate over as you add data. All data files in the same file group
should be the same size so the data is spread evenly across all of them. If
you increase the size and reindex you will see the data start to get more
proportional over time.
Andrew J. Kelly SQL MVP
"Hari Seldon" <HariSeldon@.discussions.microsoft.com> wrote in message
news:AE0BAAC1-7BF5-4F0A-99E0-20E0DE0929B0@.microsoft.com...
>I inherited a server with a database that has 3 data files in the primary
> filegroup, but SQL Server is only writing to the first one. It looks like
> the 2nd and 3rd files were not created when the database was created, but
> were added on later. The initial data file is 164 Gb in size and the 2nd
> and
> 3rd are still 1 Mb each. Any suggestions on why SQL Server is only
> writing
> to the 1st data file?
> Thanks,
> Hari|||Thanks. I had wondered if the mistake they made was in not making the
initial file size on the second two files the same as the size of the initia
l
file - or at least larger than 1 Mb.
I'm trying to maintain this server until I can upgrade it to SQL Server 2005
and migrate to a more suitable environment. Unfortunately, the server has
only a single RAID 5 array to place all of the data and log files on and
performance is a real problem. Is there any performance benefit to having
multiple data files in a database when all of them are going to be located o
n
the same physical drives anyway? Also, is there any benefit to placing them
on different logical partitions on the array or does the fact that they're
still on the same physical array negate any benefit?
Hari
"Andrew J. Kelly" wrote:

> SQL Server writes the data in each file using a proportional fill algorith
m.
> This algorithm determines the amount of free space in each file and splits
> the data based on the % of free space in each file. If the file is only 1M
B
> it has no free space compared to the 164GB file. This means all or most of
> the data goes to that one. Make the files much larger and you will start
to
> see data migrate over as you add data. All data files in the same file gro
up
> should be the same size so the data is spread evenly across all of them. I
f
> you increase the size and reindex you will see the data start to get more
> proportional over time.
> --
> Andrew J. Kelly SQL MVP
> "Hari Seldon" <HariSeldon@.discussions.microsoft.com> wrote in message
> news:AE0BAAC1-7BF5-4F0A-99E0-20E0DE0929B0@.microsoft.com...
>
>|||I would have thought you would be better off using emptyfile and dropping
them.
I can't think of a benefit.
Raid 5 is generally slow on writes and will probably be hurting your logfile
most.
If you are using a battery backed up raid controller you could try
dedicating the cache 100% to writes and see if that helps.
There always a risk with caching writes but if there's a battery there it is
minimised.
If you could get the budget to get an extra pair of disks as a mirror for
the log you should get a decent benefit.
Even a pair of IDE/SATA with NT s/w mirroring would be better than nothing.
Try pointing out the the purse holders that if a disk failed on raid 5 the
performance while running in phantom mode would probably bring the machine
to it's knees.
Paul|||I agree - I would prefer to just delete them if there's no benefit to having
them, but wanted to verify it first.
I probably should have gone into a little more detail about how the database
is used. We do a bulk insert once a day and the rest of the time it's used
for reads only, so the transaction logs aren't much of a factor in this case
.
The tables are rather large and the only reason to keep the extra files
would be if it would help speed up queries. The server has a dual core
processor if that makes any difference. This is SQL Server 2000 Standard
Edition running on a Windows 2003 server.
We have new servers and lots of hard drives on order, so I just need to
tread water for a little longer.
Thanks,
Hari
"Paul Cahill" wrote:

> I would have thought you would be better off using emptyfile and dropping
> them.
> I can't think of a benefit.
> Raid 5 is generally slow on writes and will probably be hurting your logfi
le
> most.
> If you are using a battery backed up raid controller you could try
> dedicating the cache 100% to writes and see if that helps.
> There always a risk with caching writes but if there's a battery there it
is
> minimised.
> If you could get the budget to get an extra pair of disks as a mirror for
> the log you should get a decent benefit.
> Even a pair of IDE/SATA with NT s/w mirroring would be better than nothing
.
> Try pointing out the the purse holders that if a disk failed on raid 5 the
> performance while running in phantom mode would probably bring the machine
> to it's knees.
> Paul
>
>|||It's query tuning and sneaking some extra memory in till then. Bear in mind
that some of your queries may be writing to tempdb.
We keep our tempdb on separate spindles. From what I have read, 2005 makes
much heavier use of tempdb especially with the new isolation levels (Row
level versioning).
Interesting little article by Tony Rogerson.
http://sqlblogcasts.com/blogs/tonyr.../08/24/958.aspx|||I agree with Paul in that for your current situation you may be better off
dropping those files altogether.
Andrew J. Kelly SQL MVP
"Hari Seldon" <HariSeldon@.discussions.microsoft.com> wrote in message
news:C12EE151-5272-4697-B057-E773C64AADDD@.microsoft.com...[vbcol=seagreen]
>I agree - I would prefer to just delete them if there's no benefit to
>having
> them, but wanted to verify it first.
> I probably should have gone into a little more detail about how the
> database
> is used. We do a bulk insert once a day and the rest of the time it's
> used
> for reads only, so the transaction logs aren't much of a factor in this
> case.
> The tables are rather large and the only reason to keep the extra files
> would be if it would help speed up queries. The server has a dual core
> processor if that makes any difference. This is SQL Server 2000 Standard
> Edition running on a Windows 2003 server.
> We have new servers and lots of hard drives on order, so I just need to
> tread water for a little longer.
> Thanks,
> Hari
> "Paul Cahill" wrote:
>|||That's what I'm going to do. Thanks for the tips!
Hari
"Andrew J. Kelly" wrote:

> I agree with Paul in that for your current situation you may be better off
> dropping those files altogether.
> --
> Andrew J. Kelly SQL MVP
> "Hari Seldon" <HariSeldon@.discussions.microsoft.com> wrote in message
> news:C12EE151-5272-4697-B057-E773C64AADDD@.microsoft.com...
>
>

Adding Data Files

I inherited a server with a database that has 3 data files in the primary
filegroup, but SQL Server is only writing to the first one. It looks like
the 2nd and 3rd files were not created when the database was created, but
were added on later. The initial data file is 164 Gb in size and the 2nd and
3rd are still 1 Mb each. Any suggestions on why SQL Server is only writing
to the 1st data file?
Thanks,
HariSQL Server writes the data in each file using a proportional fill algorithm.
This algorithm determines the amount of free space in each file and splits
the data based on the % of free space in each file. If the file is only 1MB
it has no free space compared to the 164GB file. This means all or most of
the data goes to that one. Make the files much larger and you will start to
see data migrate over as you add data. All data files in the same file group
should be the same size so the data is spread evenly across all of them. If
you increase the size and reindex you will see the data start to get more
proportional over time.
--
Andrew J. Kelly SQL MVP
"Hari Seldon" <HariSeldon@.discussions.microsoft.com> wrote in message
news:AE0BAAC1-7BF5-4F0A-99E0-20E0DE0929B0@.microsoft.com...
>I inherited a server with a database that has 3 data files in the primary
> filegroup, but SQL Server is only writing to the first one. It looks like
> the 2nd and 3rd files were not created when the database was created, but
> were added on later. The initial data file is 164 Gb in size and the 2nd
> and
> 3rd are still 1 Mb each. Any suggestions on why SQL Server is only
> writing
> to the 1st data file?
> Thanks,
> Hari|||Thanks. I had wondered if the mistake they made was in not making the
initial file size on the second two files the same as the size of the initial
file - or at least larger than 1 Mb.
I'm trying to maintain this server until I can upgrade it to SQL Server 2005
and migrate to a more suitable environment. Unfortunately, the server has
only a single RAID 5 array to place all of the data and log files on and
performance is a real problem. Is there any performance benefit to having
multiple data files in a database when all of them are going to be located on
the same physical drives anyway? Also, is there any benefit to placing them
on different logical partitions on the array or does the fact that they're
still on the same physical array negate any benefit?
Hari
"Andrew J. Kelly" wrote:
> SQL Server writes the data in each file using a proportional fill algorithm.
> This algorithm determines the amount of free space in each file and splits
> the data based on the % of free space in each file. If the file is only 1MB
> it has no free space compared to the 164GB file. This means all or most of
> the data goes to that one. Make the files much larger and you will start to
> see data migrate over as you add data. All data files in the same file group
> should be the same size so the data is spread evenly across all of them. If
> you increase the size and reindex you will see the data start to get more
> proportional over time.
> --
> Andrew J. Kelly SQL MVP
> "Hari Seldon" <HariSeldon@.discussions.microsoft.com> wrote in message
> news:AE0BAAC1-7BF5-4F0A-99E0-20E0DE0929B0@.microsoft.com...
> >I inherited a server with a database that has 3 data files in the primary
> > filegroup, but SQL Server is only writing to the first one. It looks like
> > the 2nd and 3rd files were not created when the database was created, but
> > were added on later. The initial data file is 164 Gb in size and the 2nd
> > and
> > 3rd are still 1 Mb each. Any suggestions on why SQL Server is only
> > writing
> > to the 1st data file?
> >
> > Thanks,
> > Hari
>
>|||I would have thought you would be better off using emptyfile and dropping
them.
I can't think of a benefit.
Raid 5 is generally slow on writes and will probably be hurting your logfile
most.
If you are using a battery backed up raid controller you could try
dedicating the cache 100% to writes and see if that helps.
There always a risk with caching writes but if there's a battery there it is
minimised.
If you could get the budget to get an extra pair of disks as a mirror for
the log you should get a decent benefit.
Even a pair of IDE/SATA with NT s/w mirroring would be better than nothing.
Try pointing out the the purse holders that if a disk failed on raid 5 the
performance while running in phantom mode would probably bring the machine
to it's knees.
Paul|||I agree - I would prefer to just delete them if there's no benefit to having
them, but wanted to verify it first.
I probably should have gone into a little more detail about how the database
is used. We do a bulk insert once a day and the rest of the time it's used
for reads only, so the transaction logs aren't much of a factor in this case.
The tables are rather large and the only reason to keep the extra files
would be if it would help speed up queries. The server has a dual core
processor if that makes any difference. This is SQL Server 2000 Standard
Edition running on a Windows 2003 server.
We have new servers and lots of hard drives on order, so I just need to
tread water for a little longer.
Thanks,
Hari
"Paul Cahill" wrote:
> I would have thought you would be better off using emptyfile and dropping
> them.
> I can't think of a benefit.
> Raid 5 is generally slow on writes and will probably be hurting your logfile
> most.
> If you are using a battery backed up raid controller you could try
> dedicating the cache 100% to writes and see if that helps.
> There always a risk with caching writes but if there's a battery there it is
> minimised.
> If you could get the budget to get an extra pair of disks as a mirror for
> the log you should get a decent benefit.
> Even a pair of IDE/SATA with NT s/w mirroring would be better than nothing.
> Try pointing out the the purse holders that if a disk failed on raid 5 the
> performance while running in phantom mode would probably bring the machine
> to it's knees.
> Paul
>
>|||It's query tuning and sneaking some extra memory in till then. Bear in mind
that some of your queries may be writing to tempdb.
We keep our tempdb on separate spindles. From what I have read, 2005 makes
much heavier use of tempdb especially with the new isolation levels (Row
level versioning).
Interesting little article by Tony Rogerson.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx|||I agree with Paul in that for your current situation you may be better off
dropping those files altogether.
--
Andrew J. Kelly SQL MVP
"Hari Seldon" <HariSeldon@.discussions.microsoft.com> wrote in message
news:C12EE151-5272-4697-B057-E773C64AADDD@.microsoft.com...
>I agree - I would prefer to just delete them if there's no benefit to
>having
> them, but wanted to verify it first.
> I probably should have gone into a little more detail about how the
> database
> is used. We do a bulk insert once a day and the rest of the time it's
> used
> for reads only, so the transaction logs aren't much of a factor in this
> case.
> The tables are rather large and the only reason to keep the extra files
> would be if it would help speed up queries. The server has a dual core
> processor if that makes any difference. This is SQL Server 2000 Standard
> Edition running on a Windows 2003 server.
> We have new servers and lots of hard drives on order, so I just need to
> tread water for a little longer.
> Thanks,
> Hari
> "Paul Cahill" wrote:
>> I would have thought you would be better off using emptyfile and dropping
>> them.
>> I can't think of a benefit.
>> Raid 5 is generally slow on writes and will probably be hurting your
>> logfile
>> most.
>> If you are using a battery backed up raid controller you could try
>> dedicating the cache 100% to writes and see if that helps.
>> There always a risk with caching writes but if there's a battery there it
>> is
>> minimised.
>> If you could get the budget to get an extra pair of disks as a mirror for
>> the log you should get a decent benefit.
>> Even a pair of IDE/SATA with NT s/w mirroring would be better than
>> nothing.
>> Try pointing out the the purse holders that if a disk failed on raid 5
>> the
>> performance while running in phantom mode would probably bring the
>> machine
>> to it's knees.
>> Paul
>>|||That's what I'm going to do. Thanks for the tips!
Hari
"Andrew J. Kelly" wrote:
> I agree with Paul in that for your current situation you may be better off
> dropping those files altogether.
> --
> Andrew J. Kelly SQL MVP
> "Hari Seldon" <HariSeldon@.discussions.microsoft.com> wrote in message
> news:C12EE151-5272-4697-B057-E773C64AADDD@.microsoft.com...
> >I agree - I would prefer to just delete them if there's no benefit to
> >having
> > them, but wanted to verify it first.
> >
> > I probably should have gone into a little more detail about how the
> > database
> > is used. We do a bulk insert once a day and the rest of the time it's
> > used
> > for reads only, so the transaction logs aren't much of a factor in this
> > case.
> > The tables are rather large and the only reason to keep the extra files
> > would be if it would help speed up queries. The server has a dual core
> > processor if that makes any difference. This is SQL Server 2000 Standard
> > Edition running on a Windows 2003 server.
> >
> > We have new servers and lots of hard drives on order, so I just need to
> > tread water for a little longer.
> >
> > Thanks,
> > Hari
> >
> > "Paul Cahill" wrote:
> >
> >> I would have thought you would be better off using emptyfile and dropping
> >> them.
> >> I can't think of a benefit.
> >>
> >> Raid 5 is generally slow on writes and will probably be hurting your
> >> logfile
> >> most.
> >> If you are using a battery backed up raid controller you could try
> >> dedicating the cache 100% to writes and see if that helps.
> >> There always a risk with caching writes but if there's a battery there it
> >> is
> >> minimised.
> >>
> >> If you could get the budget to get an extra pair of disks as a mirror for
> >> the log you should get a decent benefit.
> >> Even a pair of IDE/SATA with NT s/w mirroring would be better than
> >> nothing.
> >>
> >> Try pointing out the the purse holders that if a disk failed on raid 5
> >> the
> >> performance while running in phantom mode would probably bring the
> >> machine
> >> to it's knees.
> >>
> >> Paul
> >>
> >>
> >>
>
>