SQLSaturday 26 Sessions: 10/3 in Redmond, WA

Thanks to everyone that attended my sessions at SQLSaturday 26 in Redmond, WA on 10/3!!

This was my first SQLSaturday event. I was granted the opportunity to be a last minute replacement speaker and gave two presentations. This was my first time speaking in front of a large audience at an event. It was a thoroughly enjoyable experience, and I hope to speak at future events as well.

As promised in my presentations, I have uploaded my materials and am sharing them here through my blog.

Session 1:

Using SQL Trace/Profiler Effectively
This session covered how to use SQL Trace/SQL Profiler effectively. Topics included converting trace templates into T-SQL scripts, running server side traces (and why you should do so), reading trace files via T-SQL.

Demos included creating a trace template in SQL Profiler, converting it into a SQL script, and running a server-side trace via the SQL script. We then stop the trace and read the contents using the trace stored procedures and functions.

Also covered was how to set up a SQL job that detects high CPU utilization and starts and stops a custom SQL trace when CPU utilization crosses a predefined threshold.

The files from this sessions can be downloaded here.

Session 2:

Database Mirroring: Maximizing Availability Through Automation
Database Mirroring is often thought of as a “set it and forget it” technology. But what happens if there are problems and the one person on your team that knows mirroring inside and out is not there? You can have a prolonged outage or you can be prepared ahead of time and set up automation procedures for all of your database mirroring needs.

This session covered setting up automation scripts for managing database mirroring failovers in a controlled fashion. A demo was performed comparing mirroring failovers performed via the GUI (graphical user interface) vs. ad hoc T-SQL statements vs. a well thought out, planned, and praticed automation script.

Also discussed was an automation script to automatically fail a mirroring session back to the original principal as soon as it was back online and the databases are synchronized in order to protect your ability to cover both servers with a single SQL Server license.

The files from this sessions can be downloaded here.

Database Snapshots Can Take a Long Time to Create

Believe it, it's true!! One of the big selling point for database snapshots is that creating them is almost instantaneous. Since a database snapshot is created as an empty shell, it should be and almost always is a very quick process. What they don't tell you is long running active transactions can extensively delay the creation of the snapshot file.

What's causing my snapshot creation to freeze for long periods of time?

When you create a database snapshot, the snapshot contains all open transactions at the time. As part of the initialization process, it calls the recovery process on the snapshot. Part of the recovery process is to roll back all active transactions in the snapshot (they do not roll back in the actual database). If there are long running transactions -- such as a large index rebuild -- running at the time of the snapshot creation, the rollback can take a really long time.

If it appears that your snapshot creation is frozen, you can check sys.dm_exec_requests, and you should see rollbacks in progress in the database snapshot.

According to Books Online:

Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created (transactions in the database are not affected).

Question: Why does Database Mirroring Monitor report that my mirror is disconnected?

This question was sent to a discussion list via email. My reply follows.

Sometimes, I have seen a behavior where the Principal was unable to connect to the Mirror. What is the reason for this behavior? Could it be network? I could not find any information that explain this behavior.

Any help here would be appreciated.

My answer:

This does not mean that the principal is disconnected from the mirror.

This simply means that the Database Mirroring Monitor has not connected to the mirror yet. This is not an unusual behavior to see when first looking at a mirrored database in the monitor. The monitor connects to the principal and then to the mirror. Usually, this will clear up within a few seconds on its own as it eventually makes the connection.

If it does not make the connection on its own within a short time, then the problem exists between the monitor and the mirror.

Error when importing RegServer list from SQL 2005 to SQL 2008

I discovered the already known bug in SQL 2008 that you can't import a SQL 2005 server list (exported as a regsrvr file). For someone like me who deals with a large numger of SQL Servers, this is a major issue. Fortunately, Buck Woody has provided a simple solution on his Blog: Importing SQL Server 2005 Registered Servers to SQL Server 2008. This saved me a great deal of time and, more importantly, a great deal of annoyance!!

