:::: MENU ::::

Posts Categorized / System Administration

  • Aug 09 / 2017
  • 0
dbDigger, Monitoring and Analysis, System Administration, Windows Server

Get disk report by using PsInfo utility

In a previous post we have discussed PsInfo utility is part of PsTools set.Today we will utilize Psinfo to get disk information on any machine. Any of PsTools utility can be used by simply unzipping on disk. I have unzipped all PsTools files in D drive and PsInfo is also among those.

Get disk report by using PsInfo utilityNo we can utilize the PsInfo to get disk information for local or any other connected machine.

  • Open cmd and go to unzipped PsTools folder
  • Execute psinfo \\MachineNameHere -D disk

It would generate general information about drives like label, type, format, Size and free size.

Run PsInfo in cmd


  • Aug 07 / 2017
  • 0
Monitoring and Analysis, System Administration, Windows Server

PsTools suite for Windows administration

As a DBA we are often required to perform tasks that fall to sys admin category. It may be related to server processes, configuration, disk information etc. Recently i have used a free tool pack PsTools that provides facility to perform many such admin tasks on local or remote machine. PsTools suite includes several individual applications as mentioned in below table.

PsTools suite for Windows admin tasks

You can download the tools pack from here. Just unzip it on your disk and call any of these utilities through cmd. No installation package is required.

  • Sep 23 / 2013
  • 0
dbDigger, Linked Servers and remote data, System Administration, Windows Server

Copy paste not working during remote desktop sessions

I use Remote Desktop utility to get connect to the online servers if required. While using the remote desktop it enables us to share the clipboard among both the OS to copy the data. A couple of days back i noticed that i am no more able to copy data from or to my laptop from the server connected through remote desktop. It was problematic and first thing that came to mind was that i should verify the local resources settings in my remote desktop connection panel. It had following settings and nothing was wrong here

Data copy not working on remote desktop session

So now i had to google for any further clue and I found the reason in a minute by googling. I performed following steps to solve the issue.

  • Login to the remote server on Remote Desktop
  • Open task manager (by right clicking on taskbar)
  • Go to Processes tab
  • Search the process RDPClip.exe
  • End the process RDPClip.exe
  • Go to Applications tab in task manager
  • Click on New Task and start RDPClick.exe by entering in box

The issue is solved here. We may also implement the same  solution through following cmd commands for the same tasks
Taskkill.exe /im rdpclip.exe

  • Aug 26 / 2013
  • 2
dbDigger, Security and Permissions, Server Level Configurations, System Administration, Xp_CmdShell

Enable and work with XP_CmdShell in SQL Server 2008 R2

Xp_CmdShell enables us to run cmd commands within T-SQL environment. The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. It requires SysAdmin rights to use Xp_CmdShell. When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using proxy account. As a security measure by default Xp_CmdShell is disabled and we have to enable it explicitly before use. If disabled then following error message will be used when tried to use

SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

Enable Xp_CmdShell

We may enable Xp_CmdShell through SSMS GUI or T-SQL. So let us explore both the ways to enable Xp_CmdShell.
To enable Xp_CmdShell through SSMS GUI perform following steps.

  • Right click on server instance
  • Click on Facets
  • Choose Surface Area Configuration from facets drop down list
  •  Find Xp_cmdShell from the properties and set enabled to true
  • Click OK and Xp_CmdShell is enabled now

Following snaps will help you to perform above mentioned steps for enabling Xp_CmdShell through SSMS GUI.

To enable Xp_CmdShell through SSMS GUI step 1


To enable Xp_CmdShell through SSMS GUI step 2

To enable Xp_CmdShell through SSMS GUI step 3
To enable the Xp_CmdShell through T-SQL

 -- To allow advanced options to be changed.  
 EXEC sp_configure 'show advanced options', 1  

 -- To update the currently configured value for advanced options.  

 -- To enable the feature.  
 EXEC sp_configure 'xp_cmdshell', 1  

 -- To update the currently configured value for this feature.  

