SQL 2012 :: Rebalancing Data In File Groups

Apr 28, 2014

I am looking for the easiest way of rebalancing data across multiple files.

Instead of creating a secondary filegroup and then dropping and recreating all indexes in the database which is going to take ages (we have a lot of tables and indexes), I am trying to just add more files to the primary file group and then rebalance data evenly between these.

I guessed that adding the new files to the primary file group and then rebuilding all indexes on a table should redistribute the table over these multiple file groups evenly. This is not the case though. It does rebalance data a bit but I still end up with the majority on the first file that existed.

I have attached the script I am running, maybe it is something in the create database/file statements that is the issue.

Basically what I am seeing is to start off with the table is 160MB. I then add the file groups and rebuild all indexes on the table. The first file is then about 100MB and each of the three other files are about 20MB. I would expect them all to be the same size.

View 4 Replies


ADVERTISEMENT

SQL 2012 :: 500GB Data Warehouse - How To Split It Into File Groups

Aug 21, 2014

OK, so I have:

- 500 GB DW
- 5 GB in smaller DBs
- 220 GB TempDB
- 350 GB in Log files.

My machine is Fujitsu Primergy 64 cores (with HT) and 192 GB RAM.

I have several IO locations:

- 540 GB in-server HDD 15k RAID10
- 1 TB HDD 15k RAID10 on SAN (separete controller)
- 2 TB HDD 15k RAID10 on SAN (same controlller as below)
- 800GB SSD RAID10 on SAN (same controller as above)

Data warehouse has 2 fact tables that are absolutely crucial and quite large.

Now i want to organize DB into several Filegroups and put them on different drives. Filegroups I'm thinking of:

- FILEGROUP1: for 1st crucial Fact Table
- FILEGROUP2: for 2nd crucial Fact Table
- FILEGROUP3: for tempDB
- FILEGROUP4: for dimensions data
- FILEGROUP5: for the rest of facts data
- FILEGROUP6: for dimensions indexes
- FILEGROUP7: for the rest of facts indexes
- FILEGROUP8: for 1 log file of one smaller DB (its in full-recovery and its quite large)
- FILEGROUP9: for the rest of log files
- FILEGROUP10: others

How should I organize them across available drives? I was thinking about sth like:

800 GB SSD: FILEGROUPS 1-3
2 TB RAID10: FILEGROUPS 5+7+8
1 TB RAID10: FILEGROUPS 4+6+10
540 GB in-server: FILEGROUP 9

I know that having multiple filegroups on the same drive is pointless regarding performance, but in future i could actually add some more drives, so i want to separate them now.

Also - how much files per filegroups should i create? Considering 1 or 2. Except TempDB where I am going for 4.

View 2 Replies View Related

SQL 2012 :: Tables On Separate File Groups

Jul 30, 2014

We have a large Datawarehouse and the size is 50TB.. The tables are placed in filegroups based on the schema like fact, dimensions, raw data each sit on seperate filegroups. I am thinking will it make sense to seperate the large facts which are having billions of rows so that they reside on filegroups on their own..

View 9 Replies View Related

SQL 2012 :: Any Variation While Adding File Groups And Files?

Sep 17, 2014

When the database is configured for mirroring and you want to do partitioning on that database, How can we do? Is this similar process or any variation there while adding file groups and files? The partition will reflect in the mirroring database also?

View 1 Replies View Related

Split Data File Into Multiple File Groups..!

Dec 18, 2006

I have one of our production Accounting Databases starting from 2 GBnow grown into a 20 GB Database over the period of a few years...I have been getting timeouts when transactions are trying to updatedifferent tables in the database.. Most of the error I get are I/Orequests to the data file (Data file of the production dbAccounting_Data.MDF).I would like to implement the following to this Accounting database.I need to split the Data file into multiple files by placing some ofthe tables in different file groups. I have the server upgraded to beable to have different drives in different channels. I can place thesedata and log files in different drives so it will be less I/Oconflicts..I would like to have the following file groups..FileGroup 1 - which will have all database definitions (DDL).FileGroup 2 - I will have the AR Module tables under here..FileGroup 3 - I will have the GL module tables under here..FileGroup 4 - I will have the rest of the tables under hereFileGroup 5 - I will like to place the indexes under here....Also where will the associated transaction files go?I would like to get some help doing this. Is there any articles / helpavailable that I can refer to. Any suggestions / corrections/criticisms to what I have mentioned above is much appreciated...!Thanks in advance....

