SQL Server # Storing Hierarchical Data – Parent Child n’th level # TSQL

Introduction Today, I would like to explain one way in which we can store the HIERARCHICAL data in SQL tables. A general table structure which people come up to store this kind of data is - Where, EmployeeID id the UniqueID alloted to every new employee record inserted into the table and ManagerID is the EmployeeID of the immediate manager of the employee. Keeping in mind that Manager is also an employee. Problem Statement This table...

Read More

Last Execution Date Time of a Stored Procedure

On many forums I have found a very basic yet important query - “How can I know when was my Stored Procedure last executed?” And today through this blog I will try to answer this question. Actually speaking, without explicit logging or tracing, it is not possible to get this information for each and every Stored Procedure of our Database. However, we can get this detail along with many other relevant information for the stored procedure having it’s execution plan currently cached on the server by using - sys.dm_exec_procedure_stats It’s...

Read More

Moving MASTER Database

In my previous post we see how to move MSDB database, today we will see how to move or relocate MASTER database. While moving MASTER database we’ll have to consider few other things like changing start-up parameter for SQL Server Service. I will also mention those stops here for better understanding. Let’s do it step-by-step. Step 1: Query sys view and note down the existing location for MASTER database USE MASTER GO SELECT NAME,...

Read More

Moving MSDB to new location

In recent past we have a situation where in we required to move MSDB, Model and Master databases to new location, the reason being faulty drive. While moving system databases to new location we need to be extra cautious. Let’s see the process step-by-step. Step 1: Let’s query sys view and note down the location for database files SELECT NAME, PHYSICAL_NAME AS 'PhysicalFilePath', STATE_DESC AS 'DB Status' FROM SYS.MASTER_FILES Step...

Read More

Register for SQL Server 2012 Virtual Launch

`As we all are aware SQL Server 2012 virtual launch is scheduled after 60 hours roughly. Earlier I have discussed what’s new in SQL Server 2012 setup, MS has released SQL Server 2012 training kit and made it available for us to download. In recent past, I have blogged about a free eBook for SQL Server 2012 written by Ross Mistry ( Blog | Twitter ) and Stacia Misner ( Blog | Twitter) . You will see lots of new and insightful information on new and enhanced features introduced in SQL Server 2012, this includes: AlwaysOn Automating...

Read More

Download free eBook for SQL Server 2012

Microsoft has released one more free eBook, this time it is on SQL Server 2012. However, this is the second draft of this book. SQL Server MVP Ross Mistry and Stacia Misner has covered Administration and BI part, there are 10 chapters in total divided in two part, they are: PART I DATABASE ADMINISTRATION (by Ross Mistry)1. Denali Editions and Enhancements2. High Availability and Disaster Recovery Enhancements3. Scalability and Performance4....

Read More