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