Requirement – If you are planning to migrate your In House SQL Server to Amazon Cloud and wondering which option will be best for you b/w AMAZON RDS and SQL Server on EC2. Here, I have created comparison table based on AWS whitepaper and documents. Feature comparison :- Features RDS SQL on EC2 Contol AWS In house… Read More Comparison between AMAZON RDS and SQL Server on EC2
Issue – Login failed for user ‘xxx’. Reason: Server is in single user mode. Only one administrator can connect at this time. Cause – Ensure SQL Server is not running in Single user mode by checking start-up parameter. if you see -m; it mean your SQL is running with single user mode. -m : Starts an… Read More Login failed for user ‘xxx’. Reason: Server is in single user mode. Only one administrator can connect at this time.
Issue – Today I faced very weird issue; on multi instance SQL Server machine, couple of SQL Services was not starting after Server reboot ( In my case it was SQL 2014 and SQL 2016 ) and every time I was trying to restart my SQL Services, I was getting below error message – … Read More Server TCP provider failed to listen on [ ‘any’ 1433]. Tcp port is already in use.
My Development team contacted me and said they are not able to access database “The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context” when I looked into server server I found – Login was there and access was granted. When I looked into database I found user… Read More The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context
If you ever encounter restart computer error message while performing SQL Server patching and you don’t have opportunity\permission to restart your machine. You can bypass this situation by deleting value from PendingFileRenameOperations key. Here are the steps which you need to follow –> To Open Registry Editor. Click Start ( win + R ) –> Run,… Read More How to bypass restart computer error message while installing SQL Patch
Microsoft Best Practice Analyzer – Best Practices Analyzer (BPA) is a server management tool that is available in Windows Server® 2008 R2. BPA can help administrators reduce best practice violations by scanning one or more roles that are installed on Windows Server 2008 R2, and reporting best practice violations to the administrator. Administrators can filter… Read More Microsoft Best Practice Analyzer
We often need to create blocking alert in our Production server to capture existing blocking on server and keep record for future analysis. Here are steps which help us to create blocking alerts- Either you can receive blocking details through e-mail or you can log event id in application log and configure your monitoring tool to… Read More Create Blocking Alert – SQL 2005 , SQL 2008 R2 and SQL 2012
SSIS access permissions are managed via Component Service under DCOM Config 1. Run –> dcomcnfg.exe or Component Services -> Computers -> My Computer -> DCOM Config -> MSDTSServer 2.Right click on MSDTSServer or MSDTSServer100 ( based on SQL version ) 3.Click properties 4.Click on the Security tab 5.Select Customize and add the users/groups to the… Read More How to grant access on SSIS Server
SQL Server 2012 Upgrade from Standard Edition to Enterprise edition Step 1- Identify SQL executable file , Right click and run as admin Step 2 – click on Maintenance tab on left hand side. Step 3- Click on Edition Upgrade Step 4 – Click Okay Step 5- Click Next Step 6 – Click… Read More SQL Server 2012 Upgrade from Standard Edition to Enterprise edition
Scenario – In my SSRS report I have calculate Age field of Tickets like in 1 Day 2 Hours 20 min format and we are getting values in minute . Solution – I have created filed called Age(Minutes) in my SP or query IN SQL – datediff(minute,date_created, getdate()) as ‘Age (Minute)’ IN SSRS write expression = (… Read More SSRS – How to convert in Day/Hour/Minute format in SSRS report
How Get SQL Server Job Name with help of program name SQLAgent – TSQL JobStep (Job 0x0854F6E4F9E5AE48B164966C0553C8B0 : Step 4) Step -1 Create Below function SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[GetJobIdFromProgramName] ( @program_name nvarchar(128) ) RETURNS uniqueidentifier AS BEGIN DECLARE @start_of_job_id int SET @start_of_job_id = CHARINDEX(‘(Job 0x’, @program_name) + 7… Read More How Get SQL Server Job Name with help of program name
Being SQL DBA it’s one of Important task to have real time blocking alert on server. This example demonstrate how to configure blocking alert and capture blocking Step 1 – Create SP to check blocking log – This SP will help to identify blocking on server with specific time ( in this example it’s for 2… Read More How to create Blocking alert and generate e-mail alert.
Learn Sql Team 