Find Failed Jobs in 24 Hours

http://www.databasejournal.com/scripts/find-failed-jobs-in-24-hours.html

SELECT DISTINCT
CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS 'Failure Date',
SUBSTRING(T2.name,1,40) AS 'Job Name',
T1.step_id AS 'Step_id',
T1.step_name  AS 'Step Name',
LEFT(T1.[message],500) AS 'Error Message'
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs  T2
ON T1.job_id = T2.job_id
WHERE  T1.run_status NOT IN (1,4)
AND T1.step_id != 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112) 

Database Size - Disk Space Used for all databases.

http://www.databasejournal.com/scripts/database-size.html

;WITH DataBase_Size (SqlServerInstance,DatabaseName,DatabaseSize,LogSize,TotalSize)
AS
-- Define the CTE query.
(
  SELECT      @@SERVERNAME SqlServerInstance,
            db.name AS DatabaseName,
            SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE af.size / 128.0E END) AS DatabaseSize,
            SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize,
            SUM(af.size / 128.0E) AS TotalSize
FROM        master..sysdatabases AS db
INNER JOIN  master..sysaltfiles AS af ON af.[dbid] = db.[dbid]
WHERE       db.name NOT IN ('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb') -- System databases
            AND db.name NOT IN ('Northwind', 'pubs', 'AdventureWorks', 'AdventureWorksDW')   -- Sample databases
GROUP BY    db.name 
)
-- Define the outer query referencing the  name.
SELECT *
FROM DataBase_Size order by TotalSize desc

Import the SQL Server Error Log into a Table

http://www.databasejournal.com/scripts/article.php/3518116/Import-the-SQL-Server-Error-Log-into-a-Table.htm

CREATE PROC sp_import_errorlog
(
 @log_name sysname,
 @log_number int = 0,
 @overwrite bit = 0
)
AS
/*************************************************************************************************
Purpose: To import the SQL Server error log into a table, so that it can be queried

Written by: Anand Mahendra
 
Tested on:  SQL Server 2000

Limitation:  With error messages spanning more than one line only the first line is included in the table

Email:   anandbox@sify.com

Example 1:  To import the current error log to table myerrorlog
  EXEC sp_import_errorlog 'myerrorlog'

Example 2:  To import the current error log to table myerrorlog, and overwrite the table
  'myerrorlog' if it already exists
  EXEC sp_import_errorlog 'myerrorlog', @overwrite = 1

Example 3:  To import the previous error log to table myerrorlog
  EXEC sp_import_errorlog 'myerrorlog', 1

Example 4:  To import the second previous error log to table myerrorlog
  EXEC sp_import_errorlog 'myerrorlog', 2

*************************************************************************************************/

BEGIN
 SET NOCOUNT ON
 
 DECLARE @sql varchar(500) --Holds to SQL needed to create columns from error log

 IF (SELECT OBJECT_ID(@log_name,'U')) IS NOT NULL
  BEGIN
   IF @overwrite = 0
    BEGIN
     RAISERROR('Table already exists. Specify another name or pass 1 to @overwrite parameter',18,1)
     RETURN -1
    END
   ELSE
    BEGIN
     EXEC('DROP TABLE ' + @log_name)
    END
  END

 
 --Temp table to hold the output of sp_readerrorlog
 CREATE TABLE #errlog
 (
  err varchar(1000),
  controw tinyint
 )

 --Populating the temp table using sp_readerrorlog
 INSERT #errlog 
 EXEC sp_readerrorlog @log_number

 --This will remove the header from the errolog
 SET ROWCOUNT 4
 DELETE #errlog
 SET ROWCOUNT 0

 
 SET @sql =  'SELECT 
    CONVERT(DATETIME,LEFT(err,23)) [Date], 
    SUBSTRING(err,24,10) [spid], 
    RIGHT(err,LEN(err) - 33) [Message], 
    controw 
   INTO ' + QUOTENAME(@log_name) + 
   ' FROM #errlog ' + 
   'WHERE controw = 0'
 
 --Creates the table with the columns Date, spid, message and controw
 EXEC (@sql) 
 
 --Dropping the temporary table
 DROP TABLE #errlog
 
 SET NOCOUNT OFF
PRINT 'Error log successfully imported to table: ' + @log_name
END 

Creating Excel Using T-SQL

http://www.databasejournal.com/scripts/creating-excel-using-t-sql.html

