Transaction log file information

November 7, 2013 Leave a comment

General T-Log info

all databases

select name
		, physical_name
		, state_desc
		, (size * 8)/1024 AS [Total Size in MB]
		, (size * 8)/1024 - ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) as [Used Space In MB]
		, ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) AS [Available Space In MB]
		, max_size
		, growth
		, is_percent_growth
from sys.master_files where type_desc = 'LOG';

given database

select name
		, physical_name
		, state_desc
		, (size * 8)/1024 AS [Total Size in MB]
		, (size * 8)/1024 - ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) as [Used Space In MB]
		, ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) AS [Available Space In MB]
		, max_size
		, growth
		, is_percent_growth
from sys.database_files where type_desc = 'LOG';

detailed transaction log info

select * from ::fn_dblog(null, null);
Advertisements

Check VLFs

November 7, 2013 Leave a comment

given database

DBCC LogInfo;

all databases on the server

declare @v_sql nvarchar(max);
select @v_sql = coalesce(@v_sql + CHAR(13) + char(10), '') + N'
use [' + name + '];' + CHAR(13) +
'DBCC LogInfo;'
from sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB');
print @v_sql
exec sp_executesql @v_sql
Categories: Handy Scripts Tags:

Shrink transaction log file(s)

November 7, 2013 Leave a comment

Sometimes there is an urgent need to shrink transaction log file/s of a single or multiple database/s which has/have grown to an extent that threatens to leave to free space on the logical drive, which would lead to entire database server operation freeze up.

The “quick-and-dirty” solution in such situation is to quickly shrink the t-log file/s. There are two methods of doing this. First one would simply attempt the file shrink operation. Second option is to back this/these file/s up first. Shrinking transaction log files without prior backing them up (first method) might or might not┬ádo the job – depending on what part of this/these file/s is marked “used”. If the tail of the transaction log file (latter VLFs) is marked as used than the first method wouldn’t work, i.e. the files won’t get shrank in size and won’t release the used space back to the operating system, so the problem would persist. To take care of that, the second method is more preferable, given the┬áconstraints of time and free space elsewhere to accommodate the t-log backup file/s. In addition, it’s more safe to backup transaction log file/s prior to shrinking them in case of production database/s running in full recovery mode – just in case of DRP point-in-time scenario.

First method:

–single database

DBCC SHRINKFILE([TLog_LogicalFileName], 1)

–generic script – all databases

sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''tempdb'', ''tempdev'', ''model'', ''msdb'')
AND (SELECT recovery_model FROM master.sys.databases WHERE name = ''?'') = 1
AND (SELECT is_read_only FROM master.sys.databases WHERE name = ''?'') = 0
BEGIN
declare @LogFile nvarchar(2000)
USE [?]
SELECT @LogFile = name
FROM sys.database_files
WHERE type = 1
DBCC SHRINKFILE (@LogFile, truncateonly)
END'

Second method:

(try to stick to it, at least for all production databases as well as any databases running in full recovery mode)

–single database

USE [Database_Name]
GO
BACKUP LOG [Database_Name] TO DISK = N'[path]\[file_name].trn'
WITH NOFORMAT, INIT, NAME = N'[Database_Name] - Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, [COMPRESSION*], COPY_ONLY, STATS = 1;
DBCC SHRINKFILE([TLog_LogicalFileName], 1)
GO

* [COMPRESSION] option is available in both Enterprise and Standard editions of SQL Server 2008R2, but only in Enterprise edition of SQL Server 2008

–generic, all databases