View 1 Replies View Related

SQL 2012 :: Data Collection Fails For 1 Of 4 AlwaysOn AG Groups

Jun 5, 2014

I have a two node SQL 2012 AlwaysOn HADR cluster (v11.0.3412) with 4 availability groups configured. The AG groups are set to synchronous mode and the secondary is not readable (we do not want the synchronous replica readable so we do not risk any reads causing contention so we maintain fast performance).

On the secondary we are getting a persistent failure with the Data Collector job called Collection_Set_3_Upload. The failure occurs within the second job step. That job step is executing the following command:

dcexec -u -s 3 -i "$(ESCAPE_DQUOTE(MACH))$(ESCAPE_DQUOTE(INST))"
The error message is as follows:

Log Job History (collection_set_3_upload) Step ID 2 Server CLUSTERNODE2
Job Name collection_set_3_upload
Step Name collection_set_3_upload_upload
Duration 00:00:07

[Code] ....

I know I can prevent this error message by enabling readable secondaries, but we do not want this.

I have tried stopping the data collection jobs and purging the cache directory but to no avail. It will succeed the first time then persistently fail again with the same message every time after that.

In addition, if I set the one failing AG group to readable secondary the job succeeds. So that means that 3/4 work fine, only this one is having an issue.

View 0 Replies View Related

Problem With Matrix (in Subreport, Multiple Groups), Groups Repeating First Row Data

Jan 25, 2008

I have a new SQL 2005 (SP2) Reporting Services server to which I've just upgraded and deployed some SSRS 2000 reports.

I have a subreport that contains a matrix with two groups. The report data seems to be inexplicably repeating the data for the first row in the group for all rows in the group. Example:









ID1
ID2
DisplayData

1
1
A

1
2
B

1
3
C

2
1
A

2
2
B

2
3
C

Parent group is on ID1, child group is on ID2, report would show:








1
1
A

2
A

3
A

2
1
A

2
A

3
A


Is this a matrix bug in 2005 SP2, or do I need to do something differently? I can no longer pull a comparison version from an SSRS 2000 server to verify, but I believe it was working as expected before...

View 2 Replies View Related

SQL 2012 :: DBCC Shrinkfile Empty File Not Distributing Data Evenly In Primary File Group With Multiple Files

Apr 29, 2014

Why shrinkfile empty file does not redistribute data evenly in the primary file group with multiple files:

Please run the script attached to see what the end result is.

This is what I set up last night on my test machine.

1) Create database [FGTest] size 200MB
2) Create table called TEST on primary
3) Insert 40MB of data into test
4) Create another file group called temp in primary size 200MB
5) Shrinkfile('FGTest',emptyfile) so that all data is transfered from FGTest into temp file group.
6) Add another 2 files called DATA2 and DATA3. Both are 200MB.
7) We now have 3 empty files that I want data distributed evenly on. FGTest, DATA2 & DATA3
8) Shrinkfile('temp',emptyfile) to move all the data from temp over the 3 file groups evenly

I would expect at this stage to have the following:

FGTest = 13MB,
DATA2 = 13MB,
DATA3 = 13MB

(40MB of data over 3 files should be about 13 MBish in each file)

What I actually end up with is this:

FGTest = 20MB
DATA1 = 10MB
DATA2 = 10MB

It looks as though SQL Server is allocating 50% of all data to the original file and then 50% evenly over
the remaining files in PRIMARY.

View 3 Replies View Related

SQL 2012 :: Cannot Stop File Growth On Data File

Apr 22, 2014

We have a large 'History' database that is currently about 4.5TB, with most of that in a datafile that is 4.2TB. We wanted to stop growth on the one large data file and have SQL Server allocate new data to the other data files, but this throws an error when we attempt to change the MAXSIZE settings:

