Hi .... Friends Well Come ....
NOTE:- Frequently Asked Question In Interview.
Today I am sharing very interesting Topic with you. I always get problem in add new FileGroup in SQL Server. But today I got the solution that is How to create new and Multiple FileGroups in Single Database.
There are two way to do this thing.
1) Using T-SQL
2) Using SSMS (Sql Server Management Studio) Wizard
Solution: Now we will know How to Updating Existing Database and Adding Filegroup
1) Creating New Database And Adding Filegroup
CREATE DATABASE [DBFGTest] ON PRIMARY
( NAME = N'DBFGTest',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBFGTest.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ),
( NAME = N'DBFGTest_2',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBFGTest_2.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB )
( NAME = N'DBFGTest_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBFGTest_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 10%)
2) Using SSMS (Sql Server Management Studio) Wizard (Please Try This way also..)
a) Rigth click on New Database and Wirte New database Name
b) Select File option in properties (Showing on Left side in Dialog box)
Use this Operation
Do this operation In Showing Grid in Database Property Windows
Write the file name for the database file.
Write the location of the database file, or click the ellipsis to navigate to the location.
Initial size (MB)
Write the initial size of the database file in megabytes (MB).
Write the name of the filegroup to which the database file will belong.
D) Click OK Button .
This is very simple way to create Multiple filegroup in singale database ..
If you like this comment then please Comment it ...Thanks
Answer: Locking is a method which is used by the Microsoft SQL Server Database Engine to. It’s provide the synchronize access of Data by one or more than one users to the same piece of data at the same time.
Question: Why we use locking?
Answer: Generally user writing the query without Lock. But in the huge amount of database we should not write query or SQL script without Locking. Because When transaction running, Locking prevent the Dirty Read, it’s avoid the deadlock. We can utilize the resource of database
Question: Types of Locking?
These are the types of locking which we can use in the database
- · ROWLOCK
- · PAGLOCK
- · TABLOCK
- · DBLOCK
- · UPDLOCK
- · XLOCK
- · HOLDLOCK
- · NOLOCK
SQL Server- Database Engine Tuning Advisor
Before entering to this article concept I am sharing one thing…A Few days ago I have given the Interview in one of the IT leading company and there interviewer asked to me many questions. There was one question was ”What is Database Engine Tuning Advisor (DTA) and its utility?” Ok. Now,
In SQL Server you can do following tasks with DTA.
1. As we understand we can troubleshoot the performance of a specific problem query
2. Using DTA Tool we can perform the tuning operation on large set of queries across one or more databases
3. It’s used for optimal physical design changes on the database
4. Minimize and utilize the storage space of the database.
5. Using this we can save the Disk space.
6. Using it we can remove unwanted existing Index, View and merge the table partition and database partition.
7. DTA is not only gives the suggestion even its gives the method and script to perform the operation in the recommended object.
8. When we execute the analysis it return the Latest progress, recommendation and finally its return the Report. In the report we can find the following things-
Maximum tuning time 50 Minutes
Time taken for tuning 1 Minute
Estimated percentage improvement 0.00
Maximum space for recommendation (MB) 251
Space used currently (MB) 161
Space used by recommendation (MB) 115
Number of events in workload 30
Number of events tuned 30
Number of indexes recommended to be dropped 36
Number of indexes on views recommended to be dropped 2
There are many tasks that can do in the DTA.