Database

How to determine free space on each fixed drive of server machine using SQL SERVER ? TIP #75

  When you do  SQL Server maintenance one of important aspect is available space on server drive because your SQL SERVER data is dependent on space . Now what you need to do to get free space from each drive ? Just create a simple job which run on daily basis which send you space …

How to determine Meta data or result set information of a stored procedure /trigger ? TIP # 73

  Sometimes it may require that you don’t know what will be output of  a stored procedure ? what kind of result set it return ? In such case SQL SERVER provided a new DMV statement which is sys.dm_exec_describe_first_result_set_for_object. In other words if we want to know result set’s meta data then we can use …

How to replace a specific string from a large string with Write feature ? TIP #72

  This is very interesting feature and I recently come to know this awesome feature. Lets understand this by an example below Suppose we have a student table with following structure  as shown in below image   We have a detail column with VARCHAR(MAX) . Now as it is VARCHAR(MAX) column it may content a …

How explicitly insert default value ? TIP #72

  Suppose, sometimes you just need  entry in the table with all the default values. For example you have a Student table which looks like as below CREATE TABLE #tmpStudent (StudentId INT IDENTITY(1,1),                          FirstName VARCHAR(100) DEFAULT ‘RAJAT’,                          Course    VARCHAR(100) DEFAULT ‘MATHS’)   Now , suppose for in some situation we need just default entry for …

How to copy table structure only from a SQL Query ? Tip #69

  Recently , one of my friends shared that some interviewer asked him a question “How to copy table structure only from a  SQL Query?” So, Below is simplest query to copy structure only of a table into another table. SELECT * Into #tmpStudentStructureFROM tblStudentSource WHERE 1= 0 in the above query we want to …

How easy it is to check which statements consuming most of the CPU & RAM–TIP #68

  Problem:-  One of the most important question comes in our mind what is the cause of slow  SQL SERVER. We always struggle with following questions Which is highly CPU consuming query ? Which is highly RAM consuming query ? Who is blocking the transaction ? and many more other performance dragging questions. Believe me …

How to take Transaction Log backup ? TIP# 68

  As we discussed in earlier tips how to take full backup and differential backup now in this tip we will see how to take transaction backup . Before taking transaction backup the first and mandatory condition is that you should have a full backup. Just right click the database and go to backup option …