Friday, 29 October 2010

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. : Shrink DB File by Increment to Target Free Space

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

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

link : dba 101 : shrinkfile

Friday, 22 October 2010

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

Thursday, 21 October 2010

Wednesday, 20 October 2010

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)

Thursday, 14 October 2010

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 -


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 :

Wednesday, 13 October 2010

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

Sunday, 3 October 2010


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 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