Tech Knowledge Share

PROBLEM: You want to shrink all SQL Server databases  transaction   log files to clean up some space SOLUTION: Use the following script to shrink all databases transaction log files. You can use it also as a maintenance job using SQL Server Agent DECLARE   @Log_name   VARCHAR ( 1000 ) DECLARE   @Db_name   VARCHAR ( 1000 ) DECLARE   @Recovery_model_desc   VARCHAR ( 1000 ) DECLARE   @SQL   nvarchar ( 2000 ) DECLARE   @ParmDefinition   nvarchar ( 1000 ) DECLARE   @SizeAfter   int DECLARE   db_cursor   CURSOR   FOR SELECT         F . NAME   AS   [LOG_NAME] ,         DB . NAME   AS   [DB_NAME] ,         DB . RECOVERY_MODEL_DESC   AS   [RECOVERY_MODEL_DESC] FROM         MASTER . SYS . MASTER_FILES   F   INNER   JOIN   MASTER . SYS . DATABASES   DB         ON   DB . DATABASE_ID   =   F . DATABASE_ID WHERE   F . FILE_ID = 2  AND   DB . NAME   <>   'tempdb' OPEN   db_cursor           FETCH   NEXT   FROM   db_cursor   INTO   @Log_name ,   @Db_name

General Standards: (Code Format, Naming Conventions, Datatype and Data Length, Syntax): §   Always follow a template in designing stored procedure so that it can easier developer job while designing and integrating. For example each stored procedure should be defined as various blocks such as “Comments Section”, “Variable Declaration”, “Actual Body”, “Audit”, ”Exception Handling”, “Temp_Obj_Removel” and define environment sections if any required. §   Check proper comments are used or not. Always describe procedure, inputs and expected output in comments section. §   Check naming conventions are used properly for procedure name, variables and other internal objects. §   Check all objects used inside the procedure are prefixed with the schema name and column names are referencing with table alias. §   Check all table columns used / mapped are using the correct datatypes and column length. §   Check if all required SET based options enabled are not. §   Check if there ar

1.      Check for all the warnings given on compiling of the code and try to remove the warnings 2.      Perform  Code analysis tool and check for the following a.      Constraint Violators b.      Redundancies in Code c.       Redundancies in Symbol Declarations d.      And most important – Solve the Compiler Error 3.      “null” check needs to be performed where every application to avoid Null Reference Exception error. 4.      Naming Conventions should be followed with all variables following Camel Casing , and Class names following Pascal Casing. This rule helps in making Code better readable and this makes it more maintainable. 5.      Liskov substitution – Any derived class should not modify the behaviour of the Base Class. 6.      Code Reusability – Extract the piece of the code that is to be used multiple times and modularize the code. Developers when detect the requirements coming in are causing the code to get repetitive then the Generic methods shoul