Brian Hartsock's Blog

Using the SQL Server UI to restore a bunch of transaction logs is the most painful thing I have every done. I created this script, and then tested restoring around 10,000 transaction logs and burnt through them pretty fast.

Create the script restore-transactionLog.ps1 with the following code. (I am using integrated auth and running the script on localhost. It wouldn’t be too hard to use different connection strings in the code if you wanted)

param
(
    [string] $dbName
)
 
process {
 
    if(test-path $_){
        $sql = "RESTORE LOG [$dbName] FROM  DISK = N'$($_.fullName)' 
                WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10"
 
        write-verbose $sql
 
        sqlcmd  -Q "$sql" -E
 
    }else{
        write-error "Can't find file $_"
    }
}

Then you can use it pretty easily in the following scenario.

gci c:\MyTransactionLogDir | 
    sort LastWriteTime | 
    .\restore-transactionLog.ps1 "myDb"

I have been trying to narrow down a SQL Server connection timeout problem, that only occurs when a database is mirrored. I have yet to be able to really figure out the cause, but here is the code that describes the problem.

[TestFixture]
public class SqlMirroringTests
{
    const string connectionString = "Data Source=some_server;Initial Catalog=some_db; User ID=*****;Password=*****;";
    private static Random rand = new Random();
 
    private ArrayList times = ArrayList.Synchronized(new ArrayList());
    private ArrayList erroredTimes = ArrayList.Synchronized(new ArrayList());
 
    private void DbScenario(int identifier)
    {
        var sleepTime = Random();
 
        WriteInfo(identifier, "Sleeping " + sleepTime + "ms");
        Thread.Sleep(sleepTime);
 
        using (var conn = new SqlConnection(connectionString))
        {
            var stopWatch = new Stopwatch();
            stopWatch.Start();
 
            bool isErrored = false;
            try
            {
                conn.Open();
            }
            catch(Exception e)
            {
                Console.WriteLine(e);
                isErrored = true;
                throw e;
            }
            finally
            {
                var timespan = stopWatch.Elapsed;
                WriteInfo(identifier, "Open time: " + timespan);
 
                times.Add(timespan);
 
                if (isErrored)
                {
                    erroredTimes.Add(timespan);
                }
            }
 
            //We have to actually query for something to get the error, and it needs to be a decent chunk of data
            var command = conn.CreateCommand();
            command.CommandText = "select * from <Insert any table here with some data>";
            command.ExecuteReader();
 
            conn.Close();
        }
        WriteInfo(identifier, "DONE");
    }
 
    [Test]
    public void SlamDatabase()
    {
        var exceptions = new List<Exception>();
        var action = new Action<int>(DbScenario);
 
        var results = Enumerable.Range(0, 250)
            .Select(i => action.BeginInvoke(i, null, null))
            .ToList();
 
        results.ForEach(ar => {
            try
            {
                action.EndInvoke(ar);
            }
            catch (Exception e)
            {
                exceptions.Add(e);
            }
        });
 
        Console.WriteLine("Number of Exceptions: " + exceptions.Count);
        WriteTimeResults("All times - ", times);
        WriteTimeResults("Error times - ", erroredTimes);
    }
 
    private void WriteTimeResults(string prefix, ArrayList results)
    {
        Console.WriteLine(prefix + "Max Open time: " + results.Cast<TimeSpan>().Max());
        Console.WriteLine(prefix + "Avg Open time: " + results.Cast<TimeSpan>().Average(t => t.TotalSeconds));
    }
 
    private int Random()
    {
        return rand.Next(1000);
    }
 
    private void WriteInfo(object state, string message)
    {
        Console.WriteLine("Thread " + state.ToString() + " - " + message);
    }
}

It throws two possible errors:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()

And …

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()

The odd thing is. Here are the results:

Number of Exceptions: 118
All times - Max Open time: 00:00:02.7301659
All times - Avg Open time: 0.0934956888
Error times - Max Open time: 00:00:02.7301659
Error times - Avg Open time: 0.0855163728813559

118 errors out of 250! After one happens, a ton of others happen. I am guessing the pool get’s jacked up and starts looking at the mirror to login temporarily or something, but I can’t figure out why. Increasing the Connect Timeout resolves the problem, but I HATE increasing timeouts. The funny thing is, according to my results, the average timed out connection is .08s. That 625x less than the connect timeout.

Finally, to make matters even more confusing, if you look at my connection string, I didn’t setup the connection for failover. Although I believe that failover still works, except on the initial connection.

Any ideas would be greatly appreciated.

Ever get this error message?

Exclusive access could not be obtained because the database is in use.
RESTORE DATABASE is terminating abnormally.

You can’t do a restore will connections to that database still exist!

If you have jobs that do restores of databases for one reason or another, I bet you have seen this message. I have a job to do a nightly restore of a backup, but some users just never disconnect even though I continually shake my finger. So what do I do? I kill all the processes from problem users before starting the restore.

 
--Create the cursor
declare mycursor cursor
FOR
 
-- Query for processes by login and database
SELECT spid, Loginame
FROM master..sysProcesses
WHERE Loginame='someuser' AND dbid=db_id('somedb')
 
 
open mycursor
 
declare @spid int, @loginame varchar(255), @cmd varchar(255)
 
-- Loop through the cursor, killing each process
Fetch NEXT FROM MYCursor INTO @spid, @loginame
While (@@FETCH_STATUS <> -1)
begin
	-- I don't really know why this is necasary, but it is.
	SELECT @cmd = 'kill ' + cast(@spid AS varchar(5))
	exec(@cmd)
 
	Fetch NEXT FROM MYCursor INTO @spid, @loginame
end
 
close mycursor
deallocate mycursor
go

I am no TSQL expert, so kudos to this article for helping me figure out why kill @spid wasn’t working.

Optimizing SQL server can be a daunting task. There is a fairly large learning curve, and knowing where to start can be a problem. Over the weekend, I read over a great article on query optimization for SQL Server and found the greatest query ever.

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1), 
qs.execution_count, 
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

This query will list the top 20 most read intensive queries. What is great is it is based on total reads, so it is a quick and dirty way to find the worst queries in your system. Eliminating the top few queries on this list will give you the best bang for your buck.

I discovered one of the scariest performance problems with SQL Server I have ever seen today. This problem is so easy to over look, yet extremely detrimental to database performance.

Consider the following table.

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Users
     (
     id int NOT NULL,
     username varchar(50) NOT NULL
     )
GO
ALTER TABLE dbo.Users ADD CONSTRAINT
     PK_Users_id PRIMARY KEY CLUSTERED
     (
     id
     )
 
GO
CREATE UNIQUE NONCLUSTERED INDEX IX_Users_username ON dbo.Users
     (
     username
     )
GO
COMMIT

Now look at the query plans of the following queries, that differ by a single character.

If you look closely, the query that uses an nvarchar parameter does an index scan while the one that users varchar does an index seek. This is very important, because an index seek is orders of magnitude faster than a scan.

The reason this occurs is because the parameter and column have different collation sets. Thankfully, I ran across this article which helped me solve the problem as it related to NHibernate.