GAPTHEGURU

Geek with special skills

Monitoring tempdb Transactions and Space usage

As the tempdb database is the common global resource for all the operations going on in SQL Server, so the DBA has to be bit cautious about the use of it. Because any unexpected operations by the applications running under the SQL Server instance or any adhoc query by the user can eat all the space available to the tempdb database resulting decrease in the performance of other applications running under the same instance. So it is necessary to keep track the usage of tempdb database by various applications and processes and to take necessary actions when the size falls down below the threshold limit. Monitoring tempdb over time will help in determining the optimal size of the tempdb

Use the below query to check the current tempdb size:

 

Advertisements

02/16/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , , | 1 Comment

Cleaning up TempDB

In many significantly large production environments, the TempDB can regularly grow with little or no thought towards maintaining some control over the lifespan of tables. In most situations, TempDB is not used heavily enough to be a problem, but occasionally due to non-ideal code, many tables are created and left with no regular cleanup.

While I am a proponent of using Table Variables over Temp Tables, the fact remains that many developers, and a significant number of DBAs use Temp Tables, and it is left in the hands of the DBA to deal with any complications caused by this.

The most obvious flaw in using Temp Tables in a 24/7 Productions Environment, is that if your SQL Server box never reboots, your TempDB never gets cleaned out (unless it’s by the code that created the Temp Tables, and that never happens. To deal with this issue, I have created a SP that runs once an hour, deleting Temp Tables that are more than 12 hours old (unless they reside in a Table (TempTableToKeep) that I use to store the names of tables I want to keep, and the date/time to finally delete them.

The code:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N’[dbo].[TempTableToKeep]‘) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)
DROP TABLE [dbo].[TempTableToKeep]
GO
CREATE TABLE [dbo].[TempTableToKeep] (
[TempTable] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateToDelete] [datetime] NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT name FROM sysobjects WHERE  name = N’sp_DropTempTables’ AND type = ‘P’)
DROP PROCEDURE sp_DropTempTables
GO
CREATE PROCEDURE sp_DropTempTables
AS
DECLARE @Cursor AS CURSOR
DECLARE @Name AS VARCHAR(100)
DECLARE @TableName AS SYSNAME
DECLARE @Owner AS VARCHAR(100)
DECLARE @SQL AS NVARCHAR(200)
SET @Cursor = CURSOR SCROLL FOR
SELECT    tempdb.dbo.sysobjects.name, tempdb.dbo.sysobjects.*
FROM    TempTableToKeep
RIGHT OUTER JOIN tempdb.dbo.sysobjects ON TempTableToKeep.TempTable = tempdb.dbo.sysobjects.name
WHERE    ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete < GETDATE())) OR
((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete IS NULL))
OPEN @Cursor
FETCH FIRST FROM @Cursor
INTO @Name, @Owner
WHILE (@@FETCH_STATUS = 0)
BEGIN
If (@@FETCH_STATUS = 0)
BEGIN
IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE  name = @Name AND type = ‘U’)
BEGIN
SET @SQL = ‘DROP TABLE tempdb..’ + @Name
–PRINT @SQL
EXECUTE sp_executesql @SQL
END
FETCH NEXT FROM @Cursor
INTO @Name
END
END
CLOSE @Cursor
DEALLOCATE @Cursor
GO

02/16/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , , | 2 Comments

SQL SERVER – Get All the Information of Database using sys.databases

SELECT database_id,

CONVERT(VARCHAR(25), DB.name) AS dbName,

CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],

state_desc,

(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) =

DB.name AND type_desc = 'rows') AS DataFiles,

(SELECT SUM((size*8)/1024) FROM sys.master_files

WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],

(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) =

DB.name AND type_desc = 'log') AS LogFiles, (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE

DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],

user_access_desc AS [User access],

recovery_model_desc AS [Recovery model],

CASE compatibility_level

WHEN 60 THEN '60 (SQL Server 6.0)'

WHEN 65 THEN '65 (SQL Server 6.5)'

WHEN 70 THEN '70 (SQL Server 7.0)'

WHEN 80 THEN '80 (SQL Server 2000)'

WHEN 90 THEN '90 (SQL Server 2005)'

WHEN 100 THEN '100 (SQL Server 2008)'

END AS [compatibility level],

CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20),

create_date, 108) AS [Creation date],

-- last backup

ISNULL((SELECT TOP 1

CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L'

THEN 'Transaction log' END + ' – ' +

LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +

CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' +

CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +

CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' +

CONVERT(VARCHAR(20), backup_finish_date, 108) + ' (' + CAST(DATEDIFF(second, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' ' + 'seconds)'

FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup], CASE

WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],

CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose], page_verify_option_desc AS [page verify option],

CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only], CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],

CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],

CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],

CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],

CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS

[cleanly shutdown]

FROM sys.databases DB

ORDER BY dbName, [Last backup] DESC, NAME

01/31/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , | Leave a comment

   

%d bloggers like this: