Archive for the ‘SQL server DBA administration’ Category

Common DBA project Challenge along with resolution


To me the biggest blunder is knowing that a problem exists and either ignoring it or procrastinating on the implementing the resolution. . This tip focuses on Common DBA project Challenge that could have been prevented.


The reality is that nothing is perfect and as technical professionals we need to build a realistic solution with the time and budget available, then communicate any potential issues to the business so they are aware of them.

To deliver better application performance, DBAs should consider the following tips:

  • Be proactive and align behind end-user experience as a shared objective across the entire IT organization by looking at application performance and the impact that the database has on it continuously, not only when it becomes a major problem.
  • Measure performance based not on an infrastructure resources perspective, but on end-user wait times. Wait-time analysis gives DBAs a view into what end-users are waiting for and what the database is waiting for, providing clear visibility into bottlenecks.
  • Implement monitoring tools that provide visibility across the entire application stack, including all the infrastructure that supports the database – virtualization layers, database servers, hosts, storage systems, networks, etc.
  • Establish historic baselines of application and database performance that look at how applications performed at the same time on the same day last week, and the week before that, to detect any anomalies before they become larger problems.
  • Have a common set of goals, metrics and SLAs across all databases, ideally based on application response times, not only uptime.
  • Use tools that provide a single dashboard of performance and the ability to drill down across database technologies and deployment methods, including cloud.
  • Document a consistent set of processes for ensuring integrity and security: backup and restore processes, encryption at rest and on transit, detection of anomalies and potential security events in logs, to name a few.
  • Establish a strategy, roadmap, and guidelines for moving to the cloud (or not) and for reducing workload costs by moving databases to lower-license-cost versions or open-source alternatives.
  • Make sure team members can escape firefighting mode and spend enough time proactively optimizing performance of the databases and taking care of important maintenance tasks, which can result in significant cost savings and prevent problems in the future.

Database project Operations issue:

  • All senior team members on vacation – When you have a major deployment make sure to have your key staff members on site and available to meet the project needs. Do not fool yourself that a junior team member will be able to just push a button and deploy a solution, especially when a minimal amount of testing is conducted. When a problem arises it is the senior team member’s knowledge and expertise that is needed to quickly resolve issues. If all of those people are out on the beach and will be back next week, it makes sense to wait a week for the deployment to have your team onsite and available to address any unexpected issues.
  • Putting all of your eggs in 1 basket – When you work through an enterprise upgrade whether it is an application or hardware firmware, do not upgrade all of the systems (including the DR site) at once. Take a step back and be sure to have some systems that are out of sync for a short period of time to be migrate to a known stable platform in case an unexpected issue arises.
  • Not validating backups on a daily basis – If a serious issue occurs, make sure you have a solid last line of defense. That is a consistent and reliable set of backups on a daily basis. In addition, make sure your backup plan includes retiring tapes on a weekly, monthly or quarterly basis to be able to rollback to some point in time rather than going out of business. Also check-in with the business to ensure backups are not needed for legal or regulatory needs.
  • Not changing passwords – As an administrator you have the keys to the kingdom and need to recognize the responsibility that you have. As such, make sure your passwords are complex, change them frequently and do not share your passwords.
  • Password expiration – This is almost the opposite of the previous bullet. With SQL Server 2005 password policies can be setup for standard logins so the passwords expire and accounts get locked out. When this happens your applications will not be accessible if one of these accounts are in use. As such, setting password expiration is a good idea, just be sure to change the password and coordinate the change with your team.
  • Letting the primary file group fill up – With the rate of data growth, be sure to either cap your database size or monitor the size on a daily, weekly or monthly basis or permit your databases to automatically grow. In either circumstance, be sure to watch your disk space so that you do not fill up your disk and then have 2 problems (full file group and full disk drive).
  • Hot data centers – High temperatures mean failure for servers. The failure could be a controller card or a disk drive, but one sustained spike in the room temperature could be a critical problem that is not fully realized for a three to six month time period. Make sure your temperature is properly regulated, has a backup conditioning system and can notify your team before an issue arises.

