sql server

3 Topics

Cleanup of auto created statistics
Hello everyone, during annual database checkup i found that there were a lot of auto created statistics, which weren't used. It seems that DBMS doesn't cleanup unused auto created statistic on his own. i guess i can drop them with the following query: DROP STATISTICS Schema.TableName._WA_Sys_XXXXXXXXXXXXXXXXXXXX But i am still thinking whether it is safe, about impact on the performance. Has anybody had any experience with that?
Solved
KarolFerek
Dec 19, 2024 Place SQL Server Engine
340Views
0likes
8Comments
Catch Error in a SQL Server Logon Trigger
I have written a Logon trigger in a SQL Server to control logon authorization. ALTER TRIGGER [LOG_TRG_01] ON ALL SERVER WITH EXECUTE AS 'SA' FOR LOGON AS BEGIN if ORIGINAL_LOGIN() = 'sa' begin return; end; if ORIGINAL_LOGIN() = 'OMEGACAEVDEV1' begin -- not allowed to logon rollback; end; -- Insert Trail EXEC [OmegaCoreAudit].[OMEGACA].[P_ACC_UNF_TRAIL] 0, 'trail_details', 'ip', 'server', 'db', 0 ; END GO It does (as expected): OMEGACAEVDEV1 is not allowed to logon OMEGACAEVDEV2 is allowed to logon An audit event is inserted by proc P_ACC_UNF_TRAIL in a table for both users. All three above I want to stay this way ! But I need to have error handling in it, so that in case of whatever error the login is allowed to login - but keeping a record on another table named SYS_ERROR_LOG (with error details). In this trigger I have intentionally provoked an error by "select 10/0" The new trigger is: ALTER TRIGGER [LOG_TRG_02] ON ALL SERVER WITH EXECUTE AS 'SA' FOR LOGON AS BEGIN BEGIN TRY if ORIGINAL_LOGIN() = 'sa' begin return; end; --provoke error select 10/0; if ORIGINAL_LOGIN() = 'OMEGACAEVDEV1' begin -- not allowed to logon rollback; end; -- Insert Trail EXEC [OmegaCoreAudit].[OMEGACA].[P_ACC_UNF_TRAIL] 0, 'trail_details', 'ip', 'server', 'db', 0 ; END TRY BEGIN CATCH Insert into OmegaCoreAudit.OMEGACA.SYS_ERROR_LOG ([LOGIN_USER_NAME], [DB_USER_NAME], [USERHOST], [IP_ADDRESS], [OS_USER], [BG_JOB_ID], [ERROR_DATA]) values ('LOGIN_NAME', 'USER_NAME', 'USER_HOST', 'IP', NULL, NULL, 'ERROR_MESSAGE'); END CATCH END GO In the above code "if ORIGINAL_LOGIN() = 'OMEGACAEVDEV1'" represents a simplified version of a wider authorization process. Problem: CATCH is not working. Both users are not allowed to logon ("Logon failed for login '[user]' due to trigger execution") No record is written on table SYS_ERROR_LOG. I was expecting one for each user. What can I do to fix this problem? best regards Altin
Solved
akaraulli
Dec 17, 2024 Place SQL Server Engine
383Views
0likes
13Comments
SQL 2008 R2 Service Terminating Unexpectedly - Access Violation
We have SQL 2008 R2 SP2 and it is terminating and restarting daily. This is a big problem because it has our company financials application on it and uses are kicked out while the service restarts. I have attached the SQL dump file for your review. Additionally, when it does go down we get the following entries on the exception log: 09/18/18 00:03:19 spid 205 Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION reading address 00000F0054097B98 at 0x00000000771CEEF1 09/18/18 00:03:33 spid 206 Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION reading address 000000FF58FAB4D8 at 0x00000000771CEEF1 We have tested all the databases for errors and the backup jobs do not seem to be a problem either. Lastly, We cannot upgrade the SQL server to anything higher that 2008 R2 SP4 because the accounting package will not run of anything higher. I would appreciate any assistance. Todd
Todd Hank
Sep 27, 2018 Place SQL Server Engine
1.4KViews
0likes
1Comment