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.

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.

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.

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”

Executing CHECKDB on all user databases via TSQL

Run below Query. And once the query executes completely, go through the SQL Server Error log to see the results.

Read and filter SQL server error logs using TSQL

Command:

Parameter 1: To specify the errorlog number. Starts from 0. 0 = current, 1 = Archive #1, 2 = Archive #2

Parameter 2: Log file type. 1 or NULL for Error log and 2 for SQL Agent Log

Parameter 3: Search string

Parameter 4: Second search string further refine your search. Continue reading “Read and filter SQL server error logs using TSQL”

Question: Green arrow missing in Object Explorer Window in SSMS

Question

After connecting to SQL Server using SQL Server Management Studio, I am not able to see the green play arrow on the SQL Server DB icon before the instance name. It just shows white circle.

Resolution

Make sure that the Windows account with which you opened SQL Server Management studio have got admin privilege on the operating system and can retrieve service running status.

In Windows 2008, Even if your account has admin privilege on the OS, you need to run SSMS (for SQL Server 2008) and sqlwb (SQL Server 2005) from command prompt opened with “Run as Administrator”.