Increase the Number of SQL Server Error Logs

The SQL Server Error Log is a great place to find information about what is happening on your database server. You can execute the below TSQL command which uses the xp_readerrorlog extended stored procedure to read the SQL Server Error Log to find the location of SQL Server Error Log file used by the instance of SQL Server.

XP_READERRRORLOG

The parameters you can use with XP_READERRRORLOG are mentioned below for your reference:

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

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

3. Search string 1: String one you want to search for

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

5. Search from start time 

6. Search to end time

7. Sort order for results: N'asc' = ascending, N'desc' = descending

Location of Error Log File:

USE master

GO

xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' 

GO

Increase the Number of SQL Server Error Logs :

By default, the error log is located at “Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG” and ERRORLOG.n files. A new error log is created when an instance of SQL Server is restarted. Also database administrators can run the DBCC ERRORLOG command or sp_cycle_errorlog system stored procedure to cycle the error log without recycling the instance of SQL Server. The most recent error log backup will have a name ERRORLOG.1, the second most recent error log backup will have the name as ERRORLOG.2 and the current error log will have the name ERRORLOG.

It is a BEST PRACTICE to increase the SQL Server Error Log from the default value of 6, because the error logs may contain critical information about your database server. As mentioned, by default there will be 7 error log files that exist, 6 archives and the current one.  When a new error log is created the oldest archive gets removed and that data is then lost forever.  So if you are trying to troubleshoot a system problem and are doing several restarts of SQL Server you may end up replacing all of your archives and then loose this valuable information in the error logs.

Steps to be followed to Increase the Number of SQL Server Error Logs in SQL Server 2008 R2 :

1. Connect to SQL Server 2008 r2 Instance using SQL Server Management Studio

2. In the Object Explorer, Click on "Management" and expand "SQL Server Logs"

3. Right click SQL Server Logs and click on "Configure" option from the drop down list as shown in the below snippet

4. This will open up Configure SQL Server Error Logs window as shown in the below snippet. Here, for Maximum number of error logs option you can specify a value between 6 and 99. In this example, I have changed the value from the default value of 6 to 10.

5. Once you have specified the new value for Maximum number of error log files click OK to save the changes.

To assign a TCP/IP port number to the SQL Server Database Engine

If enabled, the default instance of the SQL Server Database Engine listens on TCP port 1433. Named instances of the Database Engine and SQL Server Compact 3.5 SP1 are configured for dynamic ports. This means they select an available port when the SQL Server service is started. When you are connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.

To assign a TCP/IP port number to the SQL Server Database Engine

1. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.
2. In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you want to configure.
3. If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.
4. In the IPn Properties area box, in the TCP Port box, type the port number you want this IP address to listen on, and then click OK.
5. In the console pane, click SQL Server Services.
6. In the details pane, right-click SQL Server (<instance name>) and then click Restart, to stop and restart SQL Server.

After you have configured SQL Server to listen on a specific port, there are three ways to connect to a specific port with a client application:

• Run the SQL Server Browser service on the server to connect to the Database Engine instance by name.
  
  
• Create an alias on the client, specifying the port number.
  
  
• Program the client to connect using a custom connection string.

Find and Drop All Orphaned Users in All SQL Server Databases Script

What Is An Orphaned SQL User:

An orphaned user is a database user that does not have an associated SQL login. There are a number of ways a user can become orphaned. The most common way is when a database from a different server is restored. When a database is backed up and restored the database users are also backed up and restored with the database, but the SQL login is not. If a database is restored to the same server the backup came from and the logins already exist then the database users will not be orphaned because security identifiers (SID) will be the same. If a database is restored to a different server and there are logins with the same name chances are the database users will be orphaned because the identifiers are not the same. And if the logins do not exist at all the database users will be orphaned. Another way database users can be orphaned is if the SQL login is deleted without checking for database users.

Find Orphans In All Databases T-SQL Command:

use DB_name;

sp_change_users_login @Action='Report';

Find Orphaned Database Users Script:

Use master
Go
Create Table #Orphans
 (
  RowID     int not null primary key identity(1,1) ,
  TDBName varchar (100),
  UserName varchar (100),
  UserSid varbinary(85)
 )
