SQL Server Management Studio (SSMS) 2005/2008/2012 Auto Recovery Files

When I started my PC today, I observed it has restarted forecefully due to windows auto update. I quickly opened Sql Server Management Studio (SSMS) wondering if my files will be recovered automatically as SSMS prompts usually. But It didn't.

I was wondering if I need to rewrite the script. but thought of sniffing temp folders to try my luck. A windows search of "*.sql" pointed me to some temp files in folder "C:\Users\\Documents\SQL Server Management Studio\Backup Files\Solution1".

That's it, SSMS keeps unsaved scripts in this folder by default. You can disable this feature if you would like.

There is no direct option to enable/disable Auto Recover settings in 2005/2008. If you are too keen you can do so by tweaking registry setting, here are the steps:

-- SSMS 2005/2008

- Open registry editor (type regedit in windows run)
- For SSMS 2005, Go to the key HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover
- For SSMS 2008, Go to the key HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\General\AutoRecover
- Change DWORD "AutoRecover Enabled" to 1 for Enabling and 0 for Disabling Auto Recovery

-- SSMS 2012

There is a better way to change this setting in SSMS 2012

- Open SSMS 2012 and click on Tools >> Options

- In Options dialog box click on Environment >> AutoRecover

- Check/Uncheck "Save AutoRecover information every" checkbox to enable or disable Auto Recovery
- You can modify the Auto Save interval between 1 to 60 minutes
- There is an setting for the number of days Auto Recover information can be persisted.

It is possible to modify registry setting for SSMS 2012 as well. The location of this registry key has changed to HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\General\AutoRecover

How To Retrieve SQL Server Database Table Size

Today, while working on my data warehouse performance tuning and cleanup activity, I wanted to identify the large space hungry tables in my database so that they can be compressed to save some space. I was aware of system stored procedure sp_spaceused which can be used to retrieve space usage details of individual tables. I came up with this little script to list the space details of all tables in my database so that this information can be shared in a report.

---------------------------------------------------------

-- Author : Irfan Sheikh (http://iirfan.blogspot.com/)

-- Date : 10/01/2012

-- Description :  Script to list space used by individual database tables

---------------------------------------------------------

USE [DatabaseName]

GO

-- Declare table variable to store table space information

DECLARE @tabSpace TABLE

(

name varchar(50),

[rows] decimal,

reseved varchar(50),

data varchar(50),

index_size varchar(50),

unused varchar(50)

)

-- Declare table variable to store table names

DECLARE @Tabs TABLE (ID INT IDENTITY(1,1), TableName VARCHAR(50))

-- Populate list of tables in current DB

INSERT INTO @Tabs (TableName)

SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

DECLARE @Ctr INT

DECLARE @TabName VARCHAR(100)

-- Initialize counter for Looping

SELECT @Ctr = MIN(ID) FROM @Tabs

SELECT @TabName = TableName FROM @Tabs WHERE ID = @Ctr

WHILE (@TabName IS NOT NULL)

BEGIN

      -- Populate output of sp_spaceused in Table variable

      INSERT INTO @tabSpace

      EXEC sp_spaceused @TabName

     

      SET @Ctr = @Ctr + 1

      SET @TabName = NULL

      SELECT @TabName = TableName FROM @Tabs WHERE ID = @Ctr

     

END

-- Final Select

SELECT * FROM @tabSpace ORDER BY CAST(REPLACE(DATA,'KB','') AS DECIMAL) DESC

Here is sample output of this script:

name	rows	reseved	data	index_size	unused
DatabaseLog	1591	6672 KB	6528 KB	72 KB	72 KB
spt_values	2506	336 KB	152 KB	152 KB	32 KB
spt_monitor	1	16 KB	8 KB	8 KB	0 KB
MSreplication_options	3	16 KB	8 KB	8 KB	0 KB
ProductCostHistory	0	0 KB	0 KB	0 KB	0 KB
ProductDescription	0	0 KB	0 KB	0 KB	0 KB
ShoppingCartItem	0	0 KB	0 KB	0 KB	0 KB

Circular Queue of Integers Using a Simple Array

Q#2. Implement a circular queue of integers of user-specified size using a simple array. Provide routines to initialize(), enqueue() and dequeue() the queue. Make it thread safe.

Ans.

Analysis:

A queue is a First in First Out (FIFO) type Data Structure. The element which is added first is removed first. In order to implement a circular queue, we will have to keep track of the queue start index, queue end index and a variable to track the number of elements in queue.

Representation of circular array:

A circular array with six elements, Total queue capacity 8 elements:

Start Index = 0		End Index = 5		No of Elements = 6
1	2	3	4	5	6

After dequeing 2 elements

Start Index = 2		End Index = 5		No of Elements = 4
		3	4	5	6

After enqueing 3 elements

Start Index = 2		End Index = 0		No of Elements = 7
9		3	4	5	6	7	8

Approach:

As required we have to define three methods Initialize, Enqueue and Dequeue. Where Initialize will initialize/reset the Queue Array, Enqueue will add elements and Dequeue will remove elements from the queue. Further, we will implement locks on Array and variables to make this implementation thread safe.

Pseudo Code:

DECLARE QueueStart=0, NoofElements=0, QueueArray                                // Declare Queue Variables

FUNCTION Initialize(INT ArraySize) :

QueueStart=0, NoofElements=0, QueueArray  = NEW INT[ArraySize] // Initiate Queue Variables

FUNCTION Enqueue(INT NewElement) :

QueueArray[NewPosition] =   NewElement                        // NewPosition method returns new Index

NoofElements +=1

FUNCTION Dequeue() :

RETURN QueueTopElement                                                        // Return element from top of Queue

NoofElements -=1

QueueStart = NewQueueStart                                                  // QueueStart is set to new Start Index

C# Code:

 public class CircularQueue  
 {  
      // Variable Declaration  
      private int _noofElements = 0;  
      private int _queueStart = 0;  
      private int[] _queueArray;  
      private object _lock;  
      public int Length  
      {  
           get  
           {  
                return _noofElements;  
           }  
      }  
      //Function to get Next Index in circular array  
      public int NextIndex()  
      {  
           int extendedSize = _queueStart + _noofElements;  
           // Restart new index from start of array if end of array is reached  
           return extendedSize >= _queueArray.Length ? extendedSize - _queueArray.Length : extendedSize;  
      }  
      //Function to Initialize Queue Array and Reset Variables  
      public void Initialize(int Size)  
      {  
           // Initialize Circular Queue Array        
           lock(_lock)  
           {  
                _queueArray = new int[Size];  
                _queueStart = 0;  
                _noofElements = 0;  
           }  
      }  
      public void Enqueue(int NewMember)  
      {  
           if(_queueArray.Length == _noofElements)  
                throw new Exception("Queue is Full");  
           // Set member at new circular index  
           lock(_lock)  
           {  
                _queueArray[NextIndex()] = NewMember;  
                _noofElements++; // Increment element counter  
           }  
      }  
      public int Dequeue()  
      {  
           if(_noofElements == 0)  
                throw new Exception("Queue is Empty");  
           int temp = _queueArray[_queueStart]; // Store position value in temp variable  
           lock(_lock)  
           {  
                _queueArray[_queueStart] = 0; // Reset position value  
                _queueStart = (_queueStart == _queueArray.Length - 1) ? 0 : _queueStart + 1; // Get new start position  
                _noofElements--; // Decrement element counter  
           }  
           return temp;  
      }  
      public void Print()  
      {  
           foreach(int i in _queueArray)  
           {  
                Console.Write("{0:D2} | ", i);  
           }  
           Console.WriteLine();  
      }  
 }  

Algorithm Problem : Sort an Array of Three Color Balls

I have been practicing Algorithm problems for a while and thought it would be a good idea to post it for people with similar intrested. I will post new questions as often as I get time from my work schedule. Your feedback is welcome :)
---------------------------------------------------------------------------------------------------------

