Limiting error log file size in SQL Server

It has often been observed that error logs in SQL server increase to large values. Further there is not way to set limit on size of log file from SSMS.

Below is a very nice article to set the limits on the size of error log file along with the number of error log files.

https://www.sqlskills.com/blogs/paul/limiting-error-log-file-size-in-sql-server-2012/

Find a job with a particular text in SQL Server

Find a job in SQL Server with a particular text.

USE [msdb]

GO

SELECT     j.job_id,

     s.srvname,

     j.name,

     js.step_id,

     js.command,

     j.enabled

FROM dbo.sysjobs j

JOIN dbo.sysjobsteps js

     ON   js.job_id = j.job_id

JOIN master.dbo.sysservers s

     ON   s.srvid = j.originating_server_id

WHERE js.command LIKE N'%text to search in job%'

Get comma separated values from table grouped by particular column

Suppose we have a table

CREATE TABLE #temp

(

     ID INT,

     name NVARCHAR(100)

)

INSERT INTO #temp(ID,name)

VALUES(1,'Akash'),

(1,'B'),

(1,'C'),

(2,'AA'),

(2,'BB')

We want to get the names as comma separated grouped by ID, so for ID 1 we will have 'Akash, B, C'. Below query will create a comma separated list grouped by ID.

select distinct u1.ID,

    Replace((select distinct u2.name + ',' as 'data()'

    from #temp u2

    where u2.ID = u1.ID

    FOR XML PATH('')) + '$', ',$', '') as Names

from #temp u1

order by u1.ID

Clear Plans for particular type of object in SQL Server

-- clear plans for a particular type of object

-- clear sql plans: adhoc sql, prepared statements

dbcc freesystemcache('SQL Plans');

-- clear object plans: stored procedures, functions, triggers

dbcc freesystemcache('Object Plans');

-- clear bound trees: views, constraints, defaults

dbcc freesystemcache('Bound Trees');

-- clear extended stored procedures

dbcc freesystemcache('Extended Stored Procedures');

Find all the stored procedures with particular text..

Below query will help find all the stored procedures with a particular text. System table syscomments contains the text of stored procedure.

DECLARE @StringToSearch VARCHAR(500)

SET @StringToSearch = '%text in stored procedure%'

SELECT Distinct SO.Name

FROM sysobjects SO (NOLOCK)

INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID

AND SO.Type = 'P'

AND SC.Text LIKE @stringtosearch

ORDER BY SO.Name    

Find Indexes Not In Use in SQL Server

We found out that we have lot of indexes on our tables which were not of any use and hence needed to be removed. Found this link that does the same. Below query helps to find out indexes which have not been used since last restart.

SELECT

o.name

, indexname=i.name

, i.index_id  

, reads=user_seeks + user_scans + user_lookups  

, writes =  user_updates  

, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)

, CASE

      WHEN s.user_updates < 1 THEN 100

      ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates

  END AS reads_per_write

, 'DROP INDEX ' + QUOTENAME(i.name)

+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'

FROM sys.dm_db_index_usage_stats s 

INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id  

INNER JOIN sys.objects o on s.object_id = o.object_id

INNER JOIN sys.schemas c on o.schema_id = c.schema_id

WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1

AND s.database_id = DB_ID()  

AND i.type_desc = 'nonclustered'

AND i.is_primary_key = 0

AND i.is_unique_constraint = 0

AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000

ORDER BY reads

How to script SQL Server Logins

Every now and then you add new instance you have to create logins on new server which are exact replica of some existing server. In that case it is tedious to create logins manually and a script that would do the job would be great.

I found a great script which does the same. Below is the script copied from the link. It is of great help.

USE [master]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_hexadecimal]    ****/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_hexadecimal] 
(
    -- Add the parameters for the function here
     @binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN

    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF' 
    WHILE (@i <= @length) 
    BEGIN
      DECLARE @tempint int
      DECLARE @firstint int
      DECLARE @secondint int
      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
      SELECT @i = @i + 1
    END
    return @charvalue

END
GO


SET NOCOUNT ON
GO
--use MASTER
GO
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Script created on ' + CAST(GETDATE() AS VARCHAR(100))
PRINT '-----------------------------------------------------------------------------'
PRINT ''
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the windows logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = ''' + [name] + ''')
    CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' + 
        default_database_name + '], DEFAULT_LANGUAGE=[us_english]
GO

'
FROM master.sys.server_principals
where type_desc In ('WINDOWS_GROUP', 'WINDOWS_LOGIN')
AND [name] not like 'BUILTIN%'
and [NAME] not like 'NT AUTHORITY%'
and [name] not like '%\SQLServer%'
GO

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the SQL Logins'
PRINT '-----------------------------------------------------------------------------'
select 'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
    CREATE LOGIN [' + [name] + '] 
        WITH PASSWORD=' + [master].[dbo].[fn_hexadecimal](password_hash) + ' HASHED,
        SID = ' + [master].[dbo].[fn_hexadecimal]([sid]) + ',  
        DEFAULT_DATABASE=[' + default_database_name + '], DEFAULT_LANGUAGE=[us_english], 
        CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
    ALTER LOGIN [' + [name] + ']
        WITH CHECK_EXPIRATION=' + 
            CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=' + 
            CASE WHEN is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + '
GO


'
--[name], [sid] , password_hash 
from master.sys.sql_logins 
where type_desc = 'SQL_LOGIN' 
and [name] not in ('sa', 'guest')

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Disable any logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'ALTER LOGIN [' + [name] + '] DISABLE
GO
' 
from master.sys.server_principals 
where is_disabled = 1

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Assign groups'
PRINT '-----------------------------------------------------------------------------'
select 
'EXEC master..sp_addsrvrolemember @loginame = N''' + l.name + ''', @rolename = N''' + r.name + '''
GO

'
from master.sys.server_role_members rm
join master.sys.server_principals r on r.principal_id = rm.role_principal_id
join master.sys.server_principals l on l.principal_id = rm.member_principal_id
where l.[name] not in ('sa')
AND l.[name] not like 'BUILTIN%'
and l.[NAME] not like 'NT AUTHORITY%'
and l.[name] not like '%\SQLServer%'

Who is Active? DMV Monitoring tool

Here is the link for the very important tool that you need to know what is running on your SQL Server... Tool is developed by Adam Machanic...



http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx

How to Partition a Table in SQL Server..

What is Table Partitioning?
Table partitioning is built-in method of horizontally  partitioning data within a table and/or index still maintaining the table as a single logical object.  Partitioning keeps the same no of columns in each partition, but reducing the number of rows.

Why do we need Table Partitioning?

- Easy management of large tables and indexes.
- Decrease load time.
- Improve query performance.
- Provide small window for maintenance, means easy to maintain chunks of data instead of whole.

How Partitioning is done?

- Create file -groups to hold the partitions.
- Add files to each file -group in the partitioning.
- Create Partition Function.
- Create Partition Scheme.
- Create table , binding  a specific column to a  Partition Scheme.

Below is an example of partitioning a table:

Create a sample database to be used for partitioning. I have added two file groups other than the primary file group to it.

CREATE DATABASE [PartitionDB] ON
PRIMARY
      (     NAME = N'PartitionDB',
            FILENAME = N'C:\PartitionDB\PartitionDB.mdf' ,
            SIZE = 3072KB , FILEGROWTH = 1024KB
      ),
FILEGROUP [FG2]
      (     NAME = N'PartitionDB_2',
            FILENAME = N'C:\PartitionDB\PartitionDB_2.ndf' ,
            SIZE = 3072KB , FILEGROWTH = 1024KB
      )
FILEGROUP [FG3]
      (     NAME = N'PartitionDB_3',
            FILENAME = N'C:\PartitionDB\PartitionDB_3.ndf' ,
            SIZE = 3072KB , FILEGROWTH = 1024KB
      )
LOG ON
      (     NAME = N'PartitionDB_log',
            FILENAME = N'C:\PartitionDB\PartitionDB_log.ldf' ,
            SIZE = 9216KB , FILEGROWTH = 10%
      )

Now we create a Partition Function. Partition Function can be used for any Partition Scheme. Please read the below link for specific details about the partition function:

http://msdn.microsoft.com/en-us/library/ms187802(v=sql.105).aspx

CREATE PARTITION FUNCTION EqualFunc (INT)
AS RANGE LEFT
FOR VALUES (0,1)

Next step is to create a Partition Scheme. Here is the link for Partition Scheme:

http://msdn.microsoft.com/en-us/library/ms179854(v=sql.105).aspx

CREATE PARTITION SCHEME EqualScheme
AS PARTITION EqualFunc
TO (FG2, FG3, [PRIMARY])

Finally we create a table using the Partition Scheme that we created. Based on the value of ID column PartitionElement will be computed and that will be used in deciding on which file group the row will be stored.

CREATE TABLE Customer
(
      ID     INT,
      Name        VARCHAR(20),
      PartitionElement AS ID % 3 PERSISTED
)
ON EqualScheme (PartitionElement)

INSERT INTO Customer(ID,Name) VALUES(3,'abc3')
INSERT INTO Customer(ID,Name) VALUES(1,'abc1')
INSERT INTO Customer(ID,Name) VALUES(2,'abc2')

Below query will determine the rows in each partition that is created for the table.

SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('Customer')

If we want to get the resultset for the particular partition here is the query

SELECT *
FROM  Customer
WHERE $PARTITION.EqualFunc(PartitionElement) = 2

It is advisable to keep the filegroups on different disk drives for parallelism. Partitioning is a great way to improve performance for the tables with large amount of data.

Memory Behavior in SQL Server

Memory behavior of SQL Server 2008 is very different from SQL Server 2005. On observing the memory utilization of the server on which SQL Server was installed we found that SQL Server takes up all the memory that is available. But we were not completely right. By doing some research I came up with below summary:

o   “When you start SQL Server, SQL Server memory usage may continue to steadily increase and not decrease, even when activity on the server is low. Additionally, the Task Manager and Performance Monitor may show that the physical memory available on the computer is steadily decreasing until the available memory is between 4 to 10 MB.This behavior alone does not indicate a memory leak. This behavior is normal and is an intended behavior of the SQL Server buffer pool.”

o   “By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache) depending on the physical memory load reported by the operating system. As long as enough memory is available to prevent paging (between 4 - 10 MB), the SQL Server buffer pool will continue to grow. As other processes on the same computer as SQL Server allocate memory, the SQL Server buffer manager will release memory as needed. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.”

o   You can establish upper and lower limits to the amount of memory (buffer pool) used by the SQL Server database engine with the min server memory and max server memory configuration options.

o   “Note that the max server memory option only limits the size of the SQL Server buffer pool. The max server memory option does not limit a remaining unreserved memory area that SQL Server leaves for allocations of other components such as extended stored procedures, COM objects, non-shared DLLs, EXEs, and MAPI components. Because of the preceding allocations, it is normal for the SQL Server private bytes to exceed themax server memory configuration. ”

o   If you are running 32-bit SQL Server, and need access to more than 2 GB of user mode VAS (Virtual Address Space), then you have to use Lock Pages in Memory; there is no debate there. You must configure the OS to use Physical Address Extensions (PAE), enable Address Windowing Extensions (AWE), and then assign the Lock Pages in Memory permission to the SQL Server account so that it can allocate AWE memory, via calls to AllocateUserPhysicalPages() (Windows API). So, in a typical 32-bit server using LPIM(Lock Pages In Memory) you'd have, in addition to the 2 GB of pageable user mode VAS, a separate AWE-mapped area, up to 64 GB (the PAE pointer was 36-bit), of non-pageable memory. This AWE-mapped area is for exclusive use by the data cache portion of the buffer pool. The rest of the buffer pool (mainly the plan cache), and other non-buffer pool allocations are still mapped within the 2 GB of user mode VAS.

o   However, the advent of a 64-bit SQL Server process completely changed the dynamics of memory allocation by the SQLOS. In place of the default 2 GB user mode VAS, for a 32-bit process, a 64-bit process has access to up to 8 TB of user mode VAS out-of-the-box, without any need for further configuration changes! 64-bit users now have a potentially-vast amount of memory for the buffer pool, but all of which is allocated via VirtualAlloc, and backed by user mode VAS, and so is pageable i.e. in the absence of LPIM, the memory allocated for the data and plan cache is pageable.

o   Since the operating system still requires memory to operate, you should set the max server memory configuration option to ensure that the operating system has sufficient memory once SQL Server has committed its memory for use.  This is especially important on 64 bit servers where the larger VAS allows for the SQL process to take all of the physical memory which can result in a paging situation for the SQL processes, degrading performance.  For this reason, 64 bit servers should use the Lock Pages in Memory right so that the AWE Mechanism is used to allocate memory, preventing paging of the BPool. 

References:

1.       http://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/

2.       http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

3.       http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/ed17f4d1-0980-4e3a-9a12-26a85cbbac04

4.       http://support.microsoft.com/kb/321363

5.      http://blogs.msdn.com/b/slavao/archive/2005/04/29/413425.aspx

TempDB Configuration in SQL Server

Recently in our production environment we were facing performance issues. After tracking down we found that reason could be because of incorrect tempDB configuration. So, I started reading few articles on msdn and written by experts about tempDB configuration and came up with below summary.

·         Should I use 1 file or multiple files for TempDB?

o   One TempDB data file per physical corefor the SQL Server instance.This prevents helps contention that can occur during peak tempdb activity.

o   The data files for tempdb should all be the same size. This allows for optimal proportional-fill performance.

o   All data files have equal growth increment

o   No data files have percent growth increment

·         Where (i.e. what disks) should I create TempDB?

o   Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.

o   Put the tempdb database on disks that differ from those that are used by user databases.Tempdb should be separated and isolated from user databases.  This creates contention between the user databases and tempdb.

o   Tempdb data files and log files should be on separate disks.

o   The TempDB files should definitely not be on the system (C) drive. If TempDB grows out of control, perhaps due to a large sort query, the system can run out of hard drive space and fail to start.

·         What should be the size of TempDB?

o   Unlike user databases where you can probably estimate the growth rate of tables over time based on the workload, estimating the size of TempDB is non-trivial.

o   The size of TempDB can be affected for example by a change in query plans, version store when there is a long running transaction and so on.

o   The best way to estimate the size of TempDB is by running your workload in a test environment. Once you have a good estimate of the size of TempDB, use Alter Database command to set its size with a safety factor that you feel is appropriate.

o   Don’t rely on auto-grow to manage file sizes. You must set the appropriate size of TempDB using Alter Database command.

o    Auto-grow causes a pause during processing when you can least afford it

o   Auto-grow leads to physical fragmentation

o   Tempdb Data files should be large enough to handle the workload for all 24 hours of the day.

References:

1.       http://blogs.msdn.com/b/dfurman/archive/2010/04/05/tempdb-configuration-check-script.aspx

2.       http://sqlserverpedia.com/blog/sql-server-bloggers/5-common-mistakes-with-tempdb/

3.       http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx

4.       http://technet.microsoft.com/en-us/library/cc966545.aspx

5.       http://sqlserverpedia.com/wiki/Configuring_Database_Files_for_Optimal_Perfomance#TempDB_Database_Configuration

Few articles about different types of locks in SQL Server

• http://www.sqlteam.com/article/application-locks-or-mutexes-in-sql-server-2005

SQL Server -- Query to see all the schemas given a database name..

This stored procedure gives all the schemas within a given database. It gives all the system schemas also. If you want to display only the schemas that have associated tables with them, you can join it with sys.tables and select only those schemas. Note that I have done error checking to make sure that the database exists in the system.

CREATE proc [dbo].[getSchemaInfo](@DatabaseName VARCHAR(100)) AS

BEGIN

      DECLARE @database_id INT

      SET @database_id = -1

      select @database_id = database_id from sys.databases

      where name = @DatabaseName

      if(@database_id = -1)

      BEGIN

            RAISERROR('Incorrect database name',1,1)

      END

      ELSE

      BEGIN

            DECLARE @SQL NVARCHAR(2000)

            SET @SQL = 'Use '+@DatabaseName+'; '+

                           'SELECT name AS [Schema Name] '+

                           'FROM sys.schemas;';

            EXEC sp_executesql @SQL

      END  

END

SQL Server --- Query that will display the columns in the table along with their data-types and their position in the table they are in..

Sys tables are the source of information for any metadata related queries. This stored procedure will take table name and schema name as an input and will display all the columns, their data types and their position in the table. Example run is for the Adventureworks database, on HumanResources.Employee table.

CREATE PROC getAllColumns(@tablename VARCHAR(130),@schemaname VARCHAR(130)) AS

BEGIN 

select DISTINCT SC.name [Columnname],STS.name [Datatype],SC.column_id [Position]

from sys.columns SC

INNER JOIN sys.types STS

ON SC.user_type_id=STS.user_type_id

INNER JOIN sys.tables ST

ON SC.object_id = ST.object_id

WHERE ST.name = @tablename AND ST.schema_id = SCHEMA_ID(@schemaname)

ORDER BY SC.column_id

END

EXEC getAllColumns 'Employee','HumanResources'

SQL Server -- Query to see all the tables in a given schema

Information about the tables can be found from sys tables. Here is the stored procedure to find all the tables for a given schema. Input to the procedure is schema name. Example is the sample run of this procedure on Adventureworks database.

CREATE PROC getAllTables(@schemaname VARCHAR(130)) AS

SELECT name AS 'tableName'

FROM sys.tables ST

WHERE SCHEMA_ID(@schemaname) = ST.schema_id

EXEC getAllTables 'HumanResources'