2008

4 months ago, I changed Job, seeking more a role with more sql development rather than focusing on administration. The dba role is interpreted very differently by smaller companies and as I wanted a more challenging environment I moved on.

Although real development project work is only in the planning stage, I've achieved a lot since September -

Fixing the obvious -

1. Implementing Backups (that's right, there were none!)
2. Addressing Index Fragmentation with scripts.
3. Fixing massive file fragmentation of live databases (damn that autogrowth setting)

Virtualisation -

1. Getting to grips with virtualisation in a production environment.
2. Virtualised SQL Server 2005 on Microsoft’s Hyper-V platform.

Networking -

1. Building my own 13 VM network for the datawarehouse on 4 physical hosts.
2. Building a Windows 2008 domain. 
3. Fun with Group Policy (controlling RDP, Windows Firewall, Windows Updates).
4. Implementing VAMT for Product Activation, Windows Updates and firewalls.

Development -

1. Rewriting worst performing front end sql queries in parameterised stored procedures.
2. Improving indexes using the Database Tuning Advisor.
3. Reviewed existing systems.

Existing systems -

The core product database has suffered from being developed quickly by 1 person.
It's major faults are >

1. Passwords stored in PHP code. - Not an easily configurable location, code not portable.
2. Application code granted excess permissions - This is due to TRUNCATE present in code.
3. Import and Reporting systems sharing the same server.
   Statistics show disk activity is 99% write, 1% read.
4. Cross db ownership - Live code accesses both application and import database using 3 part object naming i.e. db_name.schema_name.object_name.
5. Nested views! - Views that are built from views, that are built from views etc...  (Inefficient , difficult to debug, difficult to optimize).
6. Adhoc queries - SELECT statements embedded in php code where stored procedures would increase simplicity (for web developer), ensure query parameterization (for performance) and increase security (reduce chances of an injection attack),
7. Poor table design - Many tables have lots of columns. Databases badly need some normalization.

SQL 2005 : Index Performance

Index Types from Fastest > Slowest

[1] Non-Clustered Covering with Included non-key columns
[2] Non-Clustered Covering
[3] Clustered
[4] Non Clustered Non Covering
[5] No Index

therefore, for a Non-Clustered Index with Included non-Key columns >

CREATE INDEX nci_IndexName
ON TABLE (keycolumn_1,keycolumn_2)
INCLUDE (nonkeycolumn_1,nonkeycolumn_2)

SQL Query : Logical Processing Phases

(8) SELECT (9) DISTINCT (11) TOP [column list]
(1) FROM [table_1]
(3) [join_type] JOIN [table_2]
(2) ON [condition]
(4) WHERE [condition]
(5) GROUP BY [column list]
(6) WITH [CUBE | ROLLUP]
(7) HAVING [condition]
(10) ORDER BY [column list]

Agile

Interesting posts on Agile Development...

http://www.techcrunchit.com/2008/12/21/will-this-economy-finally-push-the-toyota-way-into-software-development/

http://jamesshore.com/Blog/The-Decline-and-Fall-of-Agile.html

Using Missing Indexes DMVs to generate index suggestions

I came across this today.
It uses the missing_indexes dmvs to recommend where indexes could be added.
Have modified it to include the table schema.

SELECT     'CREATE NONCLUSTERED INDEX NewNameHere ON ' + sys.schemas.name + '.' + sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN mid.inequality_columns IS NULL
THEN '' ELSE CASE WHEN mid.equality_columns IS NULL 
THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL 
THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns, 
mid.included_columns
FROM         sys.dm_db_missing_index_group_stats AS migs 
INNER JOIN   sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
INNER JOIN   sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle 
INNER JOIN   sys.objects WITH (nolock) ON mid.object_id = sys.objects.object_id
INNER JOIN   sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id 
WHERE     (migs.group_handle IN
(SELECT     TOP 100 PERCENT group_handle
FROM          sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND sys.objects.type = 'U'

Original Piece


MSDN : Using Missing Index Information to Write CREATE INDEX Statements


Brian Knight's blog on the Missing Index DMV

Log file reuse Problem

" Date 20/12/2008 17:30:19
Log SQL Server (Current - 20/12/2009 00:00:00)

Source spid62

Message 'ImportSystem' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"

Simple one this, the error tells you exactly what to do...

select log_reuse_wait_desc, *  from sys.databases

The log file cannot be reused due to an Active Transaction.

CHECKSUM Explained

good video tutorial over on SQLServerCentral

Forcing Query Plan Choice with USE PLAN hint

USE PLAN can be used to bypass SQL's choice of query plan.
Microsoft white paper here...
http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx

SQL 2008 : IFTS - Integrated Full Text Search

Full Text search is no longer an external feature.

http://sqlblogcasts.com/blogs/simons/archive/2008/02/19/SQL-Server-2008---iFTS-Introduction.aspx

SQL 2005 : SP3

SP3 for SQL 2005 is out.

Beware if you've already applied CU10 / CU11 though, as SP3 is based on CU9!
You'll need to run CU1 for SQL 2005 SP3 therefore!

Windows 2008 RDP Session gets stuck 'Preparing Desktop'

Couldn't find the answer to this anywhere.
It turned out that installing applications whilst 'User Account Control' was enabled had interfered with the desktop profiles.
Have temporarily disabled UAC to get round the situation. Not ideal, but theres a solution for anyone else encountering the problem...

SSIS : Package Execution

Command line package execution -

DTEXEC.EXE /F "D:\SSISDeployment\SSISPackage.dtsx"

BIDs -
F5 - Run Package in OS (without screen display)

Update Group Policy immediately

C:\> GPUPDATE /FORCE

Run this from the command prompt on client machines to overide the default GP refresh period i.e. have Group Policy applied straight away.

Windows 2008 Firewall Rules for SQL Server (via Group Policy)

Configuring Windows 2008 Firewall for SQL Server

1) Launch Group Policy and navigate to >
Computer Configuration > Policies > Windows Settings > Security Settings > Windows Firewall with Advanced Security
2) Right Click on Inbound rules , click 'New Rule'
3) Select 'Port Rule', Click 'Next'
4) Select 'TCP' and enter specific ports ' 1433, 1434 ' (comma separated). Click 'Next'.
5) Select 'Allow the connection' Click 'Next'.
6) Use tick boxes to restrict the view via profile. (Domain/Private/Public). Click 'Next'
7) Provide a name and optionally a description for Rule. Click 'Finish'.

Repeat the above for Outbound Rule too.
You may need to open other ports too, see SQL Server Default Ports

NOTE : Remember to run GPUPDATE /FORCE on client machines to overide the default GP refresh period.

Group Policy : Allowing RDP Access

