| Author |
Topic |
|
jusmeig
Starting Member
5 Posts |
 Posted - 2007-10-16 : 06:34:08
|
| Hi there,Firstly to give you some background. I’m working for a small company who should (but can’t) employ someone to look after what is becoming a huge database.I work as a Web Developer and as such my DBA skills will not win any awards!!! I know how to write Stored Procedures and SQL....but I’m not up to speed keeping things working under the hood!Just recently when deleting lines through SQL Server Management Studio, I am getting and error: could not delete. Possible index corruption. Run DBCC CHECKDB.Now in the past I did run the above, and the problem still remains. More recently I have clicked on the index and clicked "rebuild". In one such case the fragmentation of the index had reached 92%!!! There is no maintenance plan as such in place for our DB, and we have several tables with 3+ million lines (Maybe 30-40 tables in the entire DB). Are we crazy??? Are we also very lucky to date that nothing serious has gone wrong?Is it time to get a DBA in for a few days to sort the above!!!! (All the DBA contractors say yes...)Thanks in advance.Justin |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-16 : 06:58:48
|
| SQL 2000 or SQL 2005?Run DBCC CHECKDB('MyDatabaseName') WITH NO_INFOMSGSand see what you get. Hopefully nothing!yes you need maintenance plans to rebuild indexes and update statistics regularly, but you can use the Wizard for that.I trust you have regular backups?I'm happy to fly over and charge you a consultancy rate, but apart from the chance of some Guniess I doubt that you need me to!Kristen |
 |
|
|
jusmeig
Starting Member
5 Posts |
Posted - 2007-10-16 : 09:10:16
|
| Hi Kristen,Yes we do keep backups on a daily basis, which are stored on another server.OK I ran the command you asked and it came back (after quite some time)Command(s) completed successfully.Cheers,Justin |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-16 : 09:48:24
|
| That's good to hear. There might be a corruption in another database, so worth running that on all of them. (It will take a while, roughly proportional to the size of the DB)Also check the Event Log to see if there are any recent hardware faults. if your system is "failing" better to know about it sooner rather than later ...Kristen |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-10-16 : 10:03:12
|
| "...backups on a daily basis"...which does means you evaluated the cost of losing a whole days input/changes and found the cost lower than the cost of having continuous transaction/differential backups??? And have you proved your restoration strategy?Large databases aren't a big problem per se...once the proper processes are in place to support them. A bigger issue is managing the growth.....i.e. having a proper achive/deletion policy in place (if required). As well as having proper security and access methods.A small investment of time (and money) can save a lot in the long run. It doesn't have to be done all in one go. Small simple steps over a period of time can work quite well. Review the blogs of members NR, Kristen, TKizer and follow their best practices. Search here for links to (starting with the Kristens FAQ page posted on SQLTeam)Simple exercise (if you dare)....take the DB off-line and then see how much attention management give to their database being unavailable!. |
|
|
|
jusmeig
Starting Member
5 Posts |
Posted - 2007-10-16 : 10:03:27
|
| Its a dedicated database server, so I can rule out hardware failure.I will scan the other DB's, but this is the only one in use.I also took your advice and used the wizard to set up a monthly re-build of our indexes, as far as I know they have never been re-built ;(Erm.....the baove failed. I got a message to view the History Log? I assume this is the "Error Log". When I open that all I see is red X's saying: "Unable to read local eventLog [reason: the parameter is incorrect]"?Any idea? I guess my attempt to re-build indexes failed! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-16 : 10:39:29
|
| "Its a dedicated database server, so I can rule out hardware failure."Not sure that lets it off the hook ... one of the RAID drives may have failed, and you won't know about it until the other ones goes South ... (although I expect you have that covered, but 99.999% of database corruptions are hardware failures. That part of the database may have been reused since, which will have covered the tracks of course"I will scan the other DB's, but this is the only one in use."The system will need MASTER and MSDB to be "healthy" for your database to continue to work. Mind you, a corruption in an UN-used database could be a wake-up call, so worth checking them all when you have a moment."a monthly re-build of our indexes"I would do it as often as you can spare the system time. For some of our clients that once a week, at the weekend, but for most of them its every night (during the "quiet hours"). Might as well have the database in tip-top shape as often as it can be done."I got a message to view the History Log? "RightClick "Maintenance Plan" and choose "View history". You have to clutse about a bit checking check-boxes, but I'll sure you'll find it easily enough. Its probably the "Job history" you need (once you get to the point where you can select that)Kristen |
|
|
|
jusmeig
Starting Member
5 Posts |
Posted - 2007-10-16 : 10:57:49
|
| I will consult with our host to check the hardware.Regarding re-indexing. Basically this is the only database in use on this dedicated server. There is data coming in from midnight each night until around 11am. There is then another mass input of data @ around 12-1pm.This gives me a small window to run really long processes. I guess I can re-index while importing data?I viewed the History Log, the job failed as the transaction log was full. I have emptied it and increased its size + growth.Cheers,Justin |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-16 : 11:10:23
|
| You need to be careful that the tables are not locked by the reindex. (There are "blocking" and "non-blocking" alternatives available, not sure that the Wizard gives you the luxury of choosing though!)Ideally the system will only reindex tables which are fragmented, and thus the time-to-reindex should be short if they are done often, because few tables will need doing. But that's moving out of Ziardard territory into hand-built-procedures-land.It figures that the transaction log would fill. Reindex is a very log intensive process.You could set the log to grow as-needed (it might fill your whole disk though ...), or re-size it upwards. It will get to a size that is in equilibrium.If your database is in FULL Recovery Model you need to make sure that the Transaction Log is backed up frequently. Every 10 minutes is a good interval. We increase that to every 2 minutes during index rebuilds.Kristen |
|
|
|
jusmeig
Starting Member
5 Posts |
Posted - 2007-10-17 : 06:19:37
|
| Thank you again Kristen. Having completed a re-index I can already see improvements in the time to pull back large datasets from a group of tables we query via a view.Regarding the table locking, I will just run the re-index at a time when I am 100% there will be no data access. I am locking down the application that uses the database also to ensure the above!I also see one of the options in the manager is "Reorganise". Is it also a good idea to run this?Justin |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 06:58:21
|
| "Reorganise" sounds like the NON-blocking version of "reindex" ... but best you check the DOCs to be sure. If you can find anything there that lets you only rebuild fragmented files that will reduce the time it takes to run - all your code tables that have had no new rows added since last time don't need a rebuild!!Kristen |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-10-17 : 07:09:46
|
| search for some stuff created/posted by member tkizer....I think she's got something that's efficient at only tackling "fragmented" files. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 07:14:25
|
| here you go:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Tara%20Blog%20Houskeeping%20Routines |
|
|
|
|
Possible index corruption