SET NOCOUNT ON
 DECLARE @DBName sysname, @Qry nvarchar(4000)
 SET @Qry = ''
 SET @DBName = ''
 WHILE @DBName IS NOT NULL
 BEGIN
   SET @DBName =
     (
  SELECT MIN(name)
   FROM master..sysdatabases
   WHERE
   /** to exclude named databases add them to the Not In clause **/
   name NOT IN
     (
      'model', 'msdb',
      'distribution'
     ) And
     DATABASEPROPERTY(name, 'IsOffline') = 0
     AND DATABASEPROPERTY(name, 'IsSuspect') = 0
     AND name > @DBName
      )
   IF @DBName IS NULL BREAK
       
                Set @Qry = 'select ''' + @DBName + ''' as DBName, name AS UserName,
                sid AS UserSID from [' + @DBName + ']..sysusers
                where issqluser = 1 and (sid is not null and sid <> 0x0)
                and suser_sname(sid) is null order by name'
 Insert into #Orphans Exec (@Qry)

 End
Select * from #Orphans


---To Drop Orphan User:

Declare @SQL as varchar (200)
Declare @DDBName varchar (100)
Declare @Orphanname varchar (100)
Declare @DBSysSchema varchar (100)
Declare @From int
Declare @To int
Select @From = 0, @To = @@ROWCOUNT
from #Orphans
--Print @From
--Print @To
While @From < @To
 Begin
  Set @From = @From + 1

  Select @DDBName = TDBName, @Orphanname = UserName from #Orphans
   Where RowID = @From
   
   Set @DBSysSchema = '[' + @DDBName + ']' + '.[sys].[schemas]'
   print @DBsysSchema
   Print @DDBname
   Print @Orphanname
   set @SQL = 'If Exists (Select * from ' + @DBSysSchema
                          + ' where name = ''' + @Orphanname + ''')
    Begin
     Use ' + @DDBName
                                        + ' Drop Schema [' + @Orphanname + ']
    End'
   print @SQL
   Exec (@SQL)
   
    Begin Try
     Set @SQL = 'Use ' + @DDBName
                                        + ' Drop User [' + @Orphanname + ']'
     Exec (@SQL)
    End Try
    Begin Catch
    End Catch
 
 End


Drop table #Orphans

Moving model and msdb databases

Moving model database:

1. First get the list of model database files by using this query
   select name,physical_name from sys.master_files whereDB_NAME(database_id)='model'  
2. Then for each model database file that you need to move, execute statements like below
   Alter Database model modify
   file (NAME = 'modeldev' ,
   FILENAME = 'Drive:\Path\model.mdf') -- Mention the new location
   
   
   Alter Database model modify
   file (NAME = 'modellog' ,
   FILENAME = 'Drive:\Path\modellog.ldf') -- Mention the new location
3. Stop SQL Services
4. Move the files manually to the new location
5. Start SQL Services
6. Verify the new Location
   select name,physical_name from sys.master_files whereDB_NAME(database_id)='model'

Moving msdb database:

1. First get the list of msdb files by using this query
   select name,physical_name from sys.master_files whereDB_NAME(database_id)='msdb'  
2. Then for each msdb database file that you need to move, execute statements like below
   Alter Database msdb modify
   file (NAME = 'MSDBData' ,
   FILENAME = 'Drive:\Path\MSDBData.mdf') -- Mention the new location
   
   
   Alter Database msdb modify
   file (NAME = 'MSDBLog' ,
   FILENAME = 'Drive:\Path\MSDBLog.ldf') -- Mention the new location
3. Stop SQL Services
4. Move the files manually to the new location
5. Start SQL Services
6. Verify the new Location
   select name,physical_name from sys.master_files whereDB_NAME(database_id)='msdb'

Useful DBCC Commands

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.

Database Console Command statements are grouped into the following categories.

1. Maintenance

2. Miscellaneous

3. Informational

4. Validation

DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.

Some of the Maintenance DBCC Statements are:

DBCC CLEANTABLE

DBCC INDEXDEFRAG

DBCC DBREINDEX

DBCC SHRINKDATABASE

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DBCC SHRINKFILE

DBCC UPDATEUSAGE

Some of the Miscellaneous DBCC Statements are:

DBCC dllname (FREE)

DBCC HELP

DBCC FREESESSIONCACHE 

DBCC TRACEOFF

DBCC FREESYSTEMCACHE

DBCC TRACEON

Some of the Informational DBCC Statements are:

DBCC INPUTBUFFER

DBCC SHOWCONTIG

DBCC OPENTRAN

DBCC SQLPERF

DBCC OUTPUTBUFFER

DBCC TRACESTATUS

DBCC PROCCACHE

DBCC USEROPTIONS

DBCC SHOW_STATISTICS

Some of the Validation DBCC Statements are:

DBCC CHECKALLOC

DBCC CHECKFILEGROUP

DBCC CHECKCATALOG

DBCC CHECKIDENT

DBCC CHECKCONSTRAINTS  

DBCC CHECKTABLE

DBCC CHECKDB

SQL Server details using T-SQL

You can get SQL Server details by executing below script.







CREATE TABLE #ServerDetails(ID int,  Name  sysname, Internal_Value int, Value nvarchar(512))

INSERT #ServerDetails EXEC master.dbo.xp_msver

DECLARE @InstanceName nvarchar(50)

DECLARE @value VARCHAR(100)

DECLARE @RegKey_InstanceName nvarchar(500)

DECLARE @RegKey nvarchar(500)

DECLARE @AuditLevel int

DECLARE @DataDirectory nvarchar(500)

DECLARE @LogDirectory nvarchar(500)

DECLARE @BackupDirectory nvarchar(500)

SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),

'MSSQLSERVER'))

if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8

BEGIN

SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = @RegKey_InstanceName,

  @value_name = @InstanceName,

  @value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\'

     

EXEC master..xp_regread

  @rootkey='HKEY_LOCAL_MACHINE',

  @key=@RegKey,

  @value_name='AuditLevel',

  @value=@AuditLevel OUTPUT

EXEC master..xp_regread

  @rootkey='HKEY_LOCAL_MACHINE',

  @key=@RegKey,

  @value_name='DefaultData',

  @value=@DataDirectory OUTPUT

EXEC master..xp_regread

  @rootkey='HKEY_LOCAL_MACHINE',

  @key=@RegKey,

  @value_name='DefaultLog',

  @value=@LogDirectory OUTPUT

EXEC master..xp_regread

  @rootkey='HKEY_LOCAL_MACHINE',

  @key=@RegKey,

  @value_name='BackupDirectory',

  @value=@BackupDirectory OUTPUT

END

SELECT  SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name]

    ,SERVERPROPERTY('ServerName') AS [SQL Server Name]

    ,SERVERPROPERTY('InstanceName') AS [Instance Name]

    ,SERVERPROPERTY('Collation') AS [Server Collation]

    ,'Microsoft SQL Server ' + CAST(SERVERPROPERTY('Edition') AS varchar(250)) AS Edition

    ,SERVERPROPERTY('ProductLevel') AS [Product Level]

    ,(SELECT Value FROM #ServerDetails WHERE Name = N'Language') AS [Language]

    ,(SELECT Value FROM #ServerDetails WHERE Name = N'Platform') AS [Platform]

    ,(SELECT 'Microsoft Windows NT ' + Value from #ServerDetails where Name = N'WindowsVersion') AS [Operating System]

    ,(SELECT Internal_Value FROM #ServerDetails WHERE Name = N'ProcessorCount') AS [Processors]

    ,(SELECT CAST(Internal_Value AS varchar)+ ' (MB)' FROM #ServerDetails WHERE Name = N'PhysicalMemory') AS Memory

    , CASE WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'True' ELSE 'False' END AS IsClustered

    ,(SELECT value from sys.configurations where name='min server memory (MB)') AS 'Min Server Memory (MB)'

    ,(SELECT value from sys.configurations where name='max server memory (MB)') AS 'Max Server Memory (MB)'

    ,(SELECT CASE WHEN value=0 THEN 'True' ELSE 'False' END from sys.configurations where name='affinity mask') AS 'Automatically set processor affinity mask for all processor'

    ,(SELECT CASE WHEN value=0 THEN 'True' ELSE 'False' END from sys.configurations where name='affinity I/O mask') AS 'Automatically set I/O affinity mask for all processor'

    ,CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')= 1 THEN 'Windows Authentication Mode'

    WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')= 0 THEN 'SQL Server and Windows Authentication Mode' END AS [Server Authentication]

    ,CASE WHEN @AuditLevel = 0 THEN 'None'

    WHEN @AuditLevel = 1 THEN 'Successful Logins Only'

    WHEN @AuditLevel = 2 THEN 'Failed Logins Only'

    WHEN @AuditLevel = 3 THEN 'Both Failed and Successful Logins'

    END AS [Audit Level]

    ,(select CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations where name='remote access') AS 'Allow remote connections to this Server'

    ,(select CASE WHEN value = 0 THEN 'unlimited' ELSE value END from sys.configurations where name='user connections') AS 'Max number of concurrent Connections'

    ,(select CASE WHEN value = 0 THEN 'No Timeout' ELSE value END from sys.configurations where name='remote query timeout (s)') AS 'Query Timeout (s)'

    ,(select CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations where name='remote access') AS 'Allow Remote Connections to this server'

    ,@DataDirectory AS 'Default Data Directory'

    ,@LogDirectory AS 'Default Log Directory'

    ,@BackupDirectory AS 'Default Backup Directory'

    ,(SELECT value from sys.configurations WHERE name='max degree of parallelism') AS 'Max Degree of Parallelism'

    ,(SELECT value from sys.configurations WHERE name='remote login timeout (s)') AS 'Remote Login Timeout (s)'

    ,(SELECT CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations WHERE name='scan for startup procs') AS 'Scan for Startup Procs'

DROP TABLE #ServerDetails