Multiple Data Files and Log Files 2

[ksbrace]

Hello,
I'm trying to understand how SQL Server handles the datafiles and log files. As of right now there are only one data and one log file. In Oracle I have several datafiles, one for each logical tablespace, which hold a number of tables. There are several log files, also. Each time a checkpoint occurs, a new archive log file is created. Would someone please explain whether this can be set up within SQL Server? Thanks in advance,
Kelly

 

[ptheriault]

Here is link that might help you understand how SQL server manages data and log files

http://msdn2.microsoft.com/en-us/library/ms179316.aspx

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Log files and data files aren't handled that way in SQL Server. You only get additional files if you physically create them. And the only way to populate them automatically is to make them part of the default / Primary file group or to create a new file group and make the new one the Primary.

You can partition tables across various files, and indexes also. But again, this requires you to specifically code it before it starts happening automatically.

However, since you mentioned checkpoints...

When a checkpoint is reached in SQL Server, it writes "dirty pages" (or data held in memory) to the transaction log. Transactions are then applied to the database, but they don't "leave" the log file until you backup the log file or completely truncate it. Also, the log file doesn't create a new file, it just keeps growing until it hits its pre-defined limit or you run out of disk space.

You can find this all in Books Online under the keywords of "checkpoint", "backup log", "files-SQL Server" and "Filegroups". If you don't have a copy of BOL, MSDN's website has a copy of the latest (for SQL Server 2005) which you can download and read the entries for those keywords.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

You should also check the recovery model of your databases. This will determine the kind of backups you can create.

In Enterprise Manager (SQL 2000) or Managemenet Studio (SQL 2005) right click on your user database and select properties.
One the options page check the Recovery Model. If the recovery model is set to FULL then you can create Transaction log backups to be able to recover to a point in time.
The file page is where you set the auto grow and add new files.

Unless you are having disk space issues you don't need more than one log file. However, there can be many performance gains by having multiple filesgroups and files located on different arrays. Also put you log file (.ldf) on a seperate array from the data (.mdf)

- Paul
- Database performance looks fine, it must be the Network!

 

[ksbrace]

Thanks for the quick and informative replies. Do you guys generally have more than one datafile? I have a SAN where my datafile is located and we are going to have anywhere from 100k-400k users hitting this db. Just looking for some pointers from the SQL Server pros. Thanks,
Kelly

 

[ptheriault]

Kelly,
To balance your I/O create more than one datafile. Don't use the default for any user data. I have my data separeted out into logical groups by tables. For example. I have claims and policy data. I have put those tables in different filegroups on different arrays. I get improved performance and I could restore claims with having to shut down policy data.


- Paul
- Database performance looks fine, it must be the Network!

 

[ksbrace]

Paul,
Ok, now...the database is already created, is it too late to separate the tables?

 

[ptheriault]

no,
The actual data in each table is stored at the leaf level of the Unique Clusterd Index (PK). If you drop and recreate the PK on the new file it will move the table for you. For tables without PK's you would have to create a new table (with different name) on the new file group and select the data into it. Then drop the old and rename the new. I just had to do that for all my claims tables. Management studio can generate these scripts for you. Here is an example of what I had to do. This can be messy if there are Foriegn keys that referrence the PK.

/****** Object:  Index [PK_Claim_Performers]    Script Date: 11/21/2006 08:30:35 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_policy_InsuredContacts_ICT_Claim_Performers]') AND parent_object_id = OBJECT_ID(N'[dbo].[policy_InsuredContacts_ICT]'))
ALTER TABLE [dbo].[policy_InsuredContacts_ICT] DROP CONSTRAINT [FK_policy_InsuredContacts_ICT_Claim_Performers]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Claim_Performers]') AND name = N'PK_Claim_Performers')
ALTER TABLE [dbo].[Claim_Performers] DROP CONSTRAINT [PK_Claim_Performers]
ALTER TABLE [dbo].[Claim_Performers] ADD  CONSTRAINT [PK_Claim_Performers] PRIMARY KEY CLUSTERED 
(
	[CP_ID] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [FGClaimRef]
GO
ALTER TABLE [dbo].[policy_InsuredContacts_ICT]  WITH NOCHECK ADD  CONSTRAINT [FK_policy_InsuredContacts_ICT_Claim_Performers] FOREIGN KEY([ICT_CP_ID])
REFERENCES [dbo].[Claim_Performers] ([CP_ID])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[policy_InsuredContacts_ICT] CHECK CONSTRAINT [FK_policy_InsuredContacts_ICT_Claim_Performers]
GO

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Are you using SQL Server 2005? If so, you can do horizontal partitioning with your heaviest hit tables to separate it out amongst data files and then have the data files on different drives. That would help you spread out the I/O somewhat.

Also, consider moving TempDB to a different drive from all the others. This allows TempDB to grow on its own and moves its I/O away from your user DB.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"