Hosting DBA service with SQL Server 2014

March 19, 2017 Leave a comment

The database industry has seen significant change over the last 5 years, from the introduction of new technologies like clustering, big data, and in-memory data platforms to the recent variants of popular open source SQL alternatives like Percona Server and MariaDB making their way into popular technology stacks. Even with these new technologies becoming increasingly common, many enterprises still trust their mission critical database operation to Microsoft SQL Server. Recently, Microsoft released its latest iteration of the technology, Microsoft SQL Server 2014, with notable advancements in performance including in-memory functionality and added security with high availability.


In-Memory OLTP

As one of the first to market with support for this new functionality, SmartERP is focused on increasing database performance by allowing some data to be queried and processed in RAM instead of on constrained disk resources. Previous technology forced users to load ALL information into RAM, making large data sets problematic. Now, database administrators can choose which data tables to process in-memory, allowing those operations to achieve maximum priority to valuable processing resources. By speeding up the overall performance of query operation across a database environment, users can effectively do more with less resources, which eliminates the need to scale into expensive hardware solutions. SmartERP teams are already helping users plan for their migration to SQL Server 2014 by evaluating target data sets to load into memory.

High Availability

While replication and failover have long been part of any core strategy for business continuity, achieving true high availability for a database environment has been problematic. Organizations often must decide what level of inconsistency between database environments is acceptable – hours or days? However, with a new functionality called AlwaysOn, and strong connections between multi-cloud datacenters including ones belonging to SmartERP and Microsoft Azure, users can now implement nearly 100 percent resilient database environments that protect from both database operational failure and datacenter availability. Even though SmartERP has an industry leading record in uptime and a 100 percent uptime SLA, we want our users to meet their unique availability requirements, which may include a highly available database environment. We can help you meet those requirements with a team of DBA’s that have successfully implemented high availability for many customers. Check out this report on our support of AlwaysOn for more information.

DBA Services

Recently, we shared with you the economic benefits of our DBA services offering, including a full time DBA available 24×7 at a fraction of the cost of an in-house resource. We have seen growing interest in our DBA coverage and released a tiered offering to accommodate a variety of supplemental DBA needs. We have also witnessed some organizations relying solely on our DBA services to ensure database functionality, while others have formed close partnerships with their core database team and our DBA service. This allows our customers to focus on important development and optimization efforts without the burden of maintaining and monitoring the health of the existing database. Moving between any database iteration requires some thought and planning. Our DBA team is helping customers understand the scope of work and code change required to not only migrate, but also utilize new functionality. Our partnership with Microsoft ensures that our DBA Services team is ready to address even the more challenging scenarios when switching.

Flexible Licensing

At SmartERP, customers can consume fully managed SQL application licensing along with support at a low monthly cost. This allows users to move in-between SQL versions and hardware profiles without needing to address their software license agreement. We want to make sure you always have the best combination of optimized hardware and SQL versioning to meet your objectives.

Hybrid Potential

Lastly, while many web applications are moving to the cloud, many database users still require the performance consistency and controls of a dedicated server. Often, users partnering with a single cloud or managed hosting provider will be forced to make sacrifices by either not moving applications and web assets to the cloud because of database restrictions or sacrificing database performance and reliability by trying to operate it in a utility cloud service. With a Managed Private Cloud powered by Microsoft Cloud Platform, and the built in Database-as-a-Service capability, customers can take advantage of a fully managed, physically isolated, private cloud environment that allows customers to programmatically spin up SQL Server 2014 databases on demand. Customers can also strengthen their high availability and disaster recovery capabilities by choosing from a number of available destinations, including Microsoft Azure, as a designated disaster recovery location.
SmartERP can bridge public and private clouds running SQL Server over a strong network connection. It allows users to leverage both cloud and dedicated server resources in a single environment. Dominoes UK spoke about their use of hybrid to serve web content via the cloud while still having a reliable dedicated database in dedicated hosting. You can read more about that here.

SmartERP DBA services professionals are ready to help you wrap your brain around the cool new features in Microsoft SQL Server 2014. If you want to know more, please download our whitepaper and visit

SQL Server DBA Support

March 10, 2017 Leave a comment

SQL Server DBA Support


  • Proactive Problem management
  • Predictive capacity analysis
  • Intuitive event and incident correlation

Apart from this our service catalogue comprise of regular DBA activities like Monitoring, Backups, Space Management, Resource Utilization, License Rationalization, patch management Database Vulnerability Assessment, Object Migration and other activities. Other activities include:

  • Manage hierarchical security (access control, permissions, encryption keys and certificates) for databases and ensure data integrity using templates and schemas
  • Testing of new releases and features for application, O/S and Database upgrade & Troubleshoot application performance & Optimize query& index performance.
  • Designed & Tested a Scale-Out Architecture using Replication, DTC / 2PC, Distributed Database  with Data & Middle Tier, Web & Client Tier  for SQL Server Databases to facilitate a High-Availability environment by creating Read-Only copies for Reporting Services & Read-Write Hot copies of critical Databases, for Hot Instant Disaster Recovery.
  • Enhanced performance, scalability and usability across multiple cores & support that efficiently scale computer, networking and storage in both physical and virtual environments.
  • Establishes and maintains policies, standard operating procedures, and associated documentation for user interaction with the database environment.
  • Support on-premises to Microsoft Azure IaaS , PaaS & amazon RDS, Hybrid Scenarios with SSIS, Stretch Database, PowerBI with on-premises data.

Revenue and Risk Reward Program:


  • SLAs based on Risk Reward –Example. Availability of 99.95%
  • 10% cost reduction year over year during contractual period
  • Technology Consumption Management to ensure customer uses licenses/servers to the minimal and reduce OPEX costs.

SmartERP SQL server Portfolio

Six Basic Fears

March 29, 2015 Leave a comment

There are six Basic fears that we are all suffer from. ( Quote from Reach me)

The most common is fear of going broke
The fear of criticisms
Fear of failure of not living up to your expectation yourself of not being who you want to be
Fear of abandonment
Fear of loosing loved one
Fear of sickness
Fear of dying

Some Important Trace in Microsoft SQL server

October 28, 2014 Leave a comment

A trace flag is a directive used to “set specific server characteristics or to switch off a particular behavior”. Here I have listed some Important Trace  which can be useful as per the environment.


Trace Flag 834

• Trace flag 834 allows SQL Server 2005 to use large-page allocations for the memory that is allocated for the buffer pool. 

• May prevent the server from starting if memory is fragmented and if large pages cannot be allocated

• Best suited for servers that are dedicated to SQL Server 2005

• Page size varies depending on the hardware platform

• Page size varies from 2 MB to 16 MB. 

• Improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU

• Only applies to 64-bit architecture

• Startup

• Documented: KB920093

• Now automatic:

• Enterprise / Developer Edition

• “Lock Pages in Memory” privilege

• >= 8GB RAM


Trace Flag 835

• Trace flag 835 enables “Lock Pages in Memory”  support for SQL Server Standard Edition

• Enables SQL Server  to use AWE APIs for buffer pool allocation

• Avoids potential performance issues due to trimming working set

• Introduced in:

• SQL Server 2005 Service pack 3 Cumulative Update 4

• SQL Server 2008 Service Pack 1 Cumulative Update 2

• Only applies to 64-bit architecture

• Startup

• Documented: KB970070


Trace Flag 3226

• Trace flag 3226 prevents successful back operations from being logged

• By default SQL Server logs every successful backup operation to the ERRORLOG and

