Search results for ""
-
A Complete Guide to Migrating On-Premises SQL Server Data to Azure Cloud
29 August 2024Migrating your on-premises SQL Server databases to the Azure cloud can significantly boost scalability, security, and performance. However, to ensure a smooth and successful transition, it’s essential to follow a…
-
How to Send Emails Using PowerShell with SMTP Authentication
26 August 2024Sending emails programmatically can be a crucial part of automating tasks, managing notifications, or integrating with various services. PowerShell, a powerful scripting language for Windows, makes it easy to send…
-
Understanding Transparent Data Encryption (TDE) in SQL Server
5 August 2024Understanding Transparent Data Encryption (TDE) SQL Server offers various encryption mechanisms to secure data, and Transparent Data Encryption (TDE) is one of the prominent features. TDE provides real-time encryption and…
-
Resolving SQL Server Error 9002: Log File Full
Troubleshoot full transaction log error 9002 – SQL Server Encountering SQL Server Error 9002, which indicates a full transaction log, can disrupt your operations. Here’s a comprehensive guide to troubleshoot…
-
SQL Server Database Administrator with 6 years of experience, interview questions
26 July 2024For an L2 SQL Server Database Administrator with 6 years of experience, interview questions will typically focus on real-time scenarios, problem-solving skills, and advanced SQL Server concepts. Here are some…
-
Building SQL Server Database Inventory
15 July 2024As a Database Administrator (DBA), maintaining a well-documented inventory of your SQL Server databases is crucial for effective management, security, and performance optimization. In this blog, we’ll discuss the essential…
-
SQL Server DBA Scenarios: A Comprehensive Guide
11 July 2024As a Level 3 SQL Server DBA, you’re expected to handle a variety of complex situations that require deep technical knowledge and problem-solving skills. This guide provides insights into advanced…
-
How to Relocate tempdb Files
5 July 2024When managing SQL Server, performance optimization and efficient resource management are crucial. One often-overlooked aspect is the placement of the tempdb database files. Relocating these files can significantly improve performance…
-
Simplify SQL Server Login Transfers with sp_help_revlogin
4 July 2024Transferring logins and passwords between SQL Server instances can be a complex task, but the sp_help_revlogin stored procedure streamlines this process, ensuring a smooth migration. Here’s a comprehensive guide on…
-
Migrating SQL Server Using Backup and Restore: A Step-by-Step Guide
Migrating an SQL Server from one server to another can be a daunting task. Whether you are upgrading to a newer version, moving to a different environment, or consolidating servers,…
-
Migrating SQL Server Using Data Migration Assistant (DMA): A Step-by-Step Guide
Migrating SQL Server using the Data Migration Assistant (DMA) can simplify the process, making it more efficient and less error-prone. DMA helps identify compatibility issues, assess migration readiness, and provides…
-
Changing Server Collation in SQL Server: A Step-by-Step Guide
21 June 2024Changing the collation of an SQL Server instance after installation is a significant task that requires careful planning and execution. Collation affects how data is sorted and compared, so it’s…
-
Database Object Count Across Multiple Databases in SQL Server
20 June 2024In large SQL Server environments, managing and auditing databases can become a complex task, especially when dealing with numerous databases and a variety of objects within them. Whether you’re a…
-
Retrieving SQL Server Restore History Using T-SQL Queries
18 June 2024Introduction Explain the importance of maintaining restore history in a SQL Server environment. Introduce the problem statement: How can database administrators track restore operations effectively? Understanding the Data Model Briefly…
-
Discovering SQL Server TCP Port Configurations
In the realm of database administration, understanding and managing network configurations is paramount. SQL Server, like many database systems, relies on TCP/IP for network communications. Consequently, knowing which TCP ports…
-
Scripting Out Existing Database Mail Profile’s Settings in SQL Server
14 June 2024If you need to generate scripts for your current SQL Server 2016 Database Mail profile setup and configurations, you can use the following T-SQL script. This script will help you…
-
Troubleshooting SMTP Connections: Using Traceroute with SendGrid
When it comes to ensuring reliable email delivery, understanding the network path your emails take is crucial. Using traceroute (or tracert on Windows), you can diagnose potential issues by visualizing…
-
Essential Guide For Regular SQL Server Patching
13 June 2024In the realm of SQL Server management, keeping up with service packs and cumulative updates is not just a recommendation—it’s a necessity. Understanding these updates and their importance can make…
-
Migrating On-Premises SQL Server Data to Azure Cloud: A Step-by-Step Guide
Migrating your SQL Server data from an on-premises environment to the Azure cloud can seem daunting, but with a structured approach, you can achieve a smooth and successful transition. Here’s…
-
Beginner SQL DBA Cheat Sheet
As a budding SQL Database Administrator (DBA), mastering the fundamentals of SQL commands is essential. This cheat sheet covers the core areas you’ll need to get started: Backup and Restore,…
-
Why Your SQL Query Was Fast Yesterday but Very Slow Today
It’s a common frustration in database management: your SQL query was blazing fast yesterday, but today it’s crawling. This kind of performance inconsistency can be baffling, but there are several…
-
Optimizing SQL Queries for Improved Performance
7 June 2024In the world of databases, optimizing SQL queries can significantly enhance performance and efficiency. Let’s dive into some best practices to fine-tune your queries and keep your database humming along…
-
Adding Database Files in SQL Server Always On Availability Groups
Introduction:SQL Server Always On Availability Groups provide high availability for your databases. Adding database files while maintaining this availability requires careful planning and execution. In this guide, we’ll walk through…
-
Generate Script to Fix Orphaned Users in SQL Server Database
30 May 2024Fixing Orphaned Users in SQL Server Databases Orphaned users can be a common issue in SQL Server databases, typically arising after database restores or server migrations. An orphaned user is…
-
Applying SQL Server Patches or Cumulative Updates in Always On Availability Groups
22 May 2024Introduction Keeping your SQL Server instances updated is crucial for security, performance, and stability. In this blog, we will go through the process of applying SQL Server patches or cumulative…
-
Backups in SQL Server: Ensuring Data Integrity and Availability
20 May 2024In the realm of database management, backups are a crucial practice for ensuring data integrity and availability, especially in the face of unexpected events like hardware failures, data corruption, or…
-
Understanding the Full Backup Process in SQL Server
As a Database Administrator (DBA), one of my key responsibilities is ensuring that data is safe and recoverable. A critical part of this is taking regular backups. Today, I’ll walk…
-
SQL Server DBA interview question from !nf0$y$
15 May 2024 -
About Traces & System Stored procedures
Trace Flags What are trace flags? Trace flags are used to temporarily set specific server characteristics or to switch on/off a particular behavior. They are frequently used to diagnose performance…
-
Managing High Memory Utilization in SQL Server: A T-SQL Script Guide
14 May 2024As a SQL Server Database Administrator, managing memory utilization is paramount for ensuring optimal performance and stability of your database environment. High memory usage can lead to sluggish performance, resource…
-
Find SQL Server Stored Procedure Create, Modified, Last Execution Date
7 May 2024As someone who looks after databases (Database Administrator or DBA), it’s really important to keep track of how well stored procedures are doing. Stored procedures are like pre-planned sets of…
-
Truncate All Data from a SQL Server Database all Tables
24 April 2024In SQL Server, truncating all data from all tables in a database can be a useful operation, especially in development or testing environments where you need to reset the database…
-
Identifying list of SQL Server Encryption databases
10 April 2024In the world of database management, security is paramount. One crucial aspect of securing your SQL Server databases is encryption. Encryption helps protect sensitive data from unauthorized access, ensuring that…
-
Essential Database Management Views (DMVs)
8 April 2024In the intricate world of database management, optimizing performance is paramount. Whether you’re overseeing a bustling enterprise database or managing a small-scale system, understanding the underlying performance metrics is key…
-
Understanding the Differences Between Clustered and Non-Clustered Indexes
28 March 2024When it comes to organizing and accessing data in a database, indexes play a crucial role. Two common types of indexes used in database management systems are clustered and non-clustered…
-
How to list the names, schema, and definition of all stored procedures in the specified database
20 March 2024In the world of database management, SQL Server is a popular choice for organizing and retrieving data. One useful feature it offers is the ability to use SQL queries to…
-
Exploring Automatic Seeding in SQL Server Always On Availability Groups
17 November 2023Introduction In the dynamic landscape of database management, SQL Server Always On Availability Groups (AG) play a pivotal role in ensuring high availability and disaster recovery. One of the key…
-
Find Encrypted Stored Procedures in SQL Server
19 October 2023In SQL Server, stored procedures are often used to store and execute sets of SQL statements. Sometimes, for security reasons, you might want to encrypt your stored procedures. But how…
-
Missing MSI and MSP files error while patching SQL SERVER
10 October 2023Hello everyone, I came across the following article in a WhatsApp group. I used to encounter similar issues while patching a SQL Server in the past, but lately, I haven’t…
-
How to Find Your In-Hand Salary Based on Your CTC
Understanding job offers can be tricky, especially when the big number they throw at you (CTC) feels like a puzzle. To make it simple, here are five easy ways to…
-
Daily Commands for Database Administrators
30 September 2023As a database administrator (DBA), navigating the vast landscape of database management can be challenging and rewarding. It’s important to be able to execute the commands necessary to simplify day-to-day…
-
Understanding SQL Server Roles: Server Admin vs. Sysadmin
31 August 2023Introduction Managing a SQL Server environment involves various roles and responsibilities. Two key roles in this context are the “serveradmin” and “sysadmin” (System Administrator). Recently one of my friend was…
-
How to set Maximum Memory for SQL Server
One crucial aspect is setting the maximum memory for SQL Server. Recently one of my friend was asked this question in an interview. In this blog, we’ll explore why this…
-
Understanding SQL Server Permissions: GRANT vs. WITH GRANT
Introduction We’ve ever worked with SQL Server, we’ve probably encountered the need to manage permissions. Permissions control who can access and manipulate data within a database. Two commonly used SQL…
-
Renaming a SQL Server Database in an Always On Availability Group
11 August 2023Introduction Renaming a database in SQL Server that is part of an Always On Availability Group requires careful planning and execution to ensure minimal impact on the availability and functionality…
-
Unable to open the “Properties” window for databases on the Managed Instance
7 August 2023Issue Definition Unable to open the “Properties” window for databases on the Managed Instance using SSMS. Getting error message. Error message Subquery returned more than one value. This isn’t permitted…
-
-
Analyzing Statistics in SQL Server
19 July 2023Introduction In the world of database management, understanding the statistics of your SQL Server database is crucial for optimizing query performance. SQL Server provides a powerful set of system views…
-
Find Object-Level Permissions in SQL Server with T-SQL
14 July 2023Introduction Managing permissions is a critical aspect of database security. As a database administrator or developer, it’s important to have a clear understanding of the permissions assigned to various logins…
-
Which login has write access on database
6 July 2023Introduction In this blog post, we will explore how to use T-SQL (Transact-SQL) to identify which logins have write access to a particular database. Let’s dive into the world of…
-
Configuring Email Notifications for Backup Jobs in SQL Server
3 July 2023Setting up email notifications for your SQL database backup jobs can be a useful way to stay informed about important events and updates. In this guide, we will walk you…
-
Essential Scripts related to SQL Server Users and Logins
28 June 2023Introduction In this blog post, we will explore six (6) essential scripts that will revolutionize your SQL Server management, ranging from retrieving login details and user lists to fixing orphaned…
-
Essential Scripts related to SQL Server Database Backups
27 June 2023Introduction Database backups are crucial for ensuring data integrity and disaster recovery. As a database administrator or developer, having reliable scripts to monitor and manage backups is essential. In this…
-
T-SQL to backup all user SQL Server databases at a time
Introduction In today’s world, data is very important. We need to make sure that our data is safe and secure. One way to do this is to back up our…
-
SQL Server Installation Best Practices
Introduction SQL Server is a powerful database management system that is used by businesses of all sizes. However, if SQL Server is not installed correctly, it can lead to performance…
-
Adding a TDE-Enabled Database to an Always On Availability Group
26 June 2023Introduction Ensuring the security of your database is crucial, especially when it comes to protecting sensitive data. Transparent Data Encryption (TDE) is a feature in SQL Server that provides encryption…
-
How to fix missing msi files SQL Server
17 June 2023Introduction If you use SQL Server, you might have faced a frustrating problem: missing MSI files. These files are important for installing or managing SQL Server, but sometimes they can…
-
Reasons why you should stay back late in the office
15 June 20235 Reasons why you should stay back late in the office (plus 1 top secret reason) 1. You’ll get a free drop home In most companies, if you stay back beyond…
-
Differents ways to prevent your senior from stealing credit for your work
5 ways you can prevent your senior from stealing credit for your work Credit stealing is very common in corporate environments. The most common instance is where a senior member…
-
Differents ways to get Knowledge Transfer
5 Ways you can get Knowledge Transfer (KT) from someone when they’re not interested This is the most common problem faced by new joiners. The manager asks you to learn…
-
Resolving Failed Database Mail Issues with TLS 1.2
10 June 2023Introduction Have you ever been frustrated because your database emails failed to send? You’re certainly not alone! I received multiple calls between June 5th and June 9th from various sources…
-
Find Permissions Granted to a User-Defined Database Role
6 June 2023We now have the ability to define a User Defined Server Level Role in SQL Server 2012 and higher versions. For the security and integrity of the data stored in…
-
SQL Server Checkpoint Demystification
4 June 2023Introduction There are numerous background operations that protect data integrity and enhance performance in the world of SQL Server. SQL Server Checkpoint is one example of such a procedure. This…
-
SQL Server Database Backup Failure Scenarios
Backing up your vital data is critical, but mistakes do happen. It is critical to understand the potential causes of Database Backup Failure and how to resolve them. In this…
-
SQL Server DBA Interview Questions
2 June 2023Introduction SQL Server is a popular relational database management system (RDBMS) developed by Microsoft. It is used by a wide variety of organizations, including businesses, government agencies, and educational institutions….
-
Is it possible to restore the database except specific tables in MSSQL
31 May 2023Once upon a time, there was a innocent Database Administrator (DBA) who had to restore a database but leave out certain tables. However, he didn’t know how to do it….
-
Find Open Transactions on a SQL Server
30 May 2023Understanding DBCC OPENTRAN DBCC OPENTRAN is a SQL Server database console command (DBCC) that lets administrators to access information about the database’s oldest current transaction, as well as the oldest…
-
Story of Error : The request failed or the service did not respond in a timely fashion
29 May 2023Scenario Once upon a time, there was a poor person who worked as a database administrator (DBA) for a rich company. One day, the DBA was asked to set up…
-
SQL Server Database Backup With Ola Hallengren Maintenance Solution Scripts
Coming Soon !! Coming Soon !! Coming Soon !! Coming Soon !! Coming Soon !! Coming Soon !! Coming Soon !! Coming Soon !! Coming Soon !! Coming Soon !!…
-
Differences between configuring Always On Availability Groups on a physical and Azure virtual machine
28 May 2023Introduction In today’s data-driven world, ensuring high availability and data protection is paramount for businesses. Microsoft SQL Server offers a robust solution called Always On Availability Groups that enables the…
-
Efficiently Monitor and Manage Index Fragmentation with T-SQL
Introduction As a database administrator (DBA), one of the key responsibilities is to ensure optimal performance and efficient management of database indexes. Index fragmentation can significantly impact query performance and…
-
Understanding SQL Server Error Logs
26 May 2023Scenario SQL Server error logs are logs that store important information about events, warnings, and errors encountered by the SQL Server instance. These logs act as a valuable source of…
-
Certain MDF files growing issues in TempDB
25 May 2023Scenario If you’re facing issues with TempDB in SQL Server, particularly with certain MDF files growing while others remain unaffected, it could indicate a problem with the allocation of database…
-
Granting SELECT Permissions on a View
Introduction In SQL Server, views provide a convenient way to encapsulate complex queries and present a simplified and abstracted representation of data to users. However, there may be situations where…
-
Compatibility Level of the Master DB Remains Same After Upgrades
23 May 2023Scenario During an upgrade from SQL Server 2008 R2 to SQL Server 2016 SP2, we have noticed that all system databases, except for the master database, are automatically assigned the…
-
Monitoring SQL Server Jobs & its Schedules
22 May 2023Introduction SQL Server jobs are essential for automating tasks and maintaining the stability and efficiency of a SQL Server system. These jobs can be scheduled to run at specific times…
-
TSQL to find list of all index not used for last 30 days
16 May 2023This code is designed to provide information about the indexes in the database, including their creation dates, last usage dates, and whether they have been used recently. TSQL — Create…
-
Use ProcDump to capture sql server unexpected crash dump
11 May 2023To use ProcDump to capture a SQL Server unexpected crash dump, follow these steps: Download and extract ProcDump: Navigate to the directory where ProcDump is located: Capture the SQL Server…
-
SQL Server (MSSQLSERVER) service terminated unexpectedly
One day, we suddenly discovered that the SQL Server was not running and was in a stopped state. In a rush, we quickly enabled the SQL Server and Agent services…
-
TSQL to find index fragmentation in SQL Server
10 May 2023To determine index fragmentation in SQL Server, you can use the following steps: SELECT OBJECT_NAME(DMV.object_id) AS TABLE_NAME , SI.NAME AS INDEX_NAME , avg_fragmentation_in_percent AS FRAGMENT_PERCENT , DMV.record_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL,…
-
SQL Server DBA Daily Routine Tasks
8 May 2023As a SQL DBA, there are various essential tasks to perform every day to ensure the smooth operation and security of the database system. These responsibilities include:
-
All About DBCC and DMV Interview Question
What is the use of DBCC commands? DBCC stands for Database Console Commands, a few of us also call it database consistency checker. There are many DBCC commands in SQL Server. We…
-
List all Login, User & DB Role in SQL Server
11 April 2023Description This script generates a list of Logins, Users and the Database Roles that the user belongs to in all the databases in A SQL Server; with a couple of…
-
How to Check Database Encryption Status in SQL Server
3 April 2023Introduction Data security is a critical aspect of database management, and encrypting sensitive data is crucial to safeguarding it from unauthorized access. In SQL Server, Transparent Data Encryption (TDE) provides…
-
Find the users connected to SQL Server
28 March 2023In a SQL Server environment, it can be useful to monitor the user activity and track who is connected to the databases, from which machine they are connected, when they…
-
Check SQL Server Version & license details
12 March 2023As a SQL Server DBA, I would rephrase the statement as follows: While SQL Server does not keep track of license information, it can provide you with details about the…
-
SQL Server DBA Roles and Responsibilities
25 February 2023Are you interested in the world of database management? Do you aspire to become a SQL Server Database Administrator (DBA)? In this post, we will explore the primary responsibilities of…
-
Identify and Store Active Transactions
10 February 2023Scenario How to Identify and Store Currently Running Queries in a Table? To retrieve information about currently executing requests on a Microsoft SQL Server database, we can use several Dynamic…
-
Backup TDE protected database on Azure Managed Instance
9 February 2023We can take a COPY_ONLY backup of a TDE protected (encryption enabled) database on Azure SQL Managed Instance using the following steps. 1. Connect to your Azure SQL Managed Instance…
-
TempDB files recommendation in SQL Server
8 February 2023The number of tempdb data files in an SQL Server depends on several factors, including the number of logical processors on the server. As a general recommendation, it’s best to…
-
Shrinking TempDB in SQL Server: A Step-by-Step Guide
7 February 2023Introduction In SQL Server, TempDB is a system database used to store temporary objects, intermediate results of queries, and other temporary data. Over time, TempDB can grow significantly, leading to…
-
How to change SQL Server instance name
3 January 2023Scenario Troubleshooting host name changes. When the machine name is changed where we have installed SQL Server, all the instances services are started but replication, Jobs, Alerts, Maintenance plans causes…
-
How to fix a suspect mode database?
2 January 2023Scenario A database in Suspect mode is one that is not fully operational and may have some form of corruption or problem. If you are experiencing this issue, you will…
-
How To Check Free Space in Data and Log Files in SQL Server
31 December 2022In a database, data and log files are used to store the data and log records, respectively. It is important to monitor the free space in these files to ensure…
-
Attachment size limit in SQL Database Mail
19 December 2022Overview of Database Mail Are you looking for the quickest and most efficient way to check the attachment size limits in your SQL Database Mail? Database Mail is a powerful…
-
Operating system error 50 in Azure SQL Managed Instance
8 December 2022We encountered an error while taking a backup of a database from Azure SQL Managed Instance to Blob. This issue is not specific to Azure Managed Instance (MI), but error…
-
Password reset of SQL service account
30 November 2022Impact of resetting password for SQL Server service account Resetting the password of your SQL Server service account is a critical security measure that should not be taken lightly. In…
-
Customizing the Status Bar Color in SQL Server Management Studio
25 November 2022Introduction In database management, it is common to work with multiple environments, such as development, staging, and production. Each environment has a different purpose, and making changes to the wrong…
-
How and Why to become a SQL Server DBA?
21 November 2022What is a SQL server dba? A database administrator (DBA) is responsible for the performance, integrity, and security of a database. They may also be involved in the planning and…
-
Get CPU Utilization Data from SQL Server
19 November 2022Monitoring the CPU utilization of a SQL Server instance is crucial for maintaining optimal performance and identifying any potential bottlenecks. In this blog post, we will explore how to analyze…
-
Driver could not establish a secure connection to SQL Server
17 November 2022Driver could not establish a secure connection to SQL Server Recently windows patching was done on a server and also SQL server security got update with the same patch. Later…
-
Migrating SQL Server Database Mail Settings
16 November 2022Migrating SQL Server Database Mail Settings Migrating SQL Server Database Mail profiles, accounts and settings can be a complex and time-consuming process. This article will provide an overview of what…
-
Checking SQL Server Job History by Script
7 November 2022Mainly msdb.dbo.sysjobs and msdb.dbo.sysjobhistory contains all major information regarding the SQL Server job and its running status and history. We may directly query both objects and get the details or…
-
Checking Compatibility Level Of All Databases In SQL Server
28 October 2022SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is a server-based system that can be accessed by multiple users simultaneously. It offers many features, including…
-
Find active transactions by duration
15 July 2022Recently frequently we faced blocking issues on our production server. The main reason that I understand, recently we merged a few SQL servers and migrated all databases to a single…
-
Certificate chain issued by authority that is not trusted
21 June 2022Certificate chain issued by authority that is not trusted Recently we moved whole on-premises resources to the cloud. In an easy word, we have moved SQL Server from on-premises to…
-
Check the Status of EMail Messages Sent With Database Mail
13 June 2022In SQL Server sp_send_dbmail use to send emails and the SQL Server log all the status of each message processed by Database Mail. Below is the quick list of Database…
-
Monitoring Database Backups Details using T-SQL SELECT Statement
27 May 2022Introduction As a database administrator, one of the crucial tasks is to ensure regular backups of databases. Monitoring the Database Backups Details including backup status and locations of different types…
-
Database stuck in Mirroring, Disconnected / In Recovery
23 November 2021Mirror database stuck in the state of ‘Mirroring, Disconnected / In Recovery’ We have an interesting situation after configuring a mirror setup. We have configured mirroring between a list of…
-
Change Default TCP Port in SQL Server
22 September 2021Change Default TCP Port in SQL Server When any version or edition of MSSQL Server is installed, by default it’s installed on default port 1433. By default, we need to…
-
Things You Must Do After Installing SQL Server
13 July 2021Things You Must Do After Installing SQL Server Rolling out SQL Servers is a key task for DBAs. While for some DBAs this can be a frequent exercise, others will…
-
Find database size when it is offline
1 July 2021Hi, Tech Giants! What new to me today? Yeah, We did a SQL Server migration activity or we can say a side-by-side up-gradation activity. From our DBA side, it’s almost…
-
Auto-open new query window in SSMS
30 June 2021How to Change Product Key for SQL Server The Microsoft Evaluation Center brings you full-featured Microsoft product evaluation software available for downloads. We have downloaded from official website and install…
-
Make Named instance look like a Default instance
29 June 2021Convert or make SQL Server Named instance look like a Default instance Hi tech giants, today I have learn how to make a SQL server named instance look like a…
-
Find database growth size in SQL Server
28 June 2021All of a sudden I received an email from the database owner – “pls share file growth if u have so that the sizing can be done better” We don’t…
-
How to Configure MSSQL Backup Compression as the Default Option
28 February 2021Introduction In Microsoft SQL Server, backup compression is a powerful feature that can significantly reduce the size of database backups, thereby saving storage space and improving backup and restore performance….
-
Find blocking by T-SQL Queries
27 August 2020Blocking is an unavoidable and by-design characteristic of any relational database management system (RDBMS) with lock-based concurrency. As mentioned previously, in SQL Server, blocking occurs when one session holds a…
-
Find who dropped Database at what time
12 January 2020Find who dropped Database at what time As a DBA often we face a scenario that someone has dropped the database and we need to find out who dropped it….
-
Provide a SQL Login ALTER TRACE permission
5 January 2020Provide a SQL Login ALTER TRACE permission One SQL login holder asked DBA team “Please provide me profiler access urgently“. Mentioned user using a SQL login and he wants to…
-
Extended Security Updates for SQL 2008
22 December 2019How to get SQL Extended Security Updates for SQL 2008/2008 R2 database instance. The End of Support date for SQL Server 2008 and 2008 R2 is July 9, 2019 Extended…
-
Change ‘TempDB’ file location in SQL Server
18 December 2019Introduction The TempDB database is essential to SQL Server’s management of temporary data and execution of different tasks. The TempDB files are often kept on the same disc as the…
-
Determine or Estimated time (ETA) of Backup & Restoration
4 December 2019As a DBA you may face a few common questions whenever someone accidentally deleted some database objects or the whole database. “How long-time SQL Server will take to complete the…
-
Send Disk space report daily by a Job
29 November 2019How to send Disk space report daily by a Job Send Disk space report daily by a Job. Here we will use a TSQL script for the activity. The script…
-
Send Database Backup Report Daily by a Job
28 November 2019How to Send Database Backup Report Daily by a Job Send Database backup report daily by a Job. Here we will use a TSQL script for the activity. The script…
-
Find Server Information by TSQL script
26 November 2019Display all Server Information by using TSQL script The below listed all SQL Server Information can be retrieved by a TSQL script. But before that, we need to Enable xp_cmdshell…
-
Backup & Restore Multiple Databases
25 November 2019Backup and restore all databases from an old SQL server and restore it on new SQL server. The number of databases are approx 99+. Let’s try to make backup and…
-
Fixing Orphaned Users in SQL Server
Introduction In easier terms, a ‘Login’ is like an entry pass to get into a SQL Server, and a ‘User’ is like an entry pass to access a specific database…
-
Find cached execution plan
21 November 2019An execution plan, simply put, is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted. Execution plans can tell you how a query will…
-
Configuring Database Mail in SQL Server
17 November 2019SQL Server Database Mail facilitates email communication directly from the database engine. This capability is critical for transmitting query results, error alarms, and reports, as well as keeping you informed…
-
SQL Network Interfaces, error: 26
SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified Users often see “SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified” error message while connecting to SQL Server. Steps to…
-
Find the SQL Server Cluster details
8 November 2019Scenario A cluster includes two or more physical servers, called nodes; identical configuration is recommended. One is identified as the active node, on which a SQL Server instance is running the production workload, and…
-
Create login by CMD in SQL Server
7 November 2019Scenario You have got the SQL database engine installed but no SSMS installed on it. In this scenario how you will connect to the target database engine and create a…
-
Best Practices for SQL Database setting
6 November 20191. Database Owner: ‘sa’ As per my understanding, the Database Owner always should be ‘sa’. We can change ‘sa’ login name as per our organization standard login available in SQL…
-
Find all the users for a particular database
Introduction In SQL Server, managing user access and permissions is crucial for maintaining the security and integrity of a database. Often, there is a need to retrieve a list of…
-
How to run a SQL query using command prompt without SQL SSMS
31 October 2019Introduction How to run a SQL query using command prompt without SQL SSMS Open CMD from Run and run the below command (CMD) to connect with Database engine. sqlcmd -S…
-
Find IP and Port using SQL SSMS
Find IP and Port using SQL SSMS Find IP and Port of a SQL Server from a client machine. The script will not provide all the information if you are…
-
Find user activity without any Auditing
28 October 2019How to find out user activity on database without any particular Auditing Sometime you may need to find out who ran some script or who is accessing the database or…
-
Latest service pack to SQL Server
Apply latest service pack to standalone SQL Server We have given a Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64) Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3…
-
Find SQL Server/Instance details using SQL query
24 October 2019Scenario Sometimes you need to gather the SQL instance details in a proper format such as Server Name, Instance, Service account name. Today I am sharing a script that will…
-
Login failed for user ‘XXXX’ (Microsoft SQL Server, Error: 18456)
23 October 2019Scenario You often get complained that user is not able to connect to SQL server or Application is not able to connect to SQL for some reason. There might be…
-
Error : FILESTREAM feature is disable
17 October 2019Introduction Error – System.Data.Sqltypes.SqlClent.SqlError:FILESTREAM feature is disable. (Microsoft.SqlServer.Smo) Scenario A database backup from a test server provided to DBA team and asked to restore it on a pre-production SQL Server….
-
Run a Job on few specific dates of every month in SQL Server
16 October 2019Introduction As per business requirements, a report needs to generate four times every month. The report will generated by a Stored Procedure. So the DBA’s task is to execute the…
-
Install SQL Server 2008R2 on Windows 2012R2
15 October 2019Introduction Installing lower version SQL Server on a higher version of Windows Server may be problematic at times of installation. Recently we have faced such a scenario. We are sharing…
-
Unable to begin a Distributed Transaction
14 October 2019Problem When your application uses the distribution transaction then you may encounter below error. Error: The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “Linked_Server_Name”…
-
Rename a Database in SQL Server
11 October 2019Introduction We can rename a user-defined database in SQL Server by using SQL Server Management Studio or Transact-SQL. System databases cannot be renamed. The database name cannot be changed while…
-
Shrinking the Large Log File in SQL Server
10 October 2019Scenario We recently came across a situation where one of the drives in the production server is almost full. There are two large databases and they were not backed up….
-
Find the database file growth for all the databases in SQL Server
7 October 2019Introduction Find the database file growth for all the databases. It is not a good practice to keep your database file growth limited. In that case, you may get an…
-
Database-Level Roles in SQL Server
Introduction There are two types of database-level roles: Fixed-database roles db_accessadmin Granted: ALTER ANY USER, CREATE SCHEMA,Granted with Grant option – Connect By SSMS By TSQL USE [Our_Tech_Ideas] — Database…
-
How to get all statistics information for a database in SQL Server
4 October 2019Introduction: Sometimes your query gives you very bad performance because of statistics as SQL database engine Query optimizer uses statistics to create query plans. Updated statistics will give you good…
-
Table backup in SQL Server
Introduction Here we will discuss different backup strategies and methodologies used to take the table backup in SQL Server with real-life examples. There are different methods of taking backup in…
-
Step By Step Installation Of Microsoft SQL Server 2016
3 October 2019Introduction In this post, we will try to show step by step Microsoft SQL Server 2016 installation on Windows Server 2012R2. Microsoft SQL Server 2017 is the latest stable version…
-
Database Backup & Restore in SQL server
2 October 2019Introduction Backing up a database is one of the most important things you need to do when having a database depended application. It’s only all of your data in there,…
-
List of jobs associated with database in SQL Server
30 September 2019Introduction As a Database Administrator (DBA), my recent task involved listing all the jobs running in a production SQL Server environment, along with their respective databases. With over 200+ jobs…
-
Find Database size in SQL Server
Introduction In this article, we will try to pull all sizes from SQL server. How to find Database Size in SQL Server? How to Get size of all tables in…
-
SQL Instance Last restart date and time in SQL Server
29 September 2019Introduction: When your SQL instance was last restarted? I understand it is very simple script but we often forget simple scripts. Hence I thought to write this simple script and…
-
Database restore history in SQL Server
27 September 2019Introduction This script will help you to get the restore history for all the databases. If you need to know the restore history only for a single database then please…
-
How to create database user on secondary node if DB is in readonly mode in SQL Server?
Scenario How to provide ‘db_dataread’ database role to a SQL login when the database is in AlwaysOn & secondary node DB is in readonly mode? Introduction To provide ‘db_dataread’ database…
-
Exploring Database File Information with T-SQL
25 September 2019Introduction In the world of database management, having a clear understanding of the file information is crucial for optimizing performance and storage. In this technical blog post, we will dive…
-
Find Physical file location for all the databases in SQL Server
Introduction Very often DBAs need to find the location of the physical file (MDF, LDF & NDF) of a database. Simply we can obtain the output by executing the below…
-
Set Auto growth for all the databases wit T-SQL
24 September 2019Introduction Managing the growth of database files is an essential aspect of maintaining a healthy SQL Server environment. As databases grow over time, it becomes crucial to configure appropriate growth…
-
Find database growth size and store in a table in SQL Server
Introduction Managing database sizes is a crucial task for database administrators. Monitoring database growth and tracking size changes over time are essential for capacity planning and performance optimization. In this…
-
Find disk space using Stored procedure or TSQL
Introduction xp_fixeddrives only provide how much disk space is free on each drive but not the maximum space of the drive & percentage of free space. If you need to find…
-
Find text in Stored Procedures & then find JOB associate with Stored Procedure
23 September 2019Introduction Few days before I was asked to disable some ‘JOB’ in a production SQL Server. But the task was a little tricky!! I was given a subject line and…
-
Increase the number of ErrorLog in SQL
21 September 2019If something goes wrong on the SQL database server, the error log is the first place we look for information. Here you can find error messages, warnings, critical status, database…
-
Move SQL Server database files to another location
20 September 2019Issue If any drive is full due to the large database data file (LDF or MDF) size! How to moving MS SQL Server Database files (MDF, LDF & NDF) to…
-
Find the user who executed DELETE on tables in SQL Server
18 September 2019Introduction In the world of SQL Server administration, it’s not uncommon to encounter situations where important data mysteriously disappears from tables. As a database administrator (DBA), it’s crucial to identify…
-
TSQL Queries Used by SQL DBA in day to day
17 September 2019In this article, we’ll explore some of the most commonly used TSQL queries for DBAs, how to use them effectively, and the benefits they offer. Whether you’re a beginner or…
-
About SQL Server Versions
10 September 2019Introduction I always very much confused about SQL Server Versions. To check the SQL Server version by TSQL we may run below script. Script 01 SELECT @@VERSION AS ‘SQL Server…
-
SQL Server DBA Frequently Asked Questions
1 September 2019What is RDBMS? Relational Database Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data…
-
Monitoring and Analyzing SQL Server Sessions with T-SQL
26 May 2019Introduction SQL Server is a powerful relational database management system used by organizations to store and manage their data. As an administrator or developer, it’s crucial to have visibility into…
-
Create User Defined db_executor database role
6 June 2018Fixed database roles in SQL Server include db_datareader, db_datawriter and other. These roles grant users read and write access to the database, allowing them to interact with tables. It’s worth…
-
SQL DBA Regular Alerts & Tickets (Service Request and Incident)
7 October 2017Customer Raised Tickets Monitoring Tool/Alerts Raised
-
Important & Handy TSQL Commands
Space Management TSQL Commands 1)sp_spaceused2)dbcc sqlperf(logspace)3)xp_fixeddrives Information TSQL Commands 1) sp_helpdb2) select db_id(‘DBName’)3) select db_name(DBID)4) select * from sys.dm_exec_sessions, dm_exec_connections5) select * from sys.databases6) select * from sys.database_files Monitoring TSQL…
-
SQL Server Log shipping
14 January 2017What is LOG SHIPPING in SQL Server DBA? Log shipping: Shipping of transaction log files from the primary server database to second server database to get both database in continuous…
-
SQL Server Upgradation
13 January 2017What is SQL Server UPGRADATION? Upgrading means, migrating sql server old version to sql server 2012. Before upgradation we need to run upgrade advisor tool. Microsoft sql server 2012 upgrade…
-
SQL Server Migration
12 January 2017Migration means moving one (or) set of databases from one server to another server. SQL Server Migration Scenarios Scenario 1: As a part of launch, deployment in the production server…
-
SQL Server Automation
11 January 2017Automation refers to scheduling the regular maintenance tasks. Whenever schedule time comes automatically that task will be executed. DBA regular activities can be automated we can get the status f…
-
SQL Server Security
10 January 2017Security is the most important tasks for a DBA. we need control the access to sql server as well as provide security to the datal. Levels of SQL Server Security…
-
SQL Server Restoration Tutorial
9 January 2017Restoration is a process of using backups to recover the exist database (or) to create a new database in another server. Situations for Restoration Planned restoration, when we have planned…
-
SQL Server Backups
8 January 2017Backup means, saving the exact copy of the current version of database in some other location. Situations where backup can use Hardware failure includes operating systems CPU memory, network card…
-
SQL Server Databases
7 January 2017Database: Database is a container which stores data and data objects. It manages data and allows fast storage and retrieval of that data. Types of Databases in SQL Server: There…
-
SQL Server installation
6 January 2017Installation Whenever we want to start the installation of the SQL server we need to gather details of installation. SQL Server Versions Version (Name) RTM SP1 SP2 SP3 SP4 Sql…
-
SQL Server Transaction Log Architecture
5 January 2017Transaction Log Architecture SQL Server uses LSN (Log Sequential Number) in identifying the transaction. Each and every transaction that comes to the log file will associate with an LSN number….
-
SQL Server Database Architecture
4 January 2017SQL Server data mainly in 2 types of files, Data file stores actual data with .mdf extension. It stores permanent data. Log files stores modified recorded information with .ldf extension….
-
SQL Server Storage Architecture
3 January 2017In an SQL server all the data will be stored in the form of records, these records also called row data. All these records further grouped into a page. The…
-
SQL Server Architecture
2 January 2017Introduction If you’re curious about how SQL Server works behind the scenes or you’re new to the world of databases, you’ve come to the right place. In this article, we’ll…
-
SQL Server Database Administration Introduction
1 January 20171. Database Administration A database administrator is a person responsible for the database design, implementation, maintenance, and repair of the database. The main goal of DBA is to keep the…
-
Complete SQL Server DBA Tutorials
30 December 2016MS SQL Server DBA Training Welcome to Complete SQL Server DBA Tutorials ! This course is designed to help beginners and professionals learn the fundamentals of SQL Server DBA (Database…
-
All about SQL Server KT
5 January 2016Knowledge Transfer in IT Companies In the IT industry, knowledge transfer is the process of sharing technical and/or business knowledge between individuals or groups. It is a critical process for…
-
All about ticketing tools for DBA?
15 February 2015Ticketing tools improves communications tracking whatever is happening in the environment, reporting that what has happened within the specified duration. A Ticket is an element contained within an issue tracking…
-
Common Backup Issues in SQL Server
24 January 2015SQL Server is a powerful database management system that is used by many companies worldwide. Unfortunately, this system can suffer from backup issues which can lead to serious data loss….
-
Real time Issues and Alerts in SQL Server
5 January 2015Introduction to SQL Server Issues Are you using SQL Server to manage your database operations? If so, then you know first-hand how important it is to keep a close eye…