SQL Server # Moving MASTER database in cluster environment

Few months back I have wrote post about moving MASTER and MSDB database to new location in stand alone machine. In recent past we had a situation where customer asked us to move MASTER database to new location, below are the steps I have taken:     Connect to the Server     Open Configuration Manager -> SQL Server Service     Right Click and say Properties     Click on the Start-up Parameter     Remove start-up parameter (the highlighted...

Read More

SQL Server Database Backup Report using T-SQL

Today, I am going to share few very useful scripts which will report us on Database Backup from different view points. To get the List/History/Log of all the Successful Backups SELECT b.machine_name, b.server_name, b.database_name as DBName, b.backup_start_date, b.backup_finish_date, CASE WHEN b.[type] = 'D' THEN 'Database' WHEN b.[type] = 'I' THEN 'Differential database' WHEN b.[type] = 'L' THEN 'Log' WHEN b.[type] = 'F' THEN 'File or filegroup' WHEN b.[type] = 'G' THEN 'Differential file' ...

Read More

SQL Server # TSQL to Convert STRING in PROPER format

Problem Statement SQL Server has got in-built functions to convert the given string into LOWER() or UPPER() format but it does not provides any direct way to convert it to PROPER format. A string/text is said to be in a PROPER format if all the words in that string/text starts with a CAPITAL letter. E.g. If a string is - “Hello, how are you?”, String in Lower format = “hello, how are you?” String in Upper format = “HELLO, HOW ARE YOU?” and String in Proper format = “Hello, How Are You?”   Implementation Ideally, SQL Server...

Read More

Troubleshooting Oracle Link Server - Part2

It's my bad that I did not made a post since long lately. I would not make an excuse like I was busy, rather, I would say I am little lazy DBA, but I now onward I will try to post blog more frequently. Well, if you remember, earlier I have posted some tips about troubleshooting Link Server issue for Oracle, today I will be posting one more tip to the list aka extending Troubleshooting Oracle Link Server.  Few weeks back one of the developer reached out to me needing an assistance with Oracle Link Server. The issue with him...

Read More

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

When DBCC INPUTBUFFER disappoints

Today we have an alert from one of the client server about blocking, I have immediately start looking at it using below statement SELECT * FROM MASTER..SYSPROCESSES WHERE BLOCKED != 0  Yes, I’ve found the culprit SPID that is occupying more resources, using DBCC INPUTBUFFER(SPID). so, instead of seeing what exactly this SPID is doing I have received SP_EXECUTESQL, surprised!!! No, it’s bound to happen when SPID is running dynamic TSQL (using SP_EXECUTESQL) and/or cursor. But, yes, at the same time I would like to know...

Read More

Rebuild System Databases

Yesterday night one of FB Friend have ping me, he has issue with his local SQL Server instance. Our conversation goes like: FB Friend: Hi I : Hi FB Friend: I need your help FB Friend : My SQL Server is not starting... I : What is an error you are getting? FB Friend : Just nothing I : Ok, check the SQL Server Error Log and EventViewer I : you can find errologs at C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\Log I : open it...

Read More

Revamping SUG 2nd Meeting

Friends, Last Saturday, 21st we have 2nd meeting of SUG members and this time we made progress – I, Vinay and Matang was present in meeting .We have discussed following : We have decided that portal management will be done by I, Vinay and Matang initially, here are the module that we’ll be adding content on: Forums and Team section –> Hemantgiri S Goswami Article Section –> Matang Panchal Blog Section –> Vinay Pugalia ...

Read More

Roles and Responsibilty of SQL Server DBA

What is a role of a DBA in an organization? What are the daily activities of a DBA? What shall I check on regular basis? What all processes shall I automate ? What are the precautions, I have to take additionally? These are the common question being asked in-common on may forums, here is what my listing is • Design and implement Disaster Recovery • Design, development, upgrade and Migration of Database Server. • Providing support and handling most critical situations in vast variety of database systems. • Database...

Read More

Wishing you all a very Happy and Prosperous new year, may success comes to your doorstep Image source : Top CNN Ne...

Read More