March 28, 2017

What is LSN chain in SQL

What is LSN chain in SQL

Every trasaction in sql server is identified by lsn number .means log sequence number

If LSN is broken then u may not able to restore it. Hope LSN needed in order to perform restoration.

LSN means log sequence number. Log file consists of internally log sequence number. If LSN is miss match we can't to restore data at point of time.

We can restore For e.g you have 1 full back and 4 log files, suppose if ur LSN broken on 3rd log file, then we can restore up to log file 2 rest all we can't restore it.

Certifications options for IT Person

E-Business Suite Manufacturing,
E-Business Suite E-Business Suite Manufacturing,
E-Business Suite Procurement,
E-Business Suite Project Management,
E-Business Suite Supply Chain Management,
-Business Suite Tools and Technology

Cisco Certification,
sales force,
tera data,
Oracle 11g/12c(OCA,OCP),
E-Business Suite,
E-Business Suite Financial Management,
E-Business Suite Human Capital Management,
Oracle Endeca Commerce,
Fusion Applications,
Fusion Governance Risk and Compliance,
Data Relationship Management,
Hyperion Financial Management
JD Edwards EnterpriseOne,
JD Edwards Financial Management,
JD Edwards Project Management,
JD Edwards Supply Chain Execution (Logistics),
JD Edwards Tools and Technology
Manufacturing and Engineering,
ATG Web Commerce,
ATG Developer,
Agile Product Lifecycle Management,
CRM On Demand,
Oracle CRM On Demand,
Oracle Commerce (ATG, Endeca),
Oracle Commerce Developer,
Oracle Global Trade Management,
Oracle Global Trade Management,
Oracle Policy Automation,
Oracle Policy Modeling,
Oracle Transportation Management,
Oracle Transportation Management,
Oracle Unified Method,
Oracle Unified Method,
PeopleSoft Campus Solutions,
PeopleSoft Financial Management,
PeopleSoft Human Capital Management,
PeopleTools - Tools and Technology,
Primavera Contract Management,
Primavera P6 Enterprise Project Portfolio Management,
Primavera Portfolio Management,
Primavera Unifier (Skire),
CRM Applications,
Oracle Customer Hub,
Oracle iStore,
User Productivity Kit (UPK),
Oracle User Productivity Kit,
Java Cloud,
Oracle Java Cloud Service,
Oracle Customer Experience Cloud,
Oracle CPQ Cloud,
Oracle Marketing Cloud,
Oracle Sales Cloud,
Oracle Service Cloud,
Oracle Database Cloud,
Oracle Database Cloud,
oracle Enterprise Resource Planning Cloud,
Oracle Financials Cloud,
Oracle Procurement Cloud,,
Oracle Project Portfolio Management Cloud,
Oracle Human Capital Management Cloud,
Oracle Global Human Resources Cloud,
Oracle Talent Management Cloud,
Oracle Taleo ,
Oracle Taleo Recruiting,
Oracle Supply Chain Management Cloud,
Oracle Product Master Data Management Cloud,
Order Management Cloud,
Database Application Development,
Oracle Application Express (Oracle APEX),
MySQL Database Administration,
MySQL Developer,
Oracle Database,
Database Cloud,
Oracle Database 11g,
Oracle Database 12c,
Oracle Spatial 11g,
Enterprise Management,
Oracle Application Testing Suite,
Application Quality Management,
Oracle Enterprise Manager,
Enterprise Manager,
Oracle IT Architecture,
Oracle Technical Architecture,
Project Lifecycle Management,
Lifecycle Management,
Oracle Communications
Billing and Revenue Management
Order and Service Management,
Service Delivery,
Session Border Control,
Oracle Financial Services,
Analytical Applications,
Financial Crime and Compliance Management,
Oracle Financial Services Analytical Applications Infrastructure,
Revenue Management and Billing,
Oracle Health Sciences,
Oracle Argus,
Oracle Life Sciences Data Hub,
Oracle Insurance,
Policy Administration,
Merchandise Operations Management,
Merchandise Planning and Optimization
Retail Technology Group
Supply Chain,
Oracle Tax Applications,
Oracle Public Sector Revenue Management,
Oracle Utilities,
Customer Care and Billing,
Meter Data Management,
Mobile Workforce Management,
Smart Grid Gateway,
Java and Middleware,
Business Intelligence,
BI Applications,
BI Enterprise Edition (BI EE),
Endeca Information Discovery,
Cloud Application Foundation,
Oracle Cloud Application Foundation,
WebLogic Server,
Data Integration,
Data Integrator (ODI),
Identity Management
Access Manager,
Identity Manager.
Java EE,
Java SE,
engineered Systems
Big Data Appliance
xalogic Elastic Cloud
Server Administration,
NAS Administration,
Oracle Flash Storage System,
Pillar Axiom 600,
Tape Storage Administration,
Desktop Virtualization,
Oracle Virtual Desktop Infrastructure,
Server Virtualization,
Oracle VM,
IBM Certifications,
SAS Global Certifications.
Middleware Development Tools,
Forms and Reports,
Mobile Application Framework,

