There are some new database roles in the MSDB database in SQL Server 2005 that allow you to grant more granular permission to  non-sa users in the SQL Server agent.

If you work in an environment similar to the one i support, application and database code will pass through several… Read more

I needed to upgrade the underlying IO subsystem of one of my IO intensive SQL Servers. After some discussion with various teams it was decided that the best way to achieve this whilst minimising disruption was to build a fresh server with a new name and a fresh SQL Server… Read more

I have been tinkering lately with database snapshots and I thought the subject warranted a short post on the subject. This post will details a few things of note about snapshots and how to create a snapshot on your database.

Database snapshot provide a read-only point in time view of… Read more

I recently restored a SQL Server 2005 database to a testing environment. The database contained several CLR assemblies, when the tester tried to execute one of the CLR’s SQL returned the following error:

An error occurred in the Microsoft .NET Framework while trying to load assembly id xxxxx. The server… Read more

This is a short note explaining he sys.dm_os_wait_stats DMV.

This DMV returns the following columns/values regarding waits encountered by executed threads:

• Wait_type
• waiting_tasks_count - The number of waits on this wait type.
• wait_time_ms - The total wait time for this wait type in milliseconds (including signal_wait_time_ms)
• max_wait_time_ms
• signal_wait_time_ms The difference…

Read more

What is a SQL Server checkpoint?

A SQL Server checkpoint is the process of writing all dirty datafile pages  out to disk. A dirty page is page that has changed in memory (buffer cache) since they were read from disk or since the last checkpoint. This is done regardless of… Read more

This is a short post for a Sunday afternoon, I thought I’d share some useful reading from the week gone bye. I have started with Brent’s blog and interview with Joe Sack about the Microsoft Certified Master Program. This led me to discovering the MCM reading list, hopefully you have… Read more

Microsoft has recently announced the planned release dates for the next round of SQL Server service packs. SQL Server 2008 SP is planned for release in Q3 2010.

Microsoft does pay attention to the SQL Server community and duly noted the demand for another service pack for SQL Server 2005.… Read more

I answered a forum post the other day over on SSC on the differences between  the trace events stmtcompleted and batchcompleted. In hindsight I’m not sure that I made a very good job of it, so I thought I would write a blog post to re-enforce and confirm my understanding. Read more

I have been having an IO subsystem issue recently and I found the whitepapers very useful and wanted to post the URLs here for future reference:

SQL Server IO best Practices 

SQL Server IO Basics – Chapter 2

SQL Server Pre-deployment IO best practices

Read more

Useful link. This is a great best practice article/white paper from Tom Davidson and Danny Tambs.

The word document (880kb) can be downloaded from here http://msdn.microsoft.com/en-us/library/cc966413.aspx

Read more

This is a short post on SQL Server storage best practice and what i have learned over the years… Much of this information can be found in Microsoft’s document on Storage Best Practice and this can be found here

Test your setup before deployment. MS have useful utility called SQLIO Read more

The default trace in SQL Server is something I have been experimenting with lately. It is a feature included in SQL Server 2005 and beyond and it is enabled by default when you install SQL Server. It has a number of uses from auditing to capacity planning and I will… Read more

I recently watched  Steve Jones’ (Blog | Twitter) "Building a better blog" presentation from the 24 hours of PASS virtual conference from back last year. As I mentioned in a previous post I have known about PASS for sometime, the yearly PASS conference in particular  from advertising on… Read more