Skip to main content


Showing posts from February, 2010

SQL Agent – Giving non-SA users permissions on jobs

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 environments en-route to production. Starting on the Dev box then moving to system testing and then to user acceptance testing, if you have any SQL Agent jobs that relate to the database being tested but you don’t want these jobs to run on to run on schedule, instead you want your testers to run as and when required. You can use these new roles to give your developers and testers permissions to run the jobs when they need too.There are three roles in MSDB in SQL Server 2005 and onwards called:SQLAgentUserRole – which is the least privileged of the three roles. Users in this role have permission on jobs and schedules that they own. This was the role i needed in my case.SQLAgentReaderRole – Has all the permissio…

Renaming a Standalone Instance of SQL Server

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 installation.We could then  configure that server as an exact replica of the current production box, with a different IO configuration. We could then run tests using SQLIO and other business related loads without interfering with the current production environment and at the same time ensure that the new kit was up to the the task.When it came to switch over we would be in position whereby  we could have both machines running at the same time, with network connectivity so we could copy the necessary database backup files between servers, and start our load processing running again. As this database is only used to house read-only databases, with a weekday daily load process the switch over time (downtime) could be ke…

Setup a Database Snapshot

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 the source database. Database snapshot utilise sparse files and “copy on write” technology to take a snapshot of your database at a point in time. Database snapshots work at the page level, Before a page is changed  in the source database, the page as it exists before any change is made  is copied to the snapshot thus preserving the values on that page in the snapshot.Because of the dependency on the source database, the snapshot must reside on the same SQL Server instance as the source database.In terms of size, the largest a snapshot can get is the exact same size of the source database when the snapshot was taken. Thus if all the data in your database changes frequently you will need enough space to hol…

SQL Server 2005 CLR execution error after restore

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 may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.After a little search on Google, i came across the following Knowledge Base article quick check in SSMS showed that I the database had been restored with an owner other than SA. So I run the following script to change the database owner: USE <DBNAME>
EXEC sp_changedbowner 'sa'

I then get errors relating to trustworthy setting on the database, after checking in SSMS again I saw that the database trustworthy setting was set to false. I used the following Script to set the trustworthy setting:

USE master


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 between the time the waiting thread was signalled and when it started running. Most of these columns are self-explanatory with the exception of the last one, signal_wait_time_ms,  so I’ll explain the last column in a little more detail…A thread enters a wait state when the resource it is waiting for is not available.Once the resource becomes available, the waiting thread is signalled. However, the CPU might be busy at this point serving other threads. The attribute signal_wait_time_ms indicates the time it took from the moment the thread is signalled that the resource is available until the thread gets CPU time …

SQL Server - Checkpoint

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 the transaction that made the change. SQL Server uses a protocol called Write Ahead Logging (WAL) and it is this process that writes all log records describing a change to the data page to disk before the actual page is written to disk.Checkpoints can occur concurrently on any number of databases on an instance.How do database checkpoint occur?Before a backup the database engine automatically performs a checkpoint, this ensures that all database changes are contain in the backup.You issue a manual checkpoint command, a checkpoint is the run against the database in useSQL Server is shutdown. If the checkpoint is skipped (SHUTDOWN WITH NOWAIT) the restart will take much longerALTER DATABASE is used to add or remove a da…

Useful reading this week

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 at least some of these but its a comprehensive list of reading that as SQL Server professional you will find interesting. Everything on the list is freely available on the internet.Brent Ozar’s interview with Joe Sack: reading list:

SQL Server 2005 SP4!

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. SP4 for SQL Server 2005  is due for release in Q4 2010. The blog post can be found here.

SQL Server Trace Events

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.The question went something like this…“Can you explain the difference between BatchCompleted and StmtCompleted events.”So I will start by defining the type of events that these could relate too. T-SQL EventsA category called T-SQL exists in SQL Server profiler and amongst others contains the following two events:SQL:StmtCompleted SQL:BatchCompleted T-SQL events…“Includes event classes produced by the execution of Transact-SQL statements passed to an instance of SQL Server from the client.” The following definitions are taken from the help text you get when you hover over each event in profiler:SQL:StmtCompleted: Occurs when the Transact-SQL statement has completed.SQL: BatchCompleted: Occurs when the Transact-SQL stat…

SQL Server Waits and Queues

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

SQL Server Storage

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 hereTest your setup before deployment. MS have useful utility called SQLIO that can be used for this purpose.Place log file on a RAID 10 disks…This provides fast write performance, which suits the sequential writes of the log while maintaining fault tolerance.Separate log and data files at the physical disk levels. Spreading the write intensive write IO of the log from the Read/Write of the data files across different spindles can reduce can reduce contentionConfiguring TEMPDB correctly – MS state in its SQL Server storage best practices document that you should create one TEMPDB data file per CPU and place the TEMPDB database on a RAID 10 disksDatabase data files should be of equal size…The SQL Server allocation algorithm favours files with more space. Pre-size data and log file, t…

SQL Server Default Trace

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 look at an example here.First though lets check to see that default trace is enabled on my SQL Server instance, the following code TSQL will check for this:--Check to see that Trace is enabled.
FROM sys.configurations
WHERE configuration_id = 1568

If the trace is enabled you will get results like the screen shot below:

Notice the [Value] and [Value_in_use] columns set to 1 – this means the default trace is enabled.

If it's not enabled,[Value] and [Value_in_use] columns are set to 0 

If the trace is disabled the following code will enable it...You need to be a member sysadmin or serveradmin fixed server roles to run reconfigure.

sp_configure 'show advanced options', 1 ;
sp_configure &…

“Building a Better Blog” with Steve Jones

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 sites like SQL Server Central, I have never been to that conference and I have never really looked into it any great detail either until recently. Aside from this conference and the PASS Europe conference, PASS offers a wealth of resources available to the SQL Server professional. Steve’s presentation focused on the development of your blog and how it can help your career. It wasn’t a technical presentation about SQL Server but more how you can use your blog to create a brand and how you can use it to prove and improve your knowledge of SQL Server. It is also a great way to learn and document your knowledge. He gave some good advice in how to promote your blog, what subjects to write about a…