the System event log

• Frequent backup operations can cause log files to grow and make finding other

messages harder


Documented: BOL


Trace Flag 806

• Trace Flag 806 enables DBCC audit checks to be performed on pages to test for logical consistency problems. 

• These checks try to detect when a read operation from a disk does not experience any errors but the read operation returns data that is not valid. 

• Pages will be audited every time that they are read from disk

• Page auditing can affect performance and should only be used in systems where data Stability is in question.


Documented: KB841776


“SQL Server I/O Basics, Chapter 2” white paper


Trace Flag 818


• “Trace flag 818 enables an in-memory ring buffer that is used for tracking the last 2,048

successful write operations that are performed by the computer running SQL Server, not

including sort and workfile I/Os”

• Use to further diagnose operating system, driver, or hardware problems causing lost write

conditions or stale read conditions

• May see data integrity-related error messages such as errors 605, 823, 3448.

• Documented: KB826433



Trace Flag 3422

• Trace Flag 3422 enables log record auditing

• “Troubleshooting a system that is experiencing problems with log file corruption may be easier using the additional log record audits this trace flag provides”

• “Use this trace flag with caution as it introduces overhead to each transaction log record”

• Similarly to trace flag 806, you would only use

this to troubleshoot corruption problems




“SQL Server I/O Basics, Chapter 2” white paper


Trace Flag 2528


• Trace flag 2528 disables parallel checking of

objects during DBCC CHECKDB, DBCC


• Scope: Global | Local

• Documented: BOL

• Typically leave parallel DBCC checks enabled

• DBCC operations can dynamically change their

degree of parallelism

• Alternatives:


• MAXDOP option

• Resource Governor



Trace Flag 1224


• Trace flag 1224 disables lock escalation based on the number of locks

• Memory pressure can still trigger lock escalation

• Database engine will escalate row or page locks to table locks

• 40% of memory available for locking

• sp_configure ‘locks’

• Non-AWE memory

• Scope: Global | Session

• Documented: BOL


Trace Flag 1211


• Trace flag 1211 disables lock escalation based on memory pressure or number of locks

• Database engine will not escalate row or page locks to table locks

• Scope: Global | Session

• Documented: BOL

• Trace flag 1211 takes precedence over 1224

• Microsoft recommends using 1224

 • Trace flag 1211 prevents escalation in every case, even under

memory pressure

• Helps avoid “out-of-locks” errors when many locks are being used.

• Can generate excessive number of locks

• Can slow performance

• Cause 1204 errors


Trace Flag 1118

• Trace flag 1118 directs SQL Server to allocate full extents to each tempdb objects (instead of mixed


• Less contention on internal structures such as SGAM pages

• Story has improved in subsequent releases of SQL


• So represents a “edge case”

Scope: Global

Documented: KB328551, KB936185

Working with tempdb in SQL Server 2005 white paper


Trace Flag 4199 In SQL server 2008

October 28, 2014 Leave a comment

We have received the below error where it is indicate the database is corrupted but we have received the alert for Tempdb.



Error DESCRIPTION:           The Database ID 2, Page (6:1896), slot 0 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.





To resolve the issue we have to enable the Trace Flag 4199.To ensure this trace flag will always be set, you need to modify the startup properties of your SQL Server Windows Service so that you specify the -T4199 parameter as follows:


Trace Flag 4199         /* IMPORTANT */


• Trace flag 4199 enables all the fixes that were previously made for the query processor under many trace flags

• Policy:

• Any hotfix that could potentially affect the execution plan of a query must be controlled by a trace flag

• Except for fixes to bugs that can cause incorrect results or corruption

• Helps avoid unexpected changes to the execution plan

• Which means that virtually everyone is not necessarily running SQL Server with all the latest query processor fixes enabled?

• Scope: Session | Global


Documented: KB974006

Microsoft are strongly advising not to enable this trace flag unless you are affected