Q#1. Consider an Array of balls. A ball can be of one of the three colors Red/Green/Blue. Ask is to sort this array.

Follow up Question, Perform this operation in O (n) time.

Solution:

Analysis:

Let’s reduce some ambiguities from the problem.

n  Is there a weightage associated to colors of these balls?
No, consider all balls to have equal weightage.

n  By sorting is there a particular ordering required of these colors?
Yes, Red followed by Green and then Blue

We can consider the array of balls as a char array of length n with characters R, G and B. The array has length ‘N’

Unsorted array can be depicted as:

G

R

B

R

G

R

B

G

G

B

Sorted array can be depicted as:

R

R

R

G

G

G

G

B

B

B

Approach:

Being specific to this problem, we should try to arrange Red color at the start of the array, Blues at the rear of the array, Green’s will automatically be arranged in between.

Pseudo Code:

DECLARE CtrRed=0, CtrBlue=N-1                                 // Initiate Red and Blue counter index

FOR I = 0 TO N-1                                               // Control Loop

IF CtrRed < CtrBlue                          // Red Index cannot be greater than Blue Index

IF Array[I] = ‘R’ THEN SWAP(Array[I], Array[CtrRed++])                  //Swap Red Color

IF Array[I] = ‘B’ THEN SWAP(Array[I], Array[CtrBlue--])                  //Swap Blue Color

I = I - 1                //Decrement the counter when Blue Ball is found, since swapped color may be Red

C# Code:

       public static char[] SortColors(char[] InputArray)

        {

            //Error Handler

            if (InputArray == null || InputArray.Length == 0)

                throw new Exception("Input Array Is Null or Empty");

            //Variable Declaration and Initialization

            int ctrRed = 0, ctrBlue = InputArray.Length - 1;

            for (int i = 0; i < ctrBlue; i++)

            {
                if (ctrRed < ctrBlue)

                {
                    if (InputArray[i] == 'R')
                    {
                        Swap(ref InputArray[i], ref InputArray[ctrRed++]);
                    }

                    else if (InputArray[i] == 'B')
                    {
                        Swap(ref InputArray[i], ref InputArray[ctrBlue--]);  
                        i--; 
                    }
                }
            }

            return InputArray;

        }

        public static void Swap(ref T InputA, ref T InputB)

        {

            T temp = InputA;
            InputA = InputB;
            InputB = temp;
        }

Time complexity for this algorithm is O(n)