Kill waiting for preemptive _os_pipeops

Problem

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”.

Solution

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.

Steps

  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.

Steps

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.”

Solution

Below is what I did to rescue my beloved database.

1. Enabled emergency mode for that database.

ALTER DATABASE set EMERGENCY

2. Then brought the database into single user mode.

ALTER DATABASE set SINGLE_USER

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.

ALTER DATABASE <databasename> SET MULTI_USER

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

Issue

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.

Cause

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.

Resolution

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

Issue

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/.

Solution

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

 

Error: Text Editor is not loading or showing up in WordPress – (Fixed)

I had a terrible issue with my wordpress website www.sherbaz.com. I was unable to submit any post for a long time because of this issue. Every time when I login to wordpress dashboard to post new tips, I see a blank text field as shown below which doesn’t even accept any input either.

Text Editor Error

How was it fixed?

This was seriously effecting my work and I couldn’t post or edit or update an already existing post either. Finally after few weeks of silence I decided to tweak this out. One fine weekend I started sorting out one by one.

  1. Cleared browser cache – No luck.
  2. Changed browsers and even tried on different systems and finally tried even on my Smartphone. – Same error was there everywhere.
  3. Then I checked the error that was displayed by internet explorer while loading the page. It says “tinyMCE is undefined”. This didn’t give any clue.
  4. Installed Web developer 1.1.9 add-on to my Mozilla firefox and then executed the new post page again. The web developer error console says “illegal character in wp-tinymce.php”. On clicking the error, the file popped up. I could find only some Unicode characters. So I felt like the files might have got corrupted. I tried replacing the file alone from a new downloaded wordpress-3.3.1 package. That didn’t fix it.
  5. I then took a backup of wp-admin and wp-includes folders, deleted those folders and then uploaded again from the freshly downloaded wordpress 3.3.1 zip file. Hurray! The issue got fixed. The text editor is now showing up and working fine.