SQL SERVER – Detecting Corruption with Suspect Pages Table – Notes from the Field #043

[Note from Pinal]: This is a new episode of Notes from the Fields series. I often hear about database corruption and I like to stay away from the problems related to corruption. When database gets corrupted, there are not enough expert out in the world who can fix it properly to its original state. John who is my good friend explains a very interesting situation in this blog post where we can learn about corruption with the help of suspect pages table. It is indeed a great tip for everyone to learn even though your database is not corrupted.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces related to Detecting Corruption with Suspect Pages Table. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.

When I get asked to review a database server instance, a very important check that I perform is to review if corruption has occurred with the existing databases. In today’s tip from the field we are going to go over a simple query that will identify existing corrupted pages without running DBCC CHECKDB.

In SQL Server you will not be notified of corruption until an attempt to read a corrupt page occurs. Therefore a page could have been corrupted weeks ago and no one would have noticed if the page was never accessed since the corruption occurred. We strongly recommend using an automated schedule to check database integrity by running DBCC CHECKDB.

DBCC CHECKDB is a very I/O intensive operation so you shouldn’t run the command at will. It should be executed during defined maintenance windows. Today, you are going to see that we can track down corrupt pages with the query below.

SELECT * FROM msdb.dbo.suspect_pages

Ideally, the result set will return zero rows. Zero rows means that no pages have been identified as being corrupt. It doesn’t guarantee that you don’t have corrupt pages. It just means no corrupt pages have been accessed. If you have results then you just identified pages within database files that have a history of corruption. The error count will let you know how many times the page(s) have been accessed since it was added to the suspect pages table. The event type column will identify the type of corruption.

NOTE: The suspect_pages table in msdb database will only keep 1,000 pages. Hopefully you will not have any rows yet alone more than 1,000 corrupt pages. If you do have more than 1,000 than you would only see the last 1,000 pages in this table.

Keep in mind that the suspect_pages table will only include marked corrupted pages. Corrupt pages are only classified as marked if they have been accessed since the corruption occurred. This is why you need to include DBCC CHECKDB in your maintenance plans.


  1. Be proactive and monitor suspect_pages table in msdb database
  2. Implement DBCC CHECKDB in your maintenance plans.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Backing Up and Recovering the Tail End of a Transaction Log – Notes from the Field #042

[Notes from Pinal]: The biggest challenge which people face is not taking backup, but the biggest challenge is to restore a backup successfully. I have seen so many different examples where users have failed to restore their database because they made some mistake while they take backup and were not aware of the same. Tail Log backup was such an issue in earlier version of SQL Server but in the latest version of SQL Server, Microsoft team has fixed the confusion with additional information on the backup and restore screen itself. Now they have additional information, there are a few more people confused as they have no clue about this. Previously they did not find this as a issue and now they are finding tail log as a new learning.

Linchpin People are database coaches and wellness experts for a data driven world. In this 42nd episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains in a very simple words, Backing Up and Recovering the Tail End of a Transaction Log.

Many times when restoring a database over an existing database SQL Server will warn you about needing to make a tail end of the log backup. This might be your reminder that you have to choose to overwrite the database or could be your reminder that you are about to write over and lose any transactions since the last transaction log backup.

You might be asking yourself “What is the tail end of the transaction log”. The tail end of the transaction log is simply any committed transactions that have occurred since the last transaction log backup. This is a very crucial part of a recovery strategy if you are lucky enough to be able to capture this part of the log.

Most organizations have chosen to accept some amount of data loss. You might be shaking your head at this statement however if your organization is taking transaction logs backup every 15 minutes, then your potential risk of data loss is up to 15 minutes. Depending on the extent of the issue causing you to have to perform a restore, you may or may not have access to the transaction log (LDF) to be able to back up those vital transactions.

For example, if the storage array or disk that holds your transaction log file becomes corrupt or damaged then you wouldn’t be able to recover the tail end of the log. If you do have access to the physical log file then you can still back up the tail end of the log. In 2013 I presented a session at the PASS Summit called “The Ultimate Tail Log Backup and Restore” and have been invited back this year to present it again.

