READING THE SQL SERVER LOG FILES USING TSQL

One of the issues that I have always faced is to read the sql error log due to its large size. At times like this, i used to ponder how to read them by not stressing too much or get worried about its size. After few experiences and search in Google i learned how to read them in a proper way by using few tips and tricks.

Solution:

SQL server 2005 offers an undocumented system stored procedure called “sp_readerrorlog” , it takes four parameters.  This store procedure allows us to read the contents of the error log files directly from the query window and also allows us to read them with certain keywords. 

Elaboration of the parameters:

Ø  Value of error log file you want to read: 0 = current, 1 = Archive #1, etc...

Ø  Log file type: 1 or NULL = error log, 2 = SQL Agent log

Ø  Search string 1: String one you want to search for

Ø  Search string 2: String two you want to search for to further refine the results

HOW TO READ THE SQL SERVER DATABASE TRANSACTION LOG

Query

Have you ever wondered how SQL Server logs track transactions and what information SQL Server captures in the transaction log file?  We have a SQL Server function that can be used to read the transaction log to see what entries are made for database transactions.

Answer

There is an undocumented function called "fn_dblog" which enables you to read data from your transaction log which contains very informative data about things that are happening in your database.

Let us see this in practical

USE [master];

GO

CREATE DATABASE DataRead;

GO

-- Create tables.

USE DataRead;

GO

CREATE TABLE [Locality] (

    [Sr.No] INT IDENTITY,

    [Date] DATETIME DEFAULT GETDATE (),

    [City] CHAR (25) DEFAULT 'Bangalore');

Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.

Few days back i received a mail from one of our user saying that there "sa" login was locked out. This mail really surprised me and made me wander how come they have been using sa login in there application. Though i had to sort this issue as soon as possible because of its priority, I also decided to make sure, henceforth they are not using the "sa" login in their application which was taken care of .

sp_who4

“Sp_who4 “

What if we want to see what’s running in our server right now? We quickly go to the command window and type “sp_who2” or “sp_who2 active “ either of these two commands will provide us the required information of the various requests running on our server. The output of this command is as follows

 

Now we do have a drawback over here, we do get the required information, but not the commands that are being requested to the server in order to get the command text we need to do an additional activity i.e. run a command dbcc inputbuffer (spid).By running this command we get the command text that is executing on the server.

Using Files and file groups

SQL Server maps a database over a set of operating-system files. Data and log information are never mixed on the same file, and individual files are used only by one database. File groups are named collections of files and are used to simplify data placement and administrative tasks such as backup and restore operations.To gain more information please follow this link  Files and File groups Architecture.

At a minimum, every SQL Server database has two operating system files: a data file and a log file. Data files contain data and objects such as tables, indexes, stored procedures, and views. Log files contain the information that is required to recover all transactions in the database. Data files can be grouped together in file groups for allocation and administration purposes.

Problems faced while starting the SQL Server Analysis Services

Recently i faced an issue where i wanted to start the sql server analysis services but it didnt. I had to go back to the error log and search what went wrong with the services. After some time i found the below entries in the log.

" The service cannot be started: Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. Errors in the metadata manager. LOG file extension can be only .LOG. Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. Message-handling subsystem: The message manager for the 16393 locale cannot be found. Internal error: Failed to generate a hash string "