Size and free space calculation for each DB

SELECT name
INTO #temp
FROM sys.databases where database_id > 4
DECLARE @SQL varchar(4000)
DECLARE @DB varchar(100)


DECLARE DBSIZE CURSOR
FOR (SELECT * from #temp)


OPEN DBSIZE

FETCH NEXT FROM DBSIZE
INTO @db

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL =
'
USE ' + @db + '
INSERT INTO master..temp2
select
      name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a
'
EXEC (@SQL)

FETCH NEXT FROM DBSIZE INTO @db
END
CLOSE DBSIZE;
DEALLOCATE DBSIZE;



DROP TABLE #temp

Excel 2007 files in SSIS, connectivity problem

Download: http://www.microsoft.com/en-us/download/details.aspx?id=23734

Formatting values

Easy to use function based on a character and value parameter. The formatting codes can be used in SSRS with FORMAT(value,FORMAT)


CREATE FUNCTION [dbo].[ValueFormat]
(
 @value float
,@Character varchar(1)
)
RETURNS varchar(12)
AS
BEGIN

DECLARE @valueFormat varchar(12)
SET @valueformat =  (SELECT CASE
 WHEN (@value) < -100000000000 and @Character = '#' THEN '#,##0.00M'
 WHEN (@value) < -1000000000 and @Character = '#'  THEN '#,##0.00M'
 WHEN (@value) < -10000000 and @Character = '#'  THEN '#,##0.00M'
 WHEN (@value) < -1000000 and @Character = '#'  THEN '#,##K'
 WHEN (@value) < -10000 and @Character = '#'  THEN '#,##K'
 WHEN (@value) < -1000 and @Character = '#'  THEN '#,##K'
 WHEN (@value) < -1 and @Character = '#'  THEN '#,##0.00'
   
                      WHEN (@value) < 1 and @Character = '#'  THEN '#,##0.00'
 WHEN (@value) < 10000 and @Character = '#'  THEN '#,##K'
 WHEN (@value) < 1000000 and @Character = '#'  THEN '#,##K'
 WHEN (@value) < 10000000 and @Character = '#'  THEN '#,##0.00M'
 WHEN (@value) < 1000000000 and @Character = '#'  THEN '#,##0.00M'
 WHEN (@value) < 100000000000 and @Character = '#'  THEN '#,##0.00M'

 WHEN (@value) < 0 and @Character = '%' THEN '0.##%'
 WHEN (@value) < 1 and @Character = '%' THEN '0.##%'
 end)
RETURN @valueFormat

Table activity

Last table action (history is from the last restoredate)


WITH LastActivity (ObjectID, LastAction) AS
(
  SELECT object_id AS TableName,
         last_user_seek as LastAction
    FROM sys.dm_db_index_usage_stats u
   WHERE database_id = db_id(db_name())
   UNION
  SELECT object_id AS TableName,
         last_user_scan as LastAction
    FROM sys.dm_db_index_usage_stats u
   WHERE database_id = db_id(db_name())
   UNION
  SELECT object_id AS TableName,
         last_user_lookup as LastAction
    FROM sys.dm_db_index_usage_stats u
   WHERE database_id = db_id(db_name())
)

SELECT
      r.TableName
      ,r.LastSelect
FROM
(
  SELECT OBJECT_NAME(so.object_id) AS TableName,
         MAX(la.LastAction) as LastSelect
    FROM sys.objects so
    LEFT
    JOIN LastActivity la
      ON so.object_id = la.ObjectID
   WHERE so.type = 'U'
      GROUP BY OBJECT_NAME(so.object_id)
) AS r

ORDER BY r.TableName

Performance tuning, check index defragmentation

Check defragmentation of index

SELECT ps.database_id, ps.OBJECT_ID,

ps.index_id, b.name,

ps.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID

AND ps.index_id = b.index_id

WHERE ps.database_id = DB_ID()

ORDER BY ps.OBJECT_ID

GO

Rebuild with fillfactor 

DECLARE @TableName VARCHAR(255)

DECLARE @sql NVARCHAR(500)

DECLARE @fillfactor INT

SET @fillfactor = 95

DECLARE TableCursor CURSOR FOR

SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName

FROM sys.tables

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

EXEC (@sql)

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

GO

Performancetuning, time statistics

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

SET STATISTICS TIME ON

Reporting integrated in SharePoint, dealing with different dropdown sizes

In the directory  C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\ReportServer\styles\1033

add the following code to:  sqlrvdefault.css

SELECT {

    font-size: 8pt;

    font-family: Verdana;

    width:200px

}