During this session I demonstrate how you can back up the tail end of the log even after the data file becomes corrupt. In my demonstration I set my database offline and then delete the data file (MDF). The database can’t become more corrupt than that. I attempt to bring the database back online to change the state to RECOVERY PENDING and then backup the tail end of the log. I can do this by specifying WITH NO_TRUNCATE. Using NO_TRUNCATE is equivalent to specifying both COPY_ONLY and CONTINUE_AFTER_ERROR. It as its name says, does not try to truncate the log. This is a great demo however how could I achieve backing up the tail end of the log if the failure destroys my entire instance of SQL and all I had was the LDF file?

During my demonstration I also demonstrate that I can attach the log file to a database on another instance and then back up the tail end of the log. If I am performing proper backups then my most recent full, differential and log files should be on a server other than the one that crashed. I am able to achieve this task by creating new database with the same name as the failed database. I then set the database offline, delete my data file and overwrite the log with my good log file. I attempt to bring the database back online and then backup the log with NO_TRUNCATE just like in the first example.

I encourage each of you to view my blog post and watch the video demonstration on how to perform these tasks. I really hope that none of you ever have to perform this in production, however it is a really good idea to know how to do this just in case. It really isn’t a matter of “IF” you will have to perform a restore of a production system but more of a “WHEN”. Being able to recover the tail end of the log in these sever cases could be the difference of having to notify all your business customers of data loss or not.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – ​Finding Out What Changed in a Deleted Database – Notes from the Field #041

[Note from Pinal]: This is a 41th episode of Notes from the Field series. The real world is full of challenges. When we are reading theory or book, we sometimes do not realize how real world reacts works and that is why we have the series notes from the field, which is extremely popular with developers and DBA. Let us talk about interesting problem of how to figure out what has changed in the DELETED database. Well, you think I am just throwing the words but in reality this kind of problems are making our DBA’s life interesting and in this blog post we have amazing story from Brian Kelley about the same subject.

In this episode of the Notes from the Field series database expert Brian Kelley explains a how to find out what has changed in deleted database. Read the experience of Brian in his own words.

Sometimes, one of the hardest questions to answer is, “What changed?” A similar question is, “Did anything change other than what we expected to change?”

The First Place to Check – Schema Changes History Report:

Pinal has recently written on the
Schema Changes History report
and its requirement for the Default Trace to be enabled. This is always the first place I look when I am trying to answer these questions.

There are a couple of obvious limitations with the Schema Changes History report. First, while it reports what changed, when it changed, and who changed it, other than the base DDL operation (CREATE, ALTER, DELETE), it does not present what the changes actually were. This is not something covered by the default trace. Second, the default trace has a fixed size. When it hits that
size, the changes begin to overwrite. As a result, if you wait too long, especially on a busy database server, you may find your changes rolled off.

But the Database Has Been Deleted!

Pinal cited another issue, and that’s the inability to run the Schema Changes History report if the database has been dropped. Thankfully, all is not lost.

One thing to remember is that the Schema Changes History report is ultimately driven by the Default Trace. As you may have guess, it’s a trace, like any other database trace. And the Default Trace does write to disk. The trace files are written to the defined LOG directory for that SQL Server instance and have a prefix of log_:

Therefore, you can read the trace files like any other.

Tip: Copy the files to a working directory. Otherwise, you may occasionally receive a file in use error.

With the Default Trace files, if you ask the question early enough, you can see the information for a deleted database just the same as any other database.

Testing with a Deleted Database:

Here’s a short script that will create a database, create a schema, create an object, and then drop the database. Without the database, you can’t do a standard Schema Changes History report.

USE DeleteMe;

This sets up the perfect situation where we can’t retrieve the information using the Schema Changes History report but where it’s still available.

Finding the Information:

I’ve sorted the columns so I can see the Event Subclass, the Start Time, the Database Name, the Object Name, and the Object Type at the front, but otherwise, I’m just looking at the trace files using SQL Profiler. As you can see, the information is definitely there:

Therefore, even in the case of a dropped/deleted database, you can still determine who did what and when. You can even determine who dropped the database (loginame is captured). The key is to get the default trace files in a timely manner in order to extract the information.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SSIS Parameters in Parent-Child ETL Architectures – Notes from the Field #040

[Notes from Pinal]: SSIS is very well explored subject, however, there are so many interesting elements when we read, we learn something new. A similar concept has been Parent-Child ETL architecture’s relationship in SSIS.

Linchpin People are database coaches and wellness experts for a data driven world. In this 40th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to understand SSIS Parameters in Parent-Child ETL Architectures.

In this brief Notes from the Field post, I will review the use of SSIS parameters in parent-child ETL architectures.

