LINKS
General
Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Script
Knowledgebase Home
Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Script
Knowledgebase Home
MOST RECENT
How can I email the output of a scheduled CLI job (e.g. reports, log files)?
How to Execute large scripts generated by the Recovery Wizard
How to uninstall the ApexSqlConnectionMonitor.exe process
How To Choose between ApexSQL Audit and ApexSQL Log
What to do with large log files due to index rebuilds
Get DDL Information from Transaction Log
ApexSQL Log Basics Part 3 of 3: Truncated Data Recovery
ApexSQL Log Basics Part 2 of 3: Dropped or Deleted Data Recovery
ApexSQL Log Basics Part 1 of 3: Working with a SQL Server Transaction Log
SITE FEED
Support Forum
The fastest and most effective to get answers to your questions quickly. Click Here for more info.
FAQs 
Quick answers to common questions. Click Here for more info.
Troubleshooting
Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.
Knowledgebase
Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.
Online Documentation
All of our help files for download or convenient viewing online. Click Here for more info.
Videos
Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.
|
ApexSQL Knowledgebase Tips and How-to Articles for Apex SQL Tools How can I email the output of a scheduled CLI job (e.g. reports, log files)? DESCRIPTION To do this, we will use a SQL Server job. Below steps discuss how to email an audit report generated by ApexSQL Log (through the command line interface) SOLUTION I. In SQL Server 2005, execute the following steps: 1. Configure mail profile and account on your database server: CODE: EXEC SP_CONFIGURE 'show advanced options', 1; GO RECONFIGURE; GO EXEC SP_CONFIGURE 'Database Mail XPs', 1; GO RECONFIGURE GO EXEC MSDB.DBO.SYSMAIL_ADD_PROFILE_SP @profile_name = 'DBProfile', @description = 'Profile used to send mails.' ; GO EXEC MSDB.DBO.SYSMAIL_ADD_ACCOUNT_SP @account_name = 'DBAccount', @email_address = 'DBAccount@localhost', @display_name = 'Mail from Server', @mailserver_name = ' @username = ' @password = ' GO EXEC MSDB.DBO.SYSMAIL_ADD_PROFILEACCOUNT_SP @profile_name = 'DBProfile', @account_name = 'DBAccount', @sequence_number = 1 GO Where: Database Mail may be configured manually from Microsoft SQL Server Management Studio:  More detailed information can be found: here 2. Create a Job for SQL server and schedule it to run every Monday, 11PM. a. Open SQL Server Studio and create a new job:  b. Add new step to the job and specify ‘Operating System (CmdExec)’ as the command type:  c. Paste your CLI command into the ‘Command’ edit box: CODE: APEXSQLLOG.COM /S:TESTSERVER /D:TESTDB /SQL:<FULL PATH>\RESULT.SQL /VERBOSE The results of execution will be outputted to d. Create another step with 'Transact-SQL (T-SQL)' type and paste the following to 'Command' edit field: CODE: EXEC MSDB.DBO.SP_SEND_DBMAIL @profile_name = 'DBProfile', @recipients = ' @subject = 'Mail from DB', @file_attachments = ' Where: @recipients - Is a semicolon-delimited list of e-mail addresses to send the message to. @subject - Is the subject of the e-mail message. If no subject is specified, the default is 'SQL Server Message'. e. To run the job every Monday, 11PM, create a new schedule for the job. On Schedules, click "New":  Next, setup the schedule:  II. Click here to get more information on how to do this in SQL Server 2000. AUTHOR David Dawson LAST REVIEW DATE 06 November 2007 Labels: ApexSQL Log, General ...How to Execute large scripts generated by the Recovery Wizard DESCRIPTION In case of recovery of huge databases Recovery Wizard can generate very large result scripts. Such scripts are saved by ApexSQL Log to the flat files at a given place. These files cannot be opened and executed using SQL Server utilities such as SQL Server Management Studio, for example. SOLUTION The only way to execute such large scripts is to run osql (or sqlcmd) command line utility that comes with SQL Server. Here are short examples of usage: a. osql -H <host> -S <server> -U <user> -P <password> -i <path> b. sqlcmd -S <computername\instancename> -i <path> -o <output> LAST REVIEW DATE 30 August 2007 Labels: ApexSQL Log ...How to uninstall the ApexSqlConnectionMonitor.exe process DESCRIPTION Uninstalling ApexSQL Log client components doesn't uninstall ApexSQL Log server-side components . ApexSQL Log is licensed per server thus, server-side components can only be installed on one machine while the client-side components can be installed on as many machines as the user wants. It is by design then that server-side components are not uninstalled when client-side components are uninstalled. The exception is when the server-side components are installed WITH the client-side components during setup.  SOLUTION In this case, uninstalling the client-side components also removes the server-side components. This whole approach may be changed in future versions of ApexSQL Log to avoid confusion. For now though, the following procedure will uninstall the server-side components of ApexSQL Log: 1. Execute the following script on your server: IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlLogMonitor_Stop') EXEC master.dbo.xp_ApexSqlLogMonitor_Stop IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlConnectionMonitor_Stop') EXEC master.dbo.xp_ApexSqlConnectionMonitor_Stop IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlLog') EXEC master.dbo.sp_dropextendedproc 'xp_ApexSqlLog' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlLogApi') EXEC master.dbo.sp_dropextendedproc 'xp_ApexSqlLogApi' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_InitLogNav') EXEC master.dbo.sp_dropextendedproc 'xp_InitLogNav' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_UnInitLogNav') EXEC master.dbo.sp_dropextendedproc 'xp_UnInitLogNav' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlLogMonitor') EXEC master.dbo.sp_dropextendedproc 'xp_ApexSqlLogMonitor' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlLogMonitor_Stop') EXEC master.dbo.sp_dropextendedproc 'xp_ApexSqlLogMonitor_Stop' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlLogMonitor_Info') EXEC master.dbo.sp_dropextendedproc 'xp_ApexSqlLogMonitor_Info' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlLogMonitor_Enable') EXEC master.dbo.sp_dropextendedproc 'xp_ApexSqlLogMonitor_Enable' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlLogMonitor_Disable') EXEC master.dbo.sp_dropextendedproc 'xp_ApexSqlLogMonitor_Disable' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlConnectionMonitor') EXEC master.dbo.sp_dropextendedproc 'xp_ApexSqlConnectionMonitor' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlConnectionMonitor_Stop') EXEC master.dbo.sp_dropextendedproc 'xp_ApexSqlConnectionMonitor_Stop' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlConnectionMonitor_Info') EXEC master.dbo.sp_dropextendedproc 'xp_ApexSqlConnectionMonitor_Info' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlConnectionMonitor_Enable') EXEC master.dbo.sp_dropextendedproc 'xp_ApexSqlConnectionMonitor_Enable' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_ApexSqlConnectionMonitor_Disable') EXEC master.dbo.sp_dropextendedproc 'xp_ApexSqlConnectionMonitor_Disable' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_EnableLiveLog') EXEC master.dbo.sp_dropextendedproc 'xp_EnableLiveLog' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_DisableLiveLog') EXEC master.dbo.sp_dropextendedproc 'xp_DisableLiveLog' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_GetLiveLogStatus') EXEC master.dbo.sp_dropextendedproc 'xp_GetLiveLogStatus' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'ln_open') EXEC master.dbo.sp_dropextendedproc 'ln_open' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'ln_is_open') EXEC master.dbo.sp_dropextendedproc 'ln_is_open' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'ln_is_valid') EXEC master.dbo.sp_dropextendedproc 'ln_is_valid' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'ln_seekga') EXEC master.dbo.sp_dropextendedproc 'ln_seekga' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'ln_seekgr') EXEC master.dbo.sp_dropextendedproc 'ln_seekgr' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'ln_tellg') EXEC master.dbo.sp_dropextendedproc 'ln_tellg' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'ln_read') EXEC master.dbo.sp_dropextendedproc 'ln_read' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'ln_close') EXEC master.dbo.sp_dropextendedproc 'ln_close' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'sp_ApexSqlLogMonitor_Start') EXEC master.dbo.sp_procoption 'sp_ApexSqlLogMonitor_Start', 'startup', 'false' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'sp_ApexSqlLogMonitor_Start') EXEC master.dbo.sp_executesql N'DROP PROCEDURE sp_ApexSqlLogMonitor_Start' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'sp_ApexSqlConnectionMonitor_Start') EXEC master.dbo.sp_procoption 'sp_ApexSqlConnectionMonitor_Start', 'startup', 'false' IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'sp_ApexSqlConnectionMonitor_Start') EXEC master.dbo.sp_executesql N'DROP PROCEDURE sp_ApexSqlConnectionMonitor_Start' DBCC LogNavXp (FREE) DBCC ApexSqlLogXprocs (FREE) DBCC ApexSqlServerXprocs (FREE) 2. On the command line of your server, go to the SQL Server's Binn directory and execute the following command from : ApexSqlServerHelper /unload 3. Delete all binaries that start with ApexSql* from SQL Server's Binn folder. 4. Drop msdb.dbo.APEXSQL_LOG_LOGIN table if you don't need data that's stored in it (it's your login data - that's why we don't drop it automatically) Alternatively, the server-side components can also be uninstalled from the ApexSQL Log client.  What is the ApexSqlConnectionMonitor.exe for? To obtain NT user name and other connection specific information, ApexSQL Log uses active connection monitoring through the ApexSQLConnectionMonitor.exe process. This process is designed to be very lightweight, fast, and non-intrusive so it does not interrupt the normal functioning of Microsoft SQL Server. Once the Connection Monitor is installed (it is a part of installed server side components), it will, for each new connection, store a row into a table. During auditing, ApexSQL Log queries the login table to obtain connection specific information for each transaction. ApexSQL Log returns this information as blank if information cannot be obtained. Note: Connection monitoring works only for Microsoft SQL Server 2000 and SQL Server 2005. It requires that the account under which SQL Server service is running has login permission to the SQL Server itself. Connection monitoring also allows user names on operations from linked servers (that occurred while it was alive) to be known. Moreover, with connection monitoring, ApexSQL Log can show user names even for dropped users. As not to consume machine resources, ApexSQL Log stops the process when it fails consecutively for 5 times in 1 minute or less. AUTHOR David Dawson LAST REVIEW DATE 04 March 2008 Labels: ApexSQL Log ...How To Choose between ApexSQL Audit and ApexSQL Log DESCRIPTION ApexSQL has two auditing tools--ApexSQL Log and ApexSQL Audit. How do you know which one is for you? There is certainly a tradeoff when using one over the other. This article is an overview on how to choose between the two. SOLUTION ApexSQL Audit is trigger-based auditing tool. It helps users create audit triggers that react to DML operations and then store details of the operation into one or more auditing tables. PROS - You get total control over what tables you want to audit and what operations. - Audit data is stored in SQL Server tables and can therefore be manipulated like any other SQL Server table; audit history is immediately available. - You can write your own reporting system based on the audit data stored in SQL Server tables. - We specially prevented auditing of system databases to avoid database crashes if something goes wrong (system tables locking, etc.) - As a rule, DBAs always have full access to the data. Therefore, they can change data in the Audit tables or even truncate them. - ApexSQL Audit has a separate reporting tool called ApexSQL Audit Viewer which can be purchased separately. ApexSQL Audit Viewer is a standalone reporting application for ApexSQL Audit. It can be distributed to team members and end-users who only need to view auditing reports and don't need access to the administrative features of ApexSQL Audit. CONS - It can possibly cause performance degradation--as is the nature of all TRIGGERS. ApexSQL Log on the otherhand is transaction log-based, that is, it reads the transaction log to get audit data. PROS - If you are already working in FULL RECOVERY mode, then there is no downside on the space/performance. - You can move transaction logs to another server and audit there - Comes with a Data Recovery tool; better UNDO/recovery and is essentially maintenance free. - You can schedule ApexSQL Log to read the transaction log on a schedule. Auditing doesn't happen right after the operation being audited happens giving you the option to do your auditing during offline hours. (Note: The only data that has to be captured in real time is additional login information--NT user name, application name and client host name--since this is not in the transaction log. This information is captured by ApexSQL Log's Connection Monitor after a new login is made. It has minimal overhead.) CONS - To use ApexSQL Log's auditing feature, your database should be in FULL RECOVERY mode. If you don't plan your maintenance right, your drives can get filled with transaction logs and this can have an impact on the performance. In the long run, there is a chance of running out of disk space. Switching to FULL RECOVERY is not a simple turn on/off thing. Once you switch to FULL RECOVERY, transaction log starts to grow so you have to prepare beforehand how to deal with that. - Update operations Both tools allow you to generate reports in various formats. SEE ALSO How to Synchronize a Database on a Schedule AUTHOR Janice Lee LAST REVIEW DATE 03 December 2007 Labels: Apex SQL Audit, ApexSQL Log ...What to do with large log files due to index rebuilds DESCRIPTION Index rebuilds can create very large log files. This KB discusses what to do when this happens. SOLUTION 1) Make sure the database has a LOT of empty space in it. As in 1 to 2 YEARS worth of estimated growth if it is an active database, or double current space if it is slowly changing. This will allow the index rebuild/reorg process to lay the data/index pages down in contiguous order - as in not fragmented right off the bat. Trying to defrag a full database is a fruitless excercise! 2) Defrag only stuff that is currently highly fragmented. The standard for this is > 30%. See BOL for a script you can use plug-and-play. I also skip very small tables (< ?? pages in size, where ?? is either 50 or 100 depending on the phase of the moon :) 3) Assuming you have a very fragmented system due to not having sufficient space, open up free space and then CONTROL the first few rebuilds. Split up your tables into different runs so as not to create a huge log at one time. Be sure to backup the tlog between runs to flush committed entries and free space. SEE ALSO Excessive DAT Files AUTHOR TheSQLGuru LAST REVIEW 30 August 2007 Labels: ApexSQL Log, General ...Get DDL Information from Transaction Log DESCRIPTION This KB article describes how to get DDL information from SQL Server's transaction log using ApexSQL Log 2005.04 SOLUTION To get DDL information from the transaction log in ApexSQL Log 2005.04, use the following command via the command line interface: ApexSqlLog.com /S:<your server> /d:<your db> /ddl:ddl_output.sql This reads and outputs *all* DDL operations logged on the transaction log. The resulting script can be filtered via an external text editor. AUTHOR Janice Lee LAST REVIEW 20 August 2007 Labels: ApexSQL Log ...ApexSQL Log Basics Part 3 of 3: Truncated Data Recovery DESCRIPTION The following steps show how to recover truncated data using ApexSQL Log. ApexSQL Log can recover data that was lost due to execution of the TRUNCATE TABLE command. The source of recovered data can only be online data files. The transaction log cannot be used in this option since Truncate does not write rows to the transaction log. Recovery is done by analysis of free pages in the MDF file and quality of recovered data depends on post-event database activity. SOLUTION STEP 1: Open the "Recovery Wizard"  From the File menu, select "Recovery Wizard". STEP 2: Choose a Data Source  Connect to a SQL Server database by selecting a server, providing login credentials, and choosing a database. STEP 3: Select Recovery Option  Select "Recover Data Loss from Truncate Table Operations". Click "Next". STEP 4: Select Tables  Select the tables that you want to recover. Click "Next". STEP 5: Select Recovery Script Path  On the Script File Name, specify where you want to save your generated recovery script. Click "Finish". STEP 6: Execute generated recovery script  The result of a successful recovery operation is a saved script that, when run, will recover the database’s table structures and data. This script can be copied to the clipboard or to SQL Server's Query Analyzer. It can also be saved to a file or even executed directly to the database. Here, we choose to copy the script to Query Analyzer and run it there. STEP 7: Verify successful execution of script  When the script execution completes, the results are displayed. You have successfully recovered your data. SEE ALSO ApexSQL Log Basics Part 1 of 3: Working with a SQL Server Transaction Log ApexSQL Log Basics Part 2 of 3: Dropped or Deleted Data Recovery AUTHOR Janice Lee LAST REVIEW DATE 21 August 2007 Labels: ApexSQL Log ...ApexSQL Log Basics Part 2 of 3: Dropped or Deleted Data Recovery DESCRIPTION The following steps show how to recover deleted or dropped data. This type of recovery can be done using traditional transaction log sources (online, backup, and detached) or from online data files. The latter allows recovery even when database is not in FULL RECOVERY mode and no relevant transaction log data is available. Quality and completeness of data will depend on the post-event database activity. SOLUTION STEP 1: Open the "Recovery Wizard"  From the File menu, select "Recovery Wizard". STEP 2: Choose a Data Source Connect to a SQL Server database by selecting a server, providing login credentials, and choosing a database. STEP 3: Select Recovery Option  Select any of the following Recovery options: • Recover Data Loss from Delete Operations • Recover Data Loss from Drop Table Operations • Recover Lost Objects from Drop Operations Click "Next". STEP 4: Select availability of quality Data Source  Choose "Relevant data is available". Relevant transaction log data here means that you have a Detached Database file, a Transaction Log backup, or an online Transaction Log (if your database is set to Full Recovery Mode). If any of these is available, select Relevant data is available. If not, select Relevant data is not available—in which case, recovery will be done from the online data file. It is important to know that to recover BLOB data, the second option must be selected. BLOB data can only come from online data files. You can also specify a Time Range. To do so, simply check From and/or To and enter your dates. This will limit the recovery to events that transpired between the selected dates. STEP 5: Select Log Data Source(s)  From the list of logs, select the log file you want to recover. The online transaction log file of the current database is selected by default. You can also add a Transaction Log backup or a detached one. Click "Next". STEP 6: Select Tables  Select the tables that you want to recover. Click "Next". STEP 7: Select Recovery Script Path  On the Script File Name, specify where you want to save your generated recovery script. Click "Finish". STEP 8: Execute generated recovery script  The result of a successful recovery operation is a saved script that, when run, will recover the database’s table structures and data. This script can be copied to the clipboard or to SQL Server's Query Analyzer. It can also be saved to a file or even executed directly to the database. Here, we choose to copy the script to Query Analyzer and run it there. STEP 9: Verify successful execution of script  When the script execution completes, the results are displayed. You have successfully recovered your data. SEE ALSO ApexSQL Log Basics Part 1 of 3: Working with a SQL Server Transaction Log ApexSQL Log Basics Part 3 of 3: Truncated Data Recovery AUTHOR Janice Lee LAST REVIEW DATE 20 August 2007 Labels: ApexSQL Log ...ApexSQL Log Basics Part 1 of 3: Working with a SQL Server Transaction Log DESCRIPTION This KB article discusses how you can use ApexSQL Log to view a SQL Server transaction log and undo or redo aborted/committed transactions. SOLUTION Follow the step-by-step instructions below: STEP 1: Open the Log Wizard  From the File menu, select "New Log". This opens the Log Wizard. STEP 2: Choose a Data Source  Connect to a SQL Server transaction log by selecting a server, providing login credentials, and choosing a database. Click "Next". STEP 3: Select the SQL Log to analyze  From the list of logs, select the log file you want to analyze. The Online transaction log file of the current database is selected by default. You can also add the transaction log Backup file or Detached transaction log file. Click "Finish" when done. Note: Make sure that your database is in Full Recovery mode. STEP 4: Open the Log Filter dialog  The Log Filter dialog allows you to filter the transaction logs that you want to be displayed. Select "Create New Filter" and click "Next". STEP 5: Choose one or more filtering options  There are several filtering options available from the left-hand menu. To filter by table, select "Table Name". This displays the Table Name screen where you can check the tables of the transactions that you want to analyze. Click "Finish" when done. STEP 6: View your database’s transaction log  The Main Grid now displays your database’s transaction log filtered according to the filter options you chose previously. Select a transaction row to display its Row Details, Row History, Undo Script, or Redo Script on the lower grid. STEP 7: Generate the Undo script of one or more transactions  Check the transaction rows that you want to undo. From the Tools menu, select "Create UNDO Script". If instead you want to REDO a transaction, choose REDO Script. STEP 8: Execute UNDO script  The Undo Script generated is displayed. This can be copied to the clipboard or to SQL Server's Query Analyzer. It can also be saved to a file or even executed directly to the database. Here, we choose the last option and click the "Play" button to run the script. STEP 9: Verify successful execution of script  When the script execution completes, the results are displayed. Your transaction has been undone. SEE ALSO ApexSQL Log Basics Part 2 of 3: Dropped or Deleted Data Recovery ApexSQL Log Basics Part 3 of 3: Truncated Data Recovery AUTHOR Janice Lee LAST REVIEW DATE 20 August 2007 Labels: ApexSQL Log ...Welcome to the ApexSQL Knowledge Base Welcome to the ApexSQL Training Knowledge Base. Here you'll find tips, reviews, videos, and articles on how to get the most out of ApexSQL Tools and Microsoft SQL Server. Labels: Apex SQL Audit, Apex SQL Edit, Apex SQL Script, ApexSQL Clean, ApexSQL Code, ApexSQL Diff, ApexSQL Doc, ApexSQL Enforce, ApexSQL Law, ApexSQL Log, ApexSQL Report, General ... |
© 2007 Apex SQL Tools All Rights Reserved | 1.919.968.8444 | Contact Us | Terms of Use