1) Add Users in Active Directory to a common group e.g ; 'RDP Access Group'

2) In group policy (start > run >gpedit.msc) navigate to >
Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment.

3) Add 'RDP Access Group' to the 'Allow log on through Terminal Services group' setting.

4) Add 'RDP Access Group' to the 'Allow log on locally' policy too.

5) Run GPUPDATE to force the policy to take effect to test.

SQL DateTime > Unix Timestamp

I was blissfully ignorant of UNIX timestamps until a colleague needed to fetch datetime data from one of my servers.

Basically, its just the offset since a given epoch (1st Jan 1970) in seconds.

Returning a Unix timestamp from a SQL Date >

SELECT DATEDIFF(s, '19700101', GETDATE()) 

Returning a SQL Date from a Unix timestamp >

SELECT DATEADD(s,1228348800, '19700101') 

Unix timestamp

Moving deployed SSIS packages into folders

Moving deployed SSIS packages into folders ...

from command prompt >

dtutil /SQL "LoadDataPackage" /SourceServer SQL-LIVE-01 /MOVE SQL;"/PackageFolder/LoadDataPackage" /DestServer SQL-LIVE-01

SQL 2008 CU2

This one slipped in without me noticing!

CU2 for SQL 2008 is out...

http://support.microsoft.com/default.aspx/kb/958186/en-us

SQL Restart without a restart...

I blogged last year on clearing sql servers' various memory caches, useful for testing code.
I read this today and it turns out that what i'm achieving here is restarting the server without restarting (if you follow).

Those commands again ...

-- To clear the procedure cache
DBCC FREEPROCCACHE

-- To clear the data cache
DBCC DROPCLEANBUFFERS

UTF8 - No support in SQL Server

For Unicode support in SQL Server. Just choose NCHAR / NVARCHAR data types.

SQL implements Unicode through UCS-2 rather than UTF8.

Wikipedia : Comparison of unicode encodings

http://journeymanjournal.blogspot.com/2005/02/no-utf-8-support-in-sql-server.html

SQL Humour...

A SQL query goes into a bar, walks up to two tables and says, "Can I join you".

Good grief....

http://sql-server-performance.com/Community/blogs/editorsblog/archive/2008/11/26/happy-thanksgiving.aspx

SPAM is on the increase...

http://www.vnunet.com/vnunet/news/2231392/spam-levels-expected-rise

I’ve definitely observed an increase...

2008 Group Policy control if not on a domain controller

Group Policy is available by default on servers with the active directory role (typically domain controllers).
What if you want to control it from another machine however?

The Group Policy Management Console can be added to your windows 2008 server from Server Manager where it is a 'feature' (everything is either a 'role' or a 'feature' nowadays).

To install >
1) Launch Server Manger
2) In the Features pane, click 'Add Features'
3) Select 'Group Policy Management' followed by 'Install'
4) Run gpmc.msc to launch Group Policy Management Console or navigate to it in Server Manager.

http://www.vista123.net/content/installing-gpmc-windows-server-2008-and-windows-vista-service-pack-1

Activating / Deactivating Windows Firewall via Group Policy

How to deactivate Windows Firewall via Windows 2008 Group Policy >

(Needed to do this temporarily for Windows activation from VAMT server)


Path : Computer Configuration > Policies > Administrative Templates > Network > Network Connections > Windows Firewall > Domain Profile

Property : Windows Firewall : Protect all network connections

Windows 2008 Activation with VAMT

Had 'fun' with VAMT today (Volume Activation Management Tool) activating Windows 2008 instances.

The bottom line is that the VAMT tool (like the activation process itself) DOESNT support proxy authentication.

It also doesn't get through Windows firewall (turned this temporarily off in Group Policy) to activate them.

If you find licences slipping away before you can activate them, a temporary fix is to 'rearm' them. Execute ' slmgr.vbs /rearm ' to do this.


Proxy authentication error was as follows >

Unexpected Error
----------------
An unexpected error has occurred
The following information was found for this error:
Code: 0x8004FE33
Description: Unable to find a detailed error description.
The facility code is: ITF (0x04)
Facility error: 0xFE33 (65075L)

Sources :
http://social.technet.microsoft.com/Forums/en-US/winserversetup/thread/48ba05f1-601f-4095-9c66-e4b7024c903b/

http://social.technet.microsoft.com/Forums/en-US/winserversetup/thread/783ac1da-4411-4e7d-9da1-e6ab87be80c2/

SAC (Surface Area Configuration) gone in SQL 2008

The Surface Area Configuration tool introduced in SQL 2005 has already been deprecated and doesnt make it into SQL 2008.

http://sqlblog.com/blogs/denis_gobo/archive/2007/11/20/3441.aspx

Windows 2008 GPT Support

Use DISKPART tool convert disks from MBR to GPT so that a single partition can be larger than 2TB.

C:\> DISKPART

DISKPART> list

Microsoft DiskPart version 6.0.6001

DISK - Display a list of disks.
PARTITION - Display a list of partitions on the selected disk.
VOLUME - Display a list of volumes.


DISKPART> list disk

Disk ### Status Size Free Dyn Gpt
-------- ---------- ------- ------- --- ---
Disk 0 Online 136 GB 0 B
* Disk 1 Online 4655 GB 2048 GB


DISKPART> select disk 1

Disk 1 is now the selected disk.


DISKPART> convert

Microsoft DiskPart version 6.0.6001

BASIC - Convert a disk from dynamic to basic.
DYNAMIC - Convert a disk from basic to dynamic.
GPT - Convert a disk from MBR to GPT.
MBR - Convert a disk from GPT to MBR.


DISKPART> convert gpt

DiskPart successfully converted the selected disk to GPT format.


http://www.techotopia.com/index.php/Adding_New_GPT_and_MBR_Disks_to_Windows_Server_2008_Systems

First Look... SQL 2008 : CDC - Change Data Capture

Playing with SQL 2008's CDC - Change Data Capture with the AdventureWorks 2008 database.

CDC has excellent potential in load systems e.g. for data warehousing etc.
It can be used to capture changes on tables or just columns.

exec sys.sp_cdc_enable_table
[ @source_schema = ] 'source_schema',
[ @source_name = ] 'source_name' ,
[ @role_name = ] 'role_name'
[,[ @capture_instance = ] 'capture_instance' ]
[,[ @supports_net_changes = ] supports_net_changes ]
[,[ @index_name = ] 'index_name' ]
[,[ @captured_column_list = ] 'captured_column_list' ]
[,[ @filegroup_name = ] 'filegroup_name' ]
[,[ @partition_switch = ] 'partition_switch' ]

USE [AdventureWorks2008]
GO

