Tuesday, August 14, 2018

Enable Disk Clean-up

It seems in Windows Server latest versions the good old disk clean up tool is not enabled by default. If you just want it back you can run the below commands in a command prompt to copy the required files.

Copy C:\Windows\winsxs\amd64_microsoft-windows-cleanmgr_31bf3856ad364e35_6.1.7600.16385_none_c9392808773cd7da\cleanmgr.exe C:\Windows\System32\

Copy C:\Windows\winsxs\amd64_microsoft-windows-cleanmgr.resources_31bf3856ad364e35_6.1.7600.16385_en-us_b9cb6194b257cc63\cleanmgr.exe.mui C:\Windows\System32\en-US\

Then create a shortcut pointing to the Disk Clean up tool found in the following path.

C:\Windows\System32\CleanMgr.exe

I did try this on Windows Server 2008 R2 and hope it works as it is or with slight changes on other versions of Windows as well.

Thursday, February 15, 2018

Filtering Results of a Stored Procedure

Recently I needed to filter the results of a Stored Procedure, After some searching I found 2 options for this as below.


1. Using OpenRowSet Command

SELECT * FROM OPENROWSET ('SQLOLEDB', 'Server=ServerName;TRUSTED_CONNECTION=YES;', 'EXEC sp_Who2')

Remember in this method this feature should be enabled in the server.

2. Using Temporary Tables.

-- Creating a temporary table.
CREATE TABLE #tblSPWho2
(SPId INT, Status NVARCHAR(200), LoginName NVARCHAR(200), HostName NVARCHAR(200), BlockedBy NVARCHAR(200), DBName NVARCHAR(200),
Command NVARCHAR(200), CPUTime BIGINT, DiskIO BIGINT, LastBatch NVARCHAR(200), ProgramName NVARCHAR(200), SPId2 INT, RequestId NVARCHAR(200))

-- Inserting the results of the Stored Procedure into the temporary table.
INSERT INTO #tblSPWho2
EXEC sp_Who2

-- Doing the required filtering using the temporary table.
SELECT * FROM #tblSPWho2 WHERE LoginName = 'Domain\UserName'

-- Removing the temporary table.
DROP TABLE #tblSPWho2

SP_Who2 Stored Procedure will bring all the processes that is currently active in SQL server with the relevant information, the above query will filter the results and will only display the processes that are initiated by the given username (Domain\UserName).