Sql Server Administration Made Easy

14/07/2021

Hi Folks,

Introducing online training session from AWS Cloud Architect.

Duration: 2 Weeks
Timings: 2 hours per day.

Actual timings can be planned depending on the batch size and number. Below are the syllabus which will be covered. Please do reach us for detailed information and get start your career as AWS cloud administrator.

Contact: 91-9944344850

1: Why Cloud ?
2: AWS – An overview
3: Understand Identity Access Management of AWS
4: EC2 Instance
5: Auto-scaling
6: Load Balancing
7: EBS (Elastic Block Storage)
8: Object Storage in Cloud
9: Cloud Front
10: Route53
11: AWS Security Management
12: Amazon Virtual Private Cloud (VPC)
13: Relational Database Service (RDS)
14: Monitoring Servics
15: Application Services – An Overview
16: AWS Troubleshooting
17: AWS Architecture and Design
18: DevOps Fundamentals

All the best for your career !!!!

This page is created to help upcoming DBA's and already working professionals to gain more knowledge on SQL SERVER administration.

09/04/2021

UHG hiring for below position, interested one can send his/her details on [email protected] with job code.

21/11/2018

Hello All,

Recently we posted about the weekend drive at Hcl Chennai.
How many of you attended the interviews? Anybody got selected? Waiting for offer letter?

You can share your experience and current status so that others may be aware of the situation and you too will get some information.

15/11/2018

SQL DBA Interview with HCL Technologies this weekend Saturday 17th November 2018 for experience between 4 to 9 years.

Venue:
HCL TECH Ltd. , Chennai-SEZ,
Ground Floor Old Cafeteria ,
Sholinganallur – 119

POC : Kamesh / Sathish

Note: As per candidate experience attended earlier, there were technical written test and then f2f technical discussion.

All the best guys.

18/10/2018

http://www.sqldepository.com/how-to-take-performance-counters-in-sql-table-or-flat-files-using-command-prompt/

how to take performance counters in SQL table or Flat Files using Command Prompt Posted on January 4, 2018January 4, 2018 by sauras87 Introduction: In this post, We will use TYPEPERF command prompt utility to capture the perfmon counters. Demo: Capture performance counters in to SQL Tables: You need...

18/07/2018

Online MS SQL database administrator training is provided.
Students or professionals who are aiming to make a shift in their technology, add additional technology or junior DBA's who wish to enhance their knowledge are most situated for this course.
What's special?
1.Beyond normal SQL server administration training, this will include real time experienced DBA's training you on all the modules with practical scenarios.
2. Real environment demo will be provided.
3. Complete day to day activities of DBA's will be detailed along with various scripts and documentation on SQL server will be provided.
4. Interview questions and how to handle DBA interviews too will be trained.

Contact - 9944344850 or comment your number to get a call back.

19/12/2016

What are you going to do when you restore SQL databases and you get error as below ?????

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

1.

1.a Check the sysprocesses table and find whether is there any query running against the database and locking it ?

select spid, dbid, hostname, program_name from sys.sysprocesses where DBID = db_id('dbname')

dbcc inputbuffer(spid)

Check the event info and confirm is it not any critical query which is running. if so, wait until it completes. Else proceed in killing the SPID.

1.b Once found the spid from sysprocesses table, kill the spid.

KILL SPID

Now restore the database.

2. Still uanble to restore,

Take the database to single user mode with rollback immediate.
Ex: ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE

also can use "WITH NO_WAIT". This options waits until all current transaction completes. This is one of clean process to be followed.

Then proceed with restoring,

Ex: RESTORE DATABASE db_name FROM DISK = 'C:\db_name.BAK'
GO

3. Still unable to restore, see the % of rollback happening,

SELECT percent_complete FROM sys.dm_exec_requests WHERE session_id = SPID

Happy learning :)

20/05/2016

Temp database in SQL SERVER:
-----------------------------------------

Temp database is a global resource for all users connected to an instance. We are not using tempdb directly, but we are using tempdb while creating temp tables, cursors, rebuilding indexes etc.

