Full Backup Does Not Truncate Transaction Log

This discussion assumes a database Recovery Model of “Full,” which is the normal recommendation for production databases.  This setting allows the backup of the transaction log so that you can recover up to the point of failure by restoring log backups in order.  A Recovery Model of “Simple” will automatically truncate the transaction log periodically, but you are not allowed to backup the log in this mode, so you can only recover using the last database backup, which implies potential loss of transactions.  This setting is usually reserved for test or read-only databases.

Backing up the transaction log will not only backup the latest committed transactions within the log but will also truncate the log file. Truncation means that the transactions that were backed up are removed from the log file, freeing up space within the log file for new transactions.  The truth is, if you don’t backup the transaction log, it will continue to grow forever, until you run out of disk space.

New DBAs to SQL Server assume that the Full Database Backup truncates the transaction log – it doesn’t.  It does take a snapshot of the transaction log at the very end, so that transactions committed during a long-running full backup are actually backed up, too (which is quite clever), but it does not truncate the log.  Well, the long-term solution is to backup the transaction log frequently. This will keep the transaction log “lean and mean.”  But how frequent is frequent?  Well, it depends.  Generally, we try to keep the transaction log under 50% of the size of the data files.  If it grows beyond this, then we back it up more frequently. This is why, for some very active databases, we may backup the log every 15 minutes.   As I said, it depends.

How to: Find a Table on a SQL Server Across all Databases

Some times it is difficult to find a table name from a SQL instance that hosted tremendous database.  “Which database I suppose to search for?” is the question that often pop-up in DBA’s mind and they hope the user can give them the database name as well.  Sadly speaking in most cases user do not know which database their application connect to.

To find a table in the SQL across all databases you can use undocumented stored procedure sp_MSForEachDB.

sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%your_table_name%'''

How to: Check If xp_cmdshell is Enabled

“xp_cmdshell” allow us to run windows commands by using SQL Server but first we need to enable it. We can check whether the “xp_cmdshell” configuration is enabled from the system table sys.configurations which contains server-wide configuration option value in the system.

Execute the following T-SQL will allow you to check if “xp_cmdshell” is enabled on a server. You may need to manually enable the “xp_cmdshell” function if the return result is not “1”.

SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM  sys.configurations
WHERE  name = N'xp_cmdshell' ;

Enable “xp_cmdshell”

Execute the following T-SQL to enable the “xp_cmdshell” configuration.

--Enable Change in Advanced Options
EXEC SP_CONFIGURE N'show advanced options', 1
GO
RECONFIGURE
GO
--Enable XP_cmdshell
EXEC SP_CONFIGURE N'xp_cmdshell', 1
GO
RECONFIGURE
GO
--Disable Changes in Advanced Options
EXEC SP_CONFIGURE N'show advanced options', 0
GO
RECONFIGURE
GO

How to: Find Who Deleted the Database or Table

Found a very useful script from SQLServerCentral Forums that it will help you to easily identify who is the person that had deleted your database or table.

With cteObjectTypes AS
    (
    SELECT
        TSV.trace_event_id,
        TSV.subclass_name,
        TSV.subclass_value
    FROM
        sys.trace_subclass_values AS TSV JOIN
        sys.trace_columns AS TC ON
            TSV.trace_column_id = TC.trace_column_id 
    WHERE
        TC.[name] = 'ObjectType'
    ),
    cteEventSubClasses AS
    (
    SELECT
        TSV.trace_event_id,
        TSV.subclass_name,
        TSV.subclass_value
    FROM
        sys.trace_subclass_values AS TSV JOIN
        sys.trace_columns AS TC ON
            TSV.trace_column_id = TC.trace_column_id 
    WHERE
        TC.[name] = 'EventSubClass'
    )
SELECT
    TE.[name],
    I.ApplicationName,
    I.BigintData1,
    I.ClientProcessID,
    I.ColumnPermissions,
    I.DatabaseID,
    I.DatabaseName,
    I.DBUserName,
    I.Duration,
    I.EndTime,
    I.Error,
    I.EventSequence,
    Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
    I.FileName,
    I.HostName,
    I.IndexID,
    I.IntegerData,
    I.IsSystem,
    I.LineNumber,
    I.LoginName,
    I.LoginSid,
    I.NestLevel,
    I.NTDomainName,
    I.NTUserName,
    I.ObjectID,
    I.ObjectID2,
    I.ObjectName,
    Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,
    I.OwnerName,
    I.ParentName,
    I.Permissions,
    I.RequestID,
    I.RoleName,
    I.ServerName,
    I.SessionLoginName,
    I.Severity,
    I.SPID,
    I.StartTime,
    I.State,
    I.Success,
    I.TargetLoginName,
    I.TargetLoginSid,
    I.TargetUserName,
    I.TextData,
    I.TransactionID,
    I.Type,
    I.XactSequence
FROM
    sys.traces T CROSS Apply 
    sys.fn_trace_gettable((SELECT CONVERT(varchar(100), value)
             FROM sys.fn_trace_getinfo(default)
             WHERE property=2), default) I JOIN
    sys.trace_events AS TE ON 
        I.EventClass = TE.trace_event_id LEFT JOIN
    cteEventSubClasses AS ESC ON
        TE.trace_event_id = ESC.trace_event_id And
        I.EventSubClass = ESC.subclass_value LEFT JOIN
    cteObjectTypes AS OT ON 
        TE.trace_event_id = OT.trace_event_id AND
        I.ObjectType = OT.subclass_value
WHERE
    T.is_default = 1 AND
    TE.NAME = 'Object:Deleted'

Source from SQLServerCentral – Identify who deleted your database or table

How to: Get the Default Backup Directory

The following query provides you the default backup directory of your SQL Server instance.

-- get the default backup directory
DECLARE    @BackupDirectory varchar(1000)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',@BackupDirectory OUTPUT;

SELECT  @BackupDirectory;

How to: Start SQL Server Agent When Agent XPs show Disabled

Problem

The SQL Server Agent was found stopped and the status shows “(Agent XPs disabled)” besides the SQL Server Agent, the funny thing is the service was running according to the services console. Tried to start the service from SQL Server Management Studio, but it didn’t work.

This issue appears when ‘Agent XPs’ advance configuration option is disabled and set to 0 for the SQL Server configuration..

Agent XPs is an advanced configuration option which enables the SQL Server Agent extended stored procedures on the server. When this is not enable SQL Server Agent will not be active in SSMS. Most of the time when you start the SQL Server services it automatically enables ‘Agent XPs’, but sometime it fails to enable or sets the value to 0 and then this issue will appear.

Solution

To fix this issue we should first set the ‘Agent XPs’ to 1 and then run RECONFIGURE to bring it into effect. Execute the following query and try to restart the agent service. The SQL Server agent should be online now.

sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Agent XPs',1
go
reconfigure with override
go
sp_configure 'show advanced options',0
go
reconfigure with override
go

This will be the possible output
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

References

http://sqlserverzest.com/2012/09/19/agent-xps-server-configuration-option/

How to: Start SQL Server with Minimal Configuration

When you encounter configuration problem that prevent SQL Server from starting, you may need to start an instance using the minimal configuration option. This is the start up option -f. Starting an instance of SQL Server with minimal configuration automatically puts the server in single-user mode.

Kindly not what you can do when you start an instance of SQL Server in minimal configuration mode, as below:

  • Only a single user can connect, and the CHECKPOINT process is not executed.
  • Remote access and read-ahead are disabled.
  • Startup stored procedures do not run.

After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.

sqlservr.exe -c -f -s <Instance_Name>

You may want to know:
Starting SQL Server in Minimal Configuration
How to start SQL Server if you lost TEMPDB Data files?

Log Shipping Error “Could not find a log backup file that could be applied to secondary database”

A simple explanation on how log shipping works could be “Backup-Transfer-Restore”. Transaction log will backup on the primary database and transfer the log backup file to the secondary database server, eventually restore it to the database. Thus, any changes on the primary database will reflect on the secondary database.

How to trouble shoot when the log shipping failed and the below error message found in the job history?

Could not find a log backup file that could be applied to secondary database '<DATABASE_NAME>'.

This means the current log backup file which being restored is too recent. A backup from a prior time need to be applied first. Since the log being backed up in sequence on the primary server, the log need to restore according to the sequence as well on the secondary database.

Use the following query to check when is the last log backup file that was copied:

SELECT * FROM [msdb].[dbo].[log_shipping_secondary]

And, also check what was the last log backup file was restore.

SELECT * FROM [msdb].[dbo].[log_shipping_secondary_databases]

Sometime larger transaction log file may takes longer time to copy over to the secondary server while the newer file already copied over, thus it looks unsorted on the secondary database. So wait for the big file to be copied and restart the log shipping job again. Second case, you may need to check the retention time on backup job that run on primary server.

If you see the file on primary server that was not yet copied, you may need to manually copy that over and then start the log shipping again. If the file is not there, you may need to reconfigure the log shipping process again. You can refer to this site on how to configure log shipping for your database.

What is: the Event and Category Queries in Default Trace

The default trace is a valuable tool for the modern DBA’s tool belt. It offers a wealth of information, while minimally impacting the system. The default trace is not a widely publicized feature of SQL Server 2005, but is slowly gaining fame. The default trace gives administrators the ability to get detailed information about auditing events, database events, error events, full text events, object creation, object deletion and object alteration events. With this much information at their fingertips, administrators are more productive and can more easily identify problems in a production environment.

The following scripts will shows you the events and the information that you need to know in with regards to your instance. Default trace should not only be used re-actively but proactively. A proactive mentality will reveal small problems before they escalate to bigger problems.

List of Events

SELECT *
FROM sys.trace_events

List of Categories

SELECT *
FROM sys.trace_categories

List of everything default trace can capture

SELECT DISTINCT cat.name AS CategoryName, e.name AS EventName
FROM fn_trace_geteventinfo(1) AS rt
INNER JOIN sys.trace_events AS e ON rt.eventid = e.trace_event_id
INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id
ORDER BY CategoryName, EventName

List of Subclass Values

SELECT *
FROM sys.trace_subclass_values

Get Trace Event Columns

SELECT 
     t.EventID,
     t.ColumnID,
     e.name AS Event_Descr,
     c.name AS Column_Descr
FROM ::fn_trace_geteventinfo(1) t
     INNER JOIN sys.trace_events e 
          ON t.eventID = e.trace_event_id
     INNER JOIN sys.trace_columns c 
          ON t.columnid = c.trace_column_id

References:

How to: Get All Databases Index Fragmentation Details

This SQL script will provide you the index fragmentation details for all databases. It would be helpful while scheduling or determining maintenance task of re-indexing.

This SQL script will provides you all the databases names, object names, index id, index name, average fragmentation percent, index type & action required.

The “Action required column” on the basis of fragmentation value using sys.dm_db_index_physical_stats DMV.

If average fragmentation value is >5 & 30 index should rebuild, those value is less than 5 then no result is require.

IF EXISTS (	SELECT *
FROM [tempdb].[dbo].[sysobjects]
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]'))
DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details]
GO

CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details](
[DatabaseName] 					[nvarchar] (100) NULL,
[ObjectName]					[nvarchar] (100) NULL,
[Index_id] 						INT,
[indexName] 					[nvarchar] (100) NULL,
[avg_fragmentation_percent]		float NULL,
[IndexType] 					[nvarchar] (100) NULL,
[Action_Required] 				[nvarchar] (100) default 'NA'
) ON [PRIMARY]

DECLARE @dbname varchar(1000)
DECLARE @sqlQuery nvarchar(4000)

DECLARE dbcursor CURSOR for
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlQuery = '
USE [' + @dbname + '];

IF EXISTS
(
SELECT compatibility_level
FROM sys.databases
WHERE
name = N'''+ @dbname +'''
AND compatibility_level >= 90
)
BEGIN
INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details]
(
DatabaseName
, ObjectName
, Index_id
, indexName
, avg_fragmentation_percent
, IndexType
)
SELECT
db_name() as DatabaseName
, OBJECT_NAME (a.object_id) as ObjectName
, a.index_id, b.name as IndexName
, avg_fragmentation_in_percent
, index_type_desc
FROM
sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON
a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE
b.index_id <> 0
AND avg_fragmentation_in_percent <> 0
END;'

EXEC sp_executesql @sqlQuery

FETCH NEXT FROM dbcursor
INTO @dbname
END

CLOSE dbcursor
Deallocate dbcursor

-- Update the action require for item with average fragmentation value >30 to "Rebuild"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]
SET Action_Required = 'Rebuild'
WHERE avg_fragmentation_percent >30
GO

-- Update the action require for item with average fragmentation value >5 & <30 to "Reindex"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]
SET Action_Required = 'Reorganize'
WHERE avg_fragmentation_percent <30 and avg_fragmentation_percent >5
GO

-- Show the index fragmentation result
SELECT * FROM [tempdb].[dbo].[tmp_indexfragmentation_details]
ORDER BY databasename

NOTE: This SQL script will work on both SQL Server 2005, 2008 & 2008R2. It won’t work for SQL 2000 as DMV’s are involved in this script.

Original scripts adopted from : http://gallery.technet.microsoft.com/scriptcenter/All-Databases-Index-a68ed3c5