General Information about all Trace Flags

SQL Server has several trace flags that we can use to find on what’s happening under the hood and this can help us in troubleshooting SQL Server issues. While this information could be helpful, some of the trace flags are not recommended for production use and are only for Microsoft internal testing\troubleshooting. Hence, they should only be enabled under proper guidance. Contact Microsoft support for more information on this.

Since there are several trace flags we can use, I was looking around to find if there is any blog post/link covering all the available trace flags. I found the below link a good reference for this.
http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx

Full credit to original author for putting this together. Thank you sir for sharing this information.

Advertisement

Breaking Log Sequence Number(LSN) Chain

Sometimes, the LSN of the log is broken due to some user actions. This could be very vital mistake as you cannot take the successive log backups until a full or differential backup is taken and in case, if you have to take tail log backup, it is not possible which means you are going to have some data loss.
The actions that could cause this are switching the recovery modes or taking log backups with options such as Truncate only, no_log.
If the LSN chain is broken, it potentially invalidates the log backups thereafter.
You will receive this message when log backups are invalidated.

The things that can cause this are:
1. Switching the Recovery modes.
2. Taking log backup with Truncate only option.(This option is not present SQL versions 2008 and above).
3. Taking Log Backup with No_Log option.(This option is not present SQL versions 2008 and above).
4. Reverting the database from Database Snapshot. This requires full backup of the database, differential backup will not fix it.

To fix this, either take a full backup or Differential backup and then continue taking log backup.

1. Switching the Recovery modes
create database Test1
Backup database Test1 to Disk = 'E:\Backups\Test1.bak'
Alter database Test1 set Recovery Simple
Alter database Test1 set Recovery Full
Backup Log Test1 to disk = 'E:\Backups\Test1_Log.bak' --- Error here
Drop Database Test1
—————————————————————–
2. Taking log backup with Truncate only option.(This option is not present SQL versions 2008 and above).
create database Test1
Backup database Test1 to Disk = 'E:\Backups\Test1.bak'
Backup Log Test1 to disk = 'E:\Backups\Test1_Log.bak' with truncate_only
Backup Log Test1 to disk = 'E:\Backups\Test1_Log2.bak' --- Error here
Drop Database Test1
————————————————————————————–
3. Taking Log Backup with No_Log option.(This option is not present SQL versions 2008 and above).
create database Test1
Backup database Test1 to Disk = 'E:\Backups\Test1.bak'
Backup Log Test1 to disk = 'E:\Backups\Test1_Log.bak' with No_log
Backup Log Test1 to disk = 'E:\Backups\Test1_Log2.bak' --- Error here
Drop Database Test1
—————————————————————————————–
4. Reverting the database from Database Snapshot. To fix this, we should take Full Backup. Differential Backup will not fix this.
create database Test1
CREATE DATABASE Test1_Snapshot ON
( NAME = Test1, FILENAME =
'E:\Backups\Test1_Snapshot.ss' )
AS SNAPSHOT OF Test1;
GO
Backup database Test1 to Disk = 'E:\Backups\Test1.bak'
Restore Database TEST1 from Database_SnapShot = 'Test1_Snapshot';
GO
Backup Log Test1 to disk = 'E:\Backups\Test1_Log2.bak' --- Error here
Drop Database Test1_Snapshot
Drop Database Test1

Also, you will get this error if you have not taken Full backup prior to taking Log backup. In such case, take Full Backup.

Assigning Permissions to Individual Users from Group Logins

Let’s say we have Developer’s Group and one developer in that group needs higher permissions than others on one database.
In that case instead of creating dedicated login for the user, we can extract the login from the group and assign the required permissions on the database.

For example: assume company ABC has developer group and all developers have same permissions except that one senior developer needs higher privileges on one database.

Dev. Group Login: ABC\Developers and senior Developer: ABC\Rose

ABC\Rose authenticates herself as the part of the Dev. group on Server level and on the database level, you can create a database user and associate it with the login ABC\Rose.

The login ABC\Rose does not explicitly exist on the server but as a part of the DEV Group.

The way this works is that SQL Server knows that connection came from ABC\Rose and ABC\Rose is part of Developer group. So, ABC\Rose is authenticated on the server level and granted access. On the database level, we have to create a user for login ABC\Rose and this lets ABC\Rose to have access to the database with more permissions granted for the user.

Use [Master]
GO
Create Login [ABC\Developers] from windows WITH DEFAULT_DATABASE=[master]
GO
USE [DatabaseName]
GO
CREATE USER [ABC\Rose] FOR LOGIN [ABC\Rose]
GO
EXEC sp_addrolemember N'db_dataWriter',N'ABC\Rose'

Disabling Default Trace

Sql Server by default will run a default trace and these trace files are located in log directory. The contents of the trace file can be read using
Select * from sys.fn_trace_gettable('TraceFilePath',default)
Example :
Select * from sys.fn_trace_gettable
('E:\MSSQL10_50.SQL2008R2\MSSQL\Log\log_160.trc',default)

You can also use this query to read the information from the profiler or server side trace. Also, Microsoft does not recommend using function sys.fn_trace_gettable or Trace method to capture the server activity as this feature is going to be removed in future. Use Extended Events instead.

If you do not find the information in default trace to be useful, you can disable the trace by sp_configure. Firstly,enable advanced options using sp_configure.

sp_configure ‘show advanced options’,1
GO
Reconfigure
GO
sp_configure ‘default trace’ , 0
GO
Reconfigure
GO
sp_configure ‘show advanced options’,0
GO
Reconfigure

This turns off the small trace files the sql server writes by default.
The max size of each file can be 20480 KB. This default trace can be useful sometimes to know the events happened on the server during the last moments.

List of all backups taken after the latest full backup

To find the list of all the backups taken on a database after the latest Full Backup(including), you can use the below query:

declare @DatabaseName varchar(200)
set @DatabaseName = 'DatabaseName';

select backup_set_id,database_name,backup_size,type as BackupType,A.name as BackupName,Backup_Start_Date,Backup_Finish_date,Physical_device_name from msdb.dbo.backupset A INNER JOIN msdb.dbo.backupmediafamily B on A.media_set_id=B.media_set_id where database_name = @DatabaseName and backup_set_id>=(Select max(backup_set_id) from msdb.dbo.backupset where database_name= @DatabaseName and type='D')

This information can be useful when you are trying to figure out which/how many backups you should restore, if you want to bring the database to point in time(remember, you should also take Tail log Backup,if possible) and Also, this query works if you have Full, Differential, Log backup implementation

Disabling logging backup info in SQL Server Error Log

All the backup information is stored in the MSDB database. Also, whenever a backup is taken on the database, it will be written to the error log.

While this information is useful, it could also be overwhelming, especially when you are hosting large no of databases on one instance and taking backups very frequently. In such cases, when you want to scan your error logs, to look into potential issues, it could get cumbersome with all this backup Information.

By enabling trace flag 3226, you prevent them from logging the info in the Error Log.Try this on a Test system:

Create Database Test_BKTrace
Backup database Test_BKTrace to Disk = 'E\Backup\Test_BKTrace.bak'
--check the error log, you will see information related to the backup
DBCC TRACEON(3226)
Backup database Test_BKTrace to Disk = 'E\Backup\Test_BKTrace.bak'
--check the error log, you will not find the Backup info in the error log.
DBCC TRACEOFF (3226).
However,if the backup fails, it will still log the failure message.

To enable the trace permanently on the instance, add ‘-T3226’ trace flag to the startup parameters and restart the SQL Service.

Script to get objects from all the databases

List of all tables in the all databases on the instance:

DECLARE @name VARCHAR(50) -- database name
declare @sql nvarchar(200)
Create Table #Temp(DatabaseName varchar(200),[Schema] varchar(20),TableName varchar(200),TableType varchar(15))
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE databasePropertyex(name,'status')= 'online'
and name not in ('tempdb','master','msdb','model')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQL = 'use ['+@name+']'+
+'select * from information_schema.tables where table_type= ''BASE TABLE'' and Table_name not in (''dtproperties'')'
Insert into #Temp
Execute sp_executesql @SQL
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from #Temp
Drop Table #Temp
List of all procedures in the all databases on the instance:
DECLARE @name VARCHAR(50) -- database name
declare @sql nvarchar(250)
Create Table #Temp(DatabaseName varchar(200),[Schema] varchar(20),ProcedureName varchar(200),ProcedureDefinition ntext)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE databasePropertyex(name,'status')= 'online'
and name not in ('tempdb','master','msdb','model')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQL = 'use ['+@name+']'+
+'select Routine_Catalog,Routine_schema,Routine_Name,'+
+'Routine_Definition from sysobjects A INNER JOIN '+
+'information_schema.Routines B on A.name= B.Routine_Name '+
+'where B.Routine_Type= ''Procedure'' and A.status >=0'
Insert into #Temp
Execute sp_executesql @SQL
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from #Temp
Drop Table #Temp
List of all user defined functions in the all databases on the instance:
DECLARE @name VARCHAR(50) -- database name
declare @sql nvarchar(250)
Create Table #Temp(DatabaseName varchar(200),[Schema] varchar(20),ProcedureName varchar(200),ProcedureDefinition ntext)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE databasePropertyex(name,'status')= 'online'
and name not in ('tempdb','master','msdb','model')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQL = 'use ['+@name+']'+
+'select Routine_Catalog,Routine_schema,Routine_Name, '+
+'Routine_Definition from '+
+'information_schema.Routines B '+
+'where B.Routine_Type = ''Function'''
Insert into #Temp
Execute sp_executesql @SQL
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from #Temp
Drop Table #Temp