ALTER failed for Database 'History'
MODIFY FILE failed. Specified size is less than or equal to current size.

The SQL Server is saying we can have a max size of 2TB, and anything over that is blocked. Since this is being blocked, the file continues to grow.

Is there any way to cap the growth of the 4.2TB file and not allow any more data to be written to it?

View 1 Replies View Related

SQL 2012 :: Moving Log Files In HA Groups

Feb 11, 2014

Trying to find out if this is the best way to move log files in databases that are in an availability group.

remove the DB from the AG
Run alter database commands like you would normally to take offline ,move file,bring online ,etc
drop the db from secondary node
then rejoin the DB to the AG

Is that the only option for moving them when its in an avail group? cant find any other info on moving files in mirrors or HA groups

View 2 Replies View Related

SQL 2012 :: Database Can't Be In Two Availability Groups

Jun 30, 2014

I been trying to learn availability groups since I have not implemented it.

From my understanding you can have more than one group.

Lets pretend we have two groups in one instance:

1. Accounting
2. Engineering

From my understanding you can't make a database in two AG because it wouldn't make sense.

But lets pretending there is one database that both are used by accounting and engineering.

Would you have to make a third AG for future fail overs so that other databases in the other two group don't failover when not needed because when you fail over an AG all the databases inside it fail over.

View 3 Replies View Related

SQL 2012 :: Going From Cluster To Avail Groups

May 13, 2015

So, today we have the following:

Dallas: A 2 node Windows 2008 Cluster running SQL 2012 ENT cluster
Wash: A 2 node Windows 2008 Cluster running SQL 2012 ENT cluster

and I'm mirroring (synchronous, no witness) a database from Dallas to Wash.Crappy set up. I know.Now customer wants to have the database mirror to another server in Dallas.What are the high level steps to transform my two clusters to use Availability Groups and Always On?Do I need to basically start over and build a new environment?or can I transform my two disconnected cluster?

View 4 Replies View Related

SQL 2012 :: Multiple Availability Groups On One Instance

Aug 12, 2013

From what I understand, one SQL Server 2012 instance can host multiple availability groups. That should allow, for example, the following situation - one SQL Server 2012 instance containing three primary replica databases - each one part of a different availability group - and each availability group's secondary replica located on a separate SQL Server 2012 instance.

Can you have three SQL Server 2012 instances, each with one primary replica database and have all three secondary replica databases on one SQL Server 2012 instance? So instead of, as above, going from 1 primary server to 3 secondary servers, this time we're going from 3 primary servers to 1 secondary server? The one secondary server would then contain all three secondary replicas for each of the 3 separate primary replicas.

This would mean that the single server (where all three secondary replicas reside) was part of three separate Windows Server Failover Clustering clusters.

Is this scenario possible?

View 4 Replies View Related

SQL Server 2012 :: MIN And MAX Dates For Groups Of Rows?

Jan 21, 2014

I have a dataset that contains an EmployeeID, StartDate, EndDate, and Location. Each record tells me at which location and employee was during a payroll period (fortnightly). So the data looks like:

EMP_KEYSTART_DTEND_DTLOCATION
120130117201301318103
120130117201301318103
120130131201302143354
120130131201302148103
220130117201301311234
220130131201302144567
120130214201302283354
220130214201302281234

Employees can be at multiple locations during the two weeks. They can work at one location, stop working there, start working somewhere else, and then maybe go back to their old location. There are duplicate records here as each employee can be at the same location multiple times during the two week period. What I need to capture is the actual start and end date of an employee at each location for each 'assignment'. An assignment is defined as a continual period of employment at a location with gaps of no less than 4 days - if there is a gap of more than four days then that is classed as a new assignment.

View 7 Replies View Related

SQL 2012 :: AlwaysON Availability Groups And Listeners

Feb 13, 2014

I have four instances and each instance have its own Availability Group with its own listener.

Would like to know if you can have one listener for multiple Availability groups?

View 2 Replies View Related

