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 

Testing SQL Version

A simple ammend to my backup stored procedure today.
If it's on a sql 2008 box, use compression and increase the BUFFERCOUNT.

IF CHARINDEX('Sql Server 2008', @@VERSION) <> 0
  BEGIN
  SET @Backupcommand = @Backupcommand + ' ,COMPRESSION, BUFFERCOUNT = 250 '
  END

TSQL : Schemas that do not own objects

-- schemas that do not own objects
select sys.schemas.* from sys.schemas
where schema_id not in (select schema_id from sys.objects)

SQL 2008 : Compression TSQL for uncompressed objects

A CTE (common table expression) to build the commands to compress uncompressed objects -

WITH missingcompression
   AS (SELECT Schema_name(schema_id) + '.' + name                                                                                   AS tablename,
              'ALTER TABLE [' + Schema_name(schema_id) + '].[' + name + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' AS command
       FROM   sys.objects
              INNER JOIN sys.partitions
                ON sys.partitions.object_id = sys.objects.object_id
       WHERE  TYPE = 'u'
              AND data_compression = 0
              AND Schema_name(sys.objects.schema_id) <> 'SYS'
       UNION
       SELECT sys_schemas.name + '.' + sys_objects.name                                                                                                   AS tablename,
              'ALTER INDEX [' + sys_indexes.name + '] ON [' + sys_schemas.name + '].[' + sys_objects.name + '] REBUILD WITH ( DATA_COMPRESSION = PAGE ) ' AS command
       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'
              INNER JOIN sys.partitions
                ON sys.partitions.[object_id] = sys_objects.[object_id]
                   AND data_compression = 0)

SELECT command FROM missingcompression ORDER BY tablename ASC, command DESC

The previous version builds all compression commmands for tables and indexes.

SQL 2008 : Apply Compression to all Tables and Indexes

Apply Compression to all Tables and Indexes
(example uses Page Compression)

SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' FROM sys.objects where TYPE = 'u'
UNION
SELECT 'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' FROM sys.objects where TYPE = 'u'

Disabling -ve DNS Caching (XP+)

Save the following as a .reg file and double click it to submit ro the registry.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Dnscache\Parameters]
"ServiceDll"=hex(2):25,00,53,00,79,00,73,00,74,00,65,00,6d,00,52,00,6f,00,6f,\
 00,74,00,25,00,5c,00,53,00,79,00,73,00,74,00,65,00,6d,00,33,00,32,00,5c,00,\
 64,00,6e,00,73,00,72,00,73,00,6c,00,76,00,72,00,2e,00,64,00,6c,00,6c,00,00,\
 00
"MaxCacheEntryTtlLimit "=dword:00000001
"NegativeCacheTime"=dword:00000000
"NetFailureCacheTime"=dword:00000000
"NegativeSOACacheTime"=dword:00000000

Alternatively the full instructions / explanation are here.

SQL 2008 : Management Studio Error

Installing SQL 2008 on developer pcs I came across this when starting Management Studio.

" Unhandled exception has occurred in a component in your application. If you
click Continue, the application will ignore this error and attempt to
continue. Key not valid for use in specified state. "


Clicking 'Details' ....

" ************** Exception Text **************
System.Security.Cryptography.CryptographicException: Key not valid for use
in specified state.

at System.Security.Cryptography.ProtectedData.Unprotect(Byte[] encryptedData, Byte[] optionalEntropy, DataProtectionScope scope)

..... "

It basically means SQL 2008 is trying to use a SQL 2005 config file.

To solve this,

1. Navigate to C:\Documents and Settings\ [username] \Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell .
   
   
2. Rename the old RegSrvr.xml to something else e.g. RegSrvr.bak
   
   
3. Start Management Studio again (it will recreate the file and you'll be able to add your servers again)

Link : http://www.dbtalk.net/microsoft-public-sqlserver-tools/ssms-2008-a-512328.html

Setting Internet Explorer Proxy settings (via Windows 2008 Group Policy)

Setting Internet Explorer Proxy settings (via Windows 2008 Group Policy)

Find SQL Agent Job Steps that do / don't generate an output file

Find SQL Agent Job Steps that generate an output file for debugging -

select 
  @@servername
, name
, step_name
, output_file_name
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobsteps s
on j.job_id = s.job_id
where output_file_name is not null

Find SQL Agent Job Steps without output files -

select 
  @@servername
, name
, step_name
, output_file_name
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobsteps s
on j.job_id = s.job_id
where output_file_name is null

Management Studio : Script Multiple Jobs quickly

1. Expand SQL Server Agent
2. Click On 'Jobs'
3. Click F7 (summary pane will appear on right)
4. Highlight the jobs (Ctrl & Shift to select multiple)
5. Right Click selected jobs
6. Select 'Script Job as'
7. Select script destination

Removing a rogue SPN

1) Remove SPN -