A very common design pattern used in SQL Server Integration Services is one I call the parent-child pattern.  Simply put, this is a pattern in which packages are executed by other packages.  An ETL infrastructure built using small, single-purpose packages is very often easier to develop, debug, and troubleshoot than large, monolithic packages.  For a more in-depth look at parent-child architectures, check out my earlier blog post on this topic.

When using the parent-child design pattern, you will frequently need to pass values from the calling (parent) package to the called (child) package.  In older versions of SSIS, this process was possible but not necessarily simple.  When using SSIS 2005 or 2008, or even when using SSIS 2012 or 2014 in package deployment mode, you would have to create package configurations to pass values from parent to child packages.  Package configurations, while effective, were not the easiest tool to work with.  Fortunately, starting with SSIS in SQL Server 2012, you can now use package parameters for this purpose.

In the example I will use for this demonstration, I’ll create two packages: one intended for use as a child package, and the other configured to execute said child package.  In the parent package I’m going to build a for each loop container in SSIS, and use package parameters to pass in a value – specifically, a ClientID – for each iteration of the loop.  The child package will be executed from within the for each loop, and will create one output file for each client, with the source query and filename dependent on the ClientID received from the parent package.

Configuring the Child and Parent Packages

When you create a new package, you’ll see the Parameters tab at the package level.  Clicking over to that tab allows you to add, edit, or delete package parameters.

As shown above, the sample package has two parameters.  Note that I’ve set the name, data type, and default value for each of these.  Also note the column entitled Required: this allows me to specify whether the parameter value is optional (the default behavior) or required for package execution.  In this example, I have one parameter that is required, and the other is not.

Let’s shift over to the parent package briefly, and demonstrate how to supply values to these parameters in the child package.  Using the execute package task, you can easily map variable values in the parent package to parameters in the child package.

The execute package task in the parent package, shown above, has the variable vThisClient from the parent package mapped to the pClientID parameter shown earlier in the child package.  Note that there is no value mapped to the child package parameter named pOutputFolder.  Since this parameter has the Required property set to False, we don’t have to specify a value for it, which will cause that parameter to use the default value we supplied when designing the child pacakge.

The last step in the parent package is to create the for each loop container I mentioned earlier, and place the execute package task inside it.  I’m using an object variable to store the distinct client ID values, and I use that as the iterator for the loop (I describe how to do this more in depth here).  For each iteration of the loop, a different client ID value will be passed into the child package parameter.

The final step is to configure the child package to actually do something meaningful with the parameter values passed into it.  In this case, I’ve modified the OleDB source query to use the pClientID value in the WHERE clause of the query to restrict results for each iteration to a single client’s data.  Additionally, I’ll use both the pClientID and pOutputFolder parameters to dynamically build the output filename.

As shown, the pClientID is used in the WHERE clause, so we only get the current client’s invoices for each iteration of the loop.

For the flat file connection, I’m setting the Connection String property using an expression that engages both of the parameters for this package, as shown above.

Parting Thoughts

There are many uses for package parameters beyond a simple parent-child design pattern.  For example, you can create standalone packages (those not intended to be used as a child package) and still use parameters.  Parameter values may be supplied to a package directly at runtime by a SQL Server Agent job, through the command line (via dtexec.exe), or through T-SQL.

Also, you can also have project parameters as well as package parameters.  Project parameters work in much the same way as package parameters, but the parameters apply to all packages in a project, not just a single package.


Of the numerous advantages of using catalog deployment model in SSIS 2012 and beyond, package parameters are near the top of the list.  Parameters allow you to easily share values from parent to child packages, enabling more dynamic behavior and better code encapsulation.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Developer’s Life – Disaster Lessons – Notes from the Field #039

[Note from Pinal]: This is a 39th episode of Notes from the Field series. What is the best solution do you have when you encounter a disaster in your organization. Now many of you would answer that in this scenario you would have another standby machine or alternative which you will plug in. Now let me ask second question – What would you do if you as an individual faces disaster? 

In this episode of the Notes from the Field series database expert Mike Walsh explains a very crucial issue we face in our career, which is not technical but more to relate to human nature. Read on this may be the best blog post you might read in recent times.