SQL 2012 :: Renaming AlwaysOn Availability Groups?

Apr 28, 2014

We had 3 Availability Groups set up in SQL 2012 last year but they were poorly named so I am just looking to rename them but there doesn't seem to be any command for it that I can find.Can they not be renamed once created? I guess I could just create new ones and move the DB's into them but just thought I would check!

View 1 Replies View Related

SQL 2012 :: Bulk Loading In Availability Groups

Aug 14, 2014

I have several 2012 availability groups running on a cluster. I have one database that is bulk loaded every 30 minutes. The DB is about 1 GB in size. To be on the availability group it has to be set to full recovery mode, but simple or even bulk would obviously be better. Is there a better way to handle the transaction log size other than to run a backup after each bulk load causing extra overhead? With mirrors you could use simple, but since those are going away . . .

View 2 Replies View Related

SQL 2012 :: Availability Groups - Missing Objects

Aug 15, 2014

I would like synchronizing all the missing objects (logins, agent jobs, SSIS, and anything else that I've missed) across SQL 2012 Availability Groups.

I would like to be able to able to automate this process....

View 2 Replies View Related

SQL 2012 :: Creating A Unique ID For Groups Of Rows?

Mar 20, 2015

We have a sorted data that looks like the first 3 columns below, and fourth colum is what I want to create)

Shift_start meal_break shift_endShift ID
1 0 0 1
0 0 0 1
0 0 1 1

1 0 0 2
0 0 0 2
0 0 1 2

I need to find a method to assign unique Shift IDS to rows that correspond to a single shift. For instance, the first shift would begin on the first row when shift_start flag is turned on, and end on the third row when shift_end flag is turned on.

Can I do this in SQL ?...some kind of grouping ?

View 2 Replies View Related

SQL 2012 :: Availability Groups And Bulk Loads

Sep 8, 2015

We have some tables that are bulk-loaded every day and they do not have RI to the other tables in the database.

To ease pressure on the logs, I had the idea of spinning them off to another database on the same AG in simple or bulk-load recovery model and using synonyms to point to them so the code base would not need changing.

I know an earlier bug in 2005 existed that basically made the query analyzer ignore indexes if a table was accessed via a synonym.

View 0 Replies View Related

SQL 2012 :: Re-index With AlwaysOn Availability Groups

Sep 15, 2015

We have multiple SQL 2012 SQL servers setup in an alwaysOn availability groups. Where should we schedule the re-index? We have Server1 as the primary and 2 secondaries Server2 and Server3. Are their any tricks to have it run on which ever one is the primary?

View 1 Replies View Related

SQL 2012 :: Availability Groups And Failover Cluster Instances

Mar 21, 2014

I'm reading up on Always on and I am confused what is the difference between AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances.

View 3 Replies View Related

SQL 2012 :: Failover Clustered Instance With Availability Groups

Oct 22, 2014

Approach 1:

Prod - shared storage between server 1 and 2
Server1: clustered SQL instance with availability group as primary
Server2: Passive server for clustered instance of PROD

DR - shared storage between server 1 and 2
Server1: Clustered SQL instance with availability group as replica
Server2: Passive server for clustered instance of DR

Approach 2: Using replicated SAN
Prod -
Server 1: Standalone instance with availability group as Primary
Server 2:Standalone instance with availability group as replica

DR -
Server 1: Offline until Disk group 1 (Prod server 1) has been broken and brought online at DR
Server 2: Offline until Disk group 2 (Prod server 2) has been broken and brought online at DR

Both these approaches will work wont they? I have only built and played with normal availability groups across servers, not mixing it with clustered instance replicated SAN

View 4 Replies View Related

SQL Server 2012 :: Rank On Repeating Groups Of Bit Field?

Jan 16, 2015

I have some data where a bit value changes over time and I want to rank it by the repeating groups, how do I write the SQL so that I get the result below?

I want to sort by create date, and every time the bit changes value we start the rank over.

If I partition by the bit field, it will just group the entire data set by the bit field.

