It seems that often I see posts that say “I’m out of space on my drive, what do I do?” The answer, as with most anything in SQL Server is, it depends.
The most common issue I see for running out of space is the lack of deleting database backup files. The maintenance plan wizard instructs you to perform backups, and defaults to making a separate file for each backup, but it doesn’t necessarily clue in the “Accidental DBA” to remove old backups.
You need a maintenance cleanup task here and set it to remove old backups, but be sure that you have a new, good backup first. In other words, the maintenance cleanup comes after the full backup, and then only when that task completes with success.
You can delete old backup files manually, but think about how many you need to keep. Most people probably can get by with 1 or 2.
I know lots of people that have IIS on the same box with SQL Server for small to medium web sites. Unfortunately IIS doesn’t have a maintenance cleanup task. It creates new log files on a daily or weekly basis, and those will fill up your disk when you are least expecting it.
These are in the LogFiles folder under System32 on your system drive. Set a reminder in Outlook to trim these down occasionally.
Another very common issue is that transaction log growing to fill all disk space. This happens because so many new DBAs assume that a full backup also includes a log backup. It doesn’t, for multiple reasons, and it doesn’t matter if it should or no; it doesn’t.
What you need to do here is first make sure you have time, and then clear the log. That’s a log backup with truncate in versions 2000 and prior. It’s setting to Simple mode in 2005 and above and then . Be sure you set the mode back to Full.
Once you have space back, I’d recommend you take a full backup. You can get by with a diff, or maybe wait until your next scheduled one,but I’m conservative. I’d recommend a full backup right then. Once that’s done, you need to get a good size for the transaction log. Unfortunately that requires some trial and error.
The size of your log file depends on the load your database experiences in terms of changes, but also on your log backup schedule. A log backup allows the space in the log to be resused. If you produce 10MB of changes an hour, and back up once an hour, you need a log file that’s 10MB. Actually you’d want it larger to account for a larger load some hours, but you get the idea. If you backup the log once a day, you’d need a 240MB log ( plus pad).
So, what I’d recommend is that you schedule hourly log backups. See how large the backup files are and then size your log file appropriately. As far as the initial size, you can run a DBCC SHRINKFILE to make your log file smaller. I’d lower it to 1GB to start with. That’s just a guess, and you might want to set it to 10% of your data files, but disk space cheap, and 1GB will accommodate lots of databases.
There are other reasons, like your data file growing, but I’m not trying to cover every situation. If you’re new to SQL Server, and you have a full disk, check these items first. If none of these help you, post a note in our forums and someone will answer.
I was reading the most recent issue of TechNet from Microsoft one morning and flipped through the column on SQL Server. This is one of the few paper magazines I get, and for some reason I see things in there that interest me.
It’s not the topics, but often the delivery and format. I flip through it at my desk, or while cooking, and I’ll see things that I’ve missed on the Internet, or get reminded of things I’ve forgotten. The October issue contained one of those.
Paul Randal writes the SQL Q&A column and he usually has something in there that’s worth knowing. This month he answered a question about some strange messages in the SQL Server error log. Here’s the message from my test instance:
In case it’s hard to read, the message is:
CHECKDB for database 'db1' finished without errors on 2009-09-21 08:33:03.713 (local time). This is an informational message only; no user action is required.
This was on startup of the instance, right after the “starting up database” messages that you see. I have this same message for a couple other databases (db2 and master). It didn’t appear for model, msdb, tempdb, and a couple other databases I have on this instance.
Why not? Or rather, why did it appear for these databases? Is DBCC CHECKDB running on startup?
The answer is no, and Paul has a good explanation in his column. It’s not online yet, but I’ve read this before and Paul gave me a great reminder. The message is the “last known good” time for the database. It shows the last time that DBCC completed successfully, and gives you a reference point that you can use an a DR or corruption scenario to try and narrow down when things went bad. Note this is SQL Server 2005 and later.
It’s also a good reminder that you haven’t run dbcc in some time on a DB. When I first read the column, I checked my error log and didn’t see the message. That was a stark reminder that I didn’t have maintenance set up on this instance. It’s a test instance, only been installed for about 20 days, but still. I ran dbcc on those databases that showed messages (db1, db2, master) only as a test. I’ve now set up maintenance on the instance to grab a backup every night and run weekly maintenance.
If I had production instances, I’d love to be able to easily capture this data from a server, or even compare the messages to the databases on the server and then report back to me if CHECKDB hadn’t been run, or if it had been longer than xxx days.
Sankar Reddy, a fellow MVP, wrote a script that helps here, and it’s worth checking out his blog on the last clean DBCC value.
When I first heard about database mirroring in 2004, I was excited. Here was a great new feature that would solve some of the issues with log shipping, and have a much lower cost than clustering. It doesn't completely replace those options, and be sure that you research your high availability options and try to match them to your needs before picking a technology. However database mirroring seemed like a great idea to me.
Then SQL Server 2005 was released in Nov 2005.....but no Database Mirroring. Sure you could enable a trace flag and turn it on, but it wasn't supported in RTM. The reason given is that there was more testing needed. Somewhat annoying, and definitely a marketing driven release, but still the technology was there and there were customers were using it in test programs.
Since then, it seems that I've not heard about a lot of issues with database mirroring. Maybe not a lot of people are using it, but it seems like it's been a good technology for keeping a standby database ready in case of a failure. I do regularly see questions about some things, but not a lot of them compared with log shipping or clustering. I think that it's a fairly solid technology, as long as you get it set up correctly in the first place.
However one place I do see questions is with regards to limits. When I went to Tech Ed in 2006, I am sure that I attended a few sessions and people were claiming that 50 databases was the most you'd want to mirror on an instance because of load. After that point, in testing, the instance performance had degraded quite a bit. So when I got a question recently in the forums on the number of databases per instance, I was ready to post the 50 number and was looking for a reference when I ran across this great blog post from Geek Speek on Database MIrroring Limitations and Gotchas.
The limit given there is 10 databases per instance.
That's a long way from 50, and I can only guess that real world testing over the last 4 years has shown that 10 is a better limit, especially on 32 bit machines. Now when I heard the 50 number there were all the usual caveats that it depends on transaction load, hardware, etc, which is all true, but can you not really get above 10 on a 32 bit machine?
Apparently it has something to do with threads. If you go over to the Geek Speek link, you'll see a nice table of the default threads that exist with various CPU levels and platforms (32/64). Since a mirror uses 5 threads per instance, or maybe per database. I haven't seen a good explanation of this in white papers or documentation, but to be fair, I haven't been digging into a problem and there's a lot of information to wade through without a good reference listed from Microsoft. I've listed a few resources at the end for you to look through.
What I'd like to see from Microsoft is not a number of databases, but rather a number of transactions (or transaction byte count) along with bandwidth requirements to allow you to better size your systems. A database isn't a good generic item, and while there may be overhead, I'd bet the size of the transaction along with the number of them has more to do with mirroring limitations than anything else.
Resources
:(
I was literally stunned and it must have shown on my face because my daughter asked me if I was OK. I had just picked up the pieces of my Kindle off the blacktop of a parking lot, cracked screen and broken case. The screen saver was still on the screen, and I powered on and off a couple times on the chance that the damage was cosmetic, but no luck. It was toast.
What happened? I'm slightly embarrassed to say that I set it on the tire of my wife's truck. We were sitting on the curb, and my daughter wanted me to read a book to her. With people walking nearby, I didn't want to put in on the ground, and I should have put it back in the truck, but I was lazy. We read for about 30 minutes until it was time to go and I stood up, got in the truck, and promptly bumped across the device.
So how do I feel about the device? This was a little over a week ago and I haven't replaced it yet.
Well, I'm upset with myself, but mistakes happen. I've been lucky with most devices outside cell phones, but my wife and kids have destroyed a few iPods and Walkmans over the years as well as damaged a few cars, so in the grand scheme of things this isn't a big deal. Plus it was a $100 device for me since I'd use Amazon Associates money.
I think the value of the device is there for me. I read a lot, about 50 books a year before this, and probably closer to 70 with it. The convenience of keeping those books with me, and being able to get new books anytime (or almost anytime), is very valuable to me. I'm not sure it's $300 valuable, but it's definitely $100 valuable, probably $200 valuable.
However this is an electronic device, and it's a specialty device that's somewhat locked to a vendor, like a cell phone. Personally I think there are a couple things that would make this a better platform.
1. Offer insurance. Even Steve Jobs trying to a sell a new iPod to everyone every year won't work with a $300 iTouch. It might with a $149 Nano or a $80 Shuffle, but not much more than that. $5 a month probably would be something I'd pay, and most people would as well.
2. The device needs a lower cost option, maybe less memory, less features, for around $200. Even the iPod now goes from a $49 1GB shuffle to a $400 32GB iTouch. The Kindle ought to be able to have a smaller screen, no keyboard, no MP3, no Text->speech, less memory for $200-$250, and then keep the Kindle 2 and the DX at the higher end.
3. Grow the platform, I have some thoughts I'm working on for another post.
So will I buy another one?
I think so, but I'm caught in a spot now. For one thing I'm imposing a bit of a penance on myself for doing something stupid. Second, I'm forcing myself to earn more Associates money over time as well as find a way to fund the rest of the cost. I'm not quite ready to make my family finances eat this mistake. Lastly, I think that a new Kindle device is likely in the works. Perhaps by the end of 2009, but almost certainly in 2010, so I'm thinking I might be an early adopter for that version when it comes.
Of course when I see Brian Kelley with his at PASS I might chance my mind and just buy what's out.
It was kind of amazing to see SQLServerCentral have issues over the last few days. We’re still not sure what happened, but the load went crazy over the last few days, substantially slowing email sends and causing performance problems.
There are definitely some database, issues, and we are looking to provision a larger database server in the short term. That’s not a great fix, but we’re a business like any other and hardware is often quicker and easier to deal with than code in the very short term. As we dug in, we realized the same database server we’ve had for over 2 years, with more than double the load, just can’t keep up. I’m not sure if anything else has changed code-wise recently, but the quick fix is a new server. Since we have a hosted server with Rackspace, this is actually a fairly easy and relatively inexpensive fix.
Beyond that, we have some code to examine. As with most businesses, we had developers built the site, without a real DBA working alongside them. It wasn’t a great solution, but it also is the realization of how resources sometimes get deployed. I think our developers did some neat things in one sense, not so good in another. There is a boatload of nHibernate code in there, and some of those pages appear to be amazing POS constructs. If nothing else, I think I would be terrified of ever using nHibernate in any project just based on my experiences here.
As things seemed to fail today, we made the decision to move the forum database to our backup server. We’ve kept a second one handy for DR purposes, and since the code is fairly separate, it seemed like an easy fix to reduce the load.
Brad McGehee expressed concerns, which seem to be well founded. I’m having issues working in the forums, so I think there are still things broken.
In one sense this is the opposite problem that we had for years. Early on, Andy, Brian, and I coded quite a few things on the site, often in a half-a**ed way, since we weren’t programmers. We did have lots of RI and normalized data, but the front end code wasn’t anything to showcase. Now it seems we’re in the reverse situation. There is a lot of nice front end code, well documented and structured, but the database is a bit of a mess.
Where we go from here I’m not sure, but I expect that the next couple of weeks will involve some root cause analysis as well as some refactoring of code. I’d prefer to throw out the nHibernate design and build a simpler structure that is easier to tune, as well as maintain, but I’m not sure that will happen.
However I will try to continue to update you with information about how we will proceed.
I joined in late to a conference call this morning from Quest where a number of their experts were talking about various disaster stories that they'd experienced over the years. It's great to hear real DBAs talking about the problems and challenges they've actually experienced, and it should give those that haven't some confidence that they can learn to deal with one.
One of the presenters said that you'd be guaranteed to have a disaster at some point in your career. At first I thought, sure, that's probably true and I've had a few of my own, but then I started thinking about all the DBAs that get interviewed, that don't know about restoring to a point in time, that never restore lots, just fulls, and that have jobs and are successful.
SQL Server gets more stable all the time. Hardware becomes more redundant, disk drives detect errors before they occur, CPUs are manufactured better, I wonder if everyone will have a disaster. And what's a disaster?
Is it when someone deletes data? Is it when a patch doesn't work and you have to uninstall it? I guess those as disasters, but they're minor ones. Rolling back an upgrade, patch, or application change might involve some of the skills that you use in an unexpected disaster, but I'm not sure I think that's what I'd consider a disaster. More an incident to me.
I do think, however, that you should be prepared, and if you haven't practiced any of those skills involved in recovering, you should. Everyone should
And probably more importantly, keep your resume up to date. If a disaster strikes, hopefully you'll be able to solve it and get things fixed. However you never know when you'll take the blame for it, whether you are at fault or not. And you don't know if your company will survive.
As my son has learned in scouting, "Be Prepared" for most anything that comes your way.
I saw a question today on restoring a SQL Server 7 backup to SQL Server 2005, and I wasn't sure this was supported. So I went browsing through Books online and sure enough, it's supported. I ended up in the SQL Server 2008 books online first and it's not supported there, so it appears that you get to restore from the 2 previous versions at least for now.
That's good and it means that upgrades are possible through backup and recovery, which is a nice, safe way to move things around as far as I'm concerned. I always prefer having a fallback, in this case the original server instance.
Should MS support more versions? I'm not sure they should. That's a lot of backwards compatibility for them to maintain. I know it would seem simpler since they have the 70 mode in the database, but I think interpreting comments is much different from converting the file format.
However they have the code to convert 70 databases to 80. And from 80 to 90. Why not include a utility on the media that converts old backups to a newer format?
After being gone for 4 days, I've got a little catching up to do. I try to be prepared before I leave, but I also like to be ahead of the curve, especially with the daily grind of articles, QODs, scripts, and of course, editorials.
I took my video laptop on vacation, partially since I thought I might do some editing and my wife wanted me to edit a video for her. I did her stuff, but decided to enjoy vacation, didn't really look at email, and didn't edit down any of the footage on my laptop. It's still there, or it was yesterday am. Since traveling home I haven't fired up the laptop to see if things are still working.
In going through things for today, I ran across this blog from Paul Randal on backups and thought it was interesting. When I was packing up last Thursday, one of the things I did was to connect my external WD drive to the laptop and run a backup. I used the standard Vista backup tool since I didn't want to pick through things and I have them fairly well organized. I didn't take it with me as I don't really need real-time backup. If the laptop fails, I'll deal with it when I get back.
However the blog got me thinking since we have lots of non-work related stuff spread around here. I typically try to get the pictures and video from the family life and get that onto my desktop and then process, upload, backup , etc. from there. I'm woefully behind, especially with kids stuff in getting pictures and video up on the web. Every once in awhile Tia will grab pictures from the camera for her machine when I'm slow, but I've asked her to leave the pictures on the camera so I can get them into one place.
After nearly 7 years of digital work, I have many GB of stuff. There's an external drive up here, but I haven't backed up in a few weeks, something I need to do today. The drive flakes a little, so I don't leave it on and as a result, I don't regularly do backups.
However moving to a more regular scheme means that we really need to then invest in backups. I have an old machine, but the spare drives were flaky, so it's off. Plus we were lacking power in the office. I probably need to put it downstairs in the "studio", use it to drive a monitor or two behind me, and also make it available on the network for a general storage place. It would be better than putting a Drobo up, at the $500-700 cost, but not easier or safer. At least not unless I put $300 in there and added RAID capabilities.
It's a similar problem with corporate data. Companies often need to invest quite a bit of money in their backup schemes to handle the amount and speed of data. Add in the desire to capture stuff from laptops and desktops, and you sometimes are talking a few FTEs and lots of $$.
If you're really paranoid, you need to have a few backups. I've taken to carrying around a 4GB flash key that holds my password safe, important documents, and I transfer stuff with it. However it's not a great solution and it doesn't handle my larger Outlook/Picture stuff.
And don't forget iTunes. It asked me today to backup my purchases since I bought a few things. It took 5CDs to do that, which is annoying. I need to see if they support DVDs, which is what I really need.
