MS SQL For Beginners

12 Aug 2024

To identify all Windows servers that host a SQL Server instance and have Java installed

To identify all Windows servers that host a SQL Server instance and have Java installed, you'll need to follow these general steps:

1. Identify Servers with SQL Server Installed:

Option 1: Use SQL Server Inventory
- Centralized Management Server (CMS): If you have a CMS in your environment, it can be used to run queries across all registered SQL Server instances to check for the presence of SQL Server.
- PowerShell: You can run a PowerShell script to query servers within the domain to check if SQL Server is installed.
```
powershell
Get-WmiObject -Query "SELECT * FROM Win32_Service WHERE Name LIKE '%MSSQL%'" | Select-Object PSComputerName, Name, State
```
Option 2: Use SCCM (System Center Configuration Manager) if available, as it may have inventory data that can help identify SQL Server instances.

2. Identify Servers with Java Installed:

Option 1: PowerShell Script
- You can use PowerShell to check for Java installation by looking for java.exe in the Program Files directory or by checking the installed programs list.
```
powershell
Get-ItemProperty -Path "HKLM:\Software\JavaSoft\Java Runtime Environment" | Select-Object PSComputerName, DisplayName, DisplayVersion
```
Option 2: SCCM can also be used here if available, as it can provide data on installed software.

3. Combine Results:

After gathering information from the two steps above, you can combine the data to identify which servers have both SQL Server and Java installed. This can be done by cross-referencing the lists of servers.

PowerShell Example:

Export the results from both queries to CSV files.
Then, use a PowerShell script to compare the two lists and output the servers that appear in both.

powershell
$sqlServers = Import-Csv "SQLServers.csv"
$javaServers = Import-Csv "JavaServers.csv"

$result = $sqlServers | Where-Object { $javaServers.PSComputerName -contains $_.PSComputerName }

$result | Export-Csv "ServersWithSQLandJava.csv" -NoTypeInformation

Tools and Methods:

PowerShell: Great for scripting and automation.
SCCM: Useful for environments with SCCM deployed.
Manual Inventory: For smaller environments, manual checks or scripts run on each server.

Note:

If your environment is large, you may want to consider a more centralized management solution or database where inventory data is already collected

Unable to Backup DB due to Active Transactions

If your database is in Simple Recovery Model and you're encountering the error stating that the transaction log is full due to active transactions, here’s what you can do:

Steps to Resolve the Issue:

Identify the Active Transaction:
- You can use the following query to identify the active transaction(s) that might be causing the issue:
```
DBCC OPENTRAN('NGProd');
```
- This will give you details of the oldest active transaction, including the session ID (SPID).
Check the Session Details:
- Once you have the SPID, you can check what the session is doing by using:
```
SELECT * 
FROM sys.dm_exec_requests
WHERE session_id = <SPID>;
```
- This will give you insight into what the active session is currently doing.
Kill the Active Session (if safe):
- If you determine that the session is not needed and you want to terminate it, you can use the KILL command:
```
KILL <SPID>;
```
- Be careful when doing this, as killing a session will roll back the transaction, which could take some time depending on the size of the transaction.
Attempt to Shrink the Log File:
- Once the active transaction is cleared, you can attempt to shrink the log file:
```
DBCC SHRINKFILE('NGProd_Log', 1);
```
- This should free up space in the transaction log.

Perform a Full Backup:

Now, attempt the full backup again:

BACKUP DATABASE NGProd TO DISK = 'C:\Backups\NGProd_Full.bak';

Additional Considerations:

Monitoring for Active Transactions: Keep monitoring the transaction log reuse wait using sys.databases:
```
SELECT name, log_reuse_wait_desc 
FROM sys.databases 
WHERE name = 'NGProd';
```
Why Transactions Might Be Stuck:
- It’s important to understand why transactions are being held open. This could be due to long-running queries, open transactions in user sessions, or possibly replication issues if configured.

If the situation persists and you're not able to clear the active transaction, it might be a good idea to investigate further into the queries or applications holding the transactions open.