SQL Server - Troubleshooting long transaction, failed transaction

1. View which transactions are running

 execute sp_who2  

or

 DBCC OPENTRAN  

2. View information of running transactions

 SELECT  
   r.[session_id],  
   c.[client_net_address],  
   s.[host_name],  
   c.[connect_time],  
   [request_start_time] = s.[last_request_start_time],  
   [current_time] = CURRENT_TIMESTAMP,  
   r.[percent_complete],  
   [estimated_finish_time] = DATEADD  
     (  
       MILLISECOND,  
       r.[estimated_completion_time],  
       CURRENT_TIMESTAMP  
     ),  
   current_command = SUBSTRING  
     (  
       t.[text],  
       r.[statement_start_offset]/2,  
       COALESCE(NULLIF(r.[statement_end_offset], -1)/2, 2147483647)  
     ),  
   module = COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(t.[objectid], t.[dbid]))  
     + '.' + QUOTENAME(OBJECT_NAME(t.[objectid], t.[dbid])), '<ad hoc>'),  
   [status] = UPPER(s.[status])  
  FROM  
    sys.dm_exec_connections AS c  
  INNER JOIN  
    sys.dm_exec_sessions AS s  
    ON c.session_id = s.session_id  
  LEFT OUTER JOIN  
    sys.dm_exec_requests AS r  
    ON r.[session_id] = s.[session_id]  
  OUTER APPLY  
    sys.dm_exec_sql_text(r.[sql_handle]) AS t  
  WHERE  
    c.session_id = 59;  

where 59 is the transaction's id that can be acquired using the commands in the previous part - view which transactions are running.

3. Kill a running transaction

 KILL 59;  

where 59 is the transaction's id that can be acquired using the commands in the previous part.

SQL Server - Log file too big and disk is full, now what?

Have you ever faced the problem when your transaction log grows too big and your disk is full?

Normally, SQL Server would make you backup the transaction log first, only then would allow you to shrink transaction log file. If you want to look more into this process, take a look at this blog.

However in this case, there's not enough space to write the log backup file. So that wouldn't work.

After spending a lot of time googling, I ended up this solution

 BACKUP LOG DBNAME TO DISK ='NUL'  

where DBNAME should be changed to the database name that we are dealing with.

What this piece of code does is that it backup the transaction log to 'NUL' file, which means the backup process will be executed but there's no writing to disk. After this process completes, SQL Server will think that all the transaction log has been backed up to disk and allow us to shrink the transaction log file.

This solution is perfect, except for one thing: during this process, all transaction log will be thrown away, which means if we can't make a full backup of the data after this process, we might face a data loss if the server crash.

Therefore, use this solution at your own risk. And remember to do a full backup of the database right after this process, just to be sure. After the full backup of the database, you're safe.

Hope this helps!

Cheers.

SQL Server tricks - undocumented stored procedure

In this article, I'm gonna list some cool tricks with SQL Server using some undocumented stored procedure.

Clear database cache

As you may already know, SQL normally use cache to speed up queries that are often executed. This is no doubt a very cool feature of SQL Server.

However, sometimes when you are tuning your query, SQL Server caches your query or part of it so you can't decide if your query is really fast or is it just fast because SQL Server fetch the result from cache. In this case, you may want to clear SQL Server cache so that the query result is fetched directly from database.

Here comes the command to clear cache

 dbcc dropcleanbuffers  

That's it. Now execute your query again to see how it performs :D

Hope this helps.

Cheers!

SQL Server - Troubleshooting transaction log

In this article, I will write about some common use case when dealing with transaction log in SQL Server.

Hopes it helps! :D

