Database getting full

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

Mr.Eric,

It worked.... Many Many thanks for your help... with my bottom of my heart, I thank's for your kind help...

Goin with this problem's, I have one more question for you. Hope as ausual you will answer it in a flash.. The question's is:

I have a database and all What I want to know is, when it reaches to occupy all of the size on the device, I need system to notify me about this Event. Doesn't matter either through e-mail or through a pager. But, Is it possible? If so, would you please advice me...

Once again, Thank you with my bottom of heart.

Kaleem

-- Anonymous, September 21, 1999

Answers

Kaleem,

I presume that you would like to set an alert when the log space is full or almust full on your database, since that is the most likely way a database will become full in a hurry.

To create an alert when transaction log is xx% full, you must use Performance Monitor (PM). First, open performance monitor in NT. Second, select View -> alert. Third, create a new alert by clicking the '+' button.

With SQL Server you get a set of SQL counters for Performance Monitor. The PM object is "SQLServer-Log", the counter is "Log Space Used(%)" and the Instance is the name of your database. You must be in the Alert view of PM. Then you can run a PM alert when the counter is over the specified value and you also can run a program on that alert. A program called SQLALRTR comes with SQL Server and this program can (by using ISQL and the RAISEERROR comand) write an event into the NT Event Log. Finally, you have to define a SQL Server alert for the specified error. You can also add your own errors (with an error number over 50,000), so you can explicitly write any text you want for that error. So it works in this way:

- Performance Monitor counter causes an PM alert, when counter treshold is exceeded.

- PM alert fires SQLALRTR program, which causes an event in the NT Event Log with specified error number.

- SQL Alert, defined on the same error number, fires (and perhaps takes some action, including email or pager notification, and possibly truncating the log). More about SQLALRTR and other options can be found in the SQL Server Books Online topic, "Generating Alerts from Performance Monitor Thresholds".

Hope this helps,

Eric

-- Anonymous, September 21, 1999


Moderation questions? read the FAQ