Network Traffic Capture Without Installing Any Software

From time to time, there is a need to capture network traffic for troubleshooting on server. Some network tool like Wireshark is pretty popular for network capturing. However, these tool often need additional installation on the server, and depending on your security team rules, it may take days to be installed on the server.

There is an alternate way to capture network traffic on Windows OS without additional software installation. Using Netsh trace. Most of you may familiar with Netsh for different type of common purpose like firewall, http listener, network interface info, etc. Netsh could be also used to collect network trace.

SQL Replication - Subscriber On Different Domain

Setting up SQL Server replication with publisher / distributor on one domain and subscriber on another domain could be tricky. It could be even more interesting when these servers have the same host name. This blog post discuss issues encountered and the steps of addressing them.

SQL Server Database File - Date Modified

Recently, there was a discussion if the date modified of the database files shown in Windows explorer could be used to determine when the database was last used (or recently used). Often, this date is used to determine when a file (eg. word, excel) is last updated. Could that also be applied to SQL Serve database?

Defunct File Cause SQL Server Upgrade Failure

In the previous post, we discussed how a data file or its filegroup becomes defunct. As making data file defunct may be desirable in certain situation to resolve immediate issue and allow the database continue to be operational, you may encounter some surprise problem when you are ready to move up to the next version during the SQL Server upgrade.

Use PowerShell To Test Port

There are times when we need to identify or troubleshoot if firewall exception is configured correctly and the desired remote server port is open. Often time, I have seen IT Professional use Telnet or PuTTY to test. However, there is another way to do it with PowerShell.

SQL DMF sys.dm_os_volume_stats not returning result

If you familiar with this dynamic management function (DMF) and have been using it, but suddenly this DMF returns no result on some or all of the referring database files, this post discusses discusses the root cause and solution that may help you in your troubleshooting.

DBCC CHECKDB database snapshot failure

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

If you encounter this error, this blog post may help you in your troubleshooting.

SQL Server tempdb database size - file creation and file growth

SQL Server tempdb is a system database that used as global resource for temporary user objects, internal objects, row versioning and other operations. The database grows as these resources demand increases, and below the maximum file size limit if previously configured.

Finding Stored Procedures or Functions Contains Certain Table or SQL Text

Sometimes, there are situations where we need to find out a particular or list of stored procedures or functions that contain certain objects or SQL text. There are several ways to accomplish this purpose.

SQL Server Agent Job and Powershell Version

Since SQL Server 2008, we are able to create SQL Agent job step to run Windows PowerShell scripts. That is great! However, as the latest version 3.0 release of Windows PowerShell, be aware that SQL Server Agent may not be running the same PowerShell version. Before you deploy any PowerShell script to SQL Agent job, you need to make sure you test the PowerShell scripts in the right version.

Flushing Dirty Page To Disk

SQL Server utilizes a technique that write modified data page to the disk asynchronously. There are multiple ways to initiate this process; lazy writing, eager writing and checkpoint.

Missing SQL Performance Counters

If you have been using performance monitor (perfmon) counter or trying to setup up one to monitor your SQL Server performance, and come to find out that there are no SQL Server Perfmon counters on the list, hopefully this blog post can help in your troubleshooting.

SQL Server 2012 Simple Recovery Database Log File Explosion

If you have been enthusiastically upgrading your databases to SQL Server 2012 and have setup Simple Recovery Model on some of the databases, you may want to check your log file (LDF) size.

Identify SQL Agent Jobs Use of Certain Function or Stored Procedure

In an environment that utilize large number of SQL agent jobs, sometimes we want to identify which agent job is using a certain set of functions or stored procedures. One way of achieving this is by using SQL Server agent tables to quickly identify them.

SQL Server Trace Not Inherit Permission - Solution

If you stumble upon this post because you are trying to find out what went wrong on your SQL Server trace permission created using sp_trace_create and it is not inheriting NTFS permission from the parent folder, this post may provide you a solution. This is actually a known issue for Microsoft. You can find the ticket submitted here.

DBCC SHRINKFILE TempDB

If you try to run DBCC SHRINKFILE command after a hung DBCC SHRINKFILE session (e.g. you cancel it in the middle of the shrink process) or you are shrinking the database in the middle of backup, you may receive this error message.

File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty

SQL Print Message During Execution

PRINT statement is often used to return user defined message. It gives user some indication and help tracking multiple query statement for troubleshooting. However, PRINT statement may not be sufficient if we require immediate return message during an execution of a long running query. There is an alternative way.