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 what it is running behind the scene. There are two options that came to my mind

• Using Profiler
• using function – ::fn_get_sql(@SQLHandle) 

I preferred to use 2nd option as this is one time effort (at least as of now), and, it would be very quick. So, here is what I have used

 

-- Variable that will store the SQLHandle
DECLARE @SQLHandle BINARY(20)

-- Variable that will pass on the culprit SPID
DECLARE @SPID INT

-- value for culprit SPID
SET @SPID = 52

-- this will give you the SQLHandle for the culprit SPID
SELECT @SQLHandle = SQL_HANDLE 
FROM MASTER..SYSPROCESSES 
WHERE SPID = @SPID  

-- this statement will give you the SQL Statement for culprit SPID
SELECT [TEXT] FROM ::FN_GET_SQL(@SQLHandle)

You must be thinking why I have used this function instead sys.dm_exec_sql_text? Any guess??
Yes, you are right, customer is still using SQL server 2000 {Winking smile}
BTW, if you happened to come across something relating but on SQL Server 2005 or SQL Server 2008 and greater I have a reference script for you

Erland Sommarskog, SQL Server MVP has written aba_lockinfo and a script a.k.a. Custom Blocker Report from Aaron_Bertrand.
 
--Hemantgiri S. Goswami (http://www.sql-server-citation.com )

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 with notepad ....
FB Friend: Ok
FB Friend: Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
I : Wow, seems like your master database is corrupt, you will need to run repair or you may want to rebuild master database.
I : Is this a new installation ?
FB Friend : Yes, this is new installation, and, I don't want to re-run the installation again, what is my option ?
I : you will need to rebuild master or restore it
FB Friend: How do I do it?
I: Here are your steps

Rebuild Master:

Step 1: Insert your setup media CD or folder where you have dumped SQL Server Installation
Step 2: Open command prompt
Step 3: Go to location (wherever you have setup, Step 1)
Step 4: setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLServer /SQLSYSADMINACCOUNTS=MyAdmin /SAPWD=somePwd

Step 5: this will pop up new command window and then disappears

Step 6: you will have to refer logs so as to see whether it was successful completed or exit with error

This is it, you system database has been rebuild successfully!!
Restoring Master Database - An alternative method:
Step 1: Take backup of master database, in your case, I assume, you already have your master database backed up.

Step 2: Stop SQL Server Services * you will need Administrator permission for this operation

Step 3: Start SQL Server using -C and -M parameter; this will start SQL Server in single user mode which is required to restore Master database

Step 4: Restore database using SQLCMD

This is it, you have successfully restore your master database!!!

Is it this simple? No, wait, there are few things we need to be careful with....

The above example have save you because below conditions are true:

1. You have back up of your master database, or
2. This server is fresh installation or
3. there are no user databases or
4. this server is not production box

If the above conditions were not true, you will have to perform below steps:

1. Restore / Rebuild system databases
2. Recreate all the Login that you have on your crashed server - backing up master db helps you here
3. you will have to recreate all the jobs and SSIS/DTS Packages that you have - backing up MSDB database will save you
4. similarly distribution database backup will help you if you have replication configured

Conclusion :  I wish that you have schedule maintenance plan / job to take full backup of your system databases i.e. Master, MSDB, and Distribution(if you have configured replication).
Disclaimer : The example used here is for illustration purpose only.

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 :

1. 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:
1. Forums and Team section –> Hemantgiri S Goswami
2. Article Section –> Matang Panchal
3. Blog Section –> Vinay Pugalia
2. We’ll design and use common PPT theme during all our presentations
3. We’ll organize quize every quarter
4. From next meet, 1 member will make presentation of 15 minutes; starting with myself
5. We will try to accommodate as many as practical / self demos in initial (and, so on) events
6. We would also create PPT / Demo repository that we have used during event
7. Also, there will be a download section where we’ll share scripts/codes to download
8. We will open a bank account to manage the fund we’ll receive from Sponsors

See you in next meeting will be on 11th February 2012
Venue : 406, Empire State Building, Nr. Udhana Darwaja
Time : 7 PM


Image source: http://pmtips.net/wp-content/uploads/2010/03/meaningful-meetings.jpg

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

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 Optimization / Query Performance Tuning.

• Design and implementation Automated Database & Application fail over server setup.

• Capacity Planning, Change Management and Admin. Documentation.

• Review of existing design and specifications of the system.

• Design and documentation of operational specifications of the system.

• Monitoring of the development process, in order to confirm optimum performance of the system.

• Preparation of standards and follow up rules for the back – end system, to ensure a fully secured and a robust system.

• Design and implementation of the logical & physical structure of the database.

• Monitoring of performance of database servers and providing tuning measures.

• Performing database / application wide query tuning operations.

Try automate every process that needs to be performed on daily basis and/or requires manual intervention.

--  Hemantgiri S. Goswami (http://www.sql-server-citation.com)

Read More

Wishing you all a very Happy and Prosperous new year, may success comes to your doorstep

Image source : Top CNN News

Read More