Posts

Powershell script to automatically send HTML emails using gmail smtp

Hi please find the script/code below which can be used (as a example  use case) for sending automated reminders for filling time-sheets  to all employees on every Friday(can be any day depending on when you schedule it to run using windows task scheduler as per your requirements) using gmail gsuite or any other email providers:

Things Required:
SMTP outgoing port(587) allowed through firewallsNo-reply email which will be used for sending emailsApplication password (as gmail blocks sending emails from scripts if direct credentials(username & normal password) are used and sends warning alerts and notifications to owner and eventually blocks the account if not rectified thus application password needs to be created and used to prevent that from happening,steps for creating same are given below)Server which runs 24x7 where we will place and schedule our scripts to run at specific time.(You can use your local PC systems and execute script manually by double clicking if required)Steps:
To…

Part 2: List of Questions I have been asked in Interviews

Image
This post is in continuation of Part 1
How will you minimize downtime during a migration will least human effort ? (Mirroring)What is listener?How does listener comes to know which is secondary and which is primary ?(IP SUBNETS)Can we take backups in Always on Secondaries ?What all types of backups can you take in always on secondaries ?(COPY ONLY else it breaks log chain)Why replication when logshipping is available?(Load balancing vs disaster recovery respectively)How many server instances are required for setting up mirroring?(2 without witness (manual failover),3 with witness (automatic failover)Can we set up automatic failover in mirroring without witness server instance?Can you please explain steps for installing mirroring ?Can you please explain steps for installing logshipping?Why do people upgrade and update sql server instances when they have to pay more money for licensing cost even if they are not using new features that are made available?(bug fixes,performance improvemen…

Things DBA's get asked during a day on Job

Image
Things people ask to DBA's during a day on his\her Job:
Why this query is running slow?Why this query is running slow from app but not from ssms?Why this query is not picking up \using the right indexes?Why this query is using right indexes when running from ssms and not when executed from app?Why this query is running fast as SQL statement but running slow when same query is wrapped as a stored procedure?Why query is timing out and in result application is throwing 50* errors?Do we have proper indexes?Can we create index on this column of that table?How much time will it take to defragment the index?Are index fragmented?Can we rebuild the index now without downtime ?Can we reorganize indexes without downtime ? How much time will it take ?Why server is slow ?(When Backup and Update stats are running)Can you make query use right index but not make use of option(recompile) or any other query hint at the same time?Why people are unable to login ?Why people are not able to change passw…

Script to delete the files older then 2 days from All Available Drives On Servers:\AnyDirectory

Image
Script below can be used to delete the files which are older then 2 days from All Available Drives On Servers:\AnyDirectory.If you want to filter files with some particular extension please add the following handle to the script :/m (searchmask) Searches files according to the specified search mask. The default search mask is *.\*.More information about parameters and variables available can be found here @ECHO off Rem=================================================================================================================== Rem AUTHOR: SHIVAM KUMAR | https://www.linkedin.com/in/shivamkumars Rem VERSION: 1.0 Rem DESCRIPTION: Script to delete the files which are older then 2 days from "All_Available_Drives_On_Servers:\AnyDirectory" directory Rem How to use: Select the servers from hpsa and right click and run script,no user input is required. Rem Disclaimer: The script has to be run on proper timings after the backup or what ever patching activity else you might loose d…

Adding a new table to Replication in MSSQL Server without reinitializing the Snapshot

Image
There are some prerequisites for Tables to be a part of replication:
It should have a PRIMARY KEYIt should have IDENTITY NOT FOR REPLICATION NOT NULL: If prerequisites are not met for Primary key you will see the following error:
Msg 14088, Level 16, State 1, Procedure sp_MSrepl_addarticle, Line 1539 [Batch Start Line 0] The table '[dbo].[T_YourTableNameHere]' must have a primary key to be published using the transaction-based method. Though NOT FOR REPLICATION FOR IDENTITY will not throw any error during the time you are adding the table to replication but it will start throwing errors once you will start inserting rows or data into the table and I am guessing you must have heard this earlier "prevention is better than cure".

Alright once prerequisites has been taken care of  this tasks can be accomplished using the following steps :
Create table in Publisher Node.Create table in all Subscriber Nodes.Use sp_addarticle to add and publish the new table in your public…

List of Questions I have been asked in Interviews

Image
Note: If you are an interviewer and I have a interview scheduled with you in upcoming days or hours, please ask things which are not listed below so that I can have a opportunity to learn few more new things thanks ;)

This Page will be updated from time to time as I face new questions:
Do you know about System Pages GAM SGAM DCM ?Different modes available in Always on?Whats the difference between Synchronous and Asynchronous modes available?Which technology and version facilitates automatic page repairs in SQL Server?Difference between 2016 and 2012 version of SQL Server (New things that are available or absent).If latency is being observed in secondary replicas and transaction log on primary replica is full and not releasing space even after taking backup and shrinking and there is no open transactions what could be the possible reason and its resolution?What is the need of Bulk log recovery model apart from its minimally logged benefit for bulk operations?How did you optimized update…

T-Sql Script for Automating Replication Generation for all Stored Procedures in a SQL Server Database

Image
This involves following steps:
Creating stored procedure named Create_Replication_SP using the code provided in this post. Please find the T-sql script below that can be used to create,configure and run replication for all stored procedures in a database automatically. Once stored procedure is in place all you have to do is provide the name of the database as the parameter as shown in example below:Use NameOfDatabasewhereSpIsCreated GO EXEC [Create_Replication_SP] @Database_Name='NameOfDatabaseHere'This will generate and Print a script in return in messages tab in SSMS. Copy the generated script and paste it in new query window and execute it and wait for its completion, progress gets tracked in Messages tab in Output window. What script does:
This creates Publication with nomenclature DatabaseName_SPor if you want a custom name you can pass the parameter @Publication_Name along with @DatabaseName (should be under 20 characters). Creates a snapshot agent Gets all the stored pro…

Adding Contact form within Blogger Contact Page

Image
Let me be quick on this one :
This is 2 step process I am using Contempo template but things are pretty same for any blogger theme.
Getting the contact form widget: Add a contact form gadget to sidebar from layout section to get the contact form and then hide it from sidebar by using CSS: style="Display:None;" To do that jump to Contact form widget from Theme>> HTML Editor>>contact form widget and expand  <b:includable id='content'> Find this: <div class='contact-form-widget'> and replace it with this:
 <div class='contact-form-widget' style='display:none!important'>Moving the contact form to Page: Create a new contact page from pages section switch to HTML editor add the following html between the div tags already present there and publish it.
Note: Below code is coming from expanded content section of contact form widget from theme editor so in case you are facing some issues that's the place you should be lo…