Shrinking Datafile in steps

Shrinking a database is only desirable in migration operations e.g. restoring to an underpowered development server. If you find yourself doing it, the following link is rather useful.

The script repeatedly shrinks a data file in small increments.
This avoids long file operations and makes it easy to stop at any time.

SQLTeam.com : Shrink DB File by Increment to Target Free Space

From the script, this piece of sql is useful to show Data File Usage.

select
 [FileSizeMB] =
  convert(numeric(10,2),round(a.size/128.,2)),
 [UsedSpaceMB] =
  convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
 [UnusedSpaceMB] =
  convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
 [DBFileName] = a.name
from
 sysfiles a

link : dba 101 : shrinkfile

Script : Mark all tables for recompilation

sp_recompile - Recompiles objects.

usage : exec sp_recompile objectname tablename

Use against tables, views, triggers or stored procedures.

If you run against a table or view, it marks all SPs that reference the object to be recompiled.

So, to mark all tables (and stored procedures etc) for recompilation -

exec sp_msforeachtable  @command1="print '?'",  @command2="sp_recompile '?'"

Technet : sp_recompile

Bookmark : Check & Foreign Key constraint Improves Query Performance

Obvious after some background reading on the query optimiser, but today's SSC post does a great job of showing the effect of Foreign Key Constraints on Query Performance.

Script : Sql logins without a default database

How to find sql logins that do not have a default database set?

SELECT * FROM sys.server_principals 
WHERE [type] IN ('U', 'G', 'S','C') ]
AND default_database_name IS NULL

NB : Column TYPE_DESC provides the meaning of TYPE.

If a database has been deleted, a login could still have it's default database set to one that has been removed. To find these ...

SELECT * FROM sys.server_principals 
WHERE [type] IN ('U', 'G', 'S','C') 
AND [default_database_name] NOT IN (SELECT name FROM sys.databases)

Bookmark : How to Find Duplicate Indexes

How to Find Duplicate Indexes

Implicit column Conversions

I came across an excellent post ' Finding Implicit Column Conversions in the Plan Cache ' by Jonathan Kehayias today and had to try out his script to see if we had implicit conversions going on.

The only find was one of my most commonly used routines where I get the DATE portion from a DATETIME column.

I did do this -

SELECT CAST(DATEDIFF(D, 0, GETDATE()) AS DATETIME)

