Some Fun Facts About MSDE Logging that I Bet You Didn't Know About
From a great article on ISA Firewall Logging at:
ISA Server includes the MSSQL$MSFW service, which is an instance of the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) that can be used for logging. By default, ISA Server saves logging information in MSDE databases. Each database is stored in two files, an .mdf file and an .ldf file. For each log database, two files are created: ISALOG_yyyymmdd_xxx_nnn.mdf and ISALOG_yyyymmdd_xxx_nnn.ldf, where:
- yyyy represents the year that the log database refers to.
- mm represents the month that the log database refers to.
- dd represents the day that the log database refers to.
- xxx represents the type that the log database refers to. This can be one of the following:
- FWS. Represents the Firewall log.
- WEB. Represents the Web Proxy log.
- nnn is a counter that distinguishes between log databases that refer to the same day.
ISA Server keeps a buffer in memory for 30 seconds (or until there is a 10,000 buffer entry) before writing information to the MSDE log. This number is specified by the MSDENumberOfInsertsPerBatch property of the ISA Server FPCLogs COM object. We do not recommend reducing this buffer size. Note that Web proxy requests (HTTP GET) are only logged after the request is complete.
By default, MSDE logs are saved in the %ProgramFiles%\Microsoft ISA Server\ISALogs folder. Do not select a compressed drive as the logging directory. Saving logs to a compressed directory causes severe performance degradation for MSDE, which impacts the ISA Server firewall performance.
ISA Server creates new MSDE databases as follows:
- For each log, ISA Server creates a new database every day.
- In addition, ISA Server limits MSDE logs to 1.5 GB. When a log exceeds this limit, ISA Server automatically creates a new database.
ISA Server prepares log databases for the next day in advance. When you save logs to MSDE, a database that refers to the next day always exists.
MSDE logs can be viewed in the log viewer. This provides easy access to online information about network activity. The log viewer displays all the data as if it were in a single database. You can export the data displayed in the log viewer, to save MSDE data to a text file.
Note that the MSDE instance used by ISA Server has network protocols disabled, and you cannot connect to it remotely. You can only connect using a local SQL tool, for example Enterprise Manager, OSQL, ISQL.