Category: SQL server

Windows PowerShell run script on all SQL Servers recovery mode

Micrsoft SQL Server databases should be kept in full recovery mode (the equivalent of Oracle’s archivelog mode) except for the master, tempdb, ReportServerTempDB databases. To check all databases in a SQL Server instance, run the below script in a SQL

Posted in SQL server, Uncategorized

Shrink Transaction Logs in all every database SQL Server

SQL Server transaction logs need backed up regularly, or they will grow and grow forever, eventually filling up all available disk space. That is best done in a maintenance plan. Unfortunately you have to remember to set that up post

Posted in SQL server

Remote Desktop Connection Manager

Remote Desktop Connection Manager, a free download from, lets you switch quickly between windows servers, saves the servers and your login details in a list.

Posted in SQL server, Windows

SQL Server recently changed objects

List recently changed procedures and views in a database: SELECT name , create_date , modify_date FROM sys.objects WHERE ( create_date >= DATEADD ( DAY , -7 , GETDATE() ) OR modify_date >= DATEADD ( DAY , -7 , GETDATE() )

Posted in SQL server

Shrink msdb database SQL Server

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,

Posted in SQL server

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

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

Posted in SQL server

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

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:

Posted in Performance tuning, SQL server

Windows Server 2008 R2 Firewall for SQL Server and FTP

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

Posted in Installs, SQL server, Windows

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

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 GO Based on posts by Aaron Bertrand and David Seruyang.

Posted in Character sets, SQL server

Quick SQL Server Training for Oracle DBAs

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

Posted in SQL server