By: Brady Upton | Updated: 2013-01-09 | Comments (9) | Related: 1 | 2 | 3 | 4 | More >Partitioning
I used this software to increase the space available on my system C: partition, and reduce the space on the backup D: partition. It is very easy to use. Just a matter of dragging and clicking, and the changes are done very quickly. The program also has the ability to create a bootable CD of the Partition Wizard. Acronis Disk Director. Acronis Disk Directoris a tool that enables you to optimize, protect,. All-in-one Disk Partition Manager. As best partition manager for Windows, MiniTool Partition Wizard aims to maximize disk performance. It helps create/resize/format partition flexibly, convert disk between MBR and GPT disks, convert partition between NTFS and FAT32, and convert dynamic disk to basic without data loss in a few clicks. Partition Magic Mac has all latest techniques that keep your drive safe and secure. The software allows you to make, resize or mix the existing partitions. You can create distinct storage space, arrange data, allows multitasking of operating system, modify partitions, preview, delete or opt for automatic partition option. Partition Wizard Pro, compatible with Windows XP, Vista, 7, 8, and 10-powered machines, gives you at-a-glance information about your computer's hard drives, though the blue user interface grates.
Problem
SQL Server Partitioning can be a bit confusing and can lead some companies in a different direction as far as archiving data. Writing the T-SQL code to create partition functions, partition schemes, and then altering the table to use the partition can be overwhelming for some DBA's. SQL Server 2008 introduced a table partitioning wizard in SQL Server Management Studio that helps make this task easier. In this tip, I'll show you how to use this wizard. Let's jump in.
Solution
Based on my experience, it seems as if SQL Server table partitioning is not commonly used and I'm not sure if it's because of the complexity and management of partitions or if companies simply don't have the knowledge of partitioning data. There are a few tips that introduce you to partitioning that you can find here. This tip will focus on the SQL Server Partitioning wizard as opposed to the ins and outs of partitioning.
To start the wizard, right click on the table you want to partition in SQL Server Management Studio and select Storage, Create Partition. In this example, I'm using AdventureWorks2012.Production.TransactionHistory.
On the Select a Partitioning Column screen as shown below you will need to select the column that you want to use to partition your table. The most common column used is a date column since most tables are being archived by date. In this example, I'll choose TransactionDate so I'll partition my table based on the date when the transaction was entered into this table.
Other options include:
Collocate this table to the selected partition table: Displays related data to join with the column being partitioned.
Storage Align Non Unique Indexes and Unique Indexes with an Indexed Partition Column: Aligns all indexes of the table being partitioned with the same partition scheme. If you do not select this option, you may place indexes independently of the columns they point to.
The next screen, Select a Partition Function, is where you will create the partition function. This will create a function that maps the rows of the table or index into partitions based on the values of the TransactionDate column. In this example, I'll name the function TransactionHistoryFunction.
The next screen, Select a Partition Scheme, is where you will create the partition scheme. This will create a scheme that maps the partitions of the Production.TransactionHistory table to different filegroups. In this example, I'll name the scheme TransactionHistoryScheme.
Minitool Partition Wizard Full Free
The next screen, Map Partitions, is where you will map your partitions.
The range and values are as follows:
Filegroup: Filegroup used for the partition.
Boundary: Used for entering range values up to a specified value. Left boundary is based on Value <= Boundary and Right boundary is based on Value < Boundary.
RowCount: Read-only columns that display required space and are determined only when the Estimate Storage button is clicked.
Required Space: Read-only columns that display required space and are determined only when the Estimate Storage button is clicked.
Available Space: Read-only columns that display available space and are determined only when the Estimate Storage button is clicked.
Estimate Storage: When selected, this option determines the rowcount, required, and available space.
If you click the Set Boundaries... button the wizard will generate boundaries for you. This is a nice feature because it's easy to change and doesn't involve any complexity.
Click OK on the Set Boundary Values screen and you will notice it separates the boundaries into 3 different rows based on years. I have already created 3 new filegroups that I will change to match each boundary.
Another cool feature of this wizard is the Estimate Storage button. If you click this button you can see the rowcount, required space, and available space as shown below.
The next screen is the Output screen. This screen allows you to generate the T-SQL, run immediately, or create a schedule to execute. In this example, I'll run immediately because we are working on a sample database rather than in production.
The final screen is the Summary screen that allows you to review your selections. Click the Finish button to partition your table.
Once the table is partitioned, you can run the following query to verify the filegroup name, partition scheme, partition function, and number of rows in each filegroup.
Next Steps
- To see more tips regarding partitioning click here
- Once the partition is created, you can go back to the table and select Storage, Manage Partition and this will bring up a new wizard that will allow you to create staging tables, switch out partitions, switch in partitions, and manage partitioned data in a sliding window scenario.
Last Updated: 2013-01-09
Partition Wizard Download
Minitool Partition Wizard 12 Free
About the author
View all my tips
To create a partition or volume (the two terms are often used interchangeably) on a hard disk, you must be logged in as an administrator, and there must be either unallocated disk space or free space within an extended partition on the hard disk.
If there is no unallocated disk space, you can create some by shrinking an existing partition, deleting a partition, or by using a third-party partitioning program.
To create and format a new partition (volume)
Open Computer Management by selecting the Start button. The select Control Panel > System and Security > Administrative Tools, and then double-click Computer Management.
In the left pane, under Storage, select Disk Management.
Right-click an unallocated region on your hard disk, and then select New Simple Volume.
In the New Simple Volume Wizard, select Next.
Enter the size of the volume you want to create in megabytes (MB) or accept the maximum default size, and then select Next.
Accept the default drive letter or choose a different drive letter to identify the partition, and then select Next.
In the Format Partition dialog box, do one of the following:
If you don't want to format the volume right now, select Do not format this volume, and then select Next.
To format the volume with the default settings, select Next.
Review your choices, and then select Finish.
Note: When you create new partitions on a basic disk, the first three will be formatted as primary partitions. Beginning with the fourth, each one will be configured as a logical drive within an extended partition.
To format an existing partition (volume)
Warning: Formatting a volume will destroy any data on the partition. Be sure to back up any data you want to save before you begin.
Open Computer Management by selecting the Start button. The select Control Panel > System and Security > Administrative Tools, and then double-click Computer Management.
In the left pane, under Storage, select Disk Management.
Right-click the volume that you want to format, and then select Format.
To format the volume with the default settings, in the Format dialog box, select OK, and then select OK again.
Notes:
You cannot format a disk or partition that is currently in use, including the partition that contains Windows.
The Perform a quick format option will create a new file table, but will not fully overwrite or erase the volume. A quick format is much faster than a normal format, which fully erases any existing data on the volume.