PRINT 'Begin CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '
PRINT ''
GO

SET NOCOUNT ON
DECLARE @Conn int -- ADO Connection object to create XLS
      , @hr int -- OLE return value
      , @src varchar(255) -- OLE Error Source
      , @desc varchar(255) -- OLE Error Description
      , @Path varchar(255) -- Drive or UNC path for XLS
      , @Connect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM
      , @WKS_Created bit -- Whether the XLS Worksheet exists
      , @WKS_Name varchar(128) -- Name of the XLS Worksheet (table)
      , @ServerName nvarchar(128) -- Linked Server name for XLS
      , @DDL varchar(8000) -- Jet4 DDL for the XLS WKS table creation
      , @SQL varchar(8000) -- INSERT INTO XLS T-SQL
      , @Recs int -- Number of records added to XLS
      , @Log bit -- Whether to log process detail
 
SELECT @Recs = 0 
   , @Log = 1 

SET @Path = 'E:\Rahul\'+CONVERT(varchar(10),GETDATE(),112)+'.xls'
SET @Path = 'E:\Rahul\RecordsHistory_MobDW.xls'
SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+';Extended Properties=Excel 8.0'
SET @ServerName = 'EXCEL_TEST'
SET @WKS_Name = CONVERT(varchar(10),GETDATE(),112)


SET @DDL = 'CREATE TABLE '+@WKS_Name+' (TableName nvarchar, RowsCount int)'
SET @SQL = 'INSERT INTO '+@ServerName+'...'+@WKS_Name+' (TableName, RowsCount) '

SET @SQL = @SQL+'SELECT au_id AS SSN'
SET @SQL = @SQL+', LTRIM(RTRIM(ISNULL(au_fname,'''')+'' ''+ISNULL(au_lname,''''))) AS Name'
SET @SQL = @SQL+', phone AS Phone '
SET @SQL = @SQL+'FROM Rahul.dbo.Dim_Date_Test'

IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'

-- Create the Conn object
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT IF @hr <> 0 BEGIN
      EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
      SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END

 

IF @Log = 1 PRINT char(9)+'Assigned ConnectionString property'
 EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect IF @hr <> 0 BEGIN
      EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
      SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END

IF @Log = 1 PRINT char(9)+'Open Connection to XLS, for file Create or Append'

-- Call the Open method to create the XLS if it does not exist, can't use parameters

EXEC @hr = sp_OAMethod @Conn, 'Open'
IF @hr <> 0
BEGIN
       EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
      SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END

 

-- %%% This section could be repeated for multiple Worksheets (Tables)

IF @Log = 1 PRINT char(9)+'Execute DDL to create '''+@WKS_Name+''' worksheet'
 EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 -- adCmdText + adExecuteNoRecords

-- 0x80040E14 for table exists in ADO

IF @hr = 0x80040E14 
   OR @hr = 0x80042732
BEGIN
      IF @hr = 0x80040E14
      BEGIN
            PRINT char(9)+''''+@WKS_Name+''' Worksheet exists for append'
            SET @WKS_Created = 0
      END
      SET @hr = 0 -- ignore these errors END

IF @hr <> 0
BEGIN
      -- Return OLE error
      EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
      SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END

 

IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object'
-- Destroy the Conn object, +++ important to not leak memory +++ EXEC @hr = sp_OADestroy @Conn IF @hr <> 0 BEGIN
      -- Return OLE error
      EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
      SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
      RETURN
END

 

-- Linked Server allows T-SQL to access the XLS worksheet (Table)
--   This must be performed after the ADO stuff as the XLS must exist
--   and contain the schema for the table, or worksheet

IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName) BEGIN
      IF @Log = 1 PRINT 'Created Linked Server '''+@ServerName+''' and Login'
      EXEC sp_addlinkedserver @server = @ServerName
            , @srvproduct = 'Microsoft Excel Workbook'
            , @provider = 'Microsoft.Jet.OLEDB.4.0'
            , @datasrc = @Path
            , @provstr = 'Excel 8.0' 
      EXEC sp_addlinkedsrvlogin @ServerName, 'false' 
END


EXEC (@SQL)
PRINT char(9)+'Populated '''+@WKS_Name+''' table with '+CONVERT(varchar,@@ROWCOUNT)+' Rows'

 IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName) BEGIN
      IF @Log = 1 PRINT 'Deleted Linked Server '''+@ServerName+''' and Login'
      EXEC sp_dropserver @ServerName, 'droplogins'
END
GO

 

SET NOCOUNT OFF
PRINT ''
PRINT 'Finished CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '
GO

http://www.databasejournal.com/scripts/all-database-space-used-and-free.html

All Database Space Used and Free

By DatabaseJournal.com Staff

>>Script Language and Platform: SQL Server
Helps DBA to find out quickly which database takes a lot of space and which file could be shrunk. Very useful when output is sorted by Drive Letter when "Out of space" occurs.

Author: Leonid Sheinkman

 /*
Author: Leonid Sheinkman
Created: 2009-01-12
Updated: 2013-02-15

This script use undocumented DBCC showfilestats command */ 

USE master GO

SET NOCOUNT ON
DECLARE @Kb float
DECLARE @PageSize float
DECLARE @SQL varchar(max)

SELECT @Kb = 1024.0
SELECT @PageSize=v.low/@Kb FROM master..spt_values v WHERE v.number=1 AND v.type='E'

IF OBJECT_ID('tempdb.dbo.#FileSize') IS NOT NULL  DROP TABLE #FileSize CREATE TABLE #FileSize (  DatabaseName sysname,  [FileName] varchar(max),  FileSize int,  FileGroupName varchar(max),  LogicalName varchar(max)
)

IF OBJECT_ID('tempdb.dbo.#FileStats') IS NOT NULL  DROP TABLE #FileStats CREATE TABLE #FileStats (  FileID int,  FileGroup int,  TotalExtents int,  UsedExtents int,  LogicalName varchar(max),  FileName varchar(max)
)

IF OBJECT_ID('tempdb.dbo.#LogSpace') IS NOT NULL  DROP TABLE #LogSpace CREATE TABLE #LogSpace (  DatabaseName sysname,  LogSize float,  SpaceUsedPercent float,  Status bit
)

INSERT #LogSpace EXEC ('DBCC sqlperf(logspace)')

DECLARE @DatabaseName sysname

DECLARE cur_Databases CURSOR FAST_FORWARD FOR  SELECT DatabaseName = [name] FROM dbo.sysdatabases WHERE [name] <> 'RVR_FSA' ORDER BY DatabaseName OPEN cur_Databases FETCH NEXT FROM cur_Databases INTO @DatabaseName WHILE @@FETCH_STATUS = 0
  BEGIN
 print @DatabaseName
 SET @SQL = '
USE [' + @DatabaseName + '];
DBCC showfilestats;
INSERT #FileSize (DatabaseName, [FileName], FileSize, FileGroupName, LogicalName) SELECT ''' +@DatabaseName + ''', filename, size, ISNULL(FILEGROUP_NAME(groupid),''LOG''), [name]  FROM dbo.sysfiles sf; '
PRINT @SQL
 INSERT #FileStats EXECUTE (@SQL)
 FETCH NEXT FROM cur_Databases INTO @DatabaseName
  END

CLOSE cur_Databases
DEALLOCATE cur_Databases


SELECT
 DatabaseName = fsi.DatabaseName,
 FileGroupName = fsi.FileGroupName,
 LogicalName = RTRIM(fsi.LogicalName),
 [FileName] = RTRIM(fsi.FileName),
 DriveLetter = LEFT(RTRIM(fsi.FileName),2),  FileSize = CAST(fsi.FileSize*@PageSize/@Kb as decimal(15,2)),  UsedSpace = CAST(ISNULL((fs.UsedExtents*@PageSize*8.0/@Kb), fsi.FileSize*@PageSize/@Kb * ls.SpaceUsedPercent/100.0) as decimal(15,2)),  FreeSpace = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0)*@PageSize/@Kb), (100.0-ls.SpaceUsedPercent)/100.0 * fsi.FileSize*@PageSize/@Kb) as decimal(15,2)),  [FreeSpace %] = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0) / fsi.FileSize * 100.0), 100-ls.SpaceUsedPercent) as decimal(15,2))  FROM #FileSize fsi  LEFT JOIN #FileStats fs  ON fs.FileName = fsi.FileName  LEFT JOIN #LogSpace ls  ON ls.DatabaseName = fsi.DatabaseName  ORDER BY 5, 8 DESC

Search Find a text in all stored procedures in all database in one go

http://www.databasejournal.com/scripts/search-all-stored-procedures-in-all-databases.html

EXEC sp_MSForEachDB 

 'USE ?; 
SELECT DB_NAME(), ROUTINE_NAME 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE ''%foobar%''
    AND ROUTINE_TYPE = ''PROCEDURE'''