Thanks Buck!!

My Bug Report

I reloaded my laptop and installed a new installation of SQL 2008 RTM (Dev. Ed.). Previous setup was SQL 2005 SP2. Prior to wiping hard drive, I exported the registered servers to regsrvr files (1 for database engine, 1 for AS, and 1 for IS). I can’t import SQL 2005 regsrvr files into SQL 2008. For me, this is a big hindrance!! Is there a workaround?

When I try to import these files into the SQL 2008 SSMS, I get the following error for all 3 regsrvr files:

Cannot import from C:\Users\v-rodav\Desktop\SQLIS.regsrvr.
The operation 'Import' failed. (Microsoft.SqlServer.Management.RegisteredServers)
Serialization output is invalid. (Microsoft.SqlServer.Management.Sdk.Sfc)
'None' is an invalid XmlNodeType. Line 17, position 10. (System.Xml)

SQL 2008 SSMS Version info:

Microsoft SQL Server Management Studio      10.0.1600.22 ((SQL_PreRelease).080709-1414 )
Microsoft Analysis Services Client Tools      2007.0100.1600.022 ((SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC)      6.0.6001.18000 (longhorn_rtm.080118-1840)
Microsoft MSXML      3.0 4.0 5.0 6.0
Microsoft Internet Explorer      7.0.6001.18000
Microsoft .NET Framework      2.0.50727.3053
Operating System      6.0.6001

Error Logs Part II -- Enumerating the error logs

In my first post on SQL Server Error Logs, I briefly mentioned using xp_enumerrorlogs to list the archived error logs. Here I want to demonstrate how to use the procedure to find and output all error logs since a specific date.

xp_enumerrorlogs

This procedure returns 3 columns: Archive #, Date, and Log File Size (Byte). Archive numbering is 0 based with 0 being the currently active log file. As a log file is archived, the number increases; 0 becomes 1, 1 becomes 2, and so on. The output is ordered alphabetically by the Archive #.

 Finding which archive to start with is easy. You just find the newest file that is older than the date for which you are searching. The one catch here, is that if your date is beyond the range of the archives, your query will not find an archive. As a sanity check, if no archive is found beyond the range of my search date, I simply choose the oldest file available.

From there, it's a simple looping process to read in each file starting with the highest archive number and continuing down to 0, the currently active number.

The Script: ErrorLogsSinceDate

Declare @OldestLog datetime,
    @FirstLog int
Declare @ErrorLog Table (LogID int identity(1, 1) not null primary key,
                        LogDate datetime null,
                        ProcessInfo nvarchar(100) null,
                        LogText nvarchar(max) null)
Declare @EnumLogs Table (ArchiveNum int not null primary key,
                        ArcDate Datetime not null,
                        LogFileSize bigint not null)

Set @OldestLog = '12/31/2007'

Insert Into @EnumLogs
Exec master..xp_enumerrorlogs

Select Top 1 @FirstLog = ArchiveNum
From @EnumLogs
Where ArcDate < @OldestLog
Order By ArcDate Desc

If @FirstLog Is Null
  Begin
        Select Top 1 @FirstLog = ArchiveNum
        From @EnumLogs
        Order By ArchiveNum Desc
  End

While @FirstLog >= 0
  Begin
        Insert Into @ErrorLog (LogDate, ProcessInfo, LogText)
        Exec master..xp_readerrorlog @FirstLog

        Set @FirstLog = @FirstLog - 1
  End

Select *
From @ErrorLog
Order By LogID Desc

No, I'm not going to keep making corny references to war and being a soldier. But in many ways, we dba's are soldiers in a war against poor performance. Often it can be a struggle to get the information and resources you need, but that doesn't lessen the expectation that we can deliver speedy results.

I hope this first article will help a few people resolve issues a little bit quicker by helping them search their error logs.

I intend to follow up this article with a more advaned look at the error logs and how to use it to search for hidden problems before they become public.