Just another WordPress.com site

SQL Admin Tips–General

How to schedule a Pause in a SQL Job Execution

We currently have a SQL job which is scheduled to run hourly from 3AM-6PM on a daily basis. During a failover test, we need to stop this job from running from 8AM-1PM on a certain date, 05-12-2018. How can we achieve that?


  1. Copy the current schedule (3am-6pm) to a new schedule, set the end date to be 2018-05-11.

  2. Create 2 new schedules on 2018-05-12, one is from 3AM-8AM, and the other is from 1PM-6PM

  3. Change the current schedule (3am-6pm), set the startdate to be 2018-05-13, and no end date.ScheduleList

  4. We can check the job logs to verify the job execution later, in the message detail we should be able to see what schedule the job is executed against. JobLogs


SQL Server Best Practices



How to use DBCC CheckDB

Link: https://www.sqlcopilot.com/dbcc-checkdb.html

Get size of all tables in database

t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
CAST(ROUND(((SUM(a.total_pages) – SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
sys.tables t
sys.indexes i ON t.OBJECT_ID = i.object_id
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
sys.allocation_units a ON p.partition_id = a.container_id
sys.schemas s ON t.schema_id = s.schema_id
AND t.is_ms_shipped = 0
GROUP BY t.Name, s.Name, p.Rows
ORDER BY 4 desc

Reference Link: https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database

Database Job Roles And Microsoft SQL Server Database Certifications

Database Job Roles and Opportunities:

As you read about the various database certification programs, keep these job roles in mind:

  • Database Administrator (DBA): Responsible for installing, configuring and maintaining a database management system (DBMS). Often tied to a specific platform such as Oracle, MySQL, DB2, SQL Server and others.

  • Database Developer: Works with generic and proprietary APIs to build applications that interact with DBMSs (also platform specific, as with DBA roles).

  • Database Designer/Database Architect: Researches data requirements for specific applications or users, and designs database structures and application capabilities to match.

  • Data Analyst/Data Scientist: Responsible for analyzing data from multiple disparate sources to discover previously hidden insight, determine meaning behind the data and make business-specific recommendations.

  • Data Mining/Business Intelligence (BI) Specialist: Specializes in dissecting, analyzing and reporting on important data streams, such as customer data, supply chain data, transaction data and histories, and others.

  • Data Warehousing Specialist: Specializes in assembling and analyzing data from multiple operational systems (orders, transactions, supply chain information, customer data and so forth) to establish data history, analyze trends, generate reports and forecasts, and support general ad hoc queries.

Careful attention to these database job roles implies two important kinds of information.

First, a good general background in relational database management systems, including an understanding of the Structured Query Language (SQL), is a basic prerequisite for all database professionals.

Second, although various efforts to standardize database technology exist, much of the whiz-bang capability that databases and database applications can deliver come from proprietary, vendor-specific technologies. Most serious, heavy-duty database skills and knowledge are tied to specific platforms, including various Oracle products (such as the open source MySQL environment), Microsoft SQL Server, IBM DB2, so-called NoSQL databases and more.

Microsoft SQL Server Database Certifications

While it is not the No. 1 database platform (that honor goes to Oracle), Microsoft’s SQL Server platform bumped IBM to take second place in DBMS market share (per Gartner’s Magic Quadrant for Operational Database Management Systems, 10/16/2014) and ranks third in overall database engine popularity as of September 2015. SQL Server is particularly popular in organizations that also use Microsoft’s Visual Studio environment for software development. It offers a broad range of tools and add-ons for business intelligence, data warehousing and data-driven applications of all kinds.

That probably explains why Microsoft offers database-related credentials at every level of its certification program, from the Microsoft Technology Associate (MTA) all the way to the Microsoft Certified Solutions Expert (MCSE) program.

The MTA program includes a single database-related exam: Database Fundamentals (98-364).

There is one credential for the Microsoft Certified Solutions Associate(MCSA): Microsoft SQL Server, which requires candidates to pass three exams:

  • Querying Microsoft SQL Server 2012 (70-461)

  • Administering Microsoft SQL Server 2012 Databases (70-462)

  • Implementing a Data Warehouse with Microsoft SQL Server 2012 (70-463)

At the Microsoft MCSE level are two database credentials: Data Platform and Business Intelligence.

The MCSE: Data Platform requires:

  • MCSA: SQL Server 2012

  • Developing Microsoft SQL Server Databases (70-464)

  • Designing Database Solutions for Microsoft SQL Server (70-465)

The MCSE: Business Intelligence requires:

  • MCSA: SQL Server 2012

  • Implementing Data Models and Reports with Microsoft SQL Server (70-466)

  • Designing Business Intelligence Solutions with Microsoft SQL Server (70-467)

In April 2014, Microsoft updated the MCSE exams to include SQL Server 2014 topics; the MCSA exams remained focused on SQL Server 2012. Candidates can download evaluation copies of SQL Server 2012 and SQL Server 2014 to prepare for the exams.

Table 4: Microsoft SQL Server Certification Facts & Figures

Certification Name

MTA Database MCSA: SQL Server MCSE: Data Platform MCSE: Business Intelligence

Prerequisites/Required Courses

MTA Database: None MCSA SQL Server: None MCSE Data Platform and Business Intelligence: MCSA on SQL Server Training courses available and recommended for all certifications but not required.

Number of Exams

MTA: One exam, Database Fundamentals (98-364) MCSA: SQL Server: Three exams: Querying Microsoft SQL Server 2012 (70-461) Administering Microsoft SQL Server 2012 Databases (70-462)  Implementing a Data Warehouse with Microsoft SQL Server 2012 (70-463) MCSE: Data Platform: MCSA on SQL Server plus two exams: Developing Microsoft SQL Server Databases (70-464) Designing Solutions for Server (70-465) MCSE Business Intelligence: MCSA on SQL Server plus two exams: Implementing Data Models and Reports with Microsoft SQL Server (70-466) Designing Business Intelligence Solutions with Microsoft SQL Server (70-467)

Cost per Exam

MTA: $115 USD (or equivalent in local currency outside the USA) MCSA/MCSE: $150 USD (or equivalent) per exam



Self-Study Materials

Microsoft offers the world’s largest and best-known IT certification program, so the MTA, MCSA and MCSE certs are well-supported with books, study guides, study groups, practice tests, and so forth.

Deadlocks caused by Missing Indexes in SQL Server

During our recent dataload, there’re sometimes deadlock occur when load into the a large table eg:Dim_Account, and mostly with the Update script in a “OLE DBCommand”, the error messages are:

[Expire Existing DIM [693]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0”  Hresult: 0x80004005

Description: “Transaction (Process ID 64) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”.

I have tried below ways to resolve the deadlock, but it still happens,

  1. Take off the “Table Lock” in the insert into the “OLE DB Destination”;

  2. Sort the records by SCDAction before the “Conditional Split”;

  3. Use “WITH (ROWLOCK)” hint in the update scripts;

  4. In the OLEDB Connection, set the RetainSameConnect as “True” (a TRUE value for this property can also be useful in managing transactions and reducing the number of recurring connection requests to a server.)

 There are many articles online regarding the deadlocks with “OLE DBCommand”, and it is suggested not to use it if there are big amount of rows to be updated. It is functioned similarly as using a cursor in T-sql which we always avoid using since it is on row-by-row basis and the performance is very poor. Here’s an article: http://bidn.com/blogs/DevinKnight/ssis/91/ssis-avoid-ole-db-command

 However, it turns out the missing of indexes caused the deadlock.


How to shrink tempdb

  1. Get the current configuration for tempDB on the server

    SELECT name, size FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); 
  2. Use DBCC SHRINKFILE to shrink the files, eg, shrink the datafile to 10MG

        DBCC SHRINKFILE(tempdev, 10)