Tempdb is mostly like a user database in physical level i.e. it contains two files ldf and Mdf.Tempdb is in simple recovery model, which means it is minimally logged.

Tempdb is created each time when SQL Server instance is restarted. So there is no need for moving tempdb to another server. We can change the location of the tempdb files such as ldf and mdf to new location using the command

USE master;

GO

ALTER DATABASE tempdb

MODIFYFILE(NAME = tempdev, FILENAME = 'C:Programfiles\SQL Server \SQL Files\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFYFILE(NAME = templog, FILENAME = 'C:Programfiles\SQL Server \SQL Files\templog.ldf');

GO

Tempdb consists of :

* When we are creating temporary tables, global temporary tables, cursors.
* Tempdb is used by the SQL Server database engine, for creating work tables for doing sorting operations such as Group by, Union etc.
* Row versioning that are generated by data modifications transactions in a database.
* Row versioning that are generated by data modification such as online index operations, Multiple Active result sets and After triggers.

Sizing the tempdb :
----------------------

Default installation of any SQL Server edition will create a tempdb database with an 8MB data file and a 1MB transaction log file. For a lot of SQL Server installations these file sizes won’t be enough, but they are configured to autogrow by 10% as needed. You can see the properties window for tempdb on a default installation of SQL Server 2012 Developer Edition

Although the autogrow feature enables a more hands-off approach to maintaining many SQL Server installations, it’s not necessarily desirable because the files cannot be used while they are autogrowing, and it can lead to fragmentation of the files on the hard disk, leading to poor performance.
This is a recommendation that would apply to any SQL Server database, but for tempdb it’s even more relevant. When you restart your SQL Server instance, tempdb is re-created (files will be reused if they already exist) and sized to the value specified in the database properties, which as you’ve just seen is only 8MB for the data file and 1MB for the log file by default.
The next time SQL Server is restarted, tempdb will be just 8MB and will have to start autogrowing all over again

In this case, you can see the size of the initial files, which the DBA has set to 200MB and 50MB. The workload running against SQL Server has then caused the tempdb files to autogrow to 2450MB and 560MB.
SQL Server is then restarted and tempdb returns to 200MB and 50MB, as set by the DBA, and would have to autogrow again to fulfill the workload.

if you can give tempdb its own disk, then configure it to almost fill the drive. If nothing else will ever be on the drive, then you’re better off setting it to be larger than you’ll ever nee, and you’ll never have to worry about autogrow again.

If you’ve configured multiple data files and you want to allow autogrow, consider enabling trace flag 1117, which will force all data files to grow uniformly so you don’t break the load balancing between files.
For transaction log files, however, you need to be a lot more conservative and use a figure that balances the time it takes to autogrow and the usefulness of the extra space. Autogrowing by 1MB, for example, is quick, but you might need to do it so often that it becomes a bottleneck. Autogrowing by at least 10MB for the transaction log is a good place to start, but you may need it to be higher to provide enough space to avoid autogrowing again quickly. The best option is to avoid autogrowing in the first place by correctly sizing the files.

Still sizing tempdb requires lot more in depth information about the applications hitting the database and queries running. Regular monitoring the performance of database related to temp db helps in sizing and setting its growth perfectly.

Good Luck !!!

05/05/2016

What is shrinking .MDF file and how to do it ?
---------------------------------------------------------

Shrinking Operationis used to recover space by removing pages an empty space from database files and release the empty space either to the Operating System or to SQL Server. Shrinking does not mean compressing the file.

Shrink Option is effective after an operation that creates lots of unused space like drop table or truncate table operation.
If you shrink the database repeatedly and sees that the database size grows again after shrinking, it means that the space that was shrunk is needed for regular operations. Repeatedly shrinking the database is a wasted operation.
Repeatedly shrinking the database increases fragmentation to a degree as it does not preserve the fragmentation state of indexes in the database.
AUTO_SHRINK database option should not be kept ON unless there is a specific requirement.

Steps to shrink .mdf file:

Using SQL Server Management Studio (SSMS)
------------------------------------------------------------

1. Connect to the SQL Server Management Studio, Go to Databases
2. Select the desired database that needs to be shrunk
3. Right-click on the database, Select Tasks >> Shrink >> Files
4. Make sure that you choose Data File type for shrinking MDF File

The options of Shrink Option are as follows:

Release Unused Space
Reorganize pages before releasing unused space and Shrink File to desired size — in MB (Minimum is 3MB). Since the minimum data file size is 3MB, the shrink file size should be equal or more than 3MB while for log file it can be 0 MB.
Empty File by migrating the data to other files in the same group

After the backup of the log file is taken, there will be unused space. So, the release Unused space is often used while shrinking the log file.Second shrink option is advisable for shrinking the Master Data File (MDF). Select this option ‘Reorganize pages before releasing unused space’ and Click on OK

Using T-SQL Script
-------------------------

For shrinking MDF file using T-SQL script, following commands will be used.

USE Demo
GO
DBCC SHRINKFILE (N ‘Demo’, 3)
GO

Demo is the database name and 3 MB is the size to which the file will be shrunk.

Execute the command to shrink the MDF file.

03/05/2016

Did you know ? http://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/

It’s been an incredible year for the data business at Microsoft and an incredible year for data across the industry. This Thursday at our Data Driven event in New York, we will kick off a wave of launch activities for SQL Server 2016 with general availability later this year. This is the most signif…

03/05/2016

Use of fn_dblog:
---------------------

fn_dblog({BeginLSN | NULL}, {EndLSN | NULL})

This function is used to return active (or un-truncated) part
of transaction log file.

Examples:

select * from fn_dblog(NULL, NULL) -- returns all available T-Log records

select * from fn_dblog(NULL, '00000026:00000027:0009') -- returns all available T-Log records upto LSN '00000026:00000027:0009'

select * from fn_dblog('00000026:00000027:0009', NULL) -- returns all T-Log records starting from LSN '00000026:00000027:0009'

select * from fn_dblog('00000026:00000027:0001','00000026:00000027:0009') -- returns T-Log records between these LSNs

fn_dblog function has 116 columns (SQL 2008 R2). Just try below command.

SELECT
[Previous LSN],
[Current LSN],
[Transaction ID],
[Operation],
[CONTEXT],
[AllocUnitName],
[Page ID],
[Slot ID],
[Offset in Row],
[Transaction Name],
[Begin Time],
[End Time],
[Number of Locks],
[Lock Information],
[RowLog Contents 0],
[RowLog Contents 1],
[RowLog Contents 2],
[RowLog Contents 3],
[RowLog Contents 4],
[Log Record],
[Log Record Fixed Length],
[Log Record Length]
FROM fn_dblog(null,null)
GO

So with this query we are able to query the transactional log and list of LSN numbers, who executed the command etc. Thus it helps in capturing the more critical information which are not stored in error log before the transactional log is cleared.

02/05/2016

SQL server - Introduction to Resource governor:
----------------------------------------------

Resource Governor is a feature which can manage SQL Server Workload and System Resource Consumption. We can limit the amount of CPU and memory consumption by limiting

on the SQL Server.

Why is resource governor required?

If there are different workloads running on SQL Server and each of the workload needs different resources or when workloads are competing for resources with each other

and affecting the performance of the whole server resource governor is a very important task.

What will be the real world example of need of resource governor?

Here are two simple scenarios where the resource governor can be very useful.

Scenario 1: A server which is running OLTP workload and various resource intensive reports on the same server. The ideal situation is where there are two servers which

are data synced with each other and one server runs OLTP transactions and the second server runs all the resource intensive reports. However, not everybody has the

luxury to set up this kind of environment. In case of the situation where reports and OLTP transactions are running on the same server, limiting the resources to the

reporting workload it can be ensured that OTLP’s critical transaction is not throttled.

Scenario 2: There are two DBAs in one organization. One DBA A runs critical queries for business and another DBA B is doing maintenance of the database. At any point

in time the DBA A’s work should not be affected but at the same time DBA B should be allowed to work as well. The ideal situation is that when DBA B starts working he

get some resources but he can’t get more than defined resources.

1) Internal –This is used by database engine exclusives and user have no control.