Bit CreateDate Rank
1 3/15/2014 1
1 3/14/2014 2
0 3/9/2014 1
0 3/8/2014 2
0 3/6/2014 3
1 3/4/2014 1
0 2/28/2014 1
0 2/20/2014 2
0 2/15/2014 3
0 2/10/2014 4

View 3 Replies View Related

SQL 2012 :: High Availability Groups Service Restart

Jun 9, 2015

I inherited a SQL 2012 Ent server sitting on a 2008R2 server using AlwaysOn High Availability, two nodes.

Available Mode: Synchronous commit
Failover Mode: Manual
Connection in Primary role: Allow all connections
Readable secondary: No
seesion timeout: 10

Somebody decided to give SQL server priority boost so I need to change this ASAP. So I plan on doing the following.

1. Manually fail over to the secondary, which does not have the priority boost set to true
2. change the setting
3. restart the service
4. Manually fail over

My question is with the service restart. How does SQL handle if the DB changes on the new primary while the secondary is having the service restarted. Where can I see if the DB are sync again or if not where are they in the sync process.

View 2 Replies View Related

SQL Server 2012 :: How To Choose Max Of Each Groups Formed By Row Number

Sep 15, 2015

I have a below table as:

IF OBJECT_ID('tempdb..#Test') IS NOt NULL
DROP TABLe #Test
--===== Create the test table with
create table #Test([Year] float,
Age Int,
)
INSERT INTO #Test
([Year], Age)

[Code]...

I queried below to get additional column

Select *,row_number() over(partition by [Year] order by Age) as RN from #Test as

YearAgeRN
2014301
2014312
2014323
2015251
2015262
2015273
2015284
2015295

i want one more addtional column (Desired Output) with max of RN in each group as below"

YearAgeRNDesired output
20152515
20152625
20152735
20152845
20152955
20143013
20143123
20143233

View 7 Replies View Related

SAN's And File Groups

Jan 8, 2001

If you have a SAN, is there any real benefit to breaking out large tables into file groups?

View 1 Replies View Related

File Groups

Nov 3, 2007

I am looking to find out when to use file groups when backing up. When should you use this, what's the benefit over just doing a full db backup? Is it better when you are dealing with large db's?

Also this question has been on my mind for a while. Why shouldn't you shrink the db after every full backup? What is the negative in doing so?

Thanks

View 1 Replies View Related

File Groups

Jun 28, 2006

Hi everyone,
When I do the following, did I put the files in Test1FG1 file group to the default file group(Primary) ?

ALTER DATABASE Test1

MODIFY FILEGROUP Test1FG1 DEFAULT
GO

Thanks

View 8 Replies View Related

File Groups

Jun 26, 2006

Hi everyone,
While creating our database in only one disc(C or D), suppose that we create more than one file group in order to group our data files. However, in this situation; I wonder that whether it brings any benefit or advantage to us.

Also, I wonder that why we always have to put our data file into separate file group if we use separate discs for data files. Is not it allowed to use only one file group even if we use separate dics ?

Would you explain these to me ?

Thanks

View 13 Replies View Related

Reduce File Groups

Aug 17, 2000

I had a database that’s comprised of different file groups and log files spread out among different hard drives. I have recently upgraded the database to SQL 7.0 on a RAID 10 volume. I would like to consolidate all the file groups and files as well as various log files into one primary datafile and logfile. How do I do that? Thanks in advance.

View 2 Replies View Related

How To List All File Groups

Jul 20, 2004

How to list all the file groups

View 1 Replies View Related

Regaring File Groups

May 6, 2004

I am currently converting some Oracle scripts to SQL Server. Encountered this following code segment in a CREATE TABLE query :

CONSTRAINT ck_PK PRIMARY KEY ( O_ID) USING INDEX TABLESPACE DIRECT PCTFREE 10 STORAGE ( INITIAL 65536)) TABLESPACE DIRECT PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1)

what is the equivalent conversion in SQL Server. Is it just ON PRIMARY in the PRIMARY KEY clause ?? Are the Segments and Extents in Oracle equivalent to Filegroups in SQL Server.

Appreciate your help

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved