SQL Server is memory intensive database system and most resource intensive operations in SQL Server are DISK IO operations. SQL Server uses memory to minimize disk IO operations by creating a buffer pool where it hold pages read from the...
Today, i am going to discuss about virtual log files in SQL Server and query to find number of virtual log files present in transaction log files. Related Articles: Why Should You Always Turn Off Database Auto Shrink Property? Understand...
Have you deep dive into SQL Server checkpoint process like what checkpoints are and how they work? Here is an article which will explain you about checkpoint process in SQL Server. What is Checkpoint Process in SQL Server? As per...
Today, i will talk about SQL Server Transaction Log File Architecture. As we know, every database must have at least one transaction log file (Tlog file) that captures everything happens on database to maintain data integrity of the database. Logically,...
SQL Server data files, pages & extents are very much related to each other. These are architectural terms which are used to store data physically inside the database. First let’s start with data files then we will understand how data...
Have you ever thought how to protect stored procedure codes deployed on your SQL Server Instance? When deploying applications to a client’s server(s) or to a shared SQL Server, there is often a concern that other people might peek at...
Normally, ASYNC_IO_COMPLETION Wait type can be seen during backup and restore activities. Whenever you will see this wait type your backup/restore process will be in suspended state most of the time because the process is waiting to get IO resource...
Today I was working on an issue in which backup job was showing in executing state since last 2 days.Initially my observation was either it was hung in middle of its process or it was processing very slow. Later, i...
RESOURCE_SEMAPHORE waits occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts. High waits on RESOURCE_SEMAPHORE usually result...
Wait Types are awesome way to troubleshoot performance related issues. In SQL Server 2005 and later version we can get wait type info from various DMVs. One of the most useful DMV is sys.dm_qs_wait_stats.This DMV gives you the detail report...
Generally we don’t need this info for user database but when you have to recover or restore master database then you should have exact database version or build no else you cannot recover your database like i am giving you...
Suspect database in SQL Server is not very common issue but there are times we face it because of various reasons. Such reasons can cause a database to go in suspect mode and become inaccessible for end users. There are...
I saw SQL Server error 14258 today while i was diagnosing my log shipping issue in which primary and standby servers was not in sync. I checked log but i didn’t find anything even SQL Server agent was running and...
Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is...
Whenever you get the issue “Msg 7321” make sure your security context is correctly supplied. Otherwise you will get below error. Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE...
