Problem Steps Recorder (PSR)

1. Opportunity

I believe that every one of us takes screenshots with steps to work on something/ maybe explain the issues in a process. We have to use mspaint or snipping tool to mark and explain each steps with each screenshot. There is an automated process to do this in windows 7 and 2008 which is called the PSR. I hope we can use this for making the stuff very easy

2. How does the idea work?

This idea will help to save lot of time for documentation, For Example. We are conducting lot of internal trainings and Knowledge Transfer from Onshore team so it required step by step Documentation for future reference in this case taking each every screenshots used by other tools (MS Paint, Snipping Tool) will take hours or days to complete it but Once we started using the PSR we have been able to complete  in minutes and It’s quick, easy and effective to save you hours of troubleshooting time like if your customer required step by step details of solution steps.

 3. Method of Implementation

To do this,In Windows 7 , Windows 2008 and Vista you have to click Start, type PSR and press Enter, then click Start Record. If they then work through whatever they’re doing then the Problem Steps Recorder will record every click and keypress, take screen grabs, and package everything up into a single zipped MHTML file when they’re finished, ready for emailing to you. It’s quick, easy and effective, and will save you hours of troubleshooting time.

Kill waiting for preemptive _os_pipeops


Detaching a database with drop all connections enabled was running continuously without completing. When checked what was causing the block or lock, I found the Kill/Rollback was waiting with waittype “preemptive _os_pipeops”.


With some googling, I came to know that it is something to do with a process running at the operating system level. So the solution was to end any process like wscript or cscript or cmd running with sql server service account or whatever you think might be the process that used the SQL Database.

In my case, it was a vbscript running as wscript process running with SQL server service account. I found that in Windows task manager.

Code: Find missing indexes

Below query will be helpful in finding all missing indexes in a database. I got this query from one of my friend. I am not sure from where it was actually taken. Since I found this useful, I am just making note on the same.

Code: Rebuild Indexes for a list of tables

Use below code and make necessary modifications to make it work as per your needs. I am extremely sorry that I couldn’t test below code. It was just scripted and forwarded to my colleague to test and deploy the code.

Tip: Rename a live production database.

I had a requirement from application team that I should rename a database when the application is running and the transactions were happening. The reason was that they didn’t want any outages registered for the entire application. If they stop the services, it would impact the the whole application.


  1. Set the target database into Single user mode.
  2. Rename database.
  3. Bring the database back into Multi user mode.

Use below query to perform above steps.

Here you go !. You are done.

Workaround: Control SQL Agent Job Steps based on query outcome.

Below workaround will help you incase if you wanna control SQL agent job steps based on a SQL query output. For eg: Suppose you have to check if any backup jobs are running before executing a resource intensive job step. Here is how we can work around and control job steps. You can modify below steps anyway based on your needs as I have just used below strategy as an example.

Methord Summary

  1. Run the query to check if the backup is running. Query to check backup status is
    select count(*) from sys.dm_exec_requests where command like '%BACKUP%'
    The result will be > zero if the backup is running. and zero if no backups are running.
  2. Execute the resource intensive task if the above query returned ZERO else exit the job reporting success.


1. Create a job with first step to check the backup status.
2. Use below query to check backup status and thereby navigate the job steps by making it raise an error causing the job step to fail if the backup is running.

3. Go to advanced option for this job step and set as “quit the job reporting success” if the step fails, and to proceed to next step if the step succeeds which will trigger the resource intensive query. Instead of setting “Quite the job reporting success”, You can also make it move to a step that could send some email alert saying the task was aborted since a backup was currently running.
4. Create the next step that would be triggering the resource intensive query.

Read Disk Read/ Write for all databases in an instance and Sort them

Below Query will help you read live file level Disk read and write information. WIll behandy for performance tuning capacity planning.

Error: Transaction log file got damaged or corrupted

Once we had an issue in our environment. We had a maintenance activity on the Database server during a weekend and the server was shutdown for a Storage level snapshot backup. After the backup the server was brought back online. But unfortunately the user database was in suspect mode and could not be accessed. SQL Server Error Log says

“An error occurred while processing the log for database ‘<databasename>’.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.”


Below is what I did to rescue my beloved database.

1. Enabled emergency mode for that database.


2. Then brought the database into single user mode.


3. Executed DBCC CHECKDB with repair_allow_data_loss.

DBCC CHECKDB (<databasename>,repair_allow_data_loss) with no_infomsgs

I got below warning in Error Log right after I triggered the CHECKDB command.

Warning: The log for database ‘<databasename>’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

And once the CHECKDB completed, The errorlog log reported below message which made me very happy.

EMERGENCY MODE DBCC CHECKDB (<databasename>, repair_allow_data_loss) WITH no_infomsgs executed by SPANSION\admin_sponnamb found 0 errors and repaired 0 errors. Elapsed time: 0 hours 52 minutes 54 seconds.

After the CHECKDB completed, I reverted back the database into multiuser mode with below command.


Now the application was successfully able to access the database and everything was function very well as expected. A full backup has to be triggered to reset LSN numbers and to take transaction log backups.

Access Denied error while attaching the database files moved from Windows 2003 server to the new SQL instance on Windows 2008


It’s quite common that we use detach-attach method to migrated SQL server databases from Windows Server 2000 or 2003 to Windows server 2008. During this task, we might experience “Access Denied” error while trying to attach the databases to the new instance at Windows 2008. An access denied error sometimes occur when we try to bring a database online.


This is due to higher security measures implemented in Windows Server 2008/ Vista / 7. When the SQL Server service account and the DBA’s admin account does not have enough rights on the database file, this error would occur.


This can be fixed with two simple commands.

  1. TAKEOWN – This is to take ownership if files.
  2. ICACLS – This helps in changing the file level permissions like CHMOD command in UNIX.

To learn more on TAKEOWN, type “takeown /?” in command prompt and “icacls /?” for ICACLS.

The fix is as below.

  1. Navigate to the folder containing those migrated datafiles and log files.
  2. Take ownership of the file by executing below command.
  3. Update permission for the respective users on the files with below command.

Note: Replace the drive and directory path to the required values.

Error: Unable to access Reports via web browser soon after installing reporting Services


Soon after installing reporting services for my SQL server 2012 on Windows 7, I got the error “User ‘DOMAIN\username’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed” in web browser at http://localhost/Reports/ and http://localhost/reportserver/.


Right click Internet Explorer and select “Run as Administrator”. Now you will be able to access the below link http://localhost/reports/.

Now, to permanently fix the problem, click on “Folder Settings”Click on Folder settings icon

And then “New Role Assignment”Click on New role assignment

Fill in the below form and click OK.

Fill in this Form and click OK