Can you please suggest few of the techniques/procedures that we should do on database to run the system without any harm and to save from danger situations and to identify it before as like prior intimation?

SQL Server Administration
, , , ,
1

Can you please suggest few of the techniques/procedures that we should do on database to run the system without any harm and to save from danger situations and to identify it before as like prior intimation?

It's not limited only to the performance. What I really worried is, we are working with really very large database systems and few days back we identified that one of the table's identity column value is very close to reach the max value of that data type, it's almost like if we don't identify this issue, damn sure within 6 months the table will give overflow error and the system will be broken and it can't be solved immediately since this table identity column is used in more than 3000 stored procedures. Just think once if we don't identify this issue, the loss will be huge even the total application systems will stop working which are pointed to this table. Then after I have implemented a script to monitor the identity columns and calculating the expected run out date.

These are the ones that I’m more worried about actually. Things that could cause an outage. Just I want to know these type of very dangerous checklists to protect my database system from huge loss.

Now, really what I want to do is, to have an eye on this kind of problems.

Could you please let me know on what areas really we should monitor to avoid the horrible problems/situations on database?

Just I want to know these type of very dangerous checklists to protect my database system from huge loss.
Please help me on this.

2

If you need an emergency-fix for IDENTITY approaching its limit, and provided that your IDENTITY has only used Positive numbers, then reset the SEED to the largest NEGATIVE number - that increases the available "pot" of numbers by 2x, so will buy you some time whilst you change the column to BIGINT or whatever and retest the whole APP :frowning: . Check that nothing untoward will happen by using negative numbers - it probably won't work if the ID is for an Number that you print on an Invoice and send to a Customer !!

We use sp_Blitz to check if there is anything goofy on our servers & databases, but I'm pretty sure that does NOT check for IDENTITY approaching limit. sp_Blitz comes from what I like to call "Tara's outfit" :slight_smile:

What you are asking for is either a checklist of all Gotchas that DBAs have ever found :slight_smile: and whilst many DBAs have lots of experience I doubt any know all of the Gotchas, and I don't know of any place where there is such a list, or a utility to check them.

I have a folder stuffed full of "SQL Snippets" that I use, but its all based on experience and we either had to have had a failure, which I want to prevent happening again, or I read of someone else who had a failure and don't what that one to happen to me!!

I'm glad that I don't write safety-critical software which peoples' lives depend on ...

3

There is no particular script or tool which could help you on such level. You must regularly take database backup. And in case of any issue or error. We use SQL Server database repair tool in case any issue occur.