Oracle Linux,
Oracle Linux Administration
Oracle Solaris
Solaris 10 Administration
Solaris 11 Administration

Oracle Mobile Security,
SOA and BPM,
SOA Suite,
WebCenter Content,
WebCenter Portal,
WebCenter Sites,
Operating Systems,

Activities That Cause a Checkpoint in SQL Server

Checkpoints occur in the following situations:

1. A CHECKPOINT statement is explicitly executed. A checkpoint occurs in the current database for the connection.

2. A minimally logged operation is performed in the database; for example, a bulk-copy operation is performed on a database that is using the Bulk-Logged recovery model.

3. Database files have been added or removed by using ALTER DATABASE.

4. An instance of SQL Server is stopped by a SHUTDOWN statement or by stopping the SQL Server (MSSQLSERVER) service. Either action causes a checkpoint in each database in the instance of SQL Server.

5. An instance of SQL Server periodically generates automatic checkpoints in each database to reduce the time that the instance would take to recover the database.

6. A database backup is taken.

7. An activity requiring a database shutdown is performed. For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

8. if we have large size dB and rebuild, re-index jobs in this Database are stopped in between it trigger checkpoint processes .

Max degree of parallelism Property in sql server

Question -What is  max degree of parallelism in SQL server and how can we find the parallelism which has been in SQL server


When we execute a query number of processes use in parallel execution

Parallelism is to execute a query Some process is required if the process required more time to execute the query. So we have to enable parallelism. In parallelism we mention max degree of parallelism is number of processes is you are allowed to execute that query nothing else.

When this parallelism is applied to a query that query cost reach to cost threshold for parallelism by default its value is zero.

You can set it to 1 or another number based on hardware configuration for Memory.


sp_configure 'show advanced options', 1;
sp_configure 'max degree of parallelism', 1;

March 26, 2017

Decrease SQL Server Database and Table Size with simple steps

In this clip shows you Practical Tips to Reduce SQL Server Database annd Table Size .

The  Steps are provided but process is taking time so final result not shown . You can check from your end by applying steps and it will be for your practice exercise also.

How to Shrink MDF,LDF Database Data  and log File in SQL Server and How to check size of all tables in database.

Script is  provided to get all table records count,used space,unused space etc.

  You can check single as well as multiple table details by checking query in script.

If you clean log files before database shrinking then you will able to decrease more size for database.

Database size increased due to daily transaction by users and log file size getting huge so you can see this clip to reduce the size of database to improve disk space.

You can also set batch job to do in automatically based on time frame you given for batch job in SQL.

You can visit below online clip to get more details.

If you like this clip do not forget to subscribe it to get more updates on SQL database administration.