[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL 2005 Server Full Disk

Posted on 2009-04-04
5
Medium Priority
?
380 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 1200 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 800 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

656 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