Solved

SQL 2005 Server Full Disk

Posted on 2009-04-04
5
353 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
Comment Utility
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
Comment Utility
Don't backup to the same drive where the data and log files are.
0
 

Author Comment

by:Craig_hannagan
Comment Utility
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
Comment Utility
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
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now