Shrink inactive database log files

A friend of mine recentrly wrote a script that will step through ALL the NON-Active databases on a SQL Server, Truncate the Log files and then shrink all the database files. In the current state of the script, it will leave NO free space in any of the database’s, but there is a value to change the percentage you would like to have free. I have added comments to the script as well. I ran it on my laptop and it took about 14 seconds to run across 47 databases and freed up about 20GB + of space.

--Creates a table variable and inserts all the non active databases into the the Variable
DECLARE @DatabaseList TABLE (DatabaseID INT IDENTITY(1,1), DatabaseName VARCHAR(50))
INSERT INTO @DatabaseList
SELECT Name FROM sys.Databases
WHERE STATE = 0

--Stores the SQL String to execute in the Loop
DECLARE @SQL VARCHAR(255)

--Holds the Count of Databases to Step through
DECLARE @DatabaseCount INT
SET @DatabaseCount = (SELECT MAX(DatabaseID) FROM @DatabaseList)

PRINT 'Databse Count: ' + CAST(@DatabaseCount AS VARCHAR(4))

--WHILE Loop
WHILE @DatabaseCount <> 0
BEGIN
IF @@ERROR = 0
BEGIN
--Builds the truncation query for the database
SET @SQL = (SELECT 'BACKUP LOG ' + DatabaseName + ' WITH TRUNCATE_ONLY'
FROM @DatabaseList
WHERE DatabaseID = @DatabaseCount)
--Executes the Query
EXEC (@SQL)
--Shows the Status
PRINT @SQL + ' : HAS BEEN EXECUTED SUCCESSFULLY'

--Prepares for the next database
SET @DatabaseCount = @DatabaseCount - 1
END
ELSE
--RETURNS ERROR MESSAGE
RAISERROR ('Either the state of a database has change while you were executing this statement or a DEADLOCK has occured.',16,1)
END