-- create a test table
CREATE TABLE [HumanResources].[Employee_Sync](
[BusinessEntityID] [int] NOT NULL,
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL,
[OrganizationNode] [hierarchyid] NULL,
[OrganizationLevel]  AS ([OrganizationNode].[GetLevel]()),
[JobTitle] [nvarchar](50) NOT NULL,
[BirthDate] [date] NOT NULL,
[MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL,
[HireDate] [date] NOT NULL,
[SalariedFlag] [dbo].[Flag] NOT NULL,
[VacationHours] [smallint] NOT NULL,
[SickLeaveHours] [smallint] NOT NULL,
[CurrentFlag] [dbo].[Flag] NOT NULL,
[ModifiedDate] [datetime] NOT NULL)

GO

-- Enable CDC on the current database
exec sys.sp_cdc_enable_db

-- Check CDC has been enabled...
select name, is_cdc_enabled from sys.databases

-- Enable CDC for my test table...
exec sys.sp_cdc_enable_table
@source_schema = 'HumanResources',
@source_name = 'Employee_Sync' ,
@role_name = 'CDCRole',
@supports_net_changes = 0
-- note @supports_net_changes can only be set if there is a primary key set on the table

-- Check CDC has been enabled on the table...
select name, type, type_desc, is_tracked_by_cdc from sys.tables
where is_tracked_by_cdc = 1

-- Disable CDC for my test table...
exec sys.sp_cdc_disable_table
@source_schema = 'HumanResources',
@source_name = 'Employee_Sync',
@capture_instance = 'all'

-- Disable CDC on the current database
exec sys.sp_cdc_disable_db
-- Check CDC has been disabled...
select name, is_cdc_enabled from sys.databases

INSERT INTO [HumanResources].[Employee_Sync]
([BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[OrganizationNode]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[HireDate]
,[SalariedFlag]
,[VacationHours]
,[SickLeaveHours]
,[CurrentFlag]
,[ModifiedDate])

SELECT [BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[OrganizationNode]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[HireDate]
,[SalariedFlag]
,[VacationHours]
,[SickLeaveHours]
,[CurrentFlag]
,[ModifiedDate]
FROM [AdventureWorks2008].[HumanResources].[Employee]

-- delete 50 rows
SET ROWCOUNT 50
DELETE FROM [HumanResources].[Employee_Sync]
SET ROWCOUNT 0

-- CDC utilises the sql transaction log. The 'log sequence number' or LSN identifies transactions in the log.
-- You query the changes in CDC via LSNs.
declare @startLSN binary(10), @endLSN binary(10)
-- get the first LSN for table changes
select @startLSN = sys.fn_cdc_get_min_lsn('HumanResources_Employee_Sync')
-- get the last LSN for table changes
select @endLSN = sys.fn_cdc_get_max_lsn()

-- get net changes; group changes in the range by the pk
-- get individual changes in the range
select * from cdc.fn_cdc_get_all_changes_HumanResources_Employee_Sync(@startLSN, @endLSN, 'all');

-- if had set '@supports_net_changes' to 1, then this function works...
--select * from cdc.fn_cdc_get_net_changes_HumanResources_EmployeeSync(@startLSN, @endLSN, 'all');

Good summary of CDC objects >
http://weblogs.sqlteam.com/derekc/archive/2008/01/28/60469.aspx

Table Sizes Script

if object_id('tempdb..#tempTableList') is not null  
drop table #tempTableList  
if object_id('tempdb..#tempresults') is not null  
drop table #tempresults  

DECLARE @totalrows INT
DECLARE @currentrow INT
DECLARE @currenttable NVARCHAR(255)
CREATE TABLE #tempTableList 
([id]         INT   IDENTITY(1,1),  
[schemaname] NVARCHAR(128),
[tablename]  NVARCHAR(128),
[row_count]    BIGINT,
[kb_reserved]   BIGINT,
[kb_data]    BIGINT,
[kb_index_size] BIGINT,
[kb_unused]   BIGINT) 

CREATE TABLE #tempresults (
[tablename]  NVARCHAR(128),
[row_count]    CHAR(11),
[kb_reserved]   VARCHAR(18),
[kb_data]    VARCHAR(18),
[kb_index_size] VARCHAR(18),
[kb_unused]   VARCHAR(18)) 

INSERT INTO #tempTableList ([schemaname], [tablename])
SELECT SCHEMA_NAME(O.SCHEMA_ID)  AS TABLE_SCHEMA,  
OBJECT_NAME(O.OBJECT_ID)  AS TABLE_NAME
FROM   SYS.TABLES O 

SELECT @totalrows = COUNT(*) FROM #tempTableList
SELECT @currentrow = 1

WHILE @currentrow <= @totalrows  
BEGIN  
DELETE #tempresults
SELECT @currenttable = [schemaname] + '.' + [tablename] FROM #tempTableList WHERE [id] = @currentrow
INSERT #tempresults EXEC sp_spaceused @currenttable
UPDATE #tempTableList
SET  [row_count]   = CAST(#tempresults.[row_count] AS BIGINT)
,[kb_reserved]  = CAST(REPLACE(#tempresults.[kb_reserved],' kb','') AS BIGINT)
,[kb_data]   = CAST(REPLACE(#tempresults.[kb_data],' kb','') AS BIGINT)
,[kb_index_size] = CAST(REPLACE(#tempresults.[kb_index_size],' kb','') AS BIGINT)
,[kb_unused]  = CAST(REPLACE(#tempresults.[kb_unused],' kb','') AS BIGINT)
FROM #tempTableList
INNER JOIN #tempresults ON #tempTableList.[tablename] = #tempresults.[tablename]

SET @currentrow = @currentrow + 1  
END  
SELECT * FROM #tempTableList
ORDER BY [schemaname],[tablename]

Firefox Proxy Authentication

Wanting to (legitimately) use Firefox on a work server (to download a batch of training videos via the 'DownThemAll' plugin if you must know...) I found it to repeatedly request proxy authentication presenting me with the username/password prompt for each object on a web page.

Rather annoying as you can imagine.

Here is how to get round it, so A) i remember and B) it may help you out too...

1) Type "about:config" in the address bar.
2) Locate the "network.negotiate-auth.allow-proxies" variable
3) Set the value to "false"

http://forums.mozillazine.org/viewtopic.php?t=348211

Finding the Fragmentation of an Index and fixing it

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/finding-fragmentation-of-an-index-and-fi

(Does what is says on the tin really)

Hyper-V VM : Removing Hidden Network Adapter!

"The IP address 10.0.20.31 you have entered for this network adapter is already assigned to another adapter 'Microsoft Virtual Machine Bus Network Adapter #2' 'Microsoft Virtual Machine Bus Network Adapter #2' is hidden from the Network Connections folder because it is not physically in the computer. If the same address is assigned to both adapters and they both become active, only one of them will use this address. This may result in incorrect system configuration. Do you want to enter a different IP address for this adapater in the list of IP addreses in the Advanced dialog box?"

By changing the virtual network on the Hyper-V host, I have managed to create a ghost network adapter in my hyper-v VM!

To see the hidden adapter >

1. launch command prompt - start > run > cmd [enter]
2. type set devmgr_show_nonpresent_devices=1 [enter]
3. type start devmgmt.msc [enter]
4. from menu bar select 'View' drop down and select 'Show hidden devices'

To remove the (now visible) adapter >

1. locate the adapter in device manager under 'network adapters'
2. right-click it and select 'uninstall device'
3. reboot

http://blog.mpecsinc.ca/2008/02/sbs-shadowprotect-some-hardware.html

Random Number TSQL

SELECT ABS(CHECKSUM(NEWID())) 

Hyper-V Annoyances : Reactivation

When you move a VM across hosts, if it NICs are named differently then installing a new virtual nic in a VM is seen as a hardware change.

This in turn means that the virtual OS needs reactivation. :/

RDS - Remote Desktop Services

'Terminal Services' to be renamed to 'Remote Desktop Services' in Windows 2008 SP2.

Hyper-V to be used to deploy the VDI (Virtual Desktop Infrastructure) environment.

http://4sysops.com/archives/remote-desktop-services-windows-server-2008-r2-will-support-virtual-desktop-infrastructure-vdi/

Failed to add device Microsoft Synthetic Ethernet Port ...

Tried to start a VM from another server and got the following error.

" Failed to add device Microsoft Synthetic Ethernet Port ... "


The server has MS Forefront Client Security AV onto the server, hence need to set exclusions for the VM files

This chap found the solution...

http://joshrobi.blogspot.com/2008/06/how-to-fix-hyper-v-error-failed-to-add.html

Controlling Group Policy Update Frequency

Changing the update frequency of group policy, i.e. how quickly a policy is applied...


Path : Computer Configuration > Policies > Administrative Templates > System > Group Policy

Properties :

Group Policy refresh interval for computers
Group Policy refresh interval for domain controllers

Group policy updates can be forced instantly by typing 'gpupdate' at a command prompt / run box.

Enabling Network Map via Group Policy

How to enable the Network Map via Group Policy >


Path : Computer Configuration > Administrative Templates > Network > Link-Layer Topology Discovery

Properties :
Turn on Mapper I/O (LLTDIO driver
Turn on Responder (RSPNDR) driver

Enable these to allow machines to discover and 'be discovered' by LLTD.

Hyper-V : Language Setting

Note to self! : Remember to install Windows 2008 as default language (US) for Hyper-V to function.

http://www.servercare.nl/Lists/Posts/Post.aspx?ID=17

Originally blogged it here , when first playing....

Hyper-V : Installation on Windows 2008

Clearing all tables via Truncate

Deleting data in all tables (providing constraints don't stop you)

Use with care!!!

Version 1 : Generate sql to do this

SELECT 'TRUNCATE TABLE [' +table_schema + '].[' + TABLE_NAME +']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE_TABLE'
ORDER BY 1

Version 2: Perform the truncates directly

EXECUTE sp_MSforeachtable 'TRUNCATE TABLE ?;'  

NB :You could use these in conjunction with Disable all Foreign Keys and Triggers or the techniques I employ in Empty database script

SQL 2008 : Report Builder

Version 2 out...

http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en

Reporting Services 2008 : Invalid Namespace

Got the following error, when trying to connect to my new Reporting Services 2008 build.

" No Report Servers were found.

Details : Invalid Namespace "

Fortunately, Justin King has already solved it for us, and has an really easy to follow guide to resolving this issue here >

http://www.kingjustin.com/post/2008/08/Reporting-Services-2008-Invalid-Namespace.aspx

( Thank you, Justin ! )

SQL Server Video : Nibbling Deletes

http://www.sqlservervideos.com/player/wms/nibbling-deletes

Outlook 2007 speedup

The Addins enabled by default in Lookout 2007 hamper it's performance somewhat.

You can achieve a fair performance boost, by >

1) Open Outlook as Administrator (right-click icon, 'run as administrator' if you're not an admin already)

2) Navigate to Tools > Trust Center > Addins

3) Click 'go' (to the right of 'COM Addins' at the bottom of the screen).

4) Untick anything you don't need.

System Center Virtual Machine Manager 2008

RTM is released. Now just have to wait for the MSDN discs :)

http://msdnrss.thecoderblogs.com/2008/10/21/system-center-virtual-machine-manager-2008-has-rtmed/

http://www.microsoft.com/systemcenter/scvmm/downloadbeta.mspx

Recommended : Notepad++

Notepad++. A FREE notepad replacement.

http://notepad-plus.sourceforge.net/uk/site.htm

Am using it instead of ultraedit now...

SQL 2008 : Enable TCP/IP

After installing, we need to enable TCP/IP so external machines can access SQL...

1. run SQL Server Configuration manager

2. select SQL Server Network Configuration

3. select Protocols for 'instancename'

4. right click on TCP/IP and select 'enable'

SysPrep

Generates a new random SID (security ID), unique to the image / VM.

Start > Run > Sysprep [Enter]


If you forget to do this in preparing your VMs, you'll need NewSid.

Install Messenger on 64 bit platforms

MSN Messenger on Windows 2008 64bit...

http://www.msghelp.net/printthread.php?tid=86312

Enabling Remote Desktop Users

via Advanced User Management.

Put them in this group to avoid granting Admin permissions unnecessarily...

Index Usage in current db

Use this to review index usage >

SELECT object_name(object_id), *
FROM sys.dm_db_index_usage_stats
WHERE object_id > 100
AND database_id = db_id()

Manual Uninstall of SQL 2005

I managed to get a corrupt SQL 2005 which in turn prevented the install of SQL 2008...


A useful post, that helped me out today >

http://www.gilham.org/Blog/Lists/Posts/Post.aspx?List=aab85845-88d2-4091-8088-a6bbce0a4304&ID=190

SQL 2008 : Security

Note: In sql 2008, the ’sa’ login has been replaced by ’sysadmin’

The 'sa' still exists but is to be deprecated, i.e. will not be there in future versions.

Networking Basics - Network Clients

Installing the Hyper-V role seems to make Windows 2008 a little forgetful at times about it's network settings. On a couple of occasions I've returned to my NIC configuration to find network settings missing.

If you cannot connect to a server, check it has 'File and Printer Sharing' enabled as below >


If your server cannot see others on the network, check 'Client for Microsoft Networks' is enabled.

SQL 2008 : Server Auditing Functionality

is looking good...

Enabling Auditing
http://sqlblogcasts.com/blogs/sqldbatips/archive/2008/06/27/new-in-sql-2008-server-and-database-auditing-part-i.aspx

Server Level Auditing
http://sqlblogcasts.com/blogs/sqldbatips/archive/2008/06/27/new-in-sql-2008-server-and-database-auditing-part-ii.aspx

Database Level Auditing
http://sqlblogcasts.com/blogs/sqldbatips/archive/2008/06/27/new-in-sql-2008-server-and-database-auditing-part-iii.aspx

SQL 2008 on Hyper-V : Whitepaper

Whitepaper released 2 days ago. Performance looking very promising...

http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SQL2008inHyperV2008.docx

Currently not linked from the main whitepapers page for some reason >

http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx

Setup has detected a canonical discretionary access control list (DACL) on directory...

Got this error today during SQL 2008 install...



" Setup has detected a canonical discretionary access control list (DACL) on directory... "

Got round this by >

Adding 'Domain\Sql Service Accounts' (a domain group containing my 5 service accounts) to have full rights on c:\program files\microsoft sql server (& all subdirectories)

Syscacheobjects (Sql query plan reuse)

Viewing the contents of the Sql cache (and how many times plans have been reused).

select cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects

Dirty Reads - The NOLOCK hint

I googled ‘read uncommitted speed’ and followed a link for ‘dirty reads’ (database terminology for viewing uncommitted transactions).
Suffice to say, the page went somewhere i didn’t intend to go!

Anyway, enough waffling, what are Dirty Reads?

"a dirty read is where a query reads data from the database without lock protection. Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running"

To perform 'Dirty Reads' in a query >
Use the WITH (NOLOCK) table hint after each table involved in the query.
eg;

SELECT * FROM Table1 WITH (NOLOCK) 
INNER JOIN Table2 WITH (NOLOCK) 
        ON Table1.PK = Table2.FK

To set 'Dirty Reads' as default read type for the connection, use >

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

This latter, connection level delaration is especially useful for reporting systems.
http://blogs.neudesic.com/blogs/phil_scott/archive/2005/12/05/11.aspx
http://www.databasejournal.com/features/mssql/article.php/3553281/Controlling-Transactions-and-Locks-in-SQL-2000-and-2005---Part-3.htm

PS : If I show up in the proxy logs, it wasn’t intentional!

Reorganize / Rebuild a single index

Reorganizing an index physically reorders the leaf nodes of an index >

ALTER INDEX indexname ON schema.table REORGANIZE

Rebuilding an index drops an index and recreates it >

ALTER INDEX indexname ON schema.table REBUILD

Rebuilding is more resource intensive.

If you're using a version that supports it (Developer/Enterprise), rebuilds can be done ONLINE.

ALTER INDEX indexname ON schema.table REBUILD WITH(ONLINE = ON)

SQL 2008 : Multi Server Queries

Management Studio in SQL 2008 introduces Multi Server Queries :)
When enabled, you can run the same SQL against multiple servers simultaneously and receive the results in a single window.
It's a feature third party tools have had for a while.
Cool, but somewhat dangerous too...

http://www.sqlservercentral.com/articles/SQL+Server+Management+Studio/63650/

CTEs - Common Table Expressions

'a CTE can only be used once in your query' (by the query the immediately follows it).

source :
http://www.singingeels.com/Articles/Understanding_SQL_Complex_Queries.aspx

Vista Volume (or lack of...)

Where's my volume icon gone?
Why is my powersave icon suddenly visible even when the laptop is plugged in?

This chap has some solutions >

http://winhlp.com/node/16

It's a long , detailed article which lists several solutions. If you find your system icons missing, here's the quick fix >

1. Search registry and delete all “IconStreams” and “PastIconStreams” entries (searching for the former, will find the latter.
2. Restart Windoze
3. Add them again (task bar properties, notification area > tick the boxes!)

SQL 2008 CU1 Released

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

CU1 (or cummulative update 1) is out already for SQL 2008.
CU releases havent replaced service packs, but are a lot more regular if SQL 2005 updates were anything to go by.

Common Table Expressions : MAXRECURSION

I was looking today to generate a sequential list of dates as quickly as possible.

The fastest solution to the problem seemed to involve a common table expression (CTE) in sql 2005.

CREATE FUNCTION GenerateDates(@startdate DATETIME, @enddate DATETIME)
RETURNS TABLE
AS
RETURN 
(
      WITH DatesCTE(CurrentDate) AS
      (
            SELECT @startdate as CurrentDate
            UNION ALL
            SELECT DATEADD(day,1,CurrentDate)
            FROM DatesCTE
            WHERE CurrentDate < @enddate 
   
      ) 
      SELECT CurrentDate FROM DatesCTE
);

Excellent execution time, but there is a downfall. The function cannot cope with providing a list of more than 100 dates.

Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

This is because the defalt recursion level for a CTE is 100.

Normally the recursion level can be specified manually to get round this using OPTION (MAXRECURSION limit) where limit is a value from 10 to 32767, or 0 for unlimited recursion.

Setting the recursion level with OPTION (MAXRECURSION limit) is NOT SUPPORTED inside user defined functions and views.

Not as tidy as using a function, but we can do with a stored procedure >

CREATE PROCEDURE dbo.usp_GenerateDates
  @startdate DATETIME ,@enddate DATETIME
AS
BEGIN
;WITH DatesCTE(CurrentDate) AS
  (
    SELECT @startdate AS CurrentDate
    UNION ALL
    SELECT DATEADD(day,1,CurrentDate)
    FROM DatesCTE
    WHERE CurrentDate < @enddate  
  ) 

SELECT CurrentDate FROM DatesCTE
OPTION (MAXRECURSION 0)
END
GO

We can put the output of the procedure into a temporary table like this >

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#tblDateList%')
   DROP TABLE #tblDateList
 
CREATE TABLE #tblDateList
   ( [Date] DATETIME NOT NULL,
     CONSTRAINT [PK_#tblDateList] PRIMARY KEY CLUSTERED ([Date] ASC) 
   ) ON [PRIMARY]
  
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME

SET @enddate   = CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)
SET @startdate = DATEADD(DAY,-100,@enddate)

INSERT INTO #tblDateList
EXECUTE dbo.usp_GenerateDates
  @startdate = @startdate
 ,@enddate = @enddate

Disk System Pressure

Woah!!!!! This sql instance is really struggling with I/O >


The steps I took to rectify this were >
1) File Defragmentation (well I moved the files off and back on to the disk as it was quicker)
2) Data Defragmentation (implemented a deframentation job & scheduled it)
3) Moved tempdb to a separate physical disk

Performance Impact of Enabling Page Checksum and Default Trace

Whilst hunting options to squeeze every last drop out of a life out of an SQL instance, i toyed with the idea of disabling the database 'Page Checksum' and 'Default Trace' options.

The full article is linked below, but the upshot is that there is almost negligable performance decrease.

http://sqlblog.com/blogs/linchi_shea/archive/2007/01/16/performance-impact-of-enabling-page-checksum-and-default-trace.aspx

OLTP Performance Issues

Rather useful high level summary >

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/oltp-performance-issues.mspx

Forced Parameterization

SQL Server's query optimizer can make use of cached plans if a query is submitted that already has one.
The random nature of adhoc SQL against a database makes it difficult to reuse a plan however as similar adhoc tsql queries just dont look the same.
Turning Parameterization to 'forced' tries to improve this and alledgedly makes a difference for high volume systems.
Am currently trialing it out...

-- change parameterization to 'forced'
ALTER DATABASE databasename SET PARAMETERIZATION FORCED
GO

-- reverse the setting
ALTER DATABASE databasename SET PARAMETERIZATION Simple
GO

Table Row Count GEM !!!

Use System objects to return the count of records in tables, rather than COUNT(*).

This method is runs almost instantly but may be a little behind in terms of table statistics, i.e. the row counts may be out as statistics are not updated instantly.

SELECT 
      SCHEMA_NAME(o.SCHEMA_ID) SchemaName
    , OBJECT_NAME(o.OBJECT_ID) TableName
    , SUM(ROWS) TableRows 
FROM SYS.PARTITIONS p
INNER JOIN SYS.OBJECTS o 
   ON p.object_id = o.object_id  
WHERE INDEX_ID IN (0,1) 
GROUP BY o.SCHEMA_ID, o.OBJECT_ID 
ORDER BY 1,2,3

Statistics can be updated using DBCC UPDATEUSAGE

Link :
http://furrukhbaig.wordpress.com/2008/07/24/rowcount-for-large-tables/

Nov 2009 Update. Plenty of alternate versions on the web now, linking different system tables >
http://www.bimonkey.com/tag/row-count/
http://www.sqldev.org/transactsql/perform-a-row-count-without-causing-a-scan-60203.shtml

SQL Statman ??? (Ski-Ba-Bop-Ba-Dop-Bop)

Statman sounds a bit too much like Scatman for my liking and reminds me of a cheesey 90s dance song.

On a serious note, just what are all these Statman() entries in my sql trace?

for example >

SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [dbname] AS [SC0] FROM [dbo].[#tmp_sp_db_vardecimal_storage_format ....

The short answer is that they are nothing to worry about.
They show an automatic statistics update occuring i.e. are the effect of having AUTO_UPDATE_STATISTICS set to ON.

TempDb Optimization for Multiple Processors

"The current recommendation from Microsoft is that you have a file per logical CPU for tempdb so as to balance load not only for object creation but for extent allocation and performance."

Ref : SQL Server write behaviour with multiple files in a file group

So, split TempDB by the number of processors.
Using current size of TempDB as a guide (2GB) and 2 Processors, split to 2x 1GB files.

My previous blog entry on how to do > Moving / Adding files to TempDB

SQL Messages Window : Get Messages straight away!

If you use PRINT to pass status messages back when running a TSQL script, you typically dont get to see them until the script completes (or errors) and then, they appear all at once (not great for debugging...)

DECLARE @StatusMessage varchar (100)
SET @StatusMessage = 'Insert Finished, Update Starting : ' + CONVERT (CHAR (20), GETDATE (), 8)
RAISERROR (@StatusMessage , 10, 1) WITH NOWAIT 

http://sqlblogcasts.com/blogs/leopasta/archive/2007/07/03/instant-gratification.aspx

Hyper-V : SQL Support

SQL Server 2005 is not fully supported on a virtual machine in a Windows Server 2008 Hyper-V environment. Microsoft is considering whether to provide support for SQL Server 2005 on Hyper-V virtual machines in future updates of SQL Server 2005 >

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

( SQL Server 2008 is supported )

Decision time it is then, as currently building Hyper-V images...

Remote Desktops Tool for Vista

I'm a big fan of 'Remote Desktops', the MMC snap-in that combines a pane listing servers with RDP functionality.
For XP, this was provided by 'AdminPak'.

For Vista this is now known as RSAT. (Remote Server Administration Tools).

AdminPak for XP >
http://www.microsoft.com/downloads/details.aspx?familyid=c16ae515-c8f4-47ef-a1e4-a8dcbacff8e3&displaylang=en

RSAT for Vista >
http://www.microsoft.com/downloads/details.aspx?FamilyId=9FF6E897-23CE-4A36-B7FC-D52065DE9960&displaylang=en

New Browser : Google Chrome

Google Chrome is launched in Beta today.

Having played with it briefly, it is FAST and does initialy look worth the effort.

http://www.google.com/chrome/


oh, and this ZDNet report agrees with me about it's speed.

Random Password Generator

For when you cannot install Chaos Generator (local pc locked down!), there are multiple online password generators now >

http://www.pctools.com/guides/password/

This Leet translator is also good for passwords >

http://www.albinoblacksheep.com/text/leet

r

TSQL DMV Queries

DMV queries for Blocking, DiskIO, Memory, Running Queries, Indexes...

http://sqlblogcasts.com/blogs/thepremiers/archive/2008/07/28/more-useful-dmv-queries.aspx

(Wish I'd found them before I spent hours writing my own)

Reattach Suspect Database

Simulating recovery of a database damaged by a crash over on Paul S Randal's blog...
http://www.sqlskills.com/blogs/paul/CategoryView,category,CHECKDB%2BFrom%2BEvery%2BAngle.aspx

SQL Server Versions : Performance Comparison

Joe Chang has published some performance statistics on SQL versions, including 2008...

http://sqlblog.com/blogs/joe_chang/archive/2008/08/17/large-query-performance-from-sql-server-2000-to-2008-32-64-bit.aspx

Get Date from UTC (Coordinated Universal Time) String

Get Date from UTC (Coordinated Universal Time) String

DECLARE @INPUTDATE VARCHAR(12),
  @OUTPUTDATE VARCHAR(12)

SET @INPUTDATE = '2006-7-29T13:55:6+0:0'

-- NEGATE TIME >
SELECT CHARINDEX('T',@INPUTDATE)
SELECT @INPUTDATE = LEFT(@INPUTDATE,CHARINDEX('T',@INPUTDATE)-1)
SELECT @INPUTDATE 

-- SEPARATE OUT YEAR, MONTH, DAY, PAD SINGLE DIGIT VALUES WITH ZEROES AND ADD BACK INTO SINGLE STRING WHICH CAN BE CONVERTED TO DATETIME
SELECT @OUTPUTDATE = LEFT(@INPUTDATE,4) + RIGHT('0' + REPLACE(SUBSTRING(@INPUTDATE,6,2),'-',''),2)+ RIGHT(REPLACE('0' + SUBSTRING(@INPUTDATE,8,3),'-',''),2)
SELECT @OUTPUTDATE

-- CONVERT TO DATETIME
SELECT CONVERT(DATETIME,@OUTPUTDATE,112)

CHECKSUM on Temporary tables (Temp db)

I was attempting to compare rows using CHECKSUM on sql 2005 when I came across this issue >
Storing my data in a #table (temporary table), I tried to use CHECKSUM to compare with the target table.
Despite the data being identical, different CHECKSUM values pervailed.

The good news is that a little googling proved it is sorted in sql 2008....


http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/checksum-and-tempdb.aspx

CTE Examples (Adventureworks 2008)

Simple CTE Example using AdventureWorks2008

WITH StaffBasicsCTE (FirstName, LastName, JobTitle, MaritalStatus , Gender , Age) 
AS (
 SELECT   FirstName, LastName,JobTitle, MaritalStatus , Gender ,DATEDIFF(YEAR,[BirthDate],GETDATE()) AS Age
 FROM [AdventureWorks2008].[Person].[Person] p
 INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e
 ON p.BusinessEntityID = e.BusinessEntityID)
  
SELECT * FROM StaffBasicsCTE
WHERE Gender = 'F'
AND MaritalStatus = 'S'
AND Age < 35

Recursive CTE Example using AdventureWorks2008 (Adapted from stored procedure dbo.uspGetEmployeeManagers)

WITH [EmployeeManagersCTE] ([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) 
    AS (
        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 
        FROM [HumanResources].[Employee] e 
        INNER JOIN [Person].[Person] p 
        ON p.[BusinessEntityID] = e.[BusinessEntityID]

        UNION ALL

        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [HumanResources].[Employee] e 
        INNER JOIN [EmployeeManagersCTE]
        ON e.[OrganizationNode].GetAncestor(1) = [EmployeeManagersCTE].[OrganizationNode]
        INNER JOIN [Person].[Person] p 
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
        )

    SELECT 
          m.[BusinessEntityID]
        , m.[FirstName]
        , m.[LastName] -- Outer select from the CTE
        , m.[RecursionLevel]
        , m.[OrganizationNode].ToString() as [OrganizationNode]
        , p.[FirstName] AS 'ManagerFirstName'
        , p.[LastName] AS 'ManagerLastName' 
    FROM [EmployeeManagersCTE] m 
        INNER JOIN [HumanResources].[Employee] e 
        ON m.[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
            INNER JOIN [Person].[Person] p 
            ON p.[BusinessEntityID] = e.[BusinessEntityID]
    ORDER BY [RecursionLevel], m.[OrganizationNode].ToString()
    OPTION (MAXRECURSION 25) 

Backup / Restore Corruption

" Server: Msg 3013, Level 16, State 1, Line 1 The backup data at the end of 'devicename' is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets. Server: Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. "

The backup file being restored/written is damaged due to an error (write error during the backup or physical media error).
* This can occur when restoring from a network source too, hence maybe worth attempting to copy locally first.

If you're trying to backup to an existing file and don't care about the existing data (as you are overwriting it anyway) , replace it like this >

BACKUP DATABASE db1 TO DISK='C:\db1.bak' with FORMAT

If you're attempting a restore (gulp), it's a 2 step process.
1) Get the file number of the backup set within the backup file >

RESTORE HEADERONLY FROM DISK='C:\db1.bak'

2) Having identified a backup set (in this case number 3), try and restore it from the backup set manually >

RESTORE DATABASE db1 FROM DISK='C:\db1.bak WITH FILE = 3

Automatic USB Stick Backup

A little something i've been meaning to do for a while, automatically backing up my USB stick when i plug it in.

The obvious solution is to create an autorun.inf file on the stick to run a backup script from the device. Autorun is not always available however and I want control over where my data (for the record, encrypted by Truecrypt) is copied.

I have found 'USB Detect and Launch' by Matt Collinge which once installed allows you to run scripts when devices are plugged in, allowing different actions depending on the volume label.
The software is free for personal use or $40 for business use.

1 ) Download & Install tool from >

http://mattcollinge.wordpress.com/software/usb-detect-and-launch/

2 ) I created a directory for my backups >

D:\!USB_BACKUP\R5D4

3 ) I created D:\!\backup-r5d4.cmd (a 1 line backup command) as >

