Reading SQL Server Error Log Through TSQL Statement

One of the issue which we face while doing troubleshooting, is to read error logs of SQL server. one should have to go through GUI elements of SSMS to read the error logs . but it would be much more easier if one can read error logs through TSQL command as it provide more flexibility to filter out the information. xp_readerrrorlog is undocumented extended stored procedure which do just the same . it reads error log without going through GUI .

It takes four parameter to execute

• Parameter 1
  Type :- INT
  Name :- Error Log File
  Default Value  :- 0
  Description  :- 0 for current, 1 for Archive #1, 2 for Archive #2, and so on
• Parameter 2
  Type :- INT
  Name :- Log File Type
  Default Value :- 1
  Description :- 1 for error log and 2 for SQL Server Agent log
• Parameter 3
  Type :- Varchar (255)
  Name :- Search String 1
  Description :- String one wants to search in error log
• Parameter 2
  Type :- varchar (255)
  Name :- Search String 2
  Description :- Its second string which can be searched to refine the search result

 

The above example search for “2005” and “exec” in current error log file for SQL server instance.

Finding Port Number of SQL Instance

There are times when one needs a port number for SQL Server instance . finding port number through automated script is not that simple as the environment may have named instance and default instances. both types need different way to fetch out port number from registry key . i have wrote a script which help to automate this task. the script first finds the type of instance , whether it is default or named and then query respective keys to get the port number …

 

DECLARE @tcpport nvarchar(5)
DECLARE @insname  nvarchar(50)
DECLARE @MyKey  nvarchar(254)
if CHARINDEX(‘\’,@@servername) > 0
begin
set @insname = substring(@@servername,CHARINDEX(‘\’,@@servername)+ 1,len(@@servername))
set @MyKey = ‘SOFTWARE\MICROSOFT\Microsoft SQL Server\’ + @insname + ‘\MSSQLServer\SUPERSOCKETNETLIB\TCP’
end
else
begin
set @MyKey = ‘SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\SUPERSOCKETNETLIB\TCP’
end
EXEC xp_regread
@rootkey    =    ‘HKEY_LOCAL_MACHINE’,
@key        =    @MyKey,
@value_name    =    ‘TcpPort’,
@value        =    @tcpport OUTPUT

Select @@servername as ServerName ,@tcpport as PortNumber

 

The query works fine with both Sql Server 2008 and 2005 instances

Finding Database Status Through SQL Server Agent Job

Finding database status always remains a important job for DBA’s , as the primary duty for DBA is to look after the health of database . database can go into suspect or recovery mode from verity of reasons and most common ones are disk problem , I/O issues and consistency checks . one can query sys.databases system view and look for state column to find the status of the database. the state is a tinynt column and shows state through numbers , which are described below

0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE

or, one can use state_desc column to find the state of database in plain English. this could show one of the following entries

ONLINE
RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT
EMERGENCY
OFFLINE

the following query will give complete status of database’s health on server.

select name,state,state_desc from sys.databases

also if someone wants to automate this whole procedure and wants notifications about database health in case of any problem , then one can use this script to create a SQL Server agent job , which will notify user in case of any issue. the schedule can be set to every ten minutes

if exists(SELECT [Name] as [Database Name],state_desc as [Database State] FROM sys.databases
where state_desc not like ‘ONLINE’ and state_desc not like ‘RESTORING’)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
    @recipients=N’dba_address@domain_name.com’,
       @subject = ‘SQL Server Database Status’,
    @Query = ‘SELECT cast([Name] as nvarchar(50)) as [Database Name],cast([state_desc] as nvarchar(20)) as [Database State] FROM sys.databases
where state_desc not like ”ONLINE”’;
END

Useful Links

http://msdn.microsoft.com/en-us/library/ms178534.aspx

Finding Row Count of All Tables in SQL Server

There are times when one need to find the row count of all the tables in the database. there are many ways , one can use cursor to loop through table name and store result in temp table and then display it all . but by using undocumented stored procedure sp_msForEachTable , its become very easy to find desired result .

CREATE TABLE #Tabdata
    (
        Tablename NVARCHAR(150),
        RecordCount INT
    )

INSERT #Tabdata
EXEC sp_msForEachTable ‘SELECT ”?” as Tablename,COUNT(*) as count FROM ?’

Select Tablename,RecordCount from #tabdata order by RecordCount desc
drop table #Tabdata

Cursor free Way to kill Database Connections



Killing all connection to SQL Server database is bit tricky task. One can write script which loops through all the user connection and then kill every connection. Other way is to open activity monitor and then sort it on database name and then try to kill all the connections to database. Both the methods are very much capable of doing what we need but theses methods are time consuming. Enlisted script also does the same, but in more optimize way.

declare @dbname nvarchar(100)
declare @KillStat nvarchar(4000)
set @KillStat = ”
set @dbname = ‘dbname’
select  @KillStat = @KillStat + ‘Kill ‘ + cast(spid as nvarchar) + char(13)
 from sysprocesses where dbid = db_id(@dbname)
Print @KillStat
exec(@KillStat)
Here, the script use set based approach to create the query which can kill all the user connection and then it executes it by using exec command. It also prints all the spid’s of user connection which it is killing.

The only parameter which has to pass is @dbname. It contain the name of source database

SQL Server Client Tools licensing

Hello all, I’ve been trying to find out some licensing info concerning the client tools and SSMS, but can’t find anything concrete. But after some googling I come to know that, if a user or a device has the legal right (also known as the license) to access a SQL-Server than it is legal to install client tools in order to give access to it and manage it.  Of course, but only the users with a valid license will have the right to use these tools. There is no special licensing to take into consideration for installing the client tools other than to have the right to access a SQL-server instance.  e.g if this SQL-Server is operating under the Per Processor mode, everyone and every device in your organization have the right to install the client tools in order to access and manage this SQL-Server.  Of course, this doesn’t give the right to use these tools to access *another* SQL-Server for which a particular user/device wouldn’t have the right to access it in the first place.