September 17, 2010 at 11:52 am
WayneS (9/17/2010)
... but after the one on the woes of being abused on the forums. 😀
Don't think I'm qualified to write that one. After all, I'm the 'abuser'. 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 17, 2010 at 12:07 pm
GilaMonster (9/17/2010)
hallhome (9/17/2010)
Update: I took Gail's advice and moved tempdb to another drive and recycled SQL Server. The database in question was able to recover and I was able to shrink its transaction log.Oh good. Always nice to have a positive outcome on these kinda questions.
Always nice when the OP posts the resolution too. That will help out future searchers.
September 17, 2010 at 12:47 pm
GilaMonster (9/17/2010)
Ninja's_RGR'us (9/17/2010)
Hmm, makes you wonder if we need to make another article about dealing with severe problemes at work under pressure. And by we I mean you :hehe:.Thank you for volunteering!
Now, should that come before or after the rewrite of 'Managing transaction logs', before or after the article on recovering from a suspect database annd before or after the editorial on asking good questions?
It really makes no difference to me :w00t:!
They're all important so do what feels right for you.
September 20, 2010 at 9:46 am
hallhome (9/15/2010)
Thanks everyone. Whoever set up this database made some big mistakes. From the older logs that I have seen, it appears that they had set up full db backups for each database nightly. There is a daily process that inserts rows into two tables in the database daily. The only other activity is a handful of users running adhoc reports against it. The disk filled up over 2 weeks ago, so I highly doubt that the server is trying to recover from the log. There are no active transactions.I know the "right" things to do regarding the logs and backups going forward....what I need to know now is do I have any way out of the Catch-22 I am in? Even if I were able to expand the D: drive or free up some space, I don't imagine SQL Server waking up and using the new space on its own.
I was thinking of shrinking the size of tempdb to free up some space, then shrink this database, and expand tempdb back. Could this work?
Now that your immediate problem is fixed, might I suggest 2 things to consider (1 of which I think you're already onto):
(1) Consider whether this DB needs to be in Full recover mode or Simple. If it isn't a transactional db with frequent updates, inserts, and deletes, you may be suitably covered by Simple recovery mode. There's lots of info. online about this. In a nutshell: Full means you can (and must, as you just learned the hard way :w00t: ) perform transaction log backups and do point-in-time recoveries. Simple means you can restore full or differential backups only, and you can only restore to any one of those.
Emphasis on "suitably" covered. That's really a business decision as much as a technical one.
(2) Consider what recovery mode any new db's should be. This is set by changing the Recovery Model of the Model db. I'm pretty sure it defaults to Full in a routine installation of SQL Server, which means all new db's will be in Full recovery mode until/unless you change them. Which means you must implement log backups for new db's or you'll be back where you were 3 days ago..... (which is why I changed my Model db to Simple recovery mode)
Good luck, and thanks for posting your ultimate successful outcome,
Rich
September 21, 2010 at 7:28 am
hallhome (9/17/2010)
Update: I took Gail's advice and moved tempdb to another drive and recycled SQL Server. The database in question was able to recover and I was able to shrink its transaction log. I reclaimed 250 GB of space on the drive via these actions. Going forward I am going to implement a revcovery scheme that is more in tune with the usage of this database.While I am an experienced DBA (Oracle, DB2, Teradata, Sybase) this is my first foray into MS SQL Server and the Windows OS. Each DBMS has its own nuances when it comes to backup/recovery, and I turned to your experience so that I would not put myself in a worse postion that I already was. I greatly appreciate your helpful (and sometimes spirited!) suggestions and I'm sure I will return in the future with issues.
Kathryn
Kathryn, great news! Reading this thread was like reading a thriller! I'm glad to see the happy ending and, once again, amazed at the incredible effort that the experts in this forum will devote to help someone.
September 21, 2010 at 8:30 am
For Gila's and everyone else's bag of tricks:
To add space to a drive that's out, there are a variety of options.
Temporary:
A) ENTIRELY SAFE: Clear out IE's and any other browser cache, all offline pages, etc. Empty the recycle bin!
B) QUITE SAFE: Remove entirely or reduce the size of the the OS level page/swap file; you can likely squeeze a few gig out of it. If you wish, forcibly put a maximum on SQL Server's memory usage for this time period that leaves 2-4GB available to the OS.
C) FAIRLY SAFE: Download and run C[rap]Cleaner; perhaps use the options to remove old system restore points or, if you must, hotfix uninstallers and the like. I've never had a problem with this.
D) Break out your [company's] wallet, buy some external storage. If you're in a HIPAA/HITECH/sensitive data environment, understand that said storage may never be allowed to leave the building again in one piece/without being wiped. Add it to the server. Copy data from existing directories to the new drive, delete the data on the full drive, and then create Junctions with mklink for already used directories so that the OS sees them as being on the full drive, but they're actually on the new drive. Remove the junction and reverse the copy when done. If you lose your storage with critical files while doing this, you're going to want a drive image backup available (keep reading).
Permanent if you choose:
1a) Get a fully recoverable drive image backup; use Ghost or Acronis TrueImage or PQDI or DD or Clonezilla or whatever you like.
1b) If you've never done that before, test a restore on another machine/drives
1c) Use Partition Magic or Parted Magic to move/resize partitions because the manufacturer left most of your drive space unpartitioned (if they did; Dell sometimes did on older rackmount hardware)
1d) Replace the drives with bigger ones; if you're wise, very carefully place the old drives in anti-static bags on a shelf; those are your #1 recovery method.
1e) Buy new, bigger drives (even if they're slower - 1TB SATA drives are bigger than 600GB SAS drives, and most SAS backplanes should be able to handle SATA drives too). Install them physically, recreate the RAID if required.
1f) Restore the drive image to the new drives.
1g) If the cloning software didn't let you reside, see 1c)
September 21, 2010 at 8:44 am
Nadrek, good ideas.
I'd add *.cab files to your list: you can always re-load them if you move them off to a removable disk.
Rich
September 21, 2010 at 10:10 am
And now is the time to get some money.
The users are experiencing the penalty of poor systems configuration and operation. Get the all of the dollars & time associated with the outage.
Then use that to get (ideally):
• An automated backup solution (CommVault,..).
• Enough cheap disk to perform disk-to-disk backups (faster return to business).
• A tape library to perform the archival copies continuously from the backup disk.
• Optionally, a tape monkey to pull & fill the tape library during the business day as needed.
That is the current configuration of my system & it works so much better than using scripted BACKUP commands. It just crunches right along. And when there is a problem, it tells me, instead of me having to look all of the time.
Brad
P.S.: There is a lot of perfectly good used/refurb equipment at discounted prices out there that can be brought under a service contract.
September 21, 2010 at 11:09 pm
This chain of reply/comments is really informative and helpful..i seriously took printout of everything and had a thorough look while heading back to home in my office cab.
@steve-2 : Is there any option so that we can take print out of one post at one shot ? i had to go one by one to every page to have print out.:-D
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 22, 2010 at 4:20 am
Go in your settings and select 50 posts per page rather than 10. That's take it down to 2 prints!
September 30, 2010 at 10:19 pm
Ninja's_RGR'us (9/22/2010)
Go in your settings and select 50 posts per page rather than 10. That's take it down to 2 prints!
Topic options, print topic. No need to change settings.
--Jeff Moden
October 2, 2010 at 12:03 am
Jeff Moden (9/30/2010)
Topic options, print topic. No need to change settings.
Thanks Jeff .thats a bit better for lazy learning DBA like me 😀
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 12 posts - 76 through 86 (of 86 total)
You must be logged in to reply to this topic. Login to reply