Auto Index Maintenance for All Databases in a SQL Server Instance

Problem

A few months back, I had shared a script to automate index rebuild/reorg in a database with fragmentation analysis inplace at http://www.sherbaz.com/2013/06/resumable-auto-index-rebuild-and-reorganize-script/. But that script would perform the maintenance only on a particular database at a time.

Solution

The script is modified and it now performs maintenance on all indexes across all databases in an instance. This again has two scripts.

1. analyze2.sql – Checks fragmentation level on indexes in all databases of the SQL instance and stores the results in tempdb.

2. defrag2.sql – Based on the data stored in tempdb by analyze2.sql, the script will process the maintenance, either Index rebuild or an index reorganize based on the fragmentation and the @minfrag, @maxfrag settings in defrag.sql script. Indexes fragmented below @minfrag in percentage will be excluded. Indexes fragmented between @minfrag % and @maxfrag % will be reorganized. All remaining indexes will be rebuilt.

Scripts are pasted below for your reference. Please let me know if you find some errors, bugs or issues with the code or the method used.

Continue reading “Auto Index Maintenance for All Databases in a SQL Server Instance”

Failover info events every second in SQL Server 2008 R2 SP2 Cluster

Problem

In all SQL server failover clusters in our environment, It came to our notice that the SQL Server Agent is spamming the Application eventlogs under event viewer below entries every second in the Active node.

[sqagtres] LooksAlive request.
[sqagtres] CheckServiceAlive: returning TRUE (success)
 

Observation

It was observed that this incident started to occur after the scheduled windows patching.

Solution

Microsoft had identified this as due to a code defect in Sqagtres.dll. To resolve this issue, we would have to patch the SQL instance with Cumulative update 3 for SQL Server 2008 R2 SP2 as mentioned in below KB.

http://support.microsoft.com/kb/2718920

The hotfix download is available at http://support.microsoft.com/kb/2754552

Grant Execute Permission to a User on all non-system stored procedures

We could easily grant execute permission to a user on all stored procedures on a database as below.

But unfortunately, our database will be at risk since the user would have got access to almost all stored procedures including the system stored procedures in that database. Hence use below steps to grant access to non-system stored procedures securely.

  1. Run below script on the database and copy the output.
  2. Paste the output to a new query window.
  3. Add “USE <database name>” at the beginning of the pasted output code and execute.

Note: Values inside ‘<‘ and ‘>’ has to be replaced with your actual database name and user information where ever required.

FETCH API_CURSOR0000000000005BE4 Blocking Several Transactions

One fine morning, users started complaining my SQL server instance performance…They sent me a screenshot which indirectly says, their queries have timed out. I quickly logged in and checked if there are any blocks.

SELECT *

FROM   sysprocesses

WHERE  blocked <> 0 

There were several blocks caused by a naughty spid 80.

When I checked DBCC INPUTBUFFER(80), To my surprise, I got below scary result.

Continue reading “FETCH API_CURSOR0000000000005BE4 Blocking Several Transactions”

Query Sharepoint Content DB for Doc Library Sizes and Number of versions


SELECT v.[id],

       d.dirname,

       Count(v.[version]) MaxNumberOfVersions,

       Sum(v.[size])      TotalDocLibSize,

       Max(v.[size])      LargestDocSize

FROM   [dbo].[alldocversions] v

       JOIN [dbo].alldocs d

         ON v.id = d.id

GROUP  BY v.id,

          d.dirname

ORDER  BY 4 DESC 

SQL Server Tables ordered by Size


SELECT o.name,

       reservedpages = Sum(a.total_pages),

       usedpages = Sum(a.used_pages),

       reservedMB = ( Sum(a.total_pages) * 8 / 1024 ),

       usedMB = ( Sum(a.used_pages) * 8 / 1024 ),

       pages = Sum(CASE

                     WHEN a.type <> 1 THEN a.used_pages

                     WHEN p.index_id < 2 THEN a.data_pages

                     ELSE 0

                   END),

       rows = Sum(CASE

                    WHEN ( p.index_id < 2 )

                         AND ( a.type = 1 ) THEN p.rows

                    ELSE 0

                  END)

FROM   sys.objects o

       JOIN sys.partitions p

         ON p.object_id = o.object_id

       JOIN sys.allocation_units a

         ON p.partition_id = a.container_id

WHERE  o.type = ‘U’

GROUP  BY o.name

ORDER  BY 3 DESC 

Quick queries to Troubleshoot Database Mail in SQL Server

Use below queries one by one and hopefully, you will be able to find and fix your Database Mail issue.

  1. sysmail_help_queue_sp @queue_type = ‘Mail’
  2. select * from sysmail_event_log
  3. select * from sysmail_allitems
  4. select is_broker_enabled from sys.databases where name=’msdb’
  5. sysmail_help_status_sp
  6. sysmail_start_sp

Resumable Auto-Index Rebuild and Reorganize Script

As part of SQL server database maintenance, the indexes have to be defragmented on regular basis by rebuild or reorganize for consistant performance. Most of the time, the index maintenance jobs fail in the middle for large databases due to insufficient storage in log files and the entire rebuild process has to be restarted. This consumes a lot of time and also causes prolonged performance issues.

To tackle this, I have scripts which could resume from where it stopped. Even if the defragmentation(Rebuild/Reorg) fails in the middle due to some reason, it will continue from the table it stopped.

Continue reading “Resumable Auto-Index Rebuild and Reorganize Script”

Quickly attach files in Outlook

1. Opportunity

Attaching a file to an email message can be a fiddly process, but you can streamline it and make sure you don’t forget the attachment by using Outlook’s filing system

2. How does the idea work?

This idea will help to attach and send the files very easily  

3. Method of Implementation

To attach the files you have to browse to the file you want to attach to a message and open Outlook at your inbox.Click and drag the file from its folder onto the inbox shown in your mail folders in Outlook. This creates a new message and attaches the file in one action. Now simply address it and add a covering note

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.