XCOPY %1\*.* D:\!USB_BACKUP\R5D4 /e /i /h /y /d

Usb Detect & Launch Configuration >

4 ) In 'Main Settings' I created one entry.
This runs my backup command whenever it sees a usb stick with a label of R5D4 >

label:R5D4 exec:d:\!\backup-r5d4.cmd %1

5) In 'Main Settings' I changed the poll interval to 60 seconds.


Results :
Bingo! Plugged in my USB drive and up popped the command window which proceeded to copy my files.

Hyper-V : VHD size optimization

I previously blogged about compressing Hyper-V VHD images as they can 'bloat' and eat disk space. Unhappy with the results, I set about trying to squeeze one of my images more...

The following was performed with a VHD of Vista Ultimate with VS2008 & Office 2007 installed.

action	bytes
original image	40,212,793
set system restore to 1gb max, compressed vhd	14,006,618
ran windows defragmenter, compressed vhd	15,456,481
ran flexomiser defragmenter, compressed vhd	13,903,606

Well worth reviewing VHD file sizes periodically I think...
r

SQL 2005 CU9 Released

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

Hyper-V : Error after removing VM

On browsing the Hyper-V Server Manager, I noticed some errors that I had not spotted before >

The Virtual Machines configuration (guid) at 'C:\hyper-V-Path\' is no longer accessible.

