Sometimes when working on a remote server, you may see "Signing out" stuck forever. In such cases, you can forcefully log off the session without restarting the server.
Using Command Prompt:-
qwinsta /server:ServerName
logoff <SessionID> /server:ServerName
Example logoff 2 /server:SG-SQL-BI
Using PowerShell:-
quser /server:ServerName
logoff <SessionID> /server:ServerName
.bak files from one server to another within the same domain and on a high-bandwidth connection, this PowerShell + robocopy script is optimized for speed and reliability.I ran into an issue when trying to drop a user from SSISDB (the Integration Services Catalog database).
The database principal has granted or denied permissions to catalog objects in the database and cannot be dropped.
The transaction ended in the trigger. The batch has been aborted.
How to solve it?
Find where the user has permissions
USE SSISDB
go
SELECT *
FROM catalog.object_permissions
WHERE grantee_sid = SUSER_SID('YourUserName');
--change YourUserName to your actual username
Revoke permissions
REVOKE READ ON OBJECT::[folder_name] TO [YourUserName];
REVOKE MODIFY ON OBJECT::[project_name] TO [YourUserName];
Drop the user
DROP USER [YourUserName];
Hi, I have seen this error elsewhere online. I have gone to mmc to enable the snap in and I still have had no fix. My computer is running Windows Server 2022, SQL Server Express 2022, and SSMS. I have reinstalled, repaired, and all of the other tricks. Help!
If you encounter the error:
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
while importing Excel or CSV files into SQL Server, don't worry! This is a common issue caused by a missing or mismatched version of the Microsoft Access Database Engine.
Go to the official Microsoft download page:
Microsoft Access Database Engine 2016 Redistributable
Select the version based on your system:
Note: Using the 32-bit version resolves compatibility issues with tools like SQL Server Management Studio (SSMS), which often run in 32-bit mode.
Once downloaded, follow these steps to install properly:
Open Command Prompt as Administrator.
Navigate to the folder where the .exe file is downloaded.
AccessDatabaseEngine_X64.exe), use the exact name.This will install the provider quietly without UI interruptions.
After installation:
Restart SQL Server Management Studio (SSMS).
Try your import or export task again.
You should no longer see the 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine error!
Monitoring your SQL Server backups is crucial for disaster recovery planning.
Below is a powerful SQL query that retrieves the last full, differential, and log backup details for each database.
SELECT
db.name AS DatabaseName,
-- Last Full Backup Details
MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS LastFullBackupTime,
MAX(CASE WHEN bs.type = 'D' THEN bmf.physical_device_name END) AS FullBackupLocation,
-- Last Differential Backup Details
MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS LastDifferentialBackupTime,
MAX(CASE WHEN bs.type = 'I' THEN bmf.physical_device_name END) AS DifferentialBackupLocation,
-- Last Log Backup Details
MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS LastLogBackupTime,
MAX(CASE WHEN bs.type = 'L' THEN bmf.physical_device_name END) AS LogBackupLocation,
-- Backup Status
CASE
WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) IS NULL THEN 'No Full Backup Found'
ELSE 'Full Backup Available'
END AS FullBackupStatus,
CASE
WHEN MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) IS NULL THEN 'No Differential Backup Found'
ELSE 'Differential Backup Available'
END AS DifferentialBackupStatus,
CASE
WHEN MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) IS NULL THEN 'No Log Backup Found'
ELSE 'Log Backup Available'
END AS LogBackupStatus
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
JOIN sys.databases db ON bs.database_name = db.name
GROUP BY db.name
ORDER BY db.name;
Managing employee data efficiently requires a strong understanding of SQL queries. Below are 32 essential SQL Server queries that cover common use cases, such as fetching, filtering, updating, and analyzing employee records.
-- 01. Fetch all employees whose salary is greater than 50,000.
SELECT * FROM Employees WHERE Salary > 50000;
-- 02. Fetch all employees working in the "IT" department.
SELECT * FROM Employees WHERE Department = 'IT';
-- 03. Fetch employee names and their departments.
SELECT Name, Department FROM Employees;
-- 04. Fetch the top 3 highest-paid employees.
SELECT TOP 3 * FROM Employees ORDER BY Salary DESC;
-- 05. Fetch employees whose names start with the letter "A".
SELECT * FROM Employees WHERE Name LIKE 'A%';
-- 06. Fetch employees who were hired in the year 2022.
SELECT * FROM Employees WHERE YEAR(HireDate) = 2022;
-- 07. Count the total number of employees in each department.
SELECT Department, COUNT(*) AS TotalEmployees FROM Employees GROUP BY Department;
-- 08. Find the average salary of employees in the "Finance" department.
SELECT AVG(Salary) AS AvgSalary FROM Employees WHERE Department = 'Finance';
-- 09. Fetch employees with salaries between 30,000 and 60,000.
SELECT * FROM Employees WHERE Salary BETWEEN 30000 AND 60000;
-- 10. Fetch all employees who do not belong to the "HR" department.
SELECT * FROM Employees WHERE Department <> 'HR';
-- 11. Fetch the details of employees who have not been assigned a department (NULL department).
SELECT * FROM Employees WHERE Department IS NULL;
-- 12. Fetch employee details sorted by their salaries in descending order.
SELECT * FROM Employees ORDER BY Salary DESC;
-- 13. Fetch duplicate employee names from the employee table.
SELECT Name, COUNT(*) FROM Employees GROUP BY Name HAVING COUNT(*) > 1;
-- 14. Fetch the department name and the highest salary in each department.
SELECT Department, MAX(Salary) AS HighestSalary FROM Employees GROUP BY Department;
-- 15. Update the salary of employees in the "IT" department by 10%.
UPDATE Employees SET Salary = Salary * 1.10 WHERE Department = 'IT';
-- 16. Delete employees whose salaries are below 20,000.
DELETE FROM Employees WHERE Salary < 20000;
-- 17. Insert a new employee into the table.
INSERT INTO Employees (Name, Department, Salary, HireDate, Email, ManagerID)
VALUES ('New Employee', 'IT', 55000, '2024-01-31', 'new.employee@company.com', NULL);
-- 18. Fetch employees whose names contain the substring "Ajay".
SELECT * FROM Employees WHERE Name LIKE '%Ajay%';
-- 19. Fetch employees whose email IDs end with "@sqldbanow.com".
SELECT * FROM Employees WHERE Email LIKE '%@sqldbanow.com';
-- 20. Find the total salary paid to employees in each department.
SELECT Department, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Department;
-- 21. Fetch the employee with the second-highest salary.
SELECT * FROM Employees WHERE Salary = (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees));
-- 22. Fetch the number of employees hired in each year.
SELECT YEAR(HireDate) AS Year, COUNT(*) AS TotalEmployees FROM Employees GROUP BY YEAR(HireDate);
-- 23. Fetch employees who share the same salary as another employee.
SELECT * FROM Employees WHERE Salary IN (SELECT Salary FROM Employees GROUP BY Salary HAVING COUNT(*) > 1);
-- 24. Fetch employees along with their manager's name (using self-join).
SELECT e1.Name AS Employee, e2.Name AS Manager FROM Employees e1 LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;
-- 25. Fetch employees with the same department and job title as "John Doe."
SELECT * FROM Employees WHERE Department = (SELECT Department FROM Employees WHERE Name = 'John Doe')
AND JobTitle = (SELECT JobTitle FROM Employees WHERE Name = 'John Doe');
-- 26. Find the maximum and minimum salary of employees.
SELECT MAX(Salary) AS MaxSalary, MIN(Salary) AS MinSalary FROM Employees;
-- 27. Fetch employees who were hired in the last 6 months.
SELECT * FROM Employees WHERE HireDate >= DATEADD(MONTH, -6, GETDATE());
-- 28. Fetch employees who have worked for more than 5 years.
SELECT * FROM Employees WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5;
-- 29. Fetch all employees who do not have a manager assigned.
SELECT * FROM Employees WHERE ManagerID IS NULL;
-- 30. Fetch the first name, last name, and full name of employees.
SELECT FirstName, LastName, CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;
-- 31. Fetch employees grouped by department and sorted by the total salary in descending order.
SELECT Department, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Department ORDER BY TotalSalary DESC;
-- 32. Fetch all employees whose salaries are more than the average salary.
SELECT * FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);
Efficient database backups are crucial for maintaining data integrity and minimizing downtime. Below are several steps you can take to improve the speed of your database backups.
Using compression can significantly reduce the size of the backup file and the time required to create it. Here is an example command:
BACKUP DATABASE sqldbanow TO DISK = 'C:\Backups\sqldbanow_1.bak' WITH COMPRESSION;
Splitting the backup across multiple files can speed up the process by leveraging parallel writes.
BACKUP DATABASE sqldbanow TO DISK = 'C:\Backups\sqldbanow_1.bak', DISK ='C:\Backups\sqldbanow_2.bak';
Tuning these parameters can optimize the I/O operations during the backup.
BACKUP DATABASE sqldbanow TO DISK = 'C:\Backups\sqldbanow_1.bak', DISK = 'C:\Backups\sqldbanow_2.bak' WITH COMPRESSION, BUFFERCOUNT = 575, MAXTRANSFERSIZE = 2097152;
BLOCKSIZE: Specifies the physical block size in bytes. Supported sizes range from 512 to 65536 (64 KB).
BUFFERCOUNT: Specifies the total number of I/O buffers for the backup. Large numbers can cause "out of memory" errors, so adjust carefully.
MAXTRANSFERSIZE: Specifies the largest unit of transfer in bytes, up to 4194304 bytes (4 MB).
RAID configurations can greatly impact backup speeds. Prioritize using:
RAID 0
RAID 1
RAID 10
RAID 5
Backing up to locally attached disks can be faster than backing up across a network.
For enterprise setups, SAN technologies such as snapshot and split mirror backups can significantly enhance backup speed and reliability.
If you need to back up to other machines:
Use the fastest network cards and switches available.
Segment backup traffic from regular network traffic to reduce I/O bottlenecks.
Problem Overview:
In one of our smaller production environments, the SQL Server ERRORLOG file size unexpectedly grew to almost 60 GB. This caused a critical space crunch on the C:\ drive, leading to application timeout errors. The ERRORLOG file was located at:
C:\Program Files\Microsoft SQL Server\MSSQL14.INST1\MSSQL\Log
Challenges:
Active ERRORLOG File: The current ERRORLOG file could not be deleted directly because it was actively being used by SQL Server.
Time Constraints: Restarting the SQL Server instance to create a new ERRORLOG file required approval from the client and the change management team, which could be time-consuming.
Resolution Steps:
Step 1: Cycle the ERRORLOG File Without Restarting SQL Server
To address the issue without a service restart, we used the following command:
EXEC sp_cycle_errorlog;
GO
This command immediately created a new ERRORLOG file. The active log was cycled, and the previous ERRORLOG file was moved to the same directory with a numbered suffix (e.g., ERRORLOG.1).
Step 2: Relocate and Manage Old ERRORLOG Files
The old ERRORLOG file, which was consuming significant space, was manually moved to a different drive with sufficient free space. This provided temporary relief for the space issue on the C:\ drive. After a few days, we deleted the old log files to reclaim space permanently.
Step 3: Identify and Fix the Root Cause
Upon investigation, we discovered that one of the SQL Server Agent jobs was generating excessive logs in the ERRORLOG file. The problematic statement in the job's code was identified and removed to prevent further excessive logging.
Key Takeaways:
Proactive Monitoring: Regular monitoring of SQL Server ERRORLOG file size and disk space utilization is crucial to avoid unexpected space issues.
Efficient Log Management: Use the sp_cycle_errorlog procedure periodically to cycle logs and prevent single ERRORLOG files from growing too large.
Root Cause Analysis: Always investigate the underlying cause of excessive logging to implement a permanent fix.
GenerateUserRoleScripts: This procedure generates the SQL scripts to create users and assign roles for the specified database and stores them in the UserRoleScripts table.ExecuteUserRoleScripts: This procedure retrieves the scripts stored in UserRoleScripts and executes them on the specified database.Stored Procedure 1: GenerateUserRoleScripts
This procedure will generate and store the user-role scripts in the DBA..UserRoleScripts table for the specified database.
USE DBA; -- Change the database name as per you're requirement
GO
-- Step 1: Create the procedure to generate and store user-role scripts
CREATE PROCEDURE dbo.GenerateUserRoleScripts
@DatabaseName NVARCHAR(128) -- Input parameter for database name
AS
BEGIN
-- Dynamic SQL to target the specified database
DECLARE @SQL NVARCHAR(MAX);
-- Create the UserRoleScripts table if it doesn't exist
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserRoleScripts]') AND type in (N'U'))
BEGIN
CREATE TABLE dbo.UserRoleScripts (
ID INT IDENTITY(1,1) PRIMARY KEY,
Script NVARCHAR(MAX),
GeneratedDate DATETIME DEFAULT GETDATE()
);
END;
-- Generate the scripts for the specified database
SET @SQL = N'
INSERT INTO dbo.UserRoleScripts (Script)
SELECT
''IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '''''' + mp.name + '''''')'' + CHAR(13) +
''BEGIN'' + CHAR(13) +
'' CREATE USER ['' + mp.name + ''] FOR LOGIN ['' + mp.name + ''];'' + CHAR(13) +
''END;'' + CHAR(13) +
''ALTER ROLE ['' + dp.name + ''] ADD MEMBER ['' + mp.name + ''];'' AS Script
FROM
[' + @DatabaseName + '].sys.database_role_members drm
JOIN
[' + @DatabaseName + '].sys.database_principals dp ON drm.role_principal_id = dp.principal_id
JOIN
[' + @DatabaseName + '].sys.database_principals mp ON drm.member_principal_id = mp.principal_id
WHERE
dp.name <> ''dbo'' -- Exclude roles where the role is dbo
AND mp.name <> ''dbo'' -- Exclude users where the user is dbo
ORDER BY dp.name, mp.name;
';
-- Execute the dynamic SQL
EXEC sp_executesql @SQL;
END;
GO
In many enterprise environments, database administrators (DBAs) often need to execute scripts across multiple databases on several SQL Server instances. Doing this manually can be time-consuming and error-prone, especially when managing a large number of servers. Automating this task using PowerShell can significantly streamline the process, ensuring consistency and saving valuable time.
In this post, we'll walk through a PowerShell script that automates the execution of a stored procedure (sp_read) across all databases on multiple SQL Server instances. The script also captures the execution output and logs the status (success or failure) for each database in a detailed log file.
SQL Joins:
INNER JOIN:
SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.common_column = b.common_column;
LEFT JOIN (LEFT OUTER JOIN):
SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_column = b.common_column;
RIGHT JOIN (RIGHT OUTER JOIN):
SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_column = b.common_column;
FULL JOIN (FULL OUTER JOIN):
SELECT a.column1, b.column2
FROM table1 a
FULL OUTER JOIN table2 b ON a.common_column = b.common_column;
CROSS JOIN:
SELECT a.column1, b.column2
FROM table1 a
CROSS JOIN table2 b;
SELF JOIN:
SELECT a.column1, b.column2
FROM table a
INNER JOIN table b ON a.common_column = b.common_column;
FROM:
WHERE:
GROUP BY:
HAVING:
SELECT:
ORDER BY:
LIMIT:
Let's consider a complex query to see the order of execution in action:
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY avg_salary DESC
LIMIT 5;
Order of Execution:
employees table.hire_date is after '2020-01-01'.department.department and calculate the average salary as avg_salary.avg_salary in descending order.Atomicity ensures that the entire transaction, which in this case involves deducting money from your account and crediting your friend's account, either happens fully or not at all. In practice, if the second step fails (crediting your friend's account), the first step (deducting your account) is automatically rolled back. This way, your account will still have the original balance, and no partial transaction will occur.
Consistency maintains the integrity of the database. When you attempt to transfer ₹25,000, the system checks your balance against the minimum requirement (₹5,000). If this rule would be broken by the transaction, the system blocks it, ensuring that the rules governing account balances are respected. The database remains valid before and after the transaction.
Isolation ensures that concurrent transactions don't interfere with each other. While you are transferring ₹10,000, another user looking at your account at an intermediate stage will not see a partially updated balance. This prevents inconsistencies during the process and ensures that only complete transactions are visible to others.
Durability means that once a transaction is completed, the changes are permanent, even if there's a power outage or system crash right after the transfer. So, after your transaction is confirmed, both your account and your friend's account will reflect the updated balances, regardless of any subsequent failures.
These properties ensure that financial transactions are secure, reliable, and accurate, reflecting the real-world requirement for a robust system in handling sensitive operations like money transfers.
As a DBA, managing log file sizes is crucial to ensure your databases run smoothly. Below is a T-SQL script to shrink all database log files at once, excluding the system databases (master, tempdb, model, msdb, rdsadmin). This script uses cursors to iterate through each database and its corresponding log files.
Script to Shrink All Database Log Files
Introduction
SQL Server performance tuning is essential for maintaining a high-performing database system. Whether you're a DBA, developer, or just starting out with SQL Server, understanding the key areas to focus on can make a huge difference. In this post, we'll cover the top 10 performance tuning tips to help you get the most out of your SQL Server environment.
1. Index Optimization
Indexes are crucial for speeding up query performance. Regularly review and optimize indexes:
Identify missing indexes using dynamic management views (DMVs).
Remove unused or duplicate indexes.
Rebuild or reorganize fragmented indexes.
2. Query Optimization
Poorly written queries can significantly impact performance. Consider the following:
Use execution plans to identify bottlenecks.
Avoid SELECT *; specify only the columns needed.
Use appropriate JOINs and avoid unnecessary subqueries.
3. Database Maintenance
Regular maintenance tasks can keep your database healthy:
Implement regular index maintenance (rebuild/reorganize).
Update statistics to ensure the query optimizer has accurate data.
Perform regular database integrity checks (DBCC CHECKDB).
4. Monitor and Troubleshoot
Monitoring helps identify performance issues before they become critical:
Use SQL Server Profiler or Extended Events to trace slow queries.
Monitor wait statistics to identify resource bottlenecks.
Implement performance alerts to catch issues early.
5. Optimize TempDB
TempDB is a critical system database; optimizing it can enhance overall performance:
Place TempDB on fast storage.
Configure multiple TempDB files to reduce contention.
Regularly monitor and clean up TempDB usage.
6. Memory Management
Proper memory configuration is vital for SQL Server performance:
Set the max server memory to prevent SQL Server from using all available memory.
Monitor memory usage to ensure there are no leaks.
Use the buffer pool extension for additional memory management.
7. Disk I/O Optimization
Disk I/O can be a common performance bottleneck:
Use fast storage solutions like SSDs for critical data files.
Separate data files and log files onto different disks.
Monitor disk I/O performance and address hotspots.
8. CPU Optimization
Efficient CPU usage is critical for performance:
Monitor CPU usage to identify high-consumption queries.
Optimize CPU-heavy queries by reducing complexity.
Use the appropriate server hardware for your workload.
9. Network Optimization
Network latency can affect SQL Server performance:
Ensure a fast and reliable network connection.
Use proper network configurations and protocols.
Monitor network latency and throughput.
10. Regular Audits and Reviews
Regularly auditing and reviewing your SQL Server environment can help maintain performance:
Perform regular health checks.
Review and update your maintenance plans.
Stay updated with the latest SQL Server patches and updates.
Migrating an SQL Server database to AWS RDS Aurora PostgreSQL