Solved

SQL 2005 Server Full Disk

Posted on 2009-04-04
5
365 Views
Last Modified: 2012-05-06
Hi,

I have a quick question. I have many sql db's on a d: drive (non os) partition on a server.
Some of the db's run in full mode and all the other db's are in simple mode.
Today a transaction log backup occured on the full mode db's and on the first full mode db the transaction log backup was to big and made the disk full. From what i could see it could not complete the transaction log backup and it stopped and released the used space back to the OS then tried the next db in the list which it could complete because transaction backup was small.

My question is when the disk was full for those few seconds would of it caused any of my db's to become inconsistent or corrupt in any way? I have backups if I need to restore. Some of these db's are very important financial db's but are not accessed much by users over the weekend.

Also i know what the issue was with the large transaction backup and have address that for the future.

Cheer Craig
0
Comment
Question by:Craig_hannagan
  • 2
  • 2
5 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 300 total points
ID: 24069746
What is the frequency of your transaction log backup on the database that caused the issue?

Over the weekend, you should ideally be backing up the full database, with the transaction log backups occuring when the database is being used..

A disk being full can make a database being marked suspect.. If it is not, then just get a full backup of the database, truncating the transaction log in the process.. Also run a dbcc checkdb against the database in question to verify that it is ok..
0
 
LVL 22

Expert Comment

by:dportas
ID: 24070430
Don't backup to the same drive where the data and log files are.
0
 

Author Comment

by:Craig_hannagan
ID: 24070519
Hi reb73,

Our transaction log backups are every hour, everyday between 0500 and 1900 with full backups everyday at 2100.

Usually our transaction log backups are only small 3 - 100 meg for our databases. But as part of our corporate db maintenance all db's are reindexed, update statistics etc on Sunday 1200. (This expected by the vendor of the corporate software) This then causes a few of our db's to generate a 70 gig ldf file (mdf's are about 35 gig).

We dont shrink these large ldf's under the belief of defrag issues?? But then i hear this can also cause performance issues?? (As you can probably tell my sql skills are little rough after asking more questions). Usually the next time a transaction backup occurs on our full recovery mode db's it creates a very large transaction backup (usually about 70 gig as well).

In this case someone accidently copied lots extra unrelated data to the drive. Hence when the transaction log job went to run on the first db there was only 20 gig left on the drive and the transaction backup needed about 70 gig. My concern was it started, ran out of space, cancelled that process, then released the incomplete used space back to OS, then went onto next db listed in the job which started, ran out of space, cancelled that process, then released the incomplete used space back to OS, then went onto next db listed in the job etc etc.

No db's were marked as suspect. After your advice I moved the copied extra unrelated data to another drive, creating enough space to perform the transaction backup on all full recovery mode db's. I also have ran a dbcc checkdb on all databases and i received "CHECKDB found 0 allocation errors and 0 consistency errors in database" for all db's.

dportas: Thanks for the advice.

Thanks for your help, I hope that made things a little clearer and did not bable on too much.

Regards Craig
0
 

Author Comment

by:Craig_hannagan
ID: 24070551
Sorry forgot to ask, I guess my db's are consistent and OK etc after the dbcc check results?
0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 200 total points
ID: 24070564
If DBCC report no errors then you should be OK. I've seen DBs run out of disk space a few times and never experienced any kind of inconsistency because the result is just that open transactions get rolled back.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question