For some reason, my attempts at tidying up and moving the vm image files to a new location had not been 100% successful, leaving HyperV to periodically look for the image in the old location (despite the VM working fine from it's new location).

To rectify the situation, this technet post came in rather useful.

Basically >

1 ) Navigate to C:\ProgramData\Microsoft\Windows\Hyper-V\Virtual Machines (if you don't see anything, unhide files and folders via tools > folder options > view > 'show hidden files & folders')

2) Locate the link file that matches the GUID number in your original message.

3) In Hyper-V, click 'Stop Service' (turns off VMMS)

4) Delete the file

5) In Hyper-V, click 'Start Service' to restart the VMMS.

Errors should no longer appear every 2 minutes now.

Note : VMMS is the 'Virtual Machine Management service' and just controls your ability to manage VMs, they continue running even when VMMS has been shut down.

SQL 2008 : Installation on Windows 2008 (VM)

Screen walkthrough of SQL 2008 installation process >

SQL 2008 : Installer

At first glance, the installer (Enterprise edition pictured) looks a lot more friendly.
More administration functions are available (and are explained) >

Useful tool : FreeUndelete

FreeUndelete : A file recovery app that is FREE (and works well).

http://www.officerecovery.com/freeundelete/

(It rescued me today when my clumsy fingers deleted a folder on my usb stick)

