I THINK my job is failing....?

Question

Post reply

I THINK my job is failing....?

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 1

DrStrangepork (8/9/2010)

Brandie Tarvin (8/6/2010)

I'm curious. What recovery mode is your database in? FULL or Bulk-Logged?

I have no idea, and I've never heard of bulk-logged. How can I determine what mode I am in?

In SSMS, right-click your database and go to Properties -> Options. Recovery Model is the second box. It has three options. Full, Bulk-Logged, and Simple.

I suggest you open up Books Online and look up Recovery Model. Every DBA should have a basic understanding of Backup and Restore / Recovery options.

Here are some online links: "Recovery Overview" and "Understanding How Restore and Recovery Works"

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 2

DrStrangepork (8/9/2010)

For now, I am leaving things the way they are, because things seem to be working, and logically my changes make sense. If anyone objects or thinks otherwise, I'm open to recommendations.

I stick to my above suggestion of finding out what is causing the log file to grow and --if it is an SSIS package, Bulk Insert task, etc.--changing the recovery model during that task to keep your file growth low. But whether this works depends on what recovery model you're currently using. If you're already set to BL, then my suggestion won't work.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

jeff.mason SSCrazy Eights Points: 9555 More actions · Answer 3

I think we have already established why his transaction log is filling up -- that reindex job. There is almost no question in my mind but that this is his problem. And ironically he needs to become familiar with bulk logging vs. full immediately as this is key to reducing his issues. A full reindex of a prod DB at my last job that was 250 GB required about 200 GB of transaction log space, and that was with bulk logging turned on. Lord only knows what doing it in FULL mode would have required.

So a few comments -- there is absolutely nothing wrong with doing backups and reindexes close to each other; they may have some I/O interaction but given that both are usually after hours operations that inevitably happens every so often. Worst I have seen happen is that the backup file has all of the log portion that has not been truncated by log backups yet.

Which brings me to my ultimate solution for your problem -- sounds like you need to be doing transaction log backups during the reindex job. Hourly won't be enough; I would do it every ten to fifteen minutes during the reindex operation. This will truncate the log and allow the reindex to reuse the log file space. After the reindex is over go back to the hourly schedule.

Finally, I seriously doubt you need to do a full reindex daily. Once a week is usually enough, and if specific indexes need to be done more frequently, you can take daily snapshots of the db_index_physical_stats view and store it in a table to see which index is fragmenting quicker than usual. Setting the reindex to weekly will help out more than anything I think.