Sql Server: ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE

A quick one:

In my lab environment, I burn through a good set of databases frequently. I create a new one, play with it for the task needed; When done, DROP it. Sometimes the ‘DROP DATABASE’ command fails with this reason.

Msg 3702, Level 16, State 4, Line 1
Cannot drop database "DBADB" because it is currently in use.

Since this is a lab environment and no ones else has access to it, I could take some drastic steps to DROP it.

Example:

ALTER DATABASE DBADB

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE
GO

DROP DATABASE [DBADB]
GO

Bingo !! The database goes into SINGLE USER mode and I can quickly DROP it.

Note: Please do not try this on Production; This is just a hack for lab environment databases.

Hope this helps,
_Sqltimes

Read Full Post »

SQL Server: after changing recovery model from SIMPLE to FULL perform a FULL database backup

All night today (actually tonight), we’ve been on a production deployment call. One piece of the puzzle that, we assumed, but did not take into account is the switch back from SIMPLE to FULL recovery model. The required steps that need to be done after we make the switch back to FULL. We need to perform a FULL database backup after making the switch to maintain continuity in log chain.

Okay, this is the bigger picture.

We had a deployment tonight and in preparation for it, we took a note of all the steps and the duration it takes for each and a rollback plan if needed. One of the steps is to switch the RECOVERY model from FULL to SIMPLE during the deployment and switch it back at the end. But surprisingly we did not account for the time needed for the tasks related to switching back.

According to MSDN:

After switching from the simple recovery model

• Immediately after switching to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.

When we go to SIMPLE recovery model, the transactional log backup chain is lost. So, when you switch back to FULL recovery  model, you need to re-establish backup and TLOG backup chain. So we need to run a FULL backup before you can run any TRN LOG backups.

Glad to we have a team on the other side of the planet that can take over the rest of the deployment and send us an email when the backups are complete.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server Wait Types: ASYNC_NETWORK_IO

This is an interesting wait type. When you run Paul Randall or Glenn Berry’s query to capture top wait stats you can get a good picture of where is Sql Server experiencing the most waits. Since these are based on DMV’s they only give this since the last SQL was restarted. But this is a great way to capture what is going on under the hood.

Wait Stats Query:

;WITH Waits
 AS
 (
 SELECT wait_type AS [Wait_Type]
 , wait_time_ms / 1000.0 AS WaitS
 , (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS
 , signal_wait_time_ms / 1000.0 AS SignalS
 , waiting_tasks_count AS WaitCount
 , 100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS Percentage
 , ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC) AS [RowNum]
 FROM sys.dm_os_wait_stats
 WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK'
 , 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH'
 , 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT'
 , 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER'
 , 'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE'
 , 'DBMIRROR_EVENTS_QUEUE', 'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES', 'SLEEP_BPOOL_FLUSH'
 , 'SQLTRACE_LOCK'
 ) -- These are filtered out as these are usually benign
 )
 SELECT W1.wait_type AS WaitType
 , CAST (W1.WaitS AS DECIMAL (14, 2)) AS Wait_S
 , CAST (W1.ResourceS AS DECIMAL (14, 2)) AS Resource_S
 , CAST (W1.SignalS AS DECIMAL (14, 2)) AS Signal_S
 , W1.WaitCount AS WaitCount
 , CAST (W1.Percentage AS DECIMAL (4, 2)) AS Percentage
 , CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S
 , CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S
 , CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
 FROM Waits AS W1
 INNER JOIN Waits AS W2
 ON W2.RowNum <= W1.RowNum
 GROUP BY W1.RowNum
 , W1.wait_type
 , W1.WaitS
 , W1.ResourceS
 , W1.SignalS
 , W1.WaitCount
 , W1.Percentage
 HAVING SUM(W2.Percentage) - W1.Percentage < 95; -- percentage threshold

When I run this query on my database server, I get the following result.

WaitType	Wait_S	Resource_S	Signal_S	WaitCount	Percentage	AvgWait_S	AvgRes_S	AvgSig_S
DBMIRROR_WORKER_QUEUE	28510.88	28510.86	0.03	16	40.85	1781.9303	1781.9286	0.0017
BACKUPTHREAD	12066.34	11949.75	116.59	1236077	17.29	0.0098	0.0097	0.0001
BACKUPIO	9380.96	9367.33	13.63	4502569	13.44	0.0021	0.0021	0.0000
WRITELOG	3716.94	3705.75	11.19	227868	5.33	0.0163	0.0163	0.0000
MSQL_XP	3408.39	3408.39	0.00	83545	4.88	0.0408	0.0408	0.0000
PREEMPTIVE_OS_GETPROCADDRESS	3407.29	3407.29	0.00	83545	4.88	0.0408	0.0408	0.0000
ASYNC_NETWORK_IO	1757.29	1757.26	0.04	1454	2.52	1.2086	1.2086	0.0000
BACKUPBUFFER	1616.14	1612.76	3.38	55118	2.32	0.0293	0.0293	0.0001
PREEMPTIVE_OS_WSASETLASTERROR	1535.80	1535.80	0.00	1352	2.20	1.1359	1.1359	0.0000
PAGEIOLATCH_UP	1493.83	1493.16	0.67	103859	2.14	0.0144	0.0144	0.0000

 

Today, I am interested in talking about “ASYNC_NETWORK_IO“. From MSDN, I see that this is “Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.” This is not very helpful, If you already know what it is, then it makes sense, if not MSDN only confuses you more (as it did me). But Paul Randall recently shed some light on this topic and this is my new understanding.

 

When a client application sends a query to Sql Server, it executes it in the most efficient way and returns the results set back to the client. Once the client receives the data set they send an ACK back to Sql Server confirming the receipt. This is where is problem is.

 

Sometimes, these client machines are not capable (or configured) to receive or process the entire data set in one go. So they only retrieve it piecemeal (aka. Row- by-row processing). Sql Server has completed its task and is just waiting for Client software to process the data set and confirm that it is done. Until the client software completes processing the entire data set, it will not send the ACK back, which means Sql Server has to wait until then. Hence the wait. It’s an asynchronous process;  Network related IO type wait; Hence the name “ASYNC_NETWORK_IO”. But a misleading one.

 

Unless you see a lot of this wait type, I would not jump to immediate action. I would capture the output over a period of time and depending on the trending, I would talk with the development team.

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server: What is a hobt_id

When you run a long running query, sometimes you might get lucky enough to capture hobit_id pop up in the Activity Monitor. This is interesting.

Run a long running query, when it is waiting for the data pages to be pulled into memory you see the wait like PAGELATCHIO or LCK_M_X in the ‘Wait Type’ column and sometimes you can also see hobt_it=232134654 in the ‘Wait Resource’ column. I’ve wondered what this hobt meant.

HOBT_ID (pronounced as ‘hobbit’), refers to Heap or B-Tree ID. As you know, data in tables is stored as HEAP or a B-Tree (Balanced tree); If there is a clustered index on the table, it is stored as a B-Tree, if not it’s a HEAP. Either way, they are stored in data pages that are all put together and referred to as one logical location. This logical location of data pages (or index pages) are referred using an ID called hobt_id. In essence, its just an ID used to refer to that logical storage.

When a table is partitioned, each partition will get a partition_ID and HOBT_ID, but when a table is not partition, it still has partition ID and HOBT_ID as it is referred to as a single partition table.

Thanks Chad

Hope this helps,
_Sqltimes

Read Full Post »