Issue – While Installing SQL Server 2012 on windows 2012 server I have received below error, which indicate .Net 3.5 is not installed on this machine. In windows server 2012 .Net 3.5 do not install by default as it’s come with .Net 4.5. Let’s learn how to install .Net 3.5 on windows 2012 box. Go… Read More NetFx3 error while installing SQL Server 2012
Problem – I have found VLF size of my database abc is very huge and when i have tried to shrink the file i have received below error – Could not locate file ‘xy_abc_Log’ for database ‘abc’ in sys.database_files. Resolution – While my investigation I have found logical fine name under sys.master_files is different as compare… Read More Could not locate file ‘xy_abc_Log’ for database ‘abc’ in sys.database_files.
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
Replication issues and resolutions Today I have encountered very strange issue – Scenario – Snapshot is not getting generated for newly added articles or Error when setting up Transactional Replication via SQL scripts. Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533 Specify all articles when subscribing to a publication using concurrent snapshot processing. Step performed to… Read More Replication – Snapshot is not getting generated
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
Configuring deadlock and Storing Deadlock events in a table SQL Server 2012 and above. Verification – Ensure Extended Event “system_health” is running. SSMS, checking Management -> Extended Events -> Sessions. These should be green arrow which indicate its running. The system_health session writes to two targets, both an event file and the ring buffer: … Read More Configuring deadlock and Storing Deadlock events in a table
Originally posted on Learn Sql Team:
SQL Server 2012 always on Step by step configuration – Prerequisite to configure always on – 1. Domain accounts for SQL Server services should have local admin access 2. Two IP addess One is for Cluster Other one is for always ON listener Note – These IP address should be in…
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.
SQL Server 2012 always on Step by step configuration – Prerequisite to configure always on – 1. Domain accounts for SQL Server services should have local admin access 2. Two IP addess One is for Cluster Other one is for always ON listener Note – These IP address should be in customer facing sub-net 3. UNC share… Read More SQL Server 2012 Always On Step by Step Configuration