C:\Users\TEMP>

setspn -d MSSQL$SQL2008/DW1-SQLDEV-02.DW1.local dw1\sqlservicese

ver

Unregistering ServicePrincipalNames for CN=SQLServiceServer,CN=Users,DC=DW1,DC=

ocal

MSSQL$SQL2008/DW1-SQLDEV-02.DW1.local

Updated object

2) List SPNs (check it has gone) -

C:\Users\TEMP>

setspn -l dw1\sqlserviceserver

Registered ServicePrincipalNames for CN=SQLServiceServer,CN=Users,DC=DW1,DC=loc

l:

MSSQLSvc/DW1-SQLUAT-02.DW1.local:SQL2008

MSSQLSvc/DW1-SQLDEV-02.DW1.local:SQL2008

MSSQLSvc/DW1-SQLDEV-02.DW1.local

MSSQLSvc/DW1-SQLQA-02.DW1.local:SQL2008

MSSQLSvc/DW1-SQLPRO-05.DW1.local

MSSQLSvc/DW1-SQLPRO-03.DW1.local

MSSQLSvc/DW1-SQLPRO-03.DW1.local:1433

MSSQLSvc/DW1-SSIS-01.DW1.local:1433

MSSQLSvc/DW1-SSIS-01.DW1.local

MSSQLSvc/DW1-SSIS-02.DW1.local:1433

MSSQLSvc/DW1-SSIS-02.DW1.local

MSSQLSvc/DW1-DPM-01.DW1.local:1433

MSSQLSvc/DW1-DPM-01.DW1.local

MSSQLSvc/DW1-SQLPRO-05.DW1.local:1433

MSSQLSvc/DW1-SQLPRO-01.DW1.local:1433

MSSQLSvc/DW1-SQLPRO-01.DW1.local

MSSQLSvc/DW1-SQLQA-02.DW1.local:1433

MSSQLSvc/DW1-SQLUAT-02.DW1.local:1433

MSSQLSvc/DW1-SQLDEV-01.DW1.local:1433

MSSQLSvc/DW1-SQLDEV-01.DW1.local

MSSQLSvc/DW1-DPM-01.DW1.local:49270

MSSQLSvc/DW1-SQLQA-01.DW1.local:1433

MSSQLSvc/DW1-SQLQA-01.DW1.local

MSSQLSvc/DW1-SQLUAT-01.DW1.local:1433

MSSQLSvc/DW1-SQLUAT-01.DW1.local

MSSQLSvr/DW1-SQLQA-02.DW1.local

MSSQLSvr/DW1-SQLQA-02.DW1.local:1433

MSSQLSvc/ZZZ-SQLTEST-01.DW1.local:1433

MSSQLSvc/ZZZ-SQLTEST-01.DW1.local

Updating Performance Dashboard Reports for SQL 2008

Updating Performance Dashboard Reports for SQL 2008

All our 2008 instances have this installed. It’s a mod the sql community have done to Performance Dashboard 2005.

Once installed, from management studio, right click a server, go to ’custom reports’ and select performance_dashboard_main.rdl.


Download link : http://blogs.technet.com/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspx

tsql - Connection Details - sys.dm_exec_connections

Show properties of current connection -

select session_id, auth_scheme, net_transport , client_net_address
from sys.dm_exec_connections
where @@SPID = session_id

Advanced Performance Queries

Queries with Index Scans Due to Implicit Conversions >
http://sqlserverpedia.com/wiki/Queries_with_Index_Scans_Due_to_Implicit_Conversions

Disabling UAC via 2008 Group Policy (on a Domain)

I run a small 2008 Active Directory for my Data Warehouse network.
The only people who access it via Remote Desktop (RDP) are 2 administrators.
I have no standard 'users' to worry about :)

Anyway, here is how I've disabled group policy for my domain.

On a Domain Controller (or other server with Policy Management tools installed) >

