Sql Server

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. »

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 your_table_name"; 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. »

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. »

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. »

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. »

The past few days, I have been playing around with mirroring on SQL Server. Even though there is a GUI to guide you through setup, it isn’t that easy until you understand a few things. I won’t go into details since there are tons of great resources to help setting up mirroring, I will just point you towards some of those resources. The step-by-step guide - This has most of the info you need for mirroring, but it is way too long. »

Backing up a SQL Server database and restoring it to another server is a common operation done for reporting, testing, or development. When restoring on a different machine, a common problem is the login principals don’t actually correlate to the restored database users. This is because they are linked by an SID. This SID isn’t the user name, its some hash value, probably a GUID. The problem is fully described in this article. »

Today I wrote my first MsBuild script that uses sqlcmd.exe to execute SQL Server commands remotely. It is awesome. Although it is fairly limited, for tasks like backing up databases, it is very simple and easy. <target name="Backup"> <exec command="sqlcmd -S $(server) -q "$(sql)""></exec> </target> »