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

 

Automatically Rebuild or Reorg index based on fragmentation

The Attached SQL Query can be safely used for rebuilding or reorganizing all indexes in a particular database based on the fragmentation level.

Note: The script is currently set to perform offline index rebuild since online index rebuild is supported only in Enterprise edition SQL Server. Inorder to change it into online mode, add , ONLINE = ON statement along with

in line 100 in the attached script.

3203_AutoIndexRebuild2005andAbove (Click to download the script)

Continue reading “Automatically Rebuild or Reorg index based on fragmentation”