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

}

Table size

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT *
FROM   #t

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t

DROP TABLE #t

SQL Connectionstring

Reporting, Environmental variables and SQL configurations can all be configured with one easy connectionstring. The string below is the most common, optional items that are often added are shown below.

Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Provider=SQLNCLI10.1;Integrated Security=SSPI;

User Id=USER
Password=Password

Secondly there is a very handy way to build a connectionstring.
1. Create .txt file on your desktop
2. Rename the file to .udl
3. Enter connection details & save
4. Open file with notepad, and there is your connectionstring!