[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

SQL 2005 Server Full Disk

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
Craig_hannagan
Asked:
Craig_hannagan
  • 2
  • 2
2 Solutions
 
reb73Commented:
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
 
dportasCommented:
Don't backup to the same drive where the data and log files are.
0
 
Craig_hannaganAuthor Commented:
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
 
Craig_hannaganAuthor Commented:
Sorry forgot to ask, I guess my db's are consistent and OK etc after the dbcc check results?
0
 
dportasCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now