Management Studio Speedup : Shared Memory

If your client and server are the same server (screams silently), for instance in a local development environment then Shared Memory can help.

As of SQL 2005 it is enabled by default, but incase it isn't or you need to check -

1) Set Shared Memory as Enabled for the sql server -

2) Set Shared Memory as Enabled in the client protocols -

Ref : Default SQL Server Network Configuration

Table Design Optimization : Column Sizes Script

I was called on to help with a data problem today.
The developer concerned had misjudged data sizes and hence defined a table that she couldnt import into.
My approach was to define a table with generous text column sizes with a view to reducing them later.

This script shows defined column sizes along with the size of the largest data for that column.
Change the @SCHEMA and @TABLE variables at the top of the script to point it at the table of your choice >

SET NOCOUNT ON 
SET NOCOUNT ON 
SET ANSI_WARNINGS ON
DECLARE @SCHEMA  VARCHAR(50)
DECLARE @TABLE  VARCHAR(50)

SET @SCHEMA = 'DBO'
SET @TABLE  = 'spt_values'

DECLARE  @CURRENTROW INT
DECLARE  @TOTALROWS INT
DECLARE  @COLUMNMAXSIZE INT
DECLARE  @SQLSTRING NVARCHAR(MAX)
DECLARE  @PARAMETER NVARCHAR(500);
DECLARE  @TABLEDETAILS 
  TABLE(UNIQUEROWID     INT   IDENTITY ( 1,1 ),
     TABLE_SCHEMA    VARCHAR(255),
     TABLE_NAME      VARCHAR(255),
     COLUMN_NAME     VARCHAR(255),
     COLUMN_TYPE     VARCHAR(255),
     MAX_LENGTH      INT,
     MAX_DATA_LENGTH INT)
       