declare @SQL nvarchar(max);
declare @v_TLog_dir nvarchar(max);
declare @v_compression_YN nchar(1);
BEGIN
set @v_TLog_dir = 'C:\Temp';
set @v_compression_YN = 'Y';
select @SQL = coalesce(@SQL + char(13) + char(10),'') + N'
Use [' + d.name + '];' + CHAR(13) +
'BACKUP LOG ' + d.name + ' TO DISK = N''' + @v_TLog_dir + '\' + mf.name + '.trn'' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD' +
case @v_compression_YN
when 'Y' then
', COMPRESSION, COPY_ONLY;'
when 'N' then
', COPY_ONLY;'
end + CHAR(13) +
'DBCC SHRINKFILE (' + quotename(mf.[name],'''') + ', 1) WITH NO_INFOMSGS;'
FROM sys.databases d INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name not in ('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB')
--AND d.recovery_model_desc = 'FULL'
AND d.is_read_only = 0
AND mf.type_desc = 'LOG'
ORDER BY d.name;
--print @SQL;
exec sp_executesql @SQL;
END;

062311_1657_SQLServerse2.png

NOTE: It’s highly recommended to comment out “exec sp_executesql @SQL;” and uncomment “–print @SQL;” lines respectively to first review the result. Then the lines could be swapped or, simply, the output copied over to new query window and ran there.

Check database and server collation settings

November 7, 2013 Leave a comment

Database collation, if different from server collation

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')

Server collation

SELECT SERVERPROPERTY('Collation')

Generate index creation script

November 7, 2013 Leave a comment
SELECT SchemaName, TableName, IndexName, 
  (CASE pk 
    WHEN 'PK' THEN 'ALTER '+ 
     'TABLE '+SchemaName+'.'+TableName+' ADD CONSTRAINT '+IndexName+' PRIMARY KEY'+ 
     (CASE substring(Type,3,1) WHEN 'C' THEN ' CLUSTERED' ELSE '' END) 
    ELSE 'CREATE '+ 
     (CASE substring(Type,1,1) WHEN '1' THEN 'UNIQUE ' ELSE '' END)+ 
     (CASE substring(Type,3,1) WHEN 'C' THEN 'CLUSTERED ' ELSE '' END)+ 
     'INDEX '+IndexName+' ON '+SchemaName+'.'+TableName 
    END)+ 
  ' ('+ 
    (CASE WHEN Key1 is null THEN '' ELSE      Key1+(CASE substring(Type,4+1,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+ 
    (CASE WHEN Key2 is null THEN '' ELSE ', '+Key2+(CASE substring(Type,4+2,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+ 
    (CASE WHEN Key3 is null THEN '' ELSE ', '+Key3+(CASE substring(Type,4+3,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+ 
    (CASE WHEN Key4 is null THEN '' ELSE ', '+Key4+(CASE substring(Type,4+4,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+ 
    (CASE WHEN Key5 is null THEN '' ELSE ', '+Key5+(CASE substring(Type,4+5,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+ 
    (CASE WHEN Key6 is null THEN '' ELSE ', '+Key6+(CASE substring(Type,4+6,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+ 
    ')' as CreateIndex 
FROM ( 
	  SELECT 
	  schema_name(schema_id) as SchemaName, OBJECT_NAME(si.object_id) as TableName, si.name as IndexName, 
	  (CASE is_primary_key WHEN 1 THEN 'PK' ELSE '' END) as PK, 
	  (CASE is_unique WHEN 1 THEN '1' ELSE '0' END)+' '+ 
	  (CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+  -- B=basic, C=Clustered, X=XML 
	  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
	  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
	  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
	  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
	  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
	  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
	  '' as 'Type', 
	  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,1) as Key1, 
	  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,2) as Key2, 
	  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,3) as Key3, 
	  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,4) as Key4, 
	  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,5) as Key5, 
	  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,6) as Key6 
	FROM sys.indexes as si 
	LEFT JOIN sys.objects as so on so.object_id=si.object_id 
	WHERE index_id>0 -- omit the default heap 
	  and OBJECTPROPERTY(si.object_id,'IsMsShipped')=0 -- omit system tables 
	  and not (schema_name(schema_id)='dbo' and OBJECT_NAME(si.object_id)='sysdiagrams') -- omit sysdiagrams 
  ) as indexes 
ORDER BY SchemaName,TableName,IndexName;

 

List all indexes on table(s)/column(s)

November 6, 2013 Leave a comment
select i.name as index_name, i.type_desc as index_type, t.name as table_name, c.name as column_name, ic.index_column_id, ic.key_ordinal
from sys.indexes i 
	 inner join sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
	 inner join sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id
	 inner join sys.tables t on t.object_id = ic.object_id and t.object_id = c.object_id
and t.name = [TABLE]
and c.name = [COLUMN];

OR (all tables in a database)

select 
    ind.name as index_name
    , t.name as table_name
    , ind.index_id
    , ic.index_column_id
    , col.name
    , ind.*, ic.*, col.* 
from sys.indexes ind 
	 inner join sys.index_columns ic on ind.object_id = ic.object_id and ind.index_id = ic.index_id 
	 inner join sys.columns col on ic.object_id = col.object_id and ic.column_id = col.column_id  
	 inner join sys.tables t on ind.object_id = t.object_id 
where ind.is_primary_key = 0  
	  and ind.is_unique = 0  
      and ind.is_unique_constraint = 0 
      and t.is_ms_shipped = 0 
order by t.name, ind.name, ind.index_id, ic.index_column_id;

ALTERNATIVELY:

SELECT 
  schema_name(schema_id) as SchemaName, OBJECT_NAME(si.object_id) as TableName, si.name as IndexName, 
  (CASE is_primary_key WHEN 1 THEN 'PK' ELSE '' END) as PK, 
  (CASE is_unique WHEN 1 THEN '1' ELSE '0' END)+' '+ 
  (CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+  -- B=basic, C=Clustered, X=XML 
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ 
  '' as 'Type', 
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,1) as Key1, 
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,2) as Key2, 
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,3) as Key3, 
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,4) as Key4, 
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,5) as Key5, 
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,6) as Key6 
FROM sys.indexes as si 
LEFT JOIN sys.objects as so on so.object_id=si.object_id 
WHERE index_id>0 -- omit the default heap 
  and OBJECTPROPERTY(si.object_id,'IsMsShipped')=0 -- omit system tables 
  and not (schema_name(schema_id)='dbo' and OBJECT_NAME(si.object_id)='sysdiagrams') -- omit sysdiagrams 
ORDER BY SchemaName,TableName,IndexName

 

Categories: Handy Scripts Tags:

List all FK references to a given table

November 6, 2013 Leave a comment
declare @tableName varchar(200)
 set @tableName = [TABLE]
 select * from sys.foreign_keys k
 inner join sys.tables t on t.[object_id] = k.referenced_object_id
 where t.[name] = @tableName