1. View database size and log size

 SELECT  
   DB_NAME(db.database_id) DatabaseName,  
   (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,  
   (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,  
   (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,  
   (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB  
 FROM sys.databases db  
   LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id  
   LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id  
   LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id  
   LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id  

or

 with fs  
 as  
 (  
   select database_id, type, size * 8.0 / 1024 size  
   from sys.master_files  
 )  
 select  
   name,  
   (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,  
   (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB  
 from sys.databases db  

2. View % of log file that really contains data

 DBCC SQLPERF(LOGSPACE);  
 GO  

3. View the reason that log file cannot be reused

 select name, log_reuse_wait_desc from sys.databases  

4. Backup transaction log

Take a look at this article from Microsoft:
https://msdn.microsoft.com/en-us/library/ms179478.aspx

5. Shrink transaction log size

In SQL Server Management Studio, right click database name then click on Task -> Shrink -> Files.

Shrink file dialog will appear.

In this dialog, make sure you select the following options:

- File type: Log

- Shrink action: Release unused space

Then click OK.

After the shrink action completes, recheck the database files' size by executing scripts in step 1 - View database size and log size.

You can get more infomation in the following links:

• http://geekswithblogs.net/nagendraprasad/archive/2009/08/21/reduce-sizeshrink-transaction-log-of-the-db---sql-server.aspx
• http://sqlmag.com/blog/sizing-your-transaction-log
• http://www.sqlskills.com/blogs/paul/importance-of-proper-transaction-log-size-management/
• http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
• http://www.mssqltips.com/sqlservertip/1318/automating-transaction-log-backups-for-all-sql-server-databases/
• http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

Bonus: view size of each table in database

 SELECT  
   t.NAME AS TableName,  
   p.rows AS RowCounts,  
   SUM(a.total_pages) * 8 AS TotalSpaceKB,  
   SUM(a.used_pages) * 8 AS UsedSpaceKB,  
   (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB  
 FROM  
   sys.tables t  
 INNER JOIN  
   sys.indexes i ON t.OBJECT_ID = i.object_id  
 INNER JOIN  
   sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id  
 INNER JOIN  
   sys.allocation_units a ON p.partition_id = a.container_id  
 WHERE  
   t.NAME NOT LIKE 'dt%'  
   AND t.is_ms_shipped = 0  
   AND i.OBJECT_ID > 255  
 GROUP BY  
   t.Name, p.Rows  
 ORDER BY  
   t.Name  

Thanks for reading.
Happy coding!

SQL Server - Simple example of CURSOR

SQL Cursor is very handy to execute a loop in SQL, especially inside of a stored procedure.

Let's look at a simple example to understand how it works.

 USE AdventureWorks  
 GO  
 DECLARE @ProductID INT  
 DECLARE @getProductID CURSOR  
 SET @getProductID = CURSOR FOR  
 SELECT ProductID  
 FROM Production.Product  
 OPEN @getProductID  
 FETCH NEXT  
 FROM @getProductID INTO @ProductID  
 WHILE @@FETCH_STATUS = 0  
 BEGIN  
 PRINT @ProductID  
 FETCH NEXT  
 FROM @getProductID INTO @ProductID  
 END  
 CLOSE @getProductID  
 DEALLOCATE @getProductID  
 GO  

Javascript Promises - Part III - Chaining Promise's

In the previous parts, we have learned about Promise and Promise error handling.

In this part, we are going to learning about chaining Promise's.

Let's take a look at the following code:

var dogs = ['dog1', 'dog2', 'dog3', 'dog4', 'dog5', 'dog6']; function printName (name) { return new Promise(function(resolve, reject) { console.log('printName: ' + name); resolve(name); }); } function displayNames(names) { var currentName = names.shift(); // take on name out to process if (currentName) { // if not end of array printName(currentName).then(function(name) { console.log('printName succeeded: ' + name); displayNames(names); }).catch(function(name) { console.log('printName failed: ' + name); displayNames(names); }); } } displayNames(dogs);

In the example above, we are trying to print the list of dogs' names in a consequence order, which means name after name, not at the same time.
As you already know, in javascript, functions return immediately after it is called, and later on call the callback function when the job actually finished. This leads to a situation where we can't do a list of jobs one after another.
Promise give us the power to do just what we want by chaining promise's together.
When promise are chained together, the javascript engine will only call the second promise's job when the first promise has been either resolved or rejected. That way the promise's jobs will be executed one after another.
In the example above, we are using recursion to make the code work in consequence. However, using promise, we can make the code simpler and more expandable.
Let's look at the easy version first:

var dogs = ['dog1', 'dog2', 'dog3', 'dog4', 'dog5', 'dog6']; function printName (name) { return new Promise(function(resolve, reject) { console.log('printName: ' + name); resolve(name); }); } printName('dog1') .then(function(name){ console.log('printName succeeded: ' + name); printName('dog2').then(function(name){ console.log('printName succeeded: ' + name); }); });

Instead of writing code like this, we can leverage the power of chaining Promise and rewrite the code as below:

var dogs = ['dog1', 'dog2', 'dog3', 'dog4', 'dog5', 'dog6']; function printName (name) { return new Promise(function(resolve, reject) { console.log('printName: ' + name); resolve(name); }); } printName('dog1') .then(function(name){ console.log('printName succeeded: ' + name); return printName('dog2'); }) .then(function(name){ console.log('printName succeeded: ' + name); });

By chaining the promise's like above, we are able to print dog2 after dog1. The key point is, instead of passing the resolve and reject function to printName('dog2').then(), we can just return the printName('dog2') as a Promise, and the next .then() just do its job in exactly the same manner as if it was passed to printName('dog2').then(). This works because Promise provide us with the chaining syntax to make the code more comprehensive.
Imagine that instead of creating a chain like this p1 -> (p2 -> (p3 -> p4)), we can now create a chain like p1 -> p2 -> p3 -> p4, which functions exactly the same.

But what if we have a lot of dogs? What if the dog list is dynamically generated? We just can't know what dogs are there to print. So we must work out a way to dynamically make promise's chain.
Let's look at the upgraded version:

var dogs = ['dog1', 'dog2', 'dog3', 'dog4', 'dog5', 'dog6']; function printName (name) { return new Promise(function(resolve, reject) { console.log('printName: ' + name); resolve(name); }); } var sequence = Promise.resolve(); dogs.forEach(function(name) { sequence = sequence.then(function() { return printName(name); }).then(function(name) { console.log('printName succeeded: ' + name); }).catch(function() { console.log('printName failed: ' + name); }) })

In the code above, we use a very interesting trick by writing var sequence = Promise.resolve(); which basically creates an empty promise that has been resolved to use as the beginning Promise of the chain. After this, we can add the next promise to the chain easily just by using sequence.then()

Another thing that worth mentioning here is that if we use for instead of forEach, be careful as the index variable will not be the same by the time the promise's resolve is called as intended when creating Promise chain.
The code should look like this:

var dogs = ['dog1', 'dog2', 'dog3', 'dog4', 'dog5', 'dog6']; function printName (name) { return new Promise(function(resolve, reject) { console.log('printName: ' + name); resolve(name); }); } var sequence = Promise.resolve(); for (var i = 0; i < dogs.length; i++) { (function() { // define closure to capture i at each step of loop var capturedIndex = i; sequence = sequence.then(function() { return printName(dogs[capturedIndex]); }).then(function(name) { console.log('printName succeeded: ' + name); }).catch(function() { console.log('printName failed: ' + name); }) }()) // invoke closure function immediately }

Creating an array of promises

Instead of chaining promises together, we can also create an array of promises and wait for all the promises to finish by using Promise.all() like this:

function printName (name) { return new Promise(function(resolve, reject) { console.log('printName: ' + name); resolve(name); }); } var promiseArray = [ printName('dog1'), printName('dog2')]; Promise.all(promiseArray).then(function(names){ console.log(names); }).catch(function(names){ console.log(names); })

 Promise.all will wait for all the promises to finish and then call the final resolve function with the list of PromiseValue of resolved promises in array, as well as call final reject function with the list of PromiseValue of rejected promises in array. In other words, if promise array has 5 promises, 3 of which succeeded and 2 of which failed, then the final resolve function will be called with a list of 3 names and the final rejecte function will be called with a list of 2 names.

Congratulations, you've made it!
I hope you understand what promise is and how it works now.

If my explanation doesn't seem to work for you, you might want to try reading this very good tutorial here.

If you have any thoughts or questions, please share your comment below.

Cheers!

Javascript Promises - Part II - Handling errors

In the previous article, we've learned what Promise is and did some basic use cases of Promise. In this article, we will learn how to handle errors while using Promise.

If you haven't read part I, I would recommend that you take a look at it here.

If you've read part I, you may have wondered when will the reject function be called and how to specify a real reject function to the Promise.

Actually, in the previous part, to make life simple, we've ignored the reject function. The full version of .then() should come with 2 parameter: resolve and reject function.

Reject function

To be clearer on this, let's open our cool console tab in chrome and type the following code

var p1 = new Promise(function(resolve, reject) { console.log('1'); throw 'Uh-oh!'; }); p1;

The console outputs would be like below:

Promise {[[PromiseStatus]]: "rejected", [[PromiseValue]]: "Uh-oh!"}

Ok, something have changed. p1's status is now rejected and its PromiseValue is now "Uh-oh!".
What happened was that during its execution, p1 met an error (exception) and it cannot be resolved, but was rejected. Thus its status is rejected and the exception is saved in the PromiseValue, so that it can be passed to the reject function if specified.

Now let's try to specify the resolve function as we did in the previous part:

var p2 = p1.then(function(val) { console.log('2: ' + val); return val; }); p2;

The console outputs would be:

Promise {[[PromiseStatus]]: "rejected", [[PromiseValue]]: "Uh-oh!"}

p2 status is also rejected and the PromiseValue is forwarded to p2. More over, the resolve function was not called because there was no console log output.

Let's change p2 a little bit by adding a reject function to .then()

var p2 = p1.then( function(val) { console.log('resolve 1: ' + val); return val; }, function(err) { console.log('reject 1: ' + err); return err; } ); p2;

The console outputs would now be:

Promise {[[PromiseStatus]]: "resolved", [[PromiseValue]]: "Uh-oh!"}

p2 is now resolved, the console logs "reject 1: Uh-oh!" and the PromiseValue is "Uh-oh!", which is because we called return err; in the reject function.
In the code above, we pass to .then() 2 params, the first one is the resolve function to call when p1 succeeds, the second one is the reject function to call when p1 fails. Since p1 is rejected, the reject function is called. There's no error during execution of p2 so p2 status is resolved.
Now if we run the following code:

var p3 = p2.then( function(val) { console.log('resolve 2: ' + val); return val; }, function(err) { console.log('reject 2: ' + err); return err; } ); p3;

The console outputs would be as below:

Promise {[[PromiseStatus]]: "resolved", [[PromiseValue]]: "Uh-oh!"}

We can see that the resolve function was called and p3 is resolved .

then() and catch()

To make life even simpler and code even clearer, Promise provides us with another helpful function: catch()
Instead of writing this:

var p2 = p1.then( function(val) { console.log('resolve 1: ' + val); return val; }, function(err) { console.log('reject 1: ' + err); return err; } ); p2;

We can now write this:

var p2 = p1 .then(function(val) { console.log('resolve 1: ' + val); return val; }) .catch(function(err) { console.log('reject 1: ' + err); return err; }) p2;

Which will basically execute and output the same thing:

Promise {[[PromiseStatus]]: "resolved", [[PromiseValue]]: "Uh-oh!"}

Technically, the call to .catch(rejectFunction) is equal to calling .then(undefined, rejectFunction) and in the the above code, there was actually another promise in between, right after the first .then(). So the above code is actually:

var p1_1 = p1 .then(function(val) { console.log('resolve 1: ' + val); return val; }); p1_1; var p2 = p1_1.catch(function(err) { console.log('reject 1: ' + err); return err; }) p2;

One good thing about Promise is that if an error is not handled by a promise, it make the promise rejected and keep forwarding the error to the next promise. Therefore in the above code, p1 does not have a reject function so p1_1's status is rejected and the error get passed to the next promise. Since p1_1 has a reject function (specified by .catch()), p2 is resolved.

If we don't use .catch(), and specified the reject function inside of a .then(), then we must specified the next layer of resolve and reject function inside of the first reject function, and if this keeps going on, we would end up in the same callback hell as when we don't use Promise at all.

By using .catch(), the error handling would also be much simpler when we chain promise after promise like this:

var p2 = p1 .then(resolveFunc1) .then(resolveFunc2) .then(resolveFunc3) .then(resolveFunc4) .catch(rejectFunc);

When we write like this, if error happens during any node of the promise chain, the rejectFunc will be called.
We can also catch the error for a particular promise separately like this:

var p2 = p1 .then(resolveFunc1) .then(resolveFunc2) .catch(rejectFunc2) .then(resolveFunc3) .then(resolveFunc4) .catch(rejectFunc);

In this part, you've learned how to handle errors using reject function and .catch(). We will discuss more on Promise chain in the next part of this blog series. That's it for part II.