Voice of the DBA

Tag: administration

  • The Last DBCC CHECKDB Date and Restores

    I ran across a question on Twitter recently where someone asked about the DBCC CHECKDB date after a restore. For those of you that don’t know this, you can run this command on your instance (in a database)

    DBCC DBINFO WITH tableresults;

    This returns a lot of information, but I’ve circled one value below:

    dbcc1

    This is the last known good DBCC date that exists for this database. It’s also the value in the error log from the execution of DBCC CHECKDB on my instance early this morning (Arrow marks the entry).

    dbcc2

    If I were to restore this database, what happens to this value? I’d expect that it would be restored to the last value that was contained in the backup file. That would make sense to me, but let’s test it.

    First I run a backup:

    BACKUP DATABASE [db1] TO  DISK = N'C:\SQLBackup\db1_20121115.bak' WITH NOFORMAT, NOINIT,  NAME = N'db1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

    This should have the last DBCC date inside the file. I know run a CHECKDB and note the time in the error log.

    dbcc3

    This returns successfully (whew, didn’t want to test corruption restores). If I then run DBINFO again, I get the current date from the error log returned. Now let’s restore from my backup.

    This completes and when I run DBCC DBINFO again I find the dbccLastKnownGood date is reset back to the 12:04am value instead of the 9:11am value.

    That’s what I expect, and that was what a few other people confirmed on Twitter. It’s logical that this should be the behavior, but you never know until you’ve tested it.

  • The Cost of Storage

    SAN disks
    The cost of storage per GB drops, but the cost to companies seems to keep going up.

    One thing that we can be sure of as data professionals: we will need more storage over time. We gather more data in our organizations and more data is sent to us by partners or automated systems. At the same time we must ensure all our existing data is always available for users. I don’t know how we solve any of these problems, but I do know that any solution will require likely more storage in the future.

    As DBAs, we aren’t too often involved in the cost of storage. These days as many companies move to SAN-based storage, there are separate administrators are involved in the specification and purchasing of the actual hardware. That means that as DBAs we are removed from the impact of our requests for more storage. We don’t necessarily know what the cost to the organization is when we request another 400GB LUN. However this week I’m curious if you know the answer to this.

    What’s the cost of storage in your organization?

    I’m wondering if you know the actual cost in dollars or the impact to limited IT resources of enterprise level storage in your company. Does this impact the way you do business as a DBA? We often don’t have control over the growth of our data, but we seem to regularly be pulled into the discussions on how to best manage the amount of data and even archive data or reduce its impact on the organization’s storage.

    Let us know this week if you are impacted by the cost of storage in your organization.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Monitoring Is Essential

    Watch your systems like a hawk. You’ll be glad you did.

    An old phrase used in business is that knowledge is power. When you understand more about a particular situation, you can develop solutions and better understand which ones would both suit your environment better and solve any issues faster.

    As someone that has worked in a variety of production environments managing all different types of platforms, I have found it extremely career-enhancing to have knowledge about the entire environment. Knowing what is happening at any point in time has allowed me to answer pointed questions about the applications. With comprehensive monitoring set up in database servers, I have often found myself in a position to proactively prevent issues, or make preparations for a quick response when I do find problems.

    Over the years I developed a variety of techniques to monitor my own servers, often using third party solutions, enhanced with my own queries. I have learned that each system has its own idiosyncrasies, which often require custom queries to keep an eye on the pulse of the system. I’ve also learned that a regular exception report is the most valuable tool for me as a DBA. I assume most of my applications and scheduled tasks are working well; I just want a list of those things that have broken, or are not performing as expected sent to me every day, using the data from my various monitoring tools.

    I have found that tracking metrics and analyzing the data is a valuable tool in my DBA tool belt. I would go so far as to say that monitoring the systems that you work with is not only a best practice, it’s essential if you want to be an exceptional DBA.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Database Maintenance Essentials

    Abstract: SQL Server requires a minimal level of maintenance to ensure that the platform functions at an optimum level without any unnecessary outages or problems. This talk will look at the basics of the maintenance operations that should be performed on SQL Server on a regular basis. The topics covered include:

    • Managing MDF and LDF Files
    • Managing Indexes
    • Maintaining Statistics
    • Checking for Corruption
    • Creating Backups That Will Restore
    • Managing Maintenance Jobs

    I cover the reasons why maintenance is required and include a number of references to help you enable this maintenance in your environment.
    In some presentations I will also cover the Red Gate tools that make this maintenance easier to perform. This is optional.

    Level: 100

    Length: 60 minutes

    Slides: Database Maintenance Essentials (SQL in the City)

    Presentations:

    You can view my complete speaking schedule here: http://wp.me/P14wgJ-1tV