Posts

Always on setup end to end - Part 1 || Pre-requisites

-
Image
Hello readers,  Welcome to the new blog after a long time. In this blog I would like to discuss complete end to end always on setup guide for beginners. I will be posting couple of blogs regarding always on in SQL server. I believe that would be helpful to understand Always on in SQL server in easy way. Always on setup involved multiple steps. Hence one may find it difficult. However, if you could plan it well then you may find that always on is much easier to configure and handle. So, without wasting the time lets proceed further. Firstly, we will see what is Always on in SQL Server ? Always on is the high availability and disaster recovery solution introduced in SQL server 2012. As the name suggest, Always on means availability of database all the time even in case of disaster. We can say it is the alternative of mirroring. Only thing is windows failover cluster service also required to be configured as a pre-requisite for always on. In this blog, we will only d...
2 comments
Read more

Insufficient disk space in filegroup

-
Image
A few days before one of my DBA friend suggested to write a blog on database file growth issues and how to troubleshoot it.  I liked his suggestion as we DBAs often face space issues in production. In this blog, we will see what steps and precautions we can take if a database file growth issue is encountered.   To reproduce the issue below steps have been followed. 1) Created database “Growth” . 2) Added one filegroup named “Growth” and make it default.  3) Added one NDF named “Growth2.ndf” in the filegroup “Growth” and kept it on another drive. 4) Created Table named “employee_details” in filegroup “Growth” 5) Inserted data into the table and created multiple indexes with low fill factor till the datafile is not getting full.  Once the data file got full and there is no space available for new data, then the below error got triggered. The statement has been terminated. Msg 1101, Level 17, State 12, Line 10 Could not allocate a new page for database 'Growth' ...
Post a Comment
Read more

Disallowing page allocations for database '' due to insufficient memory in the resource pool 'default'.

-
Image
If you are getting bored, just go through SQL error logs to get interesting things. Just like I did it today. I was preparing for one blog post and while checking few things in error log I found one entry as below. 2022-11-03 13:50:05.080 spid12s      Disallowing page allocations for database 'trans_DB' due to insufficient memory in the resource pool 'default'. See 'http://go.microsoft.com/fwlink/?LinkId=510837' for more information. To know more about this issue, I went through the link given in the log. Also, referred my favorites SQL blog authors. After some analysis I found that, If you get the error message,  Disallowing page allocations for database '*\<databaseName>*' due to insufficient memory in the resource pool '*\<resourcePoolName>*'. See 'https://go.microsoft.com/fwlink/?LinkId=330673' for more information.  in the error log when the available physical memory is sufficient to allocate the page, it may be due...
1 comment
Read more

Skip SQL job execution on last day OR specific day of the month.

-
A few days before I got the requirement to schedule a job in a way that it should not run on the last day of the month. and as we know, in SQL job schedule we do not have any option to skip the job execution on a specific day.  But, we could able to achieve this using T-SQL. Below is the SQL which i have used to fulfill the requirement.  USE MSDB DECLARE @date as datetime; SET @date=(select convert(date,dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0)))) -- Getting last day of the month IF(dateadd(dd,0,DATEDIFF(dd,0,GETDATE()))=@date)  -- Comparing last day of the month with current date. begin exec sp_stop_job @job_name='<Job_Name>'   -- If condition satisfied then, job execution will stop end else begin exec sp_update_jobstep @job_name='<Job_Name>',@step_id=2  -- If condition is not satisfied then, job execution will proceed further. end We can add the above query as the first step in SQL job or a separate job can be created which would ca...
Post a Comment
Read more

Perform Automatic Switchover in Log Shipping

-
Image
 DR drill is one of the major activities of every DBA’s life. And if log shipping is in the picture, then we have to follow a long checklist. Many things we have to check like sync status, disable and top LS jobs on primary and secondary, get tail log backup, copy and restore the same, etc.  In such drills, achieving RTO and RPO is very important. Most of us might have come across the situation due to a small miss switch over or switch back got delayed. What if we could automate switchover and switch back activity in log shipping. If this could happen then we can easily achieve the RPO and RTO also human error can be avoided. I have tried to automate this process. I’m not too good in script writing. But I tried my best to complete this. I have written the scripts which perform the switchover without manual intervention. We just need to provide the database name for which switchover needs to be performed and then simply execute the script. There are two scripts written....
1 comment
Read more

Shortcuts || SSMS

-
Image
  Hello,   Today’s blog is going to be an interesting one. Many of us might be aware about SSMS shortcuts. If not then please go through this blog. In this blog I am going to share which shortcuts I often use and how we can have customized shortcuts. In SSMS we have many default shortcuts for quick actions and better interaction. I often use below default shortcuts while working on SQL server. Keys Action Alt+F1 Execute SP_HELP Ctrl+R Show/Hide query result Ctrl+M Include actual execution plan. Ctrl+L Display estimated execution plan. Shift+Alt+S Include client statistics Ctrl+K AND Ctrl+C Comment out selected line Ctrl+K AND Ctrl+U Uncomment selected line   Apart from this there are many shortcuts which we can use to add more fun. Please find below URL for more shortcuts. SQLServer Man...
1 comment
Read more