verify the current option

To verify the current status of xp_cmdshell you may use following T-SQL

-- Verify the current status of xp_cmdshell  
 SELECT * FROM sys.configurations where name = 'xp_cmdshell'  

Using the Xp_CmdShell

Here i will quote a simple example of xp_cmdshell usage from BOL.
Executing the following xp_cmdshell statement returns a directory listing of the current directory.

 EXEC xp_cmdshell 'dir *.exe';  
  • Aug 23 / 2013
  • 0
dbDigger, PowerShell, SQL Server Error messages, System Administration

File %.ps1 cannot be loaded because the execution of scripts is disabled on this system.

Now a days i am working on a powershell task. This morning i prepared a basic powershell script and run it to see that how close are the results. I called the powershell script from cmd so that any errors may be seen if generated. To my surprise it generated a different type of error pointing to some configuration

Error while executing power shell script

In a more readable text form the errror statement is

File %.ps1 cannot be loaded because the execution of scripts  is disabled on this system. Please see “get-help about_signing” for more details.

I looked for the solution and implemented it to allow PS scripts run on my stystem. Following is the brief solution that i implemented.

Step1: Check current system setting for scripts

launch powershell and execute Get-ExecutionPolicy 


It shows that current script execution is restricted.

Step2: Change the script execution policy

Now we have to change the script execution policy. We have three other options to set.

  • RemoteSigned: You can run your own scripts but downloaded scripts will have to be signed in order to run on the system.
  • AllSigned: Your own or dowloaded scripts all should be signed to run.
  • Unrestricted: Remote or your own scripts may be run without signing check.

So you may choose an appropriate option and run one of the following command according to choice

  • Set-ExecutionPolicy RemoteSigned
  • Set-ExecutionPolicy AllSigned
  • Set-ExecutionPolicy UnRestricted

Step 3: Verify

You may verify the new policy implementation by repeating step 1. This time you will get the new policy name instead of restricted policy.

After implementing this solution i proceeded with my original tasks by writing and executing the PS scripts successfully. Hope same for you.

  • Oct 25 / 2012
  • 0
DBA Interview questions, dbDigger, Monitoring and Analysis, Ports, System Administration, Windows Server

How to identify the active port/ports of SQL Server DB engine

In previous post i have discussed the ports configuration for SQL Server database engine. For any SQL Server instance what are the options to identify the active ports? For this purpose we have three major methods to identify the SQL Server port through GUI. We may use undocumented extended stored procedure master..xp_regread system  to read the registry value for this purpose.However in this post i would cover only the major GUI methods to discover the active TCP/IP port for any SQL Server instance.

  • Through SQL Server Logs
  • Through SQL Server Configuration Manager
  • Through Windows Application Event Viewer

 Through SQL Server Logs

We may use xp_readerrorlog SP in SSMS or SSMS GUI to determine the current port for SQL Server. Execute the SP in SSMS query pane and get the results

 USE master   
 xp_readerrorlog 0, 1, N'listening on', 'any', NULL, NULL, N'asc'   

Same may be determined through SSMS GUI

identify the active port of SQL Server through SSMS GUI

Through SQL Server Configuration Manager

Go to SQL server configuration manager and open TCP/IP properties in network configurations of specific instance. It would show the active TCP/IP port for SQL Server. Port may be noted or even changed to a specific static one here.

Identify SQL Server port through Configuration Manager

Through Windows Application Event Viewer

Another major option available for port discovery is through Windows event viewer. Find the event viewer in Windows Administrative Tools and go to application logs folder. There you may get the port information for your specific SQL Server instance.

Identify SQL Server port through Windows event log viewer

This is not the end, there would be some other ways to discover the same information. Also read the post and comments section on MSSQLTips for same issue.

Consult us to explore the Databases. Contact us