INSERT INTO @TABLEDETAILS
          (TABLE_SCHEMA,
           TABLE_NAME,
           COLUMN_NAME,
           COLUMN_TYPE,
           MAX_LENGTH)

SELECT SCHEMA_NAME(O.SCHEMA_ID)  AS TABLE_SCHEMA,
      OBJECT_NAME(O.OBJECT_ID)  AS TABLE_NAME,
      C.NAME                    AS COLUMN_NAME,
      T.NAME                    AS COLUMN_TYPE,
      C.MAX_LENGTH
FROM   SYS.TABLES O
INNER JOIN SYS.COLUMNS C
       ON C.OBJECT_ID = O.OBJECT_ID
INNER JOIN SYS.TYPES T
       ON C.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID
      AND T.NAME IN ('CHAR','VARCHAR','NCHAR','NVARCHAR')
WHERE SCHEMA_NAME(O.SCHEMA_ID)  <> 'sys'
AND OBJECT_NAME(O.OBJECT_ID) = @TABLE
AND SCHEMA_NAME(O.SCHEMA_ID) = @SCHEMA
                                
SELECT @TOTALROWS = COUNT(*) FROM   @TABLEDETAILS
SELECT @CURRENTROW = 1
WHILE @CURRENTROW <= @TOTALROWS   
BEGIN     
SET @COLUMNMAXSIZE = 0      
SELECT @SQLSTRING = 'SELECT @COLUMNSIZEMAX = MAX(LEN([' + COLUMN_NAME + '])) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'     FROM   @TABLEDETAILS     WHERE  UNIQUEROWID = @CURRENTROW                               
SET @PARAMETER = N'@COLUMNSIZEMAX INT OUTPUT';          
EXECUTE SP_EXECUTESQL       @SQLSTRING 
     ,       @PARAMETER 
     ,       @COLUMNSIZEMAX = @COLUMNMAXSIZE OUTPUT      
