Category Archives: SQL server

Shrink msdb database SQL Server

Posted on 18 May 2013 by Andrew Fraser

Steps to shrink msdb database in SQL Server 2005: T-SQL: truncate table msdb.dbo.log_shipping_monitor_history_detail T-SQL: exec msdb.dbo.sp_purge_jobhistory @oldest_date='2013-02-07T13:55:10'(change date above as required) T-SQL: alter index all on msdb.dbo.sysjobhistory reorganize Management Studio: system databases > msdb > tasks > shrink > databaseOR, … Continue reading →

Posted in SQL server | Leave a comment

Study notes crib sheet for exams 70-457 70-458 70-459 MCSE SQL Server 2012

Posted on 1 May 2013 by Andrew Fraser

Here is the crib sheet/study notes I used for the three exams: 70-457 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 – Part 1 70-458 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server … Continue reading →

Posted in SQL server | Leave a comment

Indexed views – workaround for “Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.”

Posted on 5 December 2011 by Andrew Fraser

Indexed views (the SQL Server equivalent of Oracle’s materialized views) are a good performance fix, especially for reporting and data warehouse systems. However they only work with inner joins. Attempts to create an indexed view with outer joins fail with: … Continue reading →

Posted in Performance tuning, SQL server | Leave a comment

Windows Server 2008 R2 Firewall for SQL Server and FTP

Posted on 23 December 2010 by Andrew Fraser

Windows Server 2008 R2 has a strict firewall by default. It will not let you run FTP (client) from the server and get files from other servers/sites It will also not let you run “SQL Server Management Studio” from your … Continue reading →

Posted in Installs, SQL server, Windows | 2 Comments

Howto remove carriage return line feed from SQL Server for displaying in Excel

Posted on 13 March 2009 by Andrew Fraser

Use this SQL to remove carriage return line feed from SQL server for displaying in excel: select REPLACE(column_name, CHAR(13) + CHAR(10), ‘, ‘) from table_name ; Based on posts by Aaron Bertrand and David Seruyang.

Posted in Character sets, SQL server | 3 Comments

Quick SQL Server Training for Oracle DBAs

Posted on 2 May 2007 by Andrew Fraser

Short SQL Server Training course uploaded in MS-Word format.. Also same in pdf format. It’s a bit dated now – is for SQL Server version 7.0 – but still relevant. I’ll update for version 2005 and convert to html format … Continue reading →

Posted in SQL server | 3 Comments

SQL Server 2005

Posted on 26 January 2007 by Andrew Fraser

SQL Server 2005 doesn’t prompt during install for locations of datafiles and logfiles, meaning they will end up on C:, which you won’t normally want. You can alter the locations during install, but it’s not especially intuitive. Or at least … Continue reading →

Posted in SQL server | 1 Comment