About Me

My Photo
Mumbai, Maharastra, India
He has more than 4.2 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in the ASP.Net, C#, VB,SQL Server, AJAX. You can reach him at viki_keshari@yahoo.co.in or viki.keshari@gmail.com http://www.facebook.com/viki.keshari

Tuesday, December 16, 2014

Partitioning Large table


Partitioning is a feature is introduced to aid in maintenance on larger tables and to present fast ways to load and remove large amounts of data from a table. Partitioning can boost query performance.

Each table has a default partition which we call it PRIMARY partition which contain all data. To add partitions, you need to define a partition function and the corresponding partition scheme. A partition scheme uses the partition function to map data ranges to appropriate file groups. If the partition function defines three ranges, then partition scheme must map them to four file groups. Three ranges defined by the partition function and the forth, catchall range, for all data outside the boundaries of the predefined ranges.

Lets take an example to understand the partition. Here we are creating a table with three field id, name and salary. Later we will base of partition on ID field.

create table tab_partitionDemo
(id int identity(1,1),
name varchar(150),
salary int)
Command(s) completed successfully.

Lets insert 1000 dumy records in newly created table.

declare @rwCnt int = 10000
while @rwCnt >= 1
begin
     insert into tab_partitionDemo values('abc' + cast(@rwCnt as varchar), 10*rwCnt)
     set @rwCnt = @rwcnt -1
end

Now we have our table ready, let add two file group. A filegroup is a logical storage unit. Every database has a primary filegroup that contains the primary data file (.mdf). An additional, user-defined, filegrups can be created to contain secondary files (.ndf). We will create two file group here, one which will store first 5,000 records and the other one will store data from 5,000 to 10,000.

ALTER DATABASE TestDB ADD FILEGROUP partitionDemoFG01_50K 
GO 
ALTER DATABASE TestDB ADD FILEGROUP partitionDemoFG50K_10K 
GO 
Command(s) completed successfully.

Now its turn to add file(.ndf) to the newly created filegroup, which will hold data.

ALTER DATABASE TestDB
  ADD FILE
  (NAME = N'data_01_10',
  FILENAME = N'D:\SQL Server\Data\data_FilePartitionDemoF01_50K.ndf',
  SIZE = 5000KB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP partitionDemoFG01_50K 
GO 
ALTER DATABASE TestDB
  ADD FILE
  (NAME = N'data_11_20',
  FILENAME = N'D:\SQL Server\Data\data_partitionDemoF50K_10K.ndf',
  SIZE = 5000KB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP partitionDemoFG50K_10K
GO 
Command(s) completed successfully.

To check the file created we can query sys.database_files

SELECT name as [FileName], physical_name as [FilePath]
FROM sys.database_files where type_desc = 'ROWS'
GO

Now its time to create partition function, a partition function is one which maps the table data to the partition based upon the partition column. Here in below code we are creating 3 partition on the bases of int value field i.e ID.

CREATE PARTITION FUNCTION IDPartitionFunction (int)
AS
RANGE LEFT FOR VALUES (5000,10000 );
Command(s) completed successfully.

Now we need to map the partition to each partition group with the help of Partition Schema.

CREATE PARTITION SCHEME IDPartitionSchema
AS PARTITION IDPartitionFunction
TO (partitionDemoFG01_50K,partitionDemoFG50K_10K,[PRIMARY]);
Command(s) completed successfully.

Now we need to redistribute the table data to various partition, for that we need to either create clustered index on selective table column or if it already present then drop and recreate with On clause on Partition column.

create clustered index ix_tab_partitionDemo_id
on  dbo.tab_partitionDemo(ID)
ON IDPartitionSchema(id)
Command(s) completed successfully.

This is the only thing one need to do for partition, lets check whether our data get distributed to various partition by querying sys.index table.

SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE '%tab_partitionDemo%'

objectname        indexname               partition_id      partition_number  rows
tab_partitionDemo ix_tab_partitionDemo_id 72057594148093952 1                 5000
tab_partitionDemo ix_tab_partitionDemo_id 72057594148159488 2                 5000
tab_partitionDemo ix_tab_partitionDemo_id 72057594148225024 3                 0

Here we saw 10K records of the table get distributed to two partition. Now lets add one more records with partition column value more then 10k.

insert into dbo.tab_partitionDemo values('Rakesh',40000)

SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE '%tab_partitionDemo%'

objectname        indexname               partition_id      partition_number  rows
tab_partitionDemo ix_tab_partitionDemo_id 72057594148093952 1                 5000
tab_partitionDemo ix_tab_partitionDemo_id 72057594148159488 2                 5000
tab_partitionDemo ix_tab_partitionDemo_id 72057594148225024 3                 1

So we can see the newly created row go to primary partition. That’s what we were expecting. Cool!!!

SQL Server, where there is mountain of happiness sits :)


Post Reference: Vikram Aristocratic Elfin Share

Sunday, December 14, 2014

Database bakup with PDF extension

Yes, I know you drag your buttock to the edge of your chair, be aware don’t let it fall on ground, they are precious, handle it with care :D. SQL Server does not have problem with the file extension as long as the file is a valid file. So you can name you bakup extension with any type.

But general industry standard says .BAK for full bakup, .DIFF for Differential and .TRN for transactional backup.

Let try out with .PDF extension.

BACKUP DATABASE [TestDB] TO  DISK = N'D:\SQL Server\myDatabaseBakup.pdf'
WITH FORMAT,  MEDIANAME = N'MyTest1',  NAME = N'TestDB-Full Database Backup'
GO
Processed 11432 pages for database 'TestDB', file 'TestDB' on file 1.
Processed 2 pages for database 'TestDB', file 'TestDB_log' on file 1.
BACKUP DATABASE successfully processed 11434 pages in 5.080 seconds (17.582 MB/sec).

Lets now try to restore it.

USE [master]
RESTORE DATABASE [MyTest101] FROM  DISK = N'D:\SQL Server\myDatabaseBakup.pdf'
WITH  FILE = 1,  MOVE N'TestDB' TO N'D:\SQL Server\Log\MyTest101.mdf',  MOVE N'TestDB_log' TO N'D:\SQL Server\Log\MyTest101_log.ldf'
GO
Processed 11432 pages for database 'MyTest101', file 'TestDB' on file 1.
Processed 2 pages for database 'MyTest101', file 'TestDB_log' on file 1.
RESTORE DATABASE successfully processed 11434 pages in 4.236 seconds (21.086 MB/sec).

Conclusion: SQL Server does not have problem with the file extension as long as the file is a valid file.

For coders world is colorful, this is just because of you SQL Server :) Ah! You are evergreen!!!

