| Author |
Topic |
|
yaro137
Starting Member
7 Posts |
 Posted - 2010-11-26 : 10:47:23
|
| I don't have much experience with SQL administration and have a problems with a SBS2003 server running SQL. The hard drive is grinding because of whatever SQL server is doing on it. As soon as I stop the SQLSERVER service all goes back to normal. When I start it back again after a while it does it again. I know that it runs a couple of databases. How would I go about pinpointing what causes such a high disk activity?yaro |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-26 : 10:49:50
|
| try running sp_who2 to see what's running - pay particular attention to anything which has high diskio.You can use dbcc inputbuffer to see what is being run.On v2008 you can use the data collector to get high resource usage queries.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
yaro137
Starting Member
7 Posts |
Posted - 2010-11-26 : 11:17:57
|
| Interesting. Thanks a lot. At the moment it shows lots of Sleeping processes and I'm guessing where it says HostId is where from someone is connected to the database. Mostly it's on host/user running Office 2007 system and the command field status is AwaitingCommand. for some of them CPUTime and DiskIO is over 1k and in one case over 41k. I'll keep an eye on it. Thanks for the great help.yaro |
|
|
|
yaro137
Starting Member
7 Posts |
Posted - 2010-11-26 : 11:28:38
|
| Just read that high IO doesn't necessarily mean something bad as long as it's accompanied by high CPU. If the CPUTime is high however and DiskIO low that's bad. yaro |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-26 : 14:20:28
|
| Do you every do Housekeeping on the database? If not the Indexes are probably "out of shape" and the statistics about them are probably "stale" in which case it may be having to work awfully hard to answer even a simple query.Rebuild indexesUpdate statisticsIf you are not in the habit of housekeeping them suggest you do Check Database Consistency too - in case anything is corrupted etc.Might be that the disks are wearing out if they are noisy when busy? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-11-27 : 06:20:07
|
| Also -- do you have backups? It sounds like this system isn't paid much attention to but people are using it.1) do you have backups2) how old are they3) have you tested they are valid.Then start looking into potential performance problems.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
yaro137
Starting Member
7 Posts |
Posted - 2010-11-29 : 06:38:55
|
| Not sure what's involved in housekeeping. As I said I don't know much about administering SQL server. I run daily smbackup on that server and test it monthly. Rebuilding and updating sounds like something that could put the server to its knees so I'll leave it for the next weekend. It gives me some time to do some reading on how's that done. Thanks again.yaro |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-29 : 07:10:00
|
"smbackup"I don't recognise the name. Is that some sort of plug-in that directly back's up the Database to Tape? If so they scare me (although there are some that people have high regard for, but we get a lot of questions here about restoring from database-to-tape software that has failed)"test it monthly"Ideally that would be:Restore to a different machineRun a DBCC CHECKDB on it to check that there are no errors detectable by SQL Server |
|
|
|
yaro137
Starting Member
7 Posts |
Posted - 2010-11-29 : 08:01:44
|
| Oh no, smbackup is the generic Windows backup tool which backs up everything on the server to an external drive. As to SQL no, there is no separate full backup set up for SQL as far as I can see, nothing in backup devices. There are some database backups set up in Maintenance Plans but I don't know what they do and who set them up. Possibly the people who installed the software the company uses for they booking system.yaro |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-29 : 08:42:46
|
| "smbackup is the generic Windows backup tool"Ah, OK. Sorry, didn't recognise the name.If you are doing a file-copy with SQL Server running then the databases most likely won't be recoverable after a restore,Either SQL server needs to be stopped (and then you take a tape backup), or you need to use SQL's BACKUP command to create a backup file on disk - typically XXX,BAK - which you can then copy to Tape, and will be easy to restore.I do ***NOT*** recommend the first method, but if that's what is currently happening its better than nothing. If you need to restore from Tape to a DIFFERENT drive to the original then you may find that you cannot restart any of the databases, but it will probably not be insurmountable, just very annoying and waste time when you are probably having an emergency anyway!If that's what you have then that's fine (but you ought to be doing a RESTORE of the SQL Backup file on another machine periodically to check that the backup can be restored, and you should use SQL "Check Database Consistency" command on the restored database to check that the database, after being restored restored, is both NOT corrupted and its internal structure and data is "logically consistent") |
|
|
|
yaro137
Starting Member
7 Posts |
Posted - 2010-11-29 : 11:41:15
|
| Smbackup uses shadow copies so it can backup a database on the fly. It does it to Exchange so I can't see a reason why it wouldn't be able to do it with SQL but I may be wrong. The company doesn't really have resources to have a spare server just for testing. I'm just creating a performance baseline so when the server starts grinding again at list I have something to compare the logs against. yaro |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-29 : 19:58:20
|
| "Smbackup uses shadow copies so it can backup a database on the fly"Are you 100% sure that will work for SQL? This is normally where conversations start when people come to this forum asking how to recover a database that they are trying to restore having done a file-level-copy to tape.There are NO DBAs on this forum that I know of who use file-copy of database files, all of them use SQL BACKUP command to create a BAK file and then back that up to tape.If I've got that wrong I'm sure they will post a reply to tell us both I am also worried that if you had to restore your database to a different drive configuration then a file-level-copy would be of limited use. SQL will be expecting to find all the databases in the exact original file locations (whereas if you use SQL commands to RESTORE from a BACKUP file then you can specify exactly where you want the database to be created) |
|
|
|
yaro137
Starting Member
7 Posts |
Posted - 2010-11-30 : 08:54:19
|
| Don't know why but I've got the impression that whenever I post a replay it only gets published here when I do it twice. So basically I'm posting a replay and nothing happens even though I get a message saying that it was successfully added. The second time I do it it works all right. Bizarre....Anyway, you're right about the smbackup. I read a few more posts and all of them confirm what you said. I went dipper to the Maintenance plans and found some jobs there that have the name of the various databases with the word "backup" and they're scheduled daily and the logs say they're successful. Also after running my baseline when no one complained about the server running slow I noticed that one of the counters, SQLServer:Databases Application Database\Percent Log Used shows 99 in case of one of the databases. I understand it's not well. How can this be fixed?yaro |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-30 : 11:46:48
|
| You probablty need to backup the log - which will mark the pages [which are backed up] available for reuse. |
|
|
|
|
Performance issues