1. Navigate to Start > Administrative Tools > Group Policy Management
2. Find the domain policy (in my case 'Default Domain Policy'), right click and 'edit'.
3. Navigate to the UAC settings located here -
   Computer Configuration > Policies > Windows Settings > Security Settings > Local Policies > Security Options
4. Change settings as follows >

User Account Control: Admin Approval Mode for the Built-In Administrator Account > Enabled
User Account Control: Behavior of the elevation prompt for administrators in Admin Approval Mode > Elevate without Prompting
User Account Control: Detect application installations and prompt for elevation > Disabled
User Account Control: Run all users in Admin Approval Mode > Disabled

Windows 2008 Firewall Rules for SQL Server

I blogged some time ago on how to configure Windows Firewall for SQL Server via Group Policy but had to do the same outside my domain today.
In this case, Windows 2008 Firewall can simply be controlled via Control Panel.
Here's how to open it up for SQL Server >

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

Was faced with this error today when configuring a new server,
" SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified "

So, after checking that your Browser service is up and running, is it accessible?  By this I mean, not blocked by a firewall, ISA etc,
PortQry is a free tool to help determine this (a bit more advanced than telneting)

You use it like this >

portqry.exe -n yourservername -p UDP -e 1434

Links :

Download PortQry version 2
MSDN blogs

Hyper-V : Jumbo Packet Support Coming

Jumbo Packet support in Hyper-V is coming in Windows 2008 R2.

http://blogs.technet.com/virtualization/archive/2009/07/22/windows-server-2008-r2-hyper-v-server-2008-r2-rtm.aspx

http://www.microsoft.com/windowsserver2008/en/us/R2.aspx

Also SQL 2008 gets a R2 release too...

http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx

SQL 2008 : Pushing Backups Further

I blogged here about using BUFFERCOUNT to speed up database backups.
Here, I attempted to use MAXTRANSFERSIZE to do more.

The command...

BACKUP DATABASE [ImportData]
TO  DISK = N'd:\ImportData.bak'
WITH NOFORMAT, INIT,  NAME = N'ImportData Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, COMPRESSION, BUFFERCOUNT = 150 , MAXTRANSFERSIZE = 1048576
GO

The default value for MAXTRANSFERSIZE is 1MB (1048576 bytes).
Possible values are multiples of 65536 bytes (64 KB) ranging up to 4MB (4194304 bytes).

The results >

MAXTRANSFERSIZE = 1048576
Processed 216 pages for database 'ImportData', file 'ImportData_log2' on file 1.
BACKUP DATABASE successfully processed 1330824 pages in 155.348 seconds (66.927 MB/sec).

MAXTRANSFERSIZE = 2097152
Processed 458 pages for database 'ImportData', file 'ImportData_log2' on file 1.
BACKUP DATABASE successfully processed 1331090 pages in 145.383 seconds (71.529 MB/sec).

MAXTRANSFERSIZE = 3145728
Processed 201 pages for database 'ImportData', file 'ImportData_log2' on file 1.
BACKUP DATABASE successfully processed 1330913 pages in 145.512 seconds (71.456 MB/sec).

MAXTRANSFERSIZE = 4194304
BACKUP DATABASE successfully processed 1330780 pages in 148.087 seconds (70.206 MB/sec).

The conclusion >

A lot of messing about to only save 10 seconds.
Oh, and performing the backups in this way stressed the server out and it refused connections during the backup period.
Think I'll leave well alone...

Moving Tempdb to a different drive...

Moving Tempdb to a different drive...

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\Data\templog.ldf');
GO

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Restart the SQL Service afterwards.

Walkthrough : Using AD Sites & Services to assist an IP migration

SSIS Findings : File System Package Deployment

It is easier to deploy SSIS packages to the file system
http://blogs.conchango.com/jamiethomson/archive/2006/02/20/SSIS_3A00_-Deploy-to-file-system-or-SQL-Server.aspx

http://msdn.microsoft.com/en-us/library/ms141802%28SQL.90%29.aspx

Vardecimal - Estimating Space Savings

Could you benefit from space savings by using the VARDECIMAL data type?

This dynamic sql will help you find out....

SELECT 'exec sys.sp_estimated_rowsize_reduction_for_vardecimal ''' +schema_name(schema_id) + '.' + name +'''' FROM sys.objects WHERE type = 'u' ORDER BY 1

Link : http://blogs.msdn.com/sqlserverstorageengine/archive/2006/11/13/estimating-the-space-savings-with-vardecimal-storage-format.aspx