I'm porting an old Access 97 application to SQL Server 2000. The Access app uses 9 separate files for a series of linked tables (one table in each file to get around the 1Gb Access file size limit). The tables vary in size form 2Mb to 800Mb so the whole data set weighs in at around 6Gb.
I've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.
My next problem is the indexes. The Access tables don't have primary keys. My SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.
At present I have all the table indexes in the PRIMARY filegroup (which in tunr just has the default MDF file in it)
I've built a small version of the DB for testing various triggers and new views and the DTS import packages and the indexing for the 300Mb of data I have now is obviously fairly quick. I am wondering if I should split the indexes out into each of the separate MDF files that I am storing the table data or should I split the indexes into their own files?
I want this thing to be fast. The VBA app that will be plugging into the DB has a huge amount of code and currently struggles especially when several dozen people are all connected to the same tables.
So any advice? Indexes in separate files? Or in with the data? or all together in one index file? Any performance impacts I should be aware of?
The DB is running on its own dedicated box. Its not huge 1Gb Ram, 30Gb drive and a 3Ghz P4. But given that it isn't running anything else it should be up to the job. It should certainly be faster than the current shared drive that the access app runs from.
Hi,I'm a newbie in this SQL Server development. I have a database with 7tables that are linked to each other and have created clustered indexfor the primary keys and non-clustered index for the secondary keys.All my indexes are on the same filegroup, what is the effect of thisbecuase the application that I'm using runs very slow duringprocessing.PS: My tables have a grown rate of about 10 000 records a day.
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
I was told to move tables to new filegroups by placing the clustered index on the filegroup and the table would follow. There are times when I see tables listed on the new filegroup but still listed also on Primary. My goal is to have only system tables on Primary. How can I get a table to totally leave the PRIMARY group?
There seems to be a system index on the table that was not created by me.
I have databse that was created when I got here and the database was created with 3 file groups. The tables in the database are spread out over the 3 file groups.
How can I find out which table belongs to which file group.
If I'm running RAID 5, is it still good practice to split system and user data onto separate filegroups ? .....On separate disks ? Thanks in advance.. Any comments welcome ! Peter
I was wondering if there is a downside of placing individual tables into their own filegroup (i.e., 1:1). This would seem to allow me to continue to backup entire databases but give me the flexibility to restore at a table level if necessary. I ask this because the number of filegroups allowed per database has increased from 256 in SS2000 to 32K in SS2005. What kind of headaches could I get into if I follow this type of design ?
i want to obtain information about all the filegroups for every database. is there a special command to obtain this information like the dbcc sqlper or something similar ??
Is there a way to list all the tables in each of the filegroup in a database. I have a database with 7 filegroups and would like to have a list of tables in each filegroup Sp_helpfilegroup gives you list of filegroups and the names of files
Is there any way in which I can move an objects/ data to another filegroup using transact sql? I have created a table such that the data is on a secondary filegroup. But the table has image and text data and this data is going to the PRIMARY filegroup. I tried chnging the filegroup for the text data but this property but does not change(I did it through EM). As a result, the disk which contains the primary filegroup is getting filled up. I have configured my database such that the primary file group is on one disk and the secondary on another and the log on the same disk as the primary filegroup. I have given lesser space to the primary file group and more space to the secondary filegroup. help on this is greatly appreciated because my system is rapidly growing with respect to data.
I was under the impression that if you are using multiple files in your filegroup to store your data that the data should be written evenly across the files.
I have 5 files and the data is being written to only one file which is growing rather large, while the other files are still at 5 - 7 MB. They are all marked for Automatic Growth at this point, but only the 1 file is growing.
Is there something else that needs to be done to enable this? BOL left me with the impression that this is automatic.
We are going to start using fielgroups to move some indexes to a different RAID. Simple ? is, when you do a full database backup, that backup alss contains the new filegroup?
Hey guys I have a VLDB and I would like to point or move several tables to another filegroup.
1). Add a filegroup to the database ALTER DATABASE dbname ADD FILEGROUP filename_table go
2). Add a file to the file group ALTER DATABASE dbname ADD FILE ..... TO FILEGROUP filename_table go
If a table is already part of the primary filegroup: Can I change it to another filegroup? When changed to the another filegroup will it move the whole table to the other filegroup or just start to write data to the newly created filegroup?
I'm trying to tweak our DB as much as I can. We have the full applicationdatabase sitting on a RAID 10. Logs, tempdb, etc.. are on separate arrays.I read some articles about making separate filegroups. Of course, they allsay to move them to different arrays, which because of budget is notpossible right now. I also read articles that say to put large tables in afilegroup then add another data file to the group. This allows SQL Serverto use multiple threads to access the tables. Therefore, to my question.If I split our database up into multipele file groups on the same array,could I see a performance gain. Would the database be able to issuedifferent threads for each group when a query is run and improve overallperformance.Thank you,Adam
We can create a queue on a filegroup for performance reasons however what about the sys.transmission_queue which could have messages backing up when the target is down.
I need some help for my Log Shipping database please.
I have a database X on the production server and on the log shipping standby server (Read-only). The logs on the production server is shipped to the standby server. The standby server restores the logs through a schedule job.
Now what happended is couple of days back we created another filegroup (Secondary) on the production server for the database X to keep our history separated from the most current data. When we created the secondary filegroup our logs on the log shipping server cannot restored. We investigate the problem and found that there is no secondary filegroup attached to our database X on the log shipping server, which is obvious.
My question now is there a way we can create a secondary filegroup for the database X on the log shipping server without breaking our existing log shipping? Do we must restore the full backup of the database X on the log shipping server in order to keep the transaction logs applied on the log shipping database X?
I want to change the filegroup of a table. While we can do this in enterprise mgr, I would like to accomplish this in transact sql. We have a new configuration where the filegroups are located on different disks and to take advantage of that, I want to move the heavily accessed tables onto different groups. Example: table emp needs to be moved from filegroup Primary to file group Secondary. please help...
To restore files and filegroups in a different server. First, Do i have to create the database with same name as the old one? or it doesn't matter... Can we use Enterprise Manager to do this restore?
I am trying to restore from a backup of an individual filegroup in SQL Server 7.0. I have backed up the individual filegroups, and I want to restore one of them (not the whole database). The process in EM is almost the same as restoring the entire database, only you choose the radio button 'Filegroups' and the files/filegroups you want to restore.
The restoration appears to complete sucessfully, but afterward, my database seems to be permanently loading. In other words, in EM the database is greyed out and you cannot use it anymore. It stays like this until you drop the database altogether. Is this a bug? Has anyone else had this problem or does anyone have experience restoring from filegroups sucessfully???
Howdy all. I want to be able to restore just 1 filegroup from a filegroup backup, without having to restore the log afterwords even if the data has changed post filegroup backup. While this would leave the data inconsistant, it may be desired at times. From BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm
************************************************** ****** If the files have been modified after the file backup was created, execute the RESTORE LOG statement to apply the transaction log backup, specifying:
The name of the database to which the transaction log will be applied. The backup device from where the transaction log backup will be restored. The NORECOVERY clause if you have another transaction log backup to apply after the current one; otherwise, specify the RECOVERY clause. The transaction log backups, if applied, must cover the time when the files and filegroups were backed up until the end of log (unless ALL database files are restored). ************************************************** ******
But thats just it, I may not want to take this action. Maybe some data got deleted from a set of tables in a filegroup right after I took my last filegroup backup, so I just want to restore that filegroup back to that backup, but leave my other filegroups alone. Restoring the log brings the data back to a consistant state when the log was backed up, which may not be what I want.
Hi, I want to take the backup of a particular filegroup in SQL server 2000 and then restore the same filegroup backup. Currently I am using the following steps : 1. Creating the database with 2 filegroups. The syntax that I used for it is as follows : create database test on primary (name = test1, filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est1.mdf'), (name = test2, filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est2.ndf'), filegroup group2 (name = test3, filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est3.ndf'), (name = test4, filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est4.ndf') log on (name = test_log, filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est_log.ldf')
2. Now I am creating two tables table1 and table2 using Enterprise Manager. Both the Tables, table1 and table2 are on the "Group2" Filegroup of test database.
3. After creating the tables, I inserted three records in each table.
4. Then I performed the backup using the following commands: backup log test to disk = 'e: est.lg1' with init
backup database test filegroup = 'group2' to disk = 'e: est_group2.bak'
5. After the backup has been taken, I deleted the two records from both the tables. Now both my tables have one record each.
6. Now I tried restoring the database using following command :
backup log test to disk = 'e: est.lg3' with init
restore database test filegroup = 'group2' from disk = 'e: est_group2.bak' with norecovery
restore log test from disk = 'e: est.lg3' with norecovery
restore database test with recovery
7. After all these commands are executed, when i checked my database, it contains again the one record in each table. Ideally, according to me the tables should contain 3 records as i had taken the backup when each table had 3 records.
How can I bring the database back to the state in which I had taken the backup.
Pls. help me as soon as possible.
And also let me know, if I need to do any SQL level or Database level settings for the above task.
Waiting for your reply. and thanking you in advance.
My company is in the process of upgrading our sql 2000 server to 2005 on new server hardware. In this process I'm charged with the actual server and database configuration on the new server so currently I'm investigating how to take advantage of new 2005 features and improve the use of common features on both platforms. We've decided to perform this upgrade in three stages (won't bother you with why) and the first stage is to install the new server with a 2000 installation with a different configuration regarding files/filegroups on the new hardware configuration that has a different raid configuration. The old server configuration was lacking in just about every department so I'm serious about getting it right this time. Its an OLTP system btw.
The question(s):
The 2780A course book states that sql 2005 can perform the following scans: "Multiple parallell scans for a single table if the filegroup of the table contains multiple files." Does this imply that the files must be on separate disks/raid channels? Ie, if I put all files in the filegroup on the same RAID 10 channel will I gain nothing over having just one file?
Can 2000 take advantage somehow of several files in a filegroup to achieve greater parallellism or is there no (end user) performance oriented reason to have more files in a filegroup? (Disregarding any impact of different backup strategies for several files).
I'm intending to place all nonclustered indexes in a separate filegroup with all files assigned to a dedicated RAID 0 channel for striping. Due to the lack of fault tolerance here I'm wondering if this is wise. But since nonclustered indexes are basically secondary metadatastructures and doesn't contain any data I'm wondering wether the server can disregard using an index when executing a query if the index resides on disk which have failed and the index is inaccessible. Is there a fallback option available to the server to perform a table scan or use another index instead?
Basically the same question can be asked for tempdb, use a RAID 10 with four disks or dare I use a RAID 0 with four disks (since 2005 use tempdb more heavily than 2000).
Also considering to put the OS on a dedicated RAID 1 and a dedicated RAID 0 for the pagefile, same concerns apply.
I appreciate any input, also examples (or links) or your experiences of good RAIDing practices for OLTP systems.
Thanks for your time.
PS. Sorry for posting this in both 2000 and 2005 forums but I don't wanna miss out on any good answers. ^^
Hey all For performance reasons i want to move the Merge Replication tables in a DB of mine (mainly MSMerge_Tomestone and MSMerge_Contents) to a seperate filegroup ... it's a heavy transactional DB and the index fragamentation rate is quite phenominal!
CREATE DATABASE Dummy ON -- Primary file contains Startup information of the database PRIMARY ( NAME = PrimaryLog, FILENAME = 'D:primary.mdf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ), -- Holds The Data of LookUPTables,TPProfile,CRM. ( NAME = Data, FILENAME = 'D:Data.ndf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ), LOG ON -- Stores The Log Information used To Recover The Database ( NAME = Log, FILENAME = 'D:Log.ldf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ) Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