2) Default – This is used by all the workloads which are not assigned to any other group.

major components of the resource governor

1. Resource Pools
2. Workload Groups
3. Classification

Process flow of resource governor,

Create resource pool
Create a workload group
Create classification function based on the criteria specified
Enable Resource Governor with classification function

SQL SERVER - Simple Example to Configure Resource Governor - Introduction to Resource Governor

Configure resource governor with T-SQL?

Step 0: Here we are assuming that there are separate login accounts for Reporting server and OLTP server.

/*-----------------------------------------------
Step 0:
Create Two User Logins
1) ReportUser, 2) OLTPUser

Use ReportUser login for Reports workload
Use OLTPUser login for OLTP workload
-----------------------------------------------*/

Step 1: Creating Resource Pool

We are creating two resource pools. 1) Report Server and 2) Primary OLTP Server. We are giving only a few resources to the Report Server Pool as described in the

scenario 1 the other server is mission critical and not the report server.
-----------------------------------------------
-- Step 1: Create Resource Pool
-----------------------------------------------
-- Creating Resource Pool for Report Server
CREATE RESOURCE POOL ReportServerPool
WITH
( MIN_CPU_PERCENT=0,
MAX_CPU_PERCENT=30,
MIN_MEMORY_PERCENT=0,
MAX_MEMORY_PERCENT=30)
GO
-- Creating Resource Pool for OLTP Primary Server
CREATE RESOURCE POOL PrimaryServerPool
WITH
( MIN_CPU_PERCENT=50,
MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=50,
MAX_MEMORY_PERCENT=100)
GO

