Powershell script function for disk free

Question: I'm currently building a script to return all SQL Servers in the environment . As part of the recordset I need to return the aggregated free space for the drives on the server. So for example , if these were the drive free space details for a specific servers C:\ 52 GB free E:\ 7 GB free F:\ 9 GB free G:\ 5 GB free than the figure to return is 73 GB , I'd like this to be a Powershell function , so then I can reference it for any server, therefore the input parameter should be servername... Read more →

Get Server Total Physical Memory with Powershell

Question: The server team have added a bunch og memory to a server . How can I get the Total Physical Memory of a server using Powershell? The server is a remote server. Answer; A common method to extract the total physical memory of server is using : Get-WMIObject and TotalPhysicalMemory According to the documentation : "The Get-WmiObject cmdlet gets instances of Windows Management Instrumentation (WMI) classes or information about the available WMI classes. " There is an option to query a remote compueter by specifying a name on the -Computername switch. An example of the Powershell code. The result... Read more →

How to start SSMS as Administrator

Sometimes you find yourself in a situation where you are logged onto a server via RDP - and want to start SQL Server Management Studio (SSMS) as another local login. I'm not talking about the actual SQL Server login process - but actually starting SSMS as another login. Why would you want to do this? Let's say you've logged onto a server with a particular login - but that login may not have permissions to start SSMS. Or you may have BUILTIN\Administrators enabled on the SQL Server , therefore you may need to initiate SSMS in the context of a... Read more →

How to troubleshoot Avamar backup issues with avsql command line

It is possible when there is either a SQL Version upgrade or Avamar plug-in changes backup issues are encountered. avsql is the command line interface for Avamar and SQL Server. The command browses SQL Server plug-in data for SQL Server backup and restores . These are the different operation available via the avsql command line interface . avsql --operation={browse | backup | restore} [options] The browse operation reports on client data available for backup. This example returns client data for SQL Server instances on the current host avsql --operation=browse --verbose There are plenty of other browsing options right down to... Read more →

How to unzip in Powershell

Question: I have a zip file , which I normally win-zip or unzip . I'd like to unzip an archive file as part of a Powershell script Answer: You can use the Powershell cmdlet - Expand-Archive . I've also included some added bits around the ps cmdlet Expand-Archive , which will assist in managing access & file clean up . # Unblock if there's a block – common problem Unblock-File test.zip -ErrorAction SilentlyContinue #the actual expand process Expand-Archive -Path test.zip -DestinationPath e:\exsample\sql_server\modules -Force #to remove your zip file Remove-Item -Path test.zip Read more on Powershell scripts Powershell Script Library for... Read more →

How to unzip in Powershell

Question: I have a zip file , which I normally win-zip or unzip . I'd like to unzip an archive file as part of a Powershell script Answer: You can use the Powershell cmdlet - Expand-Archive . I've also included some added bits around the ps cmdlet Expand-Archive , which will assist in managing access & file clean up . # Unblock if there's a block – common problem Unblock-File test.zip -ErrorAction SilentlyContinue #the actual expand process Expand-Archive -Path test.zip -DestinationPath e:\exsample\sql_server\modules -Force #to remove your zip file Remove-Item -Path test.zip Read more on Powershell scripts Powershell Script Library for... Read more →

Check SQL Agent Job Exceution Status and Messages Generated with a sql script

Question: How can I check SQL Agent Job Execution Status using t-sql? I want to be able to report on the Job Steps execution status , run time duration and Message Generated. Do you have a script example , which returns the above values based on the last 24 hrs. Answer: SELECT @@servername AS 'Server_Name', j.NAME AS 'Job_Name', CASE h.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'In Progress' END AS ExecutionStatus ,s.step_id AS 'Step' ,s.step_name AS 'Step_Name' ,msdb.dbo.agent_datetime(run_date, run_time) AS 'Run_DateTime' ,((run_duration / 10000 * 3600 +... Read more →