SQL Server – Hexadix

Troubleshooting long transaction and failed transaction in SQL Server

By Tan Nguyen | March 19, 2017 | Comments 0 Comment

When a transaction runs for too long, it may block other queries to your database. Here’s how we can find and kill it.

1. View which transactions are running

execute sp_who2

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])), ''),
    [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?

By Tan Nguyen | March 19, 2017 | Comments 0 Comment

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

You cannot shrink the transaction log file unless you do transaction log backup first. You cannot do the transaction log backup because your disk doesn’t have enough free space. The transaction log is getting bigger every minute. The clock is ticking. What will you do?

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

How to clear database cache in SQL Server

By Tan Nguyen | March 19, 2017 | Comments 0 Comment

As you may already know, SQL normally uses 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 be sure if your query is really fast or is it just fast because SQL Server fetches 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.

Fortunately, SQL Server provides us with an undocumented stored procedure to do the job.

To clear the cache, run the command as below:

dbcc dropcleanbuffers

That’s it. Now execute your query again to see how it performs 😀

Hope this helps.

Cheers!

Troubleshooting Transaction Log in SQL Server

By Tan Nguyen | March 19, 2017 | Comments 0 Comment

Dealing with SQL Server’s transaction log can be very troublesome.
In this post, we are going to take a look at some common use cases when dealing with transaction log in SQL Server.

Hopes it helps! 😀

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

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: Back Up a Transaction Log (SQL Server)

5. Shrink transaction log size

Do the following steps:

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:

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 CURSOR – A simple example

By Tan Nguyen | March 19, 2017 | Comments 0 Comment

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;