Top posts
-
sql dba TRACE FLAGS
TRACE FLAGS: -d : fully qualified path of datafile -e : error file -l : log file -c: quick start of instance than regular process -m : sinle user mode -s : sqlserver.exe -n : start instance name then the information will not trace in event viewer -t :refereing...
-
Upgrading SQL Databases From SQL Server
Prerequisites 1) Run upgrade adviser on the databases for the instance you want to upgrade. 2) If any issues are found, talk to your SME for fixing the same. 3)If issues are fixed proceed with the actual upgrade. -- Do check for the other dependencies...
-
Simple recovery modeL IN SQL SERVER
The Simple recovery model if often used in testing and development environments, where data loss is not an issue. The older transactions are marked for reuse and will be overwritten with new ones automatically. In this recovery model, there is a little...
-
How to fix tempdb Full issue ?
How to fix tempdb Full issue ? 1) Since tempdb recovery model is simple , Tempdb log will not grow drastically unless open transactions are there if so we will kill that spid and then shrink it 2) If mdf file is full we can shrink that but sometimes we...
-
SQL Server System Databases
Master Purpose - Core system database to manage the SQL Server instance. In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema. In SQL Server 2000 and previous editions of SQL Server, the Master...
-
Reading the SQL Server log files using TSQL
Reading the SQL Server log files using TSQL This is a sample of the stored procedure for SQL Server 2005. You will see that when this gets called it calls an extended stored procedure xp_readerrorlog. CREATE PROC [sys].[sp_readerrorlog]( @p1 INT = 0,...
-
Backup Compression in SQL Server
Backup Compression in SQL Server s Microsoft has introduced backup compression feature from SQL Server 2008. This feature is very useful to save the time, reduces device I/O and storage cost and it is also a good backupstrategy option. You can configure...
-
Backup All System Databases
Backup All System Databases If you want to backup all system databases master, model and msdb then use below SQL script. DECLARE @DB VARCHAR(20) DECLARE @BkpFName VARCHAR(100) DECLARE @BkpFDate VARCHAR(50) SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50),...
-
Activity Monitor in SQL Server 2012
Activity Monitor in SQL Server 2012 Activity Monitor is an inbuilt monitoring tool which comes with SQL Server Management Studio. This monitoring tool provides real-time information about processes, resource waits, data file I/O and recent expensive queries...
-
SQL Scalar functions
SQL Scalar functions SQL scalar functions return a single value, based on the input value. Useful scalar functions: UCASE() - Converts a field to upper case LCASE() - Converts a field to lower case MID() - Extract characters from a text field LEN() -...
-
What are the DMV's in SQL Server ?
What are the DMV's in SQL Server ? Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. Important Dynamic management views and functions...
-
Which Trace flag is used in Mirroring?
Which Trace flag is used in Mirroring? Trace flags are used to temporarily set specific server characteristics or to switch off/on a particular behavior. 1400 Trace flag is used in mirroring. To set trace flag for Database mirroring:- Configuration Manager...
-
What are the Operating Modes and explain them?
What are the Operating Modes and explain them? a. High Availability (principle+mirror+witness) :- High-availability mode, runs synchronously. Requires a Witness Server instance. The Principal server sends the log buffer to the mirror server, and then...
-
How to monitoring Mirroring?
How to monitoring Mirroring? There are six methods are available for monitoring the Database Mirroring a) Database Mirroring Monitor:- Database Mirroring Monitor is a GUI tool that shows update status and to configure warning thresholds. To open DM Monitor:-...
-
Database Mirroring states
What are the Database Mirroring states? 1) SYNCHRONIZING:- The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to...
-
What are the uses of Replication?
What are the uses of Replication? a) Server to Server Replication:- 1. Improving scalability and availability 2. Data warehousing and reporting 3. Integrating data from multiple sites 4. Integrating heterogeneous data 5. Offloading batch processing b)...
-
What are the common scenarios to Restoring the Data?
What are the common scenarios to Restoring the Data? 1) To restore the lost and corrupted data after a system failure 2) To restore a database onto a development system for user by application developers while developing and testing new code. 3) To restore...
-
Explain about Tail of Log? Give the syntax?
Explain about Tail of Log? Give the syntax? The tail-log backup is the last backup that is used to recover the database to the point of failure (supports only Full or Bulk-logged recovery models). (e.g. Assume that 4.00 P.M. log backup is performed and...
-
Full, Bulk-Logged & Simple Recovery Models
Explain Full, Bulk-Logged & Simple Recovery Models? Based on the importance of the data, recovery models configured to the Database. a) Full:- Every transaction logged into transaction log file.we can recover the data up to point in time.all production...
-
Queue Reader Agent
Queue Reader Agent - The Queue Reader Agent is used with transactional replication with the queued updating option. The agent runs at the Distributor and moves changes made at the Subscriber back to the Publisher. Unlike the Distribution Agent and the...
-
Merge Agent
Merge Agent - The Merge Agent is used with merge replication. It applies the initial snapshot to the Subscriber and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher...
-
How to set Recovery models using T_SQL?
How to set Recovery models using T_SQL? a) To Check current recovery model:- SELECT DATABASEPROPERTYEX('ADVENTUREWORKS', 'RECOVERY') As [Recovery Model] b) To set Simple Recovery model:- ALTER DATABASE ADVENTUREWORKS SET RECOVERY SIMPLE c) To set Bulk-Logged...
-
Distribution Agent
Distribution Agent - The Distribution Agent is used with snapshot replication and transactional replication. It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers. The Distribution Agent...
-
SQL Server replication - Interview questions
What is the best way to update data between SQL Servers? What are the scenarios you will need multiple databases with schema? How will you plan your replication? What are publisher, distributor and subscriber in “Replication”? What is “Push” and “Pull”...
-
SQL server agent
SQL Agent SQL agent, also known as SQL server agent, is a Microsoft SQL server relational database management system(RDBMS) background tool. SQL agent allows the database administrator (DBA) to schedule automated execution jobs, as well other management...