Importance of Recovery Model in SQL Server

http://www.databasejournal.com/features/mssql/importance-of-recovery-model-in-sql-server.html

Introduction

Have you ever wondered, especially in the case of a data warehousing scenario, why the transaction log file grows bigger and bigger and sometimes even much bigger than your actual database's data files? What caused it to happen? How do you control it? How does the recovery model of a database control the growing size of the transaction log? These are some of the questions I am going to explain to you in this article.

Recovery Model

Transaction logging is the internal mechanism of SQL Server that keeps logging all transactions and the database modifications that are made by each transaction as a string of log records in a serial sequence as they are created. This is important to bring the database back to a consistent state if there is a system failure. But what about the growth of the transaction log, how is its size controlled? You can learn more about Transaction Log and its architecture here.

Recovery Model is one of the mechanisms which controls and manages the growth of the transaction log file. Recovery Model controls how transactions are logged, whether there is automatic log truncation, whether the transaction log requires and/or allows backing up the transaction log, and what kind of restore operations are available.

Every database in SQL Server has a property called Recovery Model, which could have either of Simple, Bulk-logged and Full value based on your different needs for performance, storage space, and protection against data loss. You need to evaluate the trade-off between performance of your bulk operations (index creation or bulk loads), storage space needed for storage of the transaction log, the possibility of data loss (loss of committed transactions) and simplicity in backup and restore operations. A database can be switched to another recovery model at any time in order to meet the changing business needs but before doing that, please, evaluate its impact.

Full Recovery ModelDepending on your need you might need to use more than one Recovery Model. For example consider you have a mission-critical OnLine Transaction Processing (OLTP) database, so in order to be able to restore to any point in time (so that no committed transactions are lost), you should use Full recovery model but when you are performing some bulk operations (Index Creation, SELECT INTO, INSERT SELECT, BCP, BULK INSERT) you can switch to Bulk-logged recovery model for minimal logging and after completion switch back to Full recovery model. This reduces the chances of filling up the available transaction log space during bulk operations.

As its name implies, Full recovery model logs every transaction and maintains it there until a transaction log backup is taken (or full backup is taken, which includes everything). With this recovery model, you can devise a disaster recovery plan that includes a combination of full backup (or/and differential backup) and transaction log backups. To control the size of the transaction log, you need to take a transaction log backup so that it gets truncated.

With Full recovery model, you can recover to an arbitrary point in time (for example, prior to application or user error) from transaction log backups and hence no work is lost due to lost or damaged data files.

Advantage – Full recovery model provides complete protection against data loss. In the unfortunate case of disaster or application\user error, you can restore to the point-in-time by using the available transaction log backups (assuming your transaction backups are complete up to that point in time).

Disadvantage – With full recovery model, you need to setup a regular transaction log backup to ensure the growth of transaction log files are under control otherwise it will keep on growing until your next full backup. Also, if the transaction log is damaged, changes since the most recent transaction log backup must be redone.

When to use it – Full recovery model is recommended for OLTP databases, where you have mostly short lived transactions and you don’t want to lose data for a committed transaction. There are certain other features – like AlwaysOn, Database mirroring, Log shipping, Transaction replication, Change data capture – in SQL Server that require Full recovery model when you are using them.

Bulk-logged Recovery Model

Bulk-logged recovery model is similar to Full recovery model with the exception that bulk data modification operations (Index Creation, SELECT INTO, INSERT SELECT, BCP, BULK INSERT) are minimally logged in this case and hence it reduces the performance impact but at the same time, you might not be able to do point-in-time restore. As a recommended practice, Bulk-logged recovery model is used with full recovery model, i.e. you should generally have Full recovery model for normal operations and switch to Bulk-logged recovery model temporarily when you are starting occasional bulk operations. Finally at completion of bulk operation, reverse back to Full recovery model. It’s also recommended to take a transaction log backup after switching back to Full recovery model if point-in-time recovery is important.

Like Full recovery model, the transaction log file will keep on growing and hence you need to take transaction log backups frequently. If there are no bulk operations, Bulk-logged is the same as Full recovery model and you can recover to the point-in-time as the transaction log contains full sequential records of all the changes made to the database.