To prevent an implicit conversion occuring (ok, it's not exactly a massive overhead), I've changed the code to -

SELECT DATEADD(dd, DATEDIFF(dd, '1900-01-01', GETDATE()),'1900-01-01')

Further reading :

http://blogs.msdn.com/b/craigfr/archive/2008/06/05/implicit-conversions.aspx

http://blogs.msdn.com/b/craigfr/archive/2010/01/20/more-on-implicit-conversions.aspx

Delete SQL Agent Job History

-- delete sql agent job history for named job

EXEC msdb.dbo.sp_purge_jobhistory @job_name = N'data load' ;


-- delete sql agent job history for named job, with date parameters

EXEC dbo.sp_purge_jobhistory  @job_name = N'data load', @oldest_date = '2008-12-31'
 

-- delete all sql agent job history

EXEC msdb.dbo.sp_purge_jobhistory

SSIS : Error Handling & Checkpoints

SSIS : Error Handling & Checkpoints

Handling Multiple Errors
http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx
http://agilebi.com/cs/blogs/jwelch/archive/2008/01/15/handling-multiple-errors-in-ssis-revisited.aspx

Data Task Error Handler
http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/62662/

Package Level Error Handler
http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx

Checkpoints and Package Restarting
http://community.altiusconsulting.com/blogs/altiustechblog/archive/2009/09/09/ssis-2008-package-restarting-and-checkpoints.aspx

How to serialize error logging in SSIS
http://www.mssqltips.com/tip.asp?tip=1967

Implementing Logging in Packages
http://msdn.microsoft.com/en-us/library/ms138020.aspx

SSIS : Performance

SSIS : Performance

Faster Extraction Loading using SSIS
http://www.sqllion.com/2009/04/faster-extraction-loading-by-ssis/

Microsoft Whitepaper on SSIS 2005 Performance
http://technet.microsoft.com/en-gb/library/cc966529.aspx

Performance
http://jenstirrup.blogspot.com/2009/05/improving-data-flow-performance-in-ssis.html

SSIS Package Performance
Part 1 : Data Flow Task http://www.ssisbi.com/optimizing-ssis-package-performance-part-1/
Part 2 : Data Flow Components http://www.ssisbi.com/optimizing-ssis-package-performance-part-2-the-data-flow/

SSIS : Best Practices

SSIS : Best Practices

Top 10 SSIS Best Practices
http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx

Best Practices
http://blogs.vertigo.com/personal/bellis/Blog/archive/2007/07/23/sql-server-integration-services-ssis-etls-and-best-practices.aspx

Best Practices & Naming Conventions
http://blogs.conchango.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Suggested-Best-Practices-and-naming-conventions.aspx

SSIS : Basics

Am finally getting round to tidying up my favourites and notes I've made during my recent SSIS projects. This is the first of 4 bookmark posts on SSIS reading material.

SSIS : Basics

MSDN
http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx

Technet
http://technet.microsoft.com/en-us/sqlserver/cc510302.aspx

SSIS 2008 Cribsheet
http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-ssis-cribsheet/

SSIS Data Types mapping to SQL Data Types
http://pragmaticworks.com/community/blogs/brianknight/archive/2008/02/16/ssis-connection-manager-data-types-mapping-to-sql-server-data-types.aspx

SSIS Operators
http://msdn.microsoft.com/en-us/library/ms137538.aspx

Integration services 2008 videos
http://msdn.microsoft.com/en-us/library/dd299421.aspx

SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers
http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/64014/#

SQLBits VII

Having gone to Wales for SQLBits 5 and London for SQLBits 6, it was time for SQLBits to head north. York was the destination for this autumninal conference for UK SQL Server professionals with the University being the venue. Once again - the venue, organisation and grub was excellent.

The training day i attended is a post on it's own, but I'll list the Friday and Saturday sessions I attended here. I'm not going to review sessions individually, as

1. i don't have time
2. taste is subjective
3. the internet is full enough of opinions
4. others will do a better job
5. the videos will end up on sqlbits.com and you can review them yourself.

Anyway, how I spent my SQLBits ...

Friday 1st October

Automating SSIS
Andre Kamman

SSIS Dataflow Performance Tuning
Jamie Thomson

TSQL Techniques - How and Why to tune a routine
Dave Ballantyne

Common Analysis Services mistakes and how to avoid them
Chris Webb

The Developer Side of the Microsoft Business Stack
Sascha Lorenz

Saturday 2nd October

Designing and Tuning High Speed Data Loading
Thomas Kejser

SQL Server Storage - 1,000GB level
Brent Ozar

Monitoring and Tuning Parallel Query Execution - Part II
Ramesh Meyyappan

Alice's Adventures in Reporting Services
Jennifer Stirrup

SSIS Field Notes
Darren Green

The content was great and I was once again left with wanting to try everything out. This was followed by the grim realisation that my projects would not allow me to. In the breaks and evenings it was fun to finally meet some of the bloggers and sql tweeps i've been following.

Notable mentions go to Gavin Payne (blog | twitter) , Jonathan Allen (blog | twitter) , John Sansom (blog | twitter) , Mark Blakey (blog | twitter) , Phil Nolan (blog | twitter) , Rachel Clements (blog | twitter) and a very modest bloke (and SSIS legend) Jamie Thomson (blog | twitter).

Update : 04 November 2010 -

I'm linking the best of the SQLBits 7 posts.
These people have reviewed their SQLBits experiences properly...

Ashley Burton - SQLBits 7 Friday Rundown , SQLBits 7 Saturday Rundown

Jonathan Kehayias - SQLBits 7 Wrap Up

Jonathan Allen - SQLBits 7 - The Disappointment (food for thought  - the cost of those that failed to turn up)

Niall Best - SQLBits 7

Richard Back - SQLBits 7 Conference Report