Howdy! When it was my turn to share the Notes from the Field last time, I took a departure from my normal technical content to talk about Attitude and Communication.(http://blog.sqlauthority.com/2014/05/08/developers-life-attitude-and-communication-they-can-cause-problems-notes-from-the-field-027/)

Pinal said it was a popular topic so I hope he won’t mind if I stick with Professional Development for another of my turns at sharing some information here. Like I said last time, the “soft skills” of the IT world are often just as important – sometimes more important – than the technical skills. As a consultant with Linchpin People – I see so many situations where the professional skills I’ve gained and use are more valuable to clients than knowing the best way to tune a query.

Today I want to continue talking about professional development and tell you about the way I almost got myself hit by a train – and why that matters in our day jobs. Sometimes we can learn a lot from disasters. Whether we caused them or someone else did. If you are interested in learning about some of my observations in these lessons you can see more where I talk about lessons from disasters on my blog.

For now, though, onto how I almost got my vehicle hit by a train…

The Train Crash That Almost Was….

My family and I own a little schoolhouse building about a 10 mile drive away from our house. We use it as a free resource for families in the area that homeschool their children – so they can have some class space. I go up there a lot to check in on the property, to take care of the trash and to do work on the property. On the way there, there is a very small Stop Sign controlled railroad intersection. There is only two small freight trains a day passing there. Actually the same train, making a journey south and then back North. That’s it. This road is a small rural road, barely ever a second car driving in the neighborhood there when I am. The stop sign is pretty much there only for the train crossing.

When we first bought the building, I was up there a lot doing renovations on the property. Being familiar with the area, I am also familiar with the train schedule and know the tracks are normally free of trains. So I developed a bad habit. You see, I’d approach the stop sign and slow down as I roll through it. Sometimes I’d do a quick look and come to an “almost” stop there but keep on going. I let my impatience and complacency take over. And that is because most of the time I was going there long after the train was done for the day or in between the runs. This habit became pretty well established after a couple years of driving the route. The behavior reinforced a bit by the success ratio. I saw others doing it as well from the neighborhood when I would happen to be there around the time another car was there.

Well. You already know where this ends up by the title and backstory here. A few months ago I came to that little crossing, and I started to do the normal routine. I’d pretty much stopped looking in some respects because of the pattern I’d gotten into.  For some reason I looked and heard and saw the train slowly approaching and slammed on my brakes and stopped. It was an abrupt stop, and it was close. I probably would have made it okay, but I sat there thinking about lessons for IT professionals from the situation once I started breathing again and watched the cars loaded with sand and propane slowly labored down the tracks…

Here are Those Lessons…

  • It’s easy to get stuck into a routine – That isn’t always bad. Except when it’s a bad routine. Momentum and inertia are powerful. Once you have a habit and a routine developed – it’s really hard to break that. Make sure you are setting the right routines and habits TODAY. What almost dangerous things are you doing today? How are you almost messing up your production environment today? Stop doing that.
  • Be Deliberate – (Even when you are the only one) – Like I said – a lot of people roll through that stop sign. Perhaps the neighbors or other drivers think “why is he fully stopping and looking… The train only comes two times a day!” – they can think that all they want. Through deliberate actions and forcing myself to pay attention, I will avoid that oops again. Slow down. Take a deep breath. Be Deliberate in your job. Pay attention to the small stuff and go out of your way to be careful. It will save you later.
  • Be Observant – Keep your eyes open. By looking around, observing the situation and understanding what your servers, databases, users and vendors are doing – you’ll notice when something is out of place. But if you don’t know what is normal, if you don’t look to make sure nothing has changed – that train will come and get you. Where can you be more observant? What warning signs are you ignoring in your environment today?

In the IT world – trains are everywhere. Projects move fast. Decisions happen fast. Problems turn from a warning sign to a disaster quickly. If you get stuck in a complacent pattern of “Everything is okay, it always has been and always will be” – that’s the time that you will most likely get stuck in a bad situation. Don’t let yourself get complacent, don’t let your team get complacent. That will lead to being proactive. And a proactive environment spends less money on consultants for troubleshooting problems you should have seen ahead of time. You can spend your money and IT budget on improving for your customers.

If you want to get started with performance analytics and triage of virtualized SQL Servers with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Andy Defines Basic RDBMS: Isolation in Processes – Notes from the Field #038

[Note from Pinal]: This is a new episode of Notes from the Field series. Every time I give an introductory note, however, this time there is no need of intro note. This note is from Andy and as we all know he is amazing person when we have to understand the fundamentals. He has written this blog post with such an interesting way that you must read it to understand the very basic fundamental of Isolation.

When I think about SQL Server isolation in processes, it reminds me of eggs. Our family raises chickens for their eggs. Fresh eggs are very tasty, but there’s always the risk of a bad egg.

In the image above, I am preparing to scramble five eggs. I have cracked and opened five eggs (you can tell by the number of eggshells), but only four egg yolks are shown. “Why are there only four yolks, Andy?” I’m glad you asked.

My process for opening the eggs involves first dropping the contents of the egg into the mug, examining the contents, then – if satisfactory – adding them to the bowl for mixing. You don’t have to do this to make five scrambled eggs; you can crack the eggs right over the bowl.

But what happens when you open a bad egg? You risk ruining the entire batch of mostly good eggs. If you crack the eggs over the bowl, you have to pick five good eggs in a row to get a batch that’s ready to scramble. You may get lucky and it may only require five eggs. But the risk of a bad egg is ever present. You could get one good egg, followed by a bad egg. Now you have to throw both the good and bad egg out and begin again. The next time you may get three good eggs and then one bad egg. Now you’ve wasted three good eggs.

Isolating the eggs is a good practice. If I first empty the egg into a mug, I have constrained the process so that I only risk one egg at a time. In so doing, I have successfully mitigated risk to the least possible unit. I will only throw out the bad eggs without risking contamination of the good eggs in the bowl.

Isolation is generically defined as, “the process of separating somebody or something from others, or the fact of being alone and separated from others.”

In database terms, isolation is one of the four defining properties (i.e., atomic, consistent, isolated, durable—remember these by using the acronym ACID) of a Relational Database Management System (RDBMS). Similar to isolating bad eggs from the good, RDBMS isolation keeps individual database transactions from intermingling with each other during execution. It’s not that other transactions are bad, we just want to keep them separated so that data from one transaction doesn’t corrupt data from another transaction.

Are isolated transactions completely unaffected by each other? No, unlike the example of completely isolating a bad egg from the mix of good eggs, RDBMS isolation doesn’t prevent one transaction from influencing or impeding another transaction. An example of influence is resource contention; an example of impedance is locking. Isolation simply guarantees the results of the transaction will not be affected by concurrently executing transactions.

You can learn more about these properties from this awesome post by my friend, Pinal Dave: SQL Server – ACID (Atomicity, Consistency, Isolation, Durability).

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Beginning Contained Databases – Notes from the Field #037

[Note from Pinal]: This is a new episode of Notes from the Fields series. Login and Users are very confused concept. Just yesterday I wrote about a difference between Login and User. In Latest version of SQL Server now we can also have a User without Login. This concept is not easy to understand and needs a clear example.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces related to Login and Users. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.

One of the downfalls with database mirroring is synchronizing database users with logins. If you were using SQL Server accounts instead of windows domain accounts it could be a tougher challenge to ensure that the unique identifiers (SID) match up. In SQL Server 2012 and up we can leverage contained databases to mitigate this problem with Availability Groups. Contained database users are stored inside the database instead of leveraging the instance logins. This allows the database itself to manage authentication and authorization for the login for the database.  Therefore, you don’t need to fixed orphan users when you failover.

Here is how you can enable and leverage Contained Databases.

Step One: Enable Contained Database on the instances that will host the database. In an Availability Group this would be all the replicas for the Availability Group.

This can be configured in SSMS or via T-SQL as shown below.

EXEC sp_CONFIGURE 'show advanced options',1
EXEC sp_CONFIGURE 'contained database authentication',1

Step Two: Enable Contained Database on the primary replica. This is also known as the read/write replica.

This can be done via SSMS or T-SQL

USE [master]

Step Three: Create a Contained User
In order to have a contained user were going to have to create a new user inside the database and create it without a login. This will make the user a contained user. You will also need to add the needed security for your account. For the purpose of this weekly tip we will skip this part. Creating the contain login also known as SQL user without login can be done with SSMS or T-SQL as shown below.

USE [AdventureWorks2012]

Step Four: Test connectivity.

Finally, we can test connectivity. This will be done utilizing SSMS as shown below.  Make sure you change the default database to the database where the login is contained as shown below.

Now, make sure you select the Connection Properties tab and select the database where you created the contained login. In our example, this will be AdventureWorks2012.


When connection is successful with a contained database user you should see the login and the database right next to the instance in object explorer as shown below.


Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)