Advantage – Allows better performance for bulk data operations by only doing minimal logging for these transactions and not letting the transaction log grow significantly because of these bulk data operations (lowering log space consumption for bulk operations).

Disadvantage – There is a possibility of data loss if the log is damaged or bulk-logged operations occurred since the most recent log backup and hence changes since that last backup must be redone.

When to use it – Its recommended to switch to Bulk-logged recovery model before starting any occasional bulk operations and then reverse back to Full recovery model after completion of bulk operations. This way you can still restore to point-in-time (as long as your last transaction log backup does not include a bulk operation) and can have bulk operations logged minimally.

Note - Minimal logging means logging only information needed to recover the transaction without supporting point-in-time recovery. With minimal logging, the transaction log keeps track of pages that were changed by bulk operations based on Bulk Changed Map (MCP) page instead of logging each individual change (older value or new value). This way the transaction log remains smaller but when you take a backup of the transaction log, it includes all the changed pages and hence even though the transaction log remains smaller, the transaction log backup might be much bigger than this.

Simple Recovery Model

Simple recovery model is the simplest of all. It maintains only a minimum amount of information in the SQL Server transaction log file. SQL Server, on its own, truncates the transaction log files (excluding logs from any open transactions) and removes the information related to transactions which have reached transaction checkpoints (data has been written to the data file) so that the space can be reused, leaving no transaction log entries for disaster recovery purposes. Having said that, with Simple recovery model, the data is recoverable only to the most recent full database or differential backups (no transaction log backups are supported). Under the Simple recovery model, transaction log truncation happens after a checkpoint or as soon as you change the recovery model of your database to Simple recovery model.

Managing databases with Simple recovery model is much easier but it comes at the expense of higher data loss exposure if a data file is damaged. You can only restore from the latest full\differential backups; this means you will automatically lose any data modifications made between the time of the latest full/differential backup and the time of the failure. Hence, if you use Simple recovery model for your database, you should keep the backup interval long enough to keep the backup overhead from affecting production work and at the same time short enough to prevent a significant amount of data loss.

Advantage – Manageability with Simple recovery model is much easier--no need to take transactional backups. It reclaims transaction log spaces from check-pointed transactions to ensure the growth of transaction log files are under control. Bulk-logged operations perform much better because of minimal logging and as minimal transaction log space is used.

Disadvantage – You will lose any data modifications made between the time of the latest full/differential backup and the time of the failure when you want to restore.

When to use it – In a data warehousing scenario, where you mostly have bulk operations while data loading and in case of failure, the data can be regenerated from the data source. You can also prefer using Simple recovery model in your development or test environment to ensure the growth of the transaction log files are controlled.

Changing Recovery Model with T-SQL

You can use ALTER DATABASE command with the SET RECOVERY option to change the recovery model of a database. For example, the query below changes the recovery model of the AdventureWorks database to Full recovery model.

ALTER DATABASE AdventureWorks SET   RECOVERY FULL   ;
  

You can query the sys.databases catalog view to verify the recovery model of the database as shown below:

SELECT   name, recovery_model,   recovery_model_desc FROM sys.databases WHERE   name = 'AdventureWorks'   ;

You can use the command below to change the recovery model to Bulk-logged or Simple, just replace your database name in place of AdventureWorks:

--Changing recovery model to Bulk-logged
ALTER DATABASE AdventureWorks SET   RECOVERY BULK_LOGGED   ;
--Changing recovery model to Simple
ALTER DATABASE AdventureWorks SET   RECOVERY SIMPLE   ;

When you create a new database, it inherits the recovery model from the model database, which is by default Full recovery model. To change the default recovery model, you can use the ALTER DATABASE statement, as mentioned above, to change the recovery model of the model database.

Please note, if you intend to maintain a sequence of transaction log backups, you cannot switch to or from Simple recovery model.

Changing Recovery Model with SQL Server Management Studio (SSMS)

You can also change the recovery model for a database in SQL Server Management Studio. In the Object Explorer, right click on your database under the Databases node and then click on Properties. In the Database Properties dialog box, click on the Options tab and then change the recovery model as shown below:

Database Properties

Conclusion

In this article we discussed Recovery Model, which is one of the mechanisms that controls and manages the growth of the transaction log file. I discussed different types of Recovery Model, with its advantages, disadvantages and when to choose them over others. Then I talked about changing recovery model for a database either using T-SQL or SQL Server Management Studio.