6.5 performance counters/offloading logs before 1105s

[Ken Proctor]

I want to automate the offloading of SQL Server 6.5 logs before they become full. I've set this up 'manually' by triggering a job to run when the LogSpaceUsed threshold of the SQLServerLOg counter exceeds a certain value, but what I want to do now is to automate the starting of this performance counter. Ideally what I'd like is to (1) Start the counter once SQL is up, having (2) first ensured a particular instance of the counter isn't already executing (3) have some means of detecting whether this counter ever stops running in which case we'd have to restart it. This is probably more of an NT question than anything but if anyone has experience of this please let me know....I have tried trying to get SQlServer to start the performance counter as it starts up. I used sp_makestartup to define a stored procedure which runs on startup, this uses xp_cmdshell to call a CMD file which calls the saved counter definition (a .pmw file, performance monitor workspace) I thought this should work but the counter wasn't loaded......this doesn't check whewther an existing version of the counter is already running anyway.

[Craig]

There are known memory leak bugs with running performance monitor 24X7 with 6.5 and I would not recommend it.

Why don't you just properly size your transaction logs and dump the log every hour or half-hour or whatever time you need?

It seems like you are going to automate something simple into something complicated..........


------------
Ken Proctor at 5/25/99 11:10:56 AM

I want to automate the offloading of SQL Server 6.5 logs before they become full. I've set this up 'manually' by triggering a job to run when the LogSpaceUsed threshold of the SQLServerLOg counter exceeds a certain value, but what I want to do now is to automate the starting of this performance counter. Ideally what I'd like is to (1) Start the counter once SQL is up, having (2) first ensured a particular instance of the counter isn't already executing (3) have some means of detecting whether this counter ever stops running in which case we'd have to restart it. This is probably more of an NT question than anything but if anyone has experience of this please let me know....I have tried trying to get SQlServer to start the performance counter as it starts up. I used sp_makestartup to define a stored procedure which runs on startup, this uses xp_cmdshell to call a CMD file which calls the saved counter definition (a .pmw file, performance monitor workspace) I thought this should work but the counter wasn't loaded......this doesn't check whewther an existing version of the counter is already running anyway.

[ken.proctor]

Craig,

I'd be interested to know where I can find more information on the memory leaks you mention. We are dumping our transaction logs hourly already, just trying to eliminate all possible causes of loss of service or being paged in the middle of the night....


------------
Craig at 5/26/99 9:10:31 AM

There are known memory leak bugs with running performance monitor 24X7 with 6.5 and I would not recommend it.

Why don't you just properly size your transaction logs and dump the log every hour or half-hour or whatever time you need?

It seems like you are going to automate something simple into something complicated..........


------------
Ken Proctor at 5/25/99 11:10:56 AM

I want to automate the offloading of SQL Server 6.5 logs before they become full. I've set this up 'manually' by triggering a job to run when the LogSpaceUsed threshold of the SQLServerLOg counter exceeds a certain value, but what I want to do now is to automate the starting of this performance counter. Ideally what I'd like is to (1) Start the counter once SQL is up, having (2) first ensured a particular instance of the counter isn't already executing (3) have some means of detecting whether this counter ever stops running in which case we'd have to restart it. This is probably more of an NT question than anything but if anyone has experience of this please let me know....I have tried trying to get SQlServer to start the performance counter as it starts up. I used sp_makestartup to define a stored procedure which runs on startup, this uses xp_cmdshell to call a CMD file which calls the saved counter definition (a .pmw file, performance monitor workspace) I thought this should work but the counter wasn't loaded......this doesn't check whewther an existing version of the counter is already running anyway.

[Cindy]

We wrote a program inhouse that runs dbcc checktable (syslogs) to update the log size information, check the log size, and do a dump tran with no_log and dump database when the log is more than 85% full. It also writes a message to a log so that we can track which logs are being dumped frequently and either increase the log or tune the application so that it doesn't use so much log space. You might want to do something similar. We have another program which runs this utility (and others) against all SQL Servers listed in a table.

If you are loading a lot of information with BCP and can remove the indexes, you can use the select into option to reduce logging.

Also, make sure you aren't doing unnecessary updates (updating all columns when you could update just one and possibly have an update-in-place, not storing status values that change very frequently, etc.).

Would truncate log on checkpoint be an option for you? If you need recoverability but need to turn that option on, you will need to do a full database backup frequently.

------------
ken.proctor at 5/27/99 7:52:24 AM

Craig,

I'd be interested to know where I can find more information on the memory leaks you mention. We are dumping our transaction logs hourly already, just trying to eliminate all possible causes of loss of service or being paged in the middle of the night....


------------
Craig at 5/26/99 9:10:31 AM

There are known memory leak bugs with running performance monitor 24X7 with 6.5 and I would not recommend it.

Why don't you just properly size your transaction logs and dump the log every hour or half-hour or whatever time you need?

It seems like you are going to automate something simple into something complicated..........


------------
Ken Proctor at 5/25/99 11:10:56 AM

I want to automate the offloading of SQL Server 6.5 logs before they become full. I've set this up 'manually' by triggering a job to run when the LogSpaceUsed threshold of the SQLServerLOg counter exceeds a certain value, but what I want to do now is to automate the starting of this performance counter. Ideally what I'd like is to (1) Start the counter once SQL is up, having (2) first ensured a particular instance of the counter isn't already executing (3) have some means of detecting whether this counter ever stops running in which case we'd have to restart it. This is probably more of an NT question than anything but if anyone has experience of this please let me know....I have tried trying to get SQlServer to start the performance counter as it starts up. I used sp_makestartup to define a stored procedure which runs on startup, this uses xp_cmdshell to call a CMD file which calls the saved counter definition (a .pmw file, performance monitor workspace) I thought this should work but the counter wasn't loaded......this doesn't check whewther an existing version of the counter is already running anyway.