473,836 Members | 1,484 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

difference between primary&seconda ry files

1 New Member
For creating a user database three important files named primary, secondary & transaction log files. so my question is 'what is the difference between primary & secondary files?' and is 'what is the necessity of secondary files?'
Feb 1 '08 #1
2 7590
23 New Member
For creating a user database three important files named primary, secondary & transaction log files. so my question is 'what is the difference between primary & secondary files?' and is 'what is the necessity of secondary files?'
Most of the time I only use the primary file groups. I do have some apps that use partitioning to move data to a secondary file group (which is stored on another disk) essentially to archive data that is X number of days old. In this case, and very simply put, querying the newest data (less than X days old) and the archived data together will be faster (than if only one disk was used) because I have two disks to share the workload.

There are also ways to recover data from only specific file groups. Using the same "setup" as above, if the server were to crash, I can recover and restore the application using the last X days of data a lot faster than if I had to recover ALL of the data (which could run into hundreds of gigs). Most of the time, people can wait until Monday to see data from 2002 but most people can't wait for info from yesterday.

Simple examples, but it gives you a basic idea.
Feb 22 '08 #2
2,878 Recognized Expert Specialist
Start here first.

Here's some write ups I pick up here and there...

You can group data files for a database together into filegroups for allocation and administration purposes. In
some cases, you can improve performance by controlling the placement of data and indexes into specific
filegroups on specific disk drives.

.. Primary filegroup This filegroup contains the primary data file and any files not put into another specific
filegroup. All pages from system tables are always allocated from files in the primary filegroup.

.. User-defined filegroup You can create user-defined filegroups by using the FILEGROUP keyword in the

.. Default filegroup This filegroup contains the pages for all tables and indexes that aren't placed in a
specific filegroup. Only one filegroup in each database can be the default filegroup. A database owner can
change which filegroup is the default by using the ALTER DATABASE statement. If no default filegroup
was speci-fied, the primary filegroup is also the default filegroup.

Why do you need more than one filegroups?

If you need to restore a database from a backup because of a disk crash, you should be aware
that the new database must contain the same number of files as the original. For example, if your
original database consisted of one large 12-GB file, you would need to restore it into a database
with one file of that size. If you don't have another 12-GB drive immediately available, you won't
be able to restore the database! If, however, you originally created the database on several smaller
files, you have added flexibility during a restoration. You might be more likely to have several 4-
GB drives available than one large 12-GB drive.

Spreading the database onto multiple files, even onto the same drive, gives you the
flexibility of easily moving the database onto separate drives if you modify your hardware
configuration in the future. Microsoft's internal SAP system uses a SQL Server 7 database created
on 12 files. Microsoft thought this would give it the ultimate flexibility. They could separate the
files into two groups of six, six groups of two, four groups of three, and so on. This allows them to
experiment with performance enhancements gained as files are spread over different numbers of
physical drives.

-- CK
Feb 22 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Mattias Campe | last post by:
Hi, Depending on if I get an image or a text of a certain URL, I want to do something different. I don't know in advance whether I'll get an image or a text. This is a URL that returns an image: http://indicator.amessage.info/indicator.php?param1=cobnet%40jabber.org&param2=bounce&param3=http%3A%2F%2Fstudent.ugent.be%2Fastrid%2Fpics%2Fjabber%2F&param4=.png
by: jasper | last post by:
hi, i am trying to make a very simple program that will just detect and output your currently connected IDE devices. It will look something like this: IDE PRIMARY MASTER: IDE PRIMARY SLAVE: IDE SECONDARY MASTER:
by: BoonHead, The Lost Philosopher | last post by:
I think the .NET framework is great! It's nice, clean and logical; in contradiction to the old Microsoft. It only saddens me that the new Microsoft still doesn't under stand there own rules when it comes to file paths. A lot of Microsoft installers for example, and also installers of other companies, do not work because they handle paths in the following manner:
by: rajender_4 | last post by:
Hi techies I have set up a Transaction replication from My Primary Server to Secondary Server on Orders table. Thousand of records gets inserted on Orders every hour which get replicated on the secondary server. it works fine reporting apps uses Secondory server's Orders table data for generating
by: Lynn.Tilby | last post by:
I am running an experimental system, my disk filled up when running a table build from a query. I mistakenly blew away the files in .../pgsql/data/base/18773 & ../pgsql/data/pg_xlog with NO BU! Recover is only bringing back 8 of about 90 files. I can rebuild the DB from scratch relatively easily... I was going to try and just do a drop database but I get this when trying to bring up the postmaster.
by: Martin Maat | last post by:
I am puzzled. I have this object that uses a thread. The thread is encapsulated by the object, the object has Start and Stop methods to enable the client to start or stop the thread. I found that the object will not be garbage collected while the thread is running. Fair enough, the documented explanation is that the GC compresses objects on the heap and needs to update references, the references will be invalid for a couple of moments...
by: rn5a | last post by:
I have a ListBox which should list all the files & directories that exist in a particular directory. The problem is I can get the ListBox to list either all the files or all the directories but not the 2 of them together. This is what I tried: Sub Page_Load(.....) Dim dInfo As DirectoryInfo dInfo = New DirectoryInfo(Server.MapPath(MyDir))
by: titan.nyquist | last post by:
"At the implementation level, the primary difference is that C# generic type substitutions are performed at runtime and generic type information is thereby preserved for instantiated objects." - http://msdn2.microsoft.com/en-us/library/c6cyy67b.aspx Wonder if someone could elaborate this difference to me. I understand performing substitutions at runtime is slow. But, it implies in c++ templates, the type information is not preserved...
by: KimberlyM | last post by:
This has been driving me crazy. I hope someone can help. The site displays perfectly in FF but all div's do not show in IE. Please help me find the problem! Thanks! Here is my css. .node-unpublished, .comment-unpublished { background-color : #594133; } .preview .node, .preview .comment { background-color : #ffffea; }
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.