--Creates a Cursor (Please note, this statement should not be run often that is why i created a cursor.
--Cursor's should be avoided if possible. You can't execute a DBCC statement that returns results within
--a WHILE statement that is why i used a cursor here.
DECLARE @DatabaseName VARCHAR(50)
DECLARE @DatabaseCursorCount INT
DECLARE @DBCC CURSOR
SET @DBCC = CURSOR FOR
SELECT Name
FROM sys.databases
WHERE STATE = 0
OPEN @DBCC

FETCH NEXT FROM @DBCC
INTO @DatabaseName

WHILE (@@FETCH_Status = 0)
BEGIN
SET @DatabaseCursorCount = COUNT(@DatabaseName)
WHILE @DatabaseCursorCount <> 0
BEGIN
--Shrinks the database, the ( 0 ) in the parameter list is the amount of free space that
-- should be left in the database after the shrink. Change it to 10 if you want 10% free afterwards
DBCC SHRINKDATABASE (@DatabaseName,0,TRUNCATEONLY)
PRINT @DatabaseName
SET @DatabaseCursorCount = @DatabaseCursorCount - 1
END
SET @DatabaseCursorCount = @DatabaseCursorCount - 1
FETCH NEXT FROM @DBCC
INTO @DatabaseName
END

CLOSE @DBCC
DEALLOCATE @DBCC

Dynamic MetaData Reader

Hi All

Hot on the heels of the dynamic info matrix, here is script to extract dynamic metadata... Same deal - run and copy and paste into excel...

if object_Id('tempdb..#Output') is not null
drop table #output
go
Set Nocount on
go
declare @DataWarehouse varchar(200) -- the name of the DW DB
,@Dimension_Convention varchar(20)
,@Fact_Convention varchar(20)
,@SurrogateKey_Convention varchar(20)

select @DataWarehouse = 'SOME_DW'
,@Dimension_Convention = '%_dim'
,@Fact_Convention = '%_fact'
,@SurrogateKey_Convention = '%_id'



Create table #Output
(
RowID int identity (1,1)
,TableType varchar(max)
,TableName varchar(max)
,ColumnName varchar(max)
,KeyColumn varchar(max)
,ExampleQuery varchar(max)
,Example varchar(max) default ''
,[Description] varchar(max)
,DataType varchar(200)
)


declare @intRow int
,@intRows int
,@Query varchar(max)

select @Query = '
Insert into #output
(
TableType
,TableName
,ColumnName
,KeyColumn
,DataType
)
select case
when SO.Name like ' +char(39) + @Dimension_Convention + char(39) + '
then ' + char(39) + 'Dimension' + char(39) + '

When SO.Name like ' +char(39) + @Dimension_Convention + char(39) + '
then ' + char(39) + 'Fact' + char(39) + '
end as TableType
,SO.Name as TableName
,SC.Name as Columnname
,SC_Key.Name as Columnname
,ST.name + case
when SC.TypeStat < 2
then ' + char(39) + char(39) + '
else ' + char(39) + ' (' + char(39) + '+ convert(varchar(20),SC.Length) + ' + char(39) + ')' + char(39) +
'end as Suffix
from ['+ @DataWarehouse + ']..SysObjects SO
join [' + @DataWarehouse + ']..SysColumns SC
on SO.ID = SC.ID
join sys.Types ST
on SC.xType = ST.system_type_ID
left outer join ['+ @DataWarehouse + ']..SysColumns SC_KEY
on SO.ID = SC_KEY.ID
and SC_Key.TypeStat = 1
Where SO.XTYPE = ' + char(39) + 'U' + char(39) + '
and (SO.Name like ' + char(39) + @Fact_Convention + char(39) + ' or SO.Name like ' + char(39) +@Dimension_Convention + char(39) + ')'

exec (@Query)

Update #Output
set ExampleQuery = 'Select top 1 ['
+ ColumnName
+ '] from ['
+ @DataWarehouse + ']..' + '['
+ TableName
+ '] where ['
+ KeyColumn
+ '] = (select max(['
+ KeyColumn
+ ']) From ['
+ @DataWarehouse + ']..' + '['
+ TableName
+ '])'


Select @intRow = min (RowID)
,@intRows = max (RowID)
from #Output

while @intRow <= @intRows
begin
Select @Query = 'Update #Output set Example=('
+ ExampleQuery
+ ') where RowID = '
+ convert(varchar(max),@intRow)
from #Output
Where RowID = @intRow

print @Query
exec (@Query)

Select @intRow = min (RowID)
,@intRows = max (RowID)
from #Output
where RowID > @intRow
end

/* Update the description logic as needed */
Update #Output
set [Description] = case
When ColumnName = KeyColumn
then 'The primary key on the ' + TableName + ' table'

When ColumnName like @SurrogateKey_Convention
Then 'A surrogate key referenced to ' + replace(ColumnName, @SurrogateKey_Convention, '')

When ColumnName like '%datasource%'
Then 'Indicates where the data came from'

When ColumnName like '%tmstamp%'
then 'The creation date and time of the transaction'

When Datatype = 'datetime'
then 'Indicates the ' + replace(replace(columnName,'date',''),'Date','') + ' date'

when right(Upper(Columnname),2) = 'NO'
Then 'The ' + Left(ColumnName,len(ColumnName)-2) + ' number'

when right(Upper(Columnname),5) = 'DESCR'
Then 'Describes the ' + left(ColumnName,len(columnName)-5)


end

select TableType
,Tablename
,ColumnName
,DataType
,isnull(Example, 'N/A') as Example
,[Description]
--,*
from #Output
Order by 1,2,3,4,5

Dynamic Info Matrix

Hi

I recently had to do an Info matrix for a DWH with no referential integrity - all that they had were neatly named tables and key columns (but no foreign keys). There were 45 Facts and 49 dimensions. Out of sheer laziness I decided to rather create it using code than manually go and fill in the ticks. so here's the code to do it (output it to text using SSMS, and paste into your spreadsheet tool of choice, split the text data and voila !!!)

if Object_ID ('tempdb..#IM') is not null
drop table #IM
if Object_ID ('tempdb..#MasterList') is not null
drop table #MasterList

if Object_ID ('tempdb..#Dim') is not null
drop table #Dim
Go
Set Nocount on
go

declare @FactTable_Convention varchar(200) -- How are fact tables named
,@DimSK_Convention varchar(200) -- How are dim_sk's indicated
,@Warehouse_DB varchar(200) -- what is the warehouse name
,@IM_UsageIndicator varchar(20) -- what value is used to indicate that
-- a table uses a dim

Select @FactTable_Convention = '%_fact'
,@DimSK_Convention = '%_id'
,@Warehouse_DB = 'Some_DWH'
,@IM_UsageIndicator = 'a' --Webdings makes this a tick mark

-- Internal variables
Declare @intRow int -- tracks the current row
,@intRows int -- tracks the number of rows
,@Query varchar(max)



Create table #MasterList
(
RowID int identity (1,1)
,FactName varchar(200)
,SKName varchar(200)
)


Select @Query = 'Insert into #MasterList (FactName, SKname)'
+ ' Select SO.Name, case when SC.Name like '
+ char(39) + '%Date%' + char(39)
+ 'then ' + char(39) + 'Date dimension' +char(39)
+ ' else SC.Name end '
+ 'from '
+ @WareHouse_DB
+ '..SysObjects SO join '
+ + @WareHouse_DB
+ '..sysColumns SC on SO.ID = SC.ID where SO.XType = '
+ char(39) + 'U' + char(39)
+ ' and SO.Name like '
+ char(39)
+ @FactTable_Convention
+ Char(39)
+ ' and (SC.Name like '
+ char(39)
+ replace(@DimSK_Convention,',',' or SC.Name like ')
+ Char(39) + ') and SC.TypeStat=0' --TypeStat indicates ID columns

--Print @Query
Exec (@Query)



Create table #IM
(
RowID int identity (1,1)
,FactName varchar(200)
,Fact varchar(200)
)

Insert into #IM (FactName,Fact)
Select distinct FactName,
Replace(FactName,replace(@FactTable_Convention,'%',''),'')
from #MasterList


Create table #Dim
(
DimID int identity (1,1)
,DimKeyName varchar(200)
,Dimension varchar(200)

)

Insert into #Dim
(
DimKeyName
,Dimension
)
Select distinct case
when SKName like '%Date%'
then 'Date dimension'
else SKName
end,
case
when Replace(SkName,replace(@DimSK_Convention,'%',''),'')like '%Date%'
then 'Date dimension'
else Replace(SkName,replace(@DimSK_Convention,'%',''),'')
end
from #MasterList

Select @intRow = min(DimID)
,@intRows = max(DimID)
from #Dim

While @intRow <= @intRows --iterate Dim columns
begin

select @Query = 'Alter table #IM add ['
+ Dimension
+ '] varchar(5)'
from #dim
Where DimID = @intRow

--Print @Query
exec (@Query)

-- now check in which tables it occurs
Select @Query = 'Update #IM set ['
+ Dimension
+ '] = '
+ char(39)
+ @IM_UsageIndicator
+ char(39)
+ ' from #IM IM join (select FactName from #masterlist where SKName= '
+ char(39) + DimKeyName + char(39)
+ ') List on IM.FactName = List.FactName'
from #Dim
Where DimID = @intRow

--Print @Query
exec (@Query)

Select @Query = 'Update #IM set ['
+ Dimension
+ '] = '
+ char(39) + char(39)
+ ' Where ['
+ Dimension
+ '] is null'
from #Dim
Where DimID = @intRow

--Print @Query
exec (@Query)

Select @intRow = min(DimID)
from #Dim
Where DimID > @intRow

End

Alter table #IM
drop Column RowID


Alter table #IM
drop Column FactName

select *
from #IM

----Print courtesy of http://my-pc-hates-me.blogspot.com

Importing an image into an image column using SQL 2005

Hi

Ever had to import an image into a column ? Here is a simple Insert statement...

Insert MyLogoTable (Logo)
Select BulkColumn
from Openrowset( Bulk 'c:\SomeJPEGFile.jpg', Single_Blob) as Logo


Cheers