UPDATE @TABLEDETAILS     
SET    MAX_DATA_LENGTH = @COLUMNMAXSIZE     
WHERE  UNIQUEROWID = @CURRENTROW                               

-- DISPLAY PROGRESS (May exceed max results sets if uncommented)     
-- SELECT * FROM @TABLEDETAILS WHERE  UNIQUEROWID = @CURRENTROW       
SET @CURRENTROW = @CURRENTROW + 1   
END      

SELECT   TABLE_SCHEMA
  ,TABLE_NAME
  ,COLUMN_NAME
  ,COLUMN_TYPE
  ,CASE MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(CHAR(10),MAX_LENGTH) END AS COLUMN_MAX_LENGTH
  ,MAX_DATA_LENGTH 
FROM @TABLEDETAILS 

TSQL : List System Stored Procedures

select * from msdb.sys.objects 
where type_desc = 'SQL_STORED_PROCEDURE' and is_ms_shipped = 1

Management Studio Speedup : Disable Error & Usage Reporting

I usually disable this when I perform an installation, but ocassionally I take over a server where it is still active.

Navigate to - Start > All Programs > Micrsoft SQL Server 200x >  SQL Server Error and Usage Reporting

Untick the settings as follows  >

Management Studio Speedup : Remove Splash Screen

Remove that pesky splash screen!

Simple this, change the shortcut that launches Management Studio to include the /NOSPLASH switch as shown below.

Reporting Services : Installing 32bit version on a 64bit server

SQL 2005's 'System Configuration Check' reported a version clash on my Windows 2008 box when trying to install Reporting Services.
" 64-bit ASP .Net is Registered. Required 32-bit ASP .Net to install Microsoft Reporting Service 2005 (32.bit). "
Sounds a bit backwards to me!
I needed to revert to 32-bit .NET on 64-bit IIS 7.0 (which is basically done by changing which DLL IIS is using)
Here's what to do...

1) from a command prompt >

C:\>cd C:\inetpub\AdminScripts

C:\inetpub\AdminScripts>cscript adsutil.vbs set w3svc/AppPools/Enable32bitAppOnWin64 1 Microsoft (R) Windows Script Host Version 5.7 Copyright (C) Microsoft Corporation. All rights reserved. Enable32bitAppOnWin64 : (BOOLEAN) True


2) Install Reporting Services (and SP2, and hotfixes... yawn...)

3) Configure Reporting Services (subject of another post, i'm sure...)


see also http://technet.microsoft.com/en-us/library/ms143293.aspx