Step 2: Creating Workload Group

We are creating two workloads each mapping to each of the resource pool which we have just created.
-----------------------------------------------
-- Step 2: Create Workload Group
-----------------------------------------------
-- Creating Workload Group for Report Server
CREATE WORKLOAD GROUP ReportServerGroup
USING ReportServerPool ;
GO
-- Creating Workload Group for OLTP Primary Server
CREATE WORKLOAD GROUP PrimaryServerGroup
USING PrimaryServerPool ;
GO

Step 3: Creating user defined function which routes the workload to the appropriate workload group.

In this example we are checking SUSER_NAME() and making the decision of Workgroup selection. We can use other functions such as HOST_NAME(), APP_NAME(), IS_MEMBER()

etc.
-----------------------------------------------
-- Step 3: Create UDF to Route Workload Group
-----------------------------------------------
CREATE FUNCTION dbo.UDFClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE AS SYSNAME
IF(SUSER_NAME() = 'ReportUser')
SET = 'ReportServerGroup'
ELSE IF (SUSER_NAME() = 'OLTPUser')
SET = 'PrimaryServerGroup'
ELSE
SET = 'default'
RETURN
END
GO

Step 4: In this final step we enable the resource governor with the classifier function created in earlier step 3.
-----------------------------------------------
-- Step 4: Enable Resource Governer
-- with UDFClassifier
-----------------------------------------------
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.UDFClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
Step 5: If you are following this demo and want to clean up your example, you should run following script. Running them will disable your resource governor as well

delete all the objects created so far.
-----------------------------------------------
-- Step 5: Clean Up
-- Run only if you want to clean up everything
-----------------------------------------------
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR DISABLE
GO
DROP FUNCTION dbo.UDFClassifier
GO
DROP WORKLOAD GROUP ReportServerGroup
GO
DROP WORKLOAD GROUP PrimaryServerGroup
GO
DROP RESOURCE POOL ReportServerPool
GO
DROP RESOURCE POOL PrimaryServerPool
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

I hope this introductory example give enough light on the subject of Resource Governor. In future posts we will take this same example and learn a few more details.