Monthly Archives: September 2014

Find all Locks and Blocks on a Database or Server – Point in time

On one of our Databases I noticed frequent blocks resulting web page timeouts, had to analyze what is resulting these blocks and which tables are major contributors. SP_WHO2 is the easy way to find the processes which are getting blocked … Continue reading

Posted in Performance, Productivity, Script, SQL | Tagged , , , | Leave a comment

Database Restoration history of all databases

I had to find how frequently and which databases are restored by whom on one of my Development Servers. Following SQL Query does it!

Posted in Productivity, Script, SQL, SQL Query | Tagged , , , | Leave a comment

Basics of Hosts file; HOSTS file location and usage

• Host File is like first referenced address book which associates hostname with IP address. • Example to associate vijredexample.com to 192.168.1.1: o o Last line in below image will add the mapping • Location of hosts file %systemroot%\system32\drivers\etc\ • … Continue reading

Posted in knowledge, Productivity, Windows Server | Tagged , | Leave a comment

Maximum and Minimum values that DATETIME data type can store…

Maximum value: 9999-12-31 23:59:59.997 Minimum value: 1753-01-01 00:00:00.000 Yes, I am referring to SQL Server  -Vijred

Posted in SQL | Tagged , | Leave a comment

Database in Single User Mode – Resolution

When the database gets stuck in Single_User mode, try these options to bring it back to Multi_User mode! • T-SQL statement to change it to MULTI_USER • You will not able to complete above query if there is any active … Continue reading

Posted in knowledge, Script, SQL, SQL Error | Tagged , , , | Leave a comment

Example of Recursive Query using CTE – SQL Server

I would like to give a very simple example of recursion using CTE (Common Table Expression) As part of the demo, I will create a table with ID, EmployeeName, and ManagerID indicating org structure. Following CTE queries will fetch the … Continue reading

Posted in Productivity, Script, SQL, SQL Query | Tagged , , , | Leave a comment

TSQL – How to get sizes of all databases on a server

Typical database in SQL Server is a combination of Data Files and Log Files. Here is the query to find size of Data files, Log Files, and Total Size of Database in GB There are plenty of other ways to … Continue reading

Posted in Productivity, Script, SQL | Tagged , , , , , | Leave a comment

How to get greenwich mean time in SQL Server

GETUTCDATE() is the function to get Greenwich mean time… Just for fun…. If we want to get the same value without using this function? I have created user defined function as follows… -Vijred

Posted in Productivity, SQL, SQL Query | Tagged , , , , , | 2 Comments

Difference between ISNULL and COALESCE (SQL Server)

Though ISNULL and COALESCE can be interchangeably used in common scenarios basic functionality of ISNULL and COALESCE are different. ISNULL: ISNULL Accepts 2 parameters, if the first parameter is NULL, second parameter value will be returned. COALESCE: COALESCE accepts multiple … Continue reading

Posted in Productivity, Script, SQL, SQL Query | Tagged , , | Leave a comment