Management Studio - Password Cache

Having changed a lot of passwords recently, I found that removing (or renaming for safety) the following file helped.

(Management Studio recreates it when you next start)

C:\Documents and Settings\[username]\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin


http://stackoverflow.com/questions/349668/removing-the-remembered-login-and-password-list-in-sql-management-studio

Tools : 7zip

7zip is a file compression utility.
Like Winzip and Winzip Command line put together, oh, and it is FREE!

Download from here : http://www.7-zip.org/

I used the following command in a batch file to zip up an entire folder.
"C:\Program Files (x86)\7zip\7za.exe" a -r e:\sqlbackup\ssrs\ssrsarchive.7z e:\sqlbackup\ssrs\*.* -x!ssrsarchive.7z

the 'a' parameter at the start is tells it to add files.
the '-x' at the end tells it what to exclude (in this case the archive itself!)

SP : Testing Linked Server Availability

SQL Server comes with a system stored procedure sys.sp_testlinkedserver to test linked server availability.
Here I simply put that inside by own procedure to generate an email too.

CREATE PROCEDURE utils.[LinkedServerTest] @ServerName SYSNAME
AS 
 BEGIN
 DECLARE @Test BIT

 BEGIN TRY
 EXEC @Test= sys.sp_testlinkedserver @servername 

 PRINT 'Sucessfully connected to ' + CAST(@servername as VARCHAR(30))
 END TRY

 BEGIN CATCH
 PRINT 'Failed to connect to ' + CAST(@servername as VARCHAR(30))

 DECLARE @chvFrom VARCHAR(255)
 DECLARE @chvTo VARCHAR(255)
 DECLARE @chvSubject VARCHAR(255)
 DECLARE @chvBody VARCHAR(8000)

 SET @chvFrom = 'sql.admin@domain.co.uk'
 SET @chvTo = 'sql.admin@domain.co.uk'
 SET @chvSubject = 'Linked Server Connnection Failure : ' + @servername + ' cannot be accessed from ' + @@SERVERNAME
 SET @chvBody  =  @chvSubject

 EXEC msdb.dbo.sp_send_dbmail 
    @profile_name='Mail Profile'
  , @recipients=@chvTo
  , @body=@chvBody
  , @subject=@chvSubject
  , @importance='High'
  
 RAISERROR ('Linked Server Failure', 16, 1, @chvSubject) WITH LOG
 
 END CATCH

 END
GO

Usage :

exec  utils.LinkedServerTest @ServerName = 'my linked server'

WINSXS folder size explained

A very good post that explains te difference between files and 'hard links' >

http://www.davidlenihan.com/2008/11/winsxs_disk_space_usage_its_no.html

WINSXS still takes up a lot of space, even without the 'hard links' mind!

Task Scheduler Task does not run - Error 2147943785

Googling this, all i could come across was >
Local policies -> user rights assignment & add the user
running the task to the "log on as a batch job" Policy.

Seeing i'm running a domain I needed to adjust my domain account via Group Policy >

Created account Domain\TaskScheduler (with usual random 25 character, punctuation etc 25 password)

I added the account to the 'Log in as a batch job' policy , ran gpupdate on my machines but it wasnt enough.

I solved the issue by placing the account in my service accounts group Domain\Service Accounts

The group has the following rights

Allow log on locally
Act as part of the operating system
Adjust memory quotas for aprocess
Bypass traverse checking
Lock Pages in memory
Log in as a batch job
Log in as a service
Perform volume maintenance tasks
Replace a process level token

Enabling VT (Virtualisation Technology) for Hyper-V hosts

If you forget to enable VT in the BIOS and try to install Hyper-V, this is what you get...

" Hyper-V cannot be installed

Server Manager has detected that the processor on this computer is not capable with Hyper-V. To install this role the processor must have a supported version of hardware assisted virtualization, and that feature must be turned on in the BIOS. "



The message is pretty self explanatory and I had indeed turned it on in the BIOS. What it doesnt tell you is that you need to cold boot after making BIOS changes i.e. physically turn off the server, wait a while and start the server back up!
Whlst in the BIOS, make sure 'Execute Disable' is 'Enabled' (confusing I felt).

TSQL : Unused Indexes and Index Sizes

A quick exercise in looking at saving a little data space by dropping some unused indexes.
I'm using a CTE to draw from 2 pieces of code and recommend indexes to lose.

The first is Jason Massie's Unused Index Query and the second is my Index Size script.

WITH UnusedIndexQuery (Object_ID, ObjectName, IndexName, Index_ID, Reads, Writes, Rows) AS
(
SELECT s.object_id,
  objectname=OBJECT_NAME(s.OBJECT_ID)
, indexname=i.name
, i.index_id  
, reads=user_seeks + user_scans + user_lookups  
, writes =  user_updates  
, p.rows
FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i 
ON i.index_id = s.index_id AND s.OBJECT_ID = i.OBJECT_ID  
JOIN sys.partitions p ON p.index_id = s.index_id AND s.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1  
AND s.database_id = DB_ID()  
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND p.rows > 10000
)

, IndexSizes (schemaname,tablename,object_id,indexname,index_id,indextype,indexsizekb,indexsizemb,indexsizegb) AS
(
SELECT   sys_schemas.name AS SchemaName
  ,sys_objects.name AS TableName
  ,sys_objects.[object_id] AS object_id
  ,sys_indexes.name AS IndexName
  ,sys_indexes.index_id as index_id
  ,sys_indexes.type_desc AS IndexType
  ,partition_stats.used_page_count * 8 AS IndexSizeKB
  ,CAST(partition_stats.used_page_count * 8 / 1024.00 AS Decimal(10,3))AS IndexSizeMB
  ,CAST(partition_stats.used_page_count * 8 / 1048576.00 AS Decimal(10,3)) AS IndexSizeGB
FROM sys.dm_db_partition_stats partition_stats
INNER JOIN sys.indexes sys_indexes
  ON partition_stats.[object_id] = sys_indexes.[object_id] 
    AND partition_stats.index_id = sys_indexes.index_id
    AND sys_indexes.type_desc <> 'HEAP'
INNER JOIN sys.objects sys_objects
  ON sys_objects.[object_id] = partition_stats.[object_id] 
INNER JOIN sys.schemas sys_schemas  
  ON sys_objects.[schema_id] = sys_schemas.[schema_id] 
  AND sys_schemas.name <> 'SYS'
)


select IndexSizes.* 
, UnusedIndexQuery.Reads
, UnusedIndexQuery.Writes
, UnusedIndexQuery.Rows
from UnusedIndexQuery
inner join IndexSizes
on UnusedIndexQuery.object_id = IndexSizes.object_id
and UnusedIndexQuery.index_id = IndexSizes.index_id
order by reads