Skip to main content


Showing posts from March, 2010

SP_change_users_login and SQL Server 2008 onwards

Today I discovered, from a post on the forums and a follow up dig into BOL that sp_change_users_login is to be depreciated in future versions of SQL Server and the new ALTER USER should be used instead.  Now let me make it clear here, sp_change_users_login is still available in SQL Server 2008, but BOL suggests that  it will be removed in future versions.Curious, I looked up ALTER USER in BOL and I find that it does most of what sp_change_users_login  does in terms of fixing orphaned users…with a small exception  it doesn’t have the equivalent of the sp_change_users_login 'report'

to give us a report of any orphaned users in the database.

This prompted me to dig into the code of the sp_change_users_login to see what the report returns… it runs a select from on sysusers. According to BOL  the sysusers system table is included as a view for backward compatibility and that this will be removed in future versions. I checked the BOL entry that matches old SQL 2000 system tables to SQ…

Scheduling backups in SQL Server Express

I have seen many forum posts asking how do you schedule backups for SQL Express databases and the answers always have similar responses that go something like:“There is no SQL agent in Express…the only way to backup databases on a schedule is to write a script and call it from a batch file.” I saw this recent Microsoft  knowledge base article and thought I would post a link to it here, it talks you through in detail how to schedule backups in SQL express using the above method:;en-us;2019698&sd=rss&spid=2855

TempDB – Relocate the TempDB Database Files

I took control of a new server recently and noticed that Tempdb was not configured to best practice. It was not on its own drive and it consisted of one data file and one log file…The server had a couple of cores, I’m not sure how relevant this is anymore but SQL server 2000 it was recommended you create a TEMPDB data file for CPU…I’m not sure that you need a file for each CPU/core in later versions but as Brad McGehee says in his TEMPDB optimisation chapter in MVP deep dives (This is my review of the book) it does give you a place to start.My first script here moves both the current log and data file to its new home.USE master;
MODIFYFILE (NAME = tempdev, FILENAME = 'S:\SQLTEMP\tempdb.mdf');
MODIFYFILE (NAME = templog, FILENAME = 'S:\SQLTEMP\templog.ldf');

Once you have run this you will need to restart SQL Server for the changes to take affect. Also you will have two copies of the old TEMPDB files in the in the old location,…

SQL Server 2008 Activity Monitor

For those people who like to use the Activity Monitor in the SQL Server Management Studio GUI instead of, or along with,  sp_who, sp_who2 and DMVs in SQL Server, it is not where you left it in SQL Server 2005.To view the activity monitor interface in SQL Server 2008 Management studio you need to:1. Connect to the instance in Object Explorer2. Right-Click the <Server Name>3. Select <Activity Monitor>It should the open as a pane on the right-hand windowBrad McGehee has a good article over on Simple Talk where he explains the new improvements and features that can be found in Activity Monitor in SQL Server 2008

Managing Your Backup Files

I recently posted a couple of  scripts that backup all databases on your SQL Server instance to disk with a date and time stamp, so you know from the file name when it was taken and hopefully helps you identify how useful those backup are to you. The second script is a script that runs through the databases in full recovery mode and takes a transaction log backup of each one.
You can then combine these scripts with the SQL Agent to create and schedule some jobs to ensure your backup schedule meets the restore requirements of your business.A ‘standard’ backup schedule, that I have seen many shops implement unless there is a specific requirement not met by this process:
Fullback – All databases daily
Transaction Log backup – Hourly throughout the day. All copied or mirrored to another server.
All of these backups are to disk are written off to tape on nightly basis and then become part of the tape cycle.
Which brings us onto the next issue, with at least 25 files per database being cre…

Script: Transaction Log Backup

My last post showed a little script that I use for taking full backups of all my databases on an Instance.
This script below will take a transaction log backup of all your database in full recovery mode, again it can be used in conjunction with the SQL agent to schedule it regularly. This is one size fits all to transaction log backups though and this may not fit all environments perfectly. If you have a database that needs a different more specific schedule from transaction log backups on the instance in question then you will need to find a way to work that in.
DECLARE @DBName varchar(255) DECLARE @DS VARCHAR(50) DECLARE @PathVARCHAR(255) DECLARE TLOG_BACKUP CURSORFORselect name from sys.databases where recovery_model = 1 --Onlyreturn databases infull recovery AND name NOTIN ('TEMPDB') -- Exclude TEMPDBANDstate = 0 --Exclude offline databases, they won't backup if they offline anyway AND Source_database_id is null -- Removes snapshots from the databases returned OPEN…

Script: Backup All your Databases

We all know the importance of taking regular backups of our SQL Server databases. This is a useful little script that I use to take a full backup of my databases on an instance. It can be used in combination with the SQL Agent to schedule:
DECLARE @DBName varchar(255) DECLARE @DS VARCHAR(50) DECLARE @PathVARCHAR(255) DECLARE Full_Backup CURSORFORSELECT name from sys.databases WHERE name NOTIN ('TEMPDB') ANDstate = 0 --Exclude offline databases, they won't backup if they offline anyway AND Source_database_id is null -- Removes snapshots from the databases returned, these can't be backed up eith OPEN Full_Backup FETCHNEXTFROM Full_Backup INTO @DBName -- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE@@FETCH_STATUS = 0 BEGIN --Set the filename valuesof the backup files SET @DS = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '') + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '') SET @Path …

Enabling Filestreaming in SQL Server 2008