Post Reference: Vikram Aristocratic Elfin Share

Know the type of bakup and database without restoring it to your database

I was killing out time with one of my SQL DBA community, while juggling various queries, a particular question squeeze my interest lime, the question was

How to find which database and type of bakup it is without restoring the bak file 

The answer to this is very simple, lets try to see with an example, I have a bakup file at “D:\SQL Server\myBakupFile.bak” lets see how we can get the detail of bakup type and database name without restoring it.

Step1: Verify whether the bakup file is valid for restore.

RESTORE VERIFYONLY FROM
disk = 'D:\SQL Server\myBakupFile.bak'
The backup set on file 1 is valid.

Step2: Get the header info of the bak file.

RESTORE HEADERONLY FROM 
disk = 'D:\SQL Server\myBakupFile.bak'

BackupName                  DatabaseName 
--------------------------  ---------------
TestDB-Full Database Backup TestDB       

(1 row(s) affected)

That’s all!!! :D pretty simple

Conclusion: Use HEADERONLY option of Restore to get the bak file information without actually restoring the bakup file to the server.

Pretty simple you are, anyone who came closer to you, fall in love with you, SQL Server :)

The media set has 2 media families but only 1 are provided.



Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

This is very common error, which generally comes with Restore and Backup. To dig into the problem lets stimulate this error.

Here I have a bakup file stored in D:\ drive, I want to use RESOTE common to verify the bak file.

RESTORE VERIFYONLY FROM
Disk ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Backup\TestDB.bak'

Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

As I hit F5, I got this error, the error clearly specify, that the backup of the database wrote its data to two files, not one.   Therefore, you need both files in order to do a successful restore.

To locate the missing part you can use the following query.

SELECT b.physical_device_name
FROM msdb..backupmediaset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.media_uuid = '<Media_UUID>'

Now Media_UUID you can get from

RESTORE LABELONLY FROM
disk = 'D:\SQL Server\myBakup.bak'

MediaName MediaSetId                          
----------------------------------------------
NULL      A335C850-4788-4A6B-BAE0-984AF5F4E6CC

Now here we have MediaSetId, we can use this mediaSetId to the above query to find the missing bak file location.

SELECT b.physical_device_name
FROM msdb..backupmediaset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.media_uuid = 'A335C850-4788-4A6B-BAE0-984AF5F4E6CC''

physical_device_name
--------------------------------------------------------------------------------------
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Backup\TestDB.bak
D:\SQL Server\myBakup.bak

So here we found that the bakup was splited into two different file.  Now we can use both the RESTORE Command to use both the

RESTORE VERIFYONLY FROM
disk = 'D:\SQL Server\myBakup.bak',
Disk ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Backup\TestDB.bak'

The backup set on file 1 is valid.

Conclusion: You can msdb..backupmediaset and msdb..backupmediafamily table to find the missing file location.

The only gears that loom in coders dream are their love to code. Love you dear SQL Server  :)


Post Reference: Vikram Aristocratic Elfin Share