This is a short post on enabling file streaming. I have read a few sources that cover the same topic but I like to write about these things to reinforce my knowledge. If you are interested in these other useful resources and to be honest this is where I learned about this process, along with actually doing it for myself check out these websites:Books Online (BOL)The SQL Server Storage Engine BlogFilestream Storage in SQL Server 2008 -  A white paper by Paul RandalLets assume that you have just completed your installation of SQL Server 2008 and applied SP1 and you also know that this new instance will be used to house a file stream database.Open SQL Server configuration manager:
<START><Program Files><Microsoft SQL Server 2008><Configuration Tools><SQL Server Configuration manager> When configuration manager has opened right click on the SQL Server 2008 instance Select <Properties> Select the <FILESTREAM> tab Ensure that the following check …

Setting Up a Server Side Trace

Recently I wrote a short post  on the default trace in SQL Server. You can read that here. In this post I am going to discuss the advantages of a server side trace over the SQL Server profiler GUI. I’ll look at an easy method for creating server side traces, especially if you are familiar with the SQL Server profiler GUI. And I’ll demonstrate how to stop and start and  remove a server side trace.Server Side Trace Versus SQL Server Profiler GUIThe main advantage of server side traces over its SQL Profiler counterpart lie in the fact that server side traces use less resources than a SQL profiler trace. You can also programmatically start and stop Server side traces.OK, technically to create a server side trace you need to learn the syntax of the following system stored procedures:sp_trace_create sp_trace_generateevent sp_trace_setevent sp_trace_setfilter sp_trace_setstatus Along with  I’m not going to explain the syntax of these events here, instead I’m going to show how can get the Pro…

Setup Database Snapshot against a Mirrored Database (Part 2)

This post is the second post in my series about Database mirroring. For part one is titled Configuring Database Mirroring in SQL Server. I believe that high availability is the primary reason people mirror their important production databases. Depending on your situation there can be some other benefits of using mirroring that can help performance of your production database. With database mirroring in SQL Server you have two exact copies of your production database on two separate instances , wouldn’t it be nice to have access to that mirrored database to act as a read-only database supporting all your reporting requirements? This post will show how to set that up.
So we have our mirrored database all setup and its sat in a RECOVERING state, so how can we use it as reporting solution? Well, the answer lies in another feature new to SQL 2005… Database Snapshots.
You can’t run queries against your mirrored DB because it is a in a RECOVERING state but you can create a snapshot of your m…

TSQL Tuesday #4: IO

“IO IO, It’s off to disk we go!” was in Mike Walsh’s blog post that introduced this TSQL Tuesday and I thought this would be an ideal opportunity for me to get my feet wet with a TSQL Tuesday contribution of my own, my very first. This kind of got me thinking….IO what a vast and varied subject to write about, so I started looking back through my career about the some of the IO related issues I have come across and I realised that I had a couple to chose from that are IO related.My most recent issue/problem was a combination of IO, Virtualisation, method’s of working. The problem was caused as a result of snapshot taken by a sys admin against a VM that housed a SQL Server that I support and  that snapshot was not removed when it should have been. When my SQL disk backups (Full and Transaction log backups) were being written to disk, then tape, then deleted some days later, the snapshot recording all these disk changes and filled up all its available space, This in turn caused the serve…

Configuring Database Mirroring in SQL Server

I plan on writing a series of posts on database mirroring, this is the first. Database Mirroring is a new feature of SQL Server 2005 and I have been configuring and testing it lately so I thought I would write a short post to document what I have learned.
This post will walk through configuring mirroring, I will be using my laptop and a couple of SQL 2008 instances for this example, so this is nothing more than a demo, but the process should be the same for two separate SQL Server instances on two separate servers.
The key to configuring database mirroring as with most things is in the preparation.
Pre – Preparation StepsIf you are following along and you have a database you want to mirror, then you don’t need this step, I have included this section for completeness, I need a database to mirror. On my primary server I run the following script safe in the knowledge  my  model database is in Full recovery mode.

USE [master] GOCR…

Time Off, Down Time, Round Up

I have taken some time off this week, I have been to the beach with my two golden retriever puppies. My one dog, Seth, hates the car so we were a little concerned that he wouldn’t like it. After a couple of puppy travel sickness tablets though he calmed right down and chilled out for the hour or so car ride. So the four of us made our way to the Gower in South Wales. I think we have the second largest tidal range in the world and the tide was out, so we had miles and miles of wet sand to play fetch and chase the seagulls. I went to University in Swansea, I left there in 2002 and I never really a lot of time in the Gower but this week we have been there a few times and my dogs think its the best place on the planet.I do have some great photo’s but I haven’t got them off the camera yet, when I do I will post the here.Anyway, I have been scheduling the blog posts for a while now and writing them using Windows Live Writer, its a neat tool and I would recommend it to anyone thinking of blo…

SQL Server - MVP Deep Dives

I got my copy of MVP deep dives earlier this month and have been making good progress in reading some of the chapters. Its not a start to finish book and by that I mean you can really read any chapter in any order you like. Me being me, I started somewhere toward the back. These are some of the Chapters that I have read thus far:47 - How to use Dynamic Management Views
Glenn Berry 43 - How to optimize tempdb performance
Brad M. McGehee 39 Running SQL Server on Hyper-V
John Paul Cook 45 - Correlating SQL Profiler with PerfMon
Kevin Kline I intend to read this chapter soon, as I would like to learn more about power shell 26 Power Shell in SQL Server
Richard SiddawayThe authors profit’s from this book go to a charity called War Child which is a very worth while cause. The book has 53 authors and 6 technical editors all with MVP status, and include names like Paul Randal, Adam Mechanic, Kimberly Tripp, Gail Shaw, Paul Nielsen and Itzak Ben-Gan to name just a few, which basi…