Get all the database and their size from a server

SELECT    DB_NAME(db.database_id) as DatabaseName,
    CAST((CAST(mfrows.RowSize as decimal(18,2)) * 8) /POWER(1024,2) AS DECIMAL(18,2)) as DataFileSizeGB,
    CAST((CAST(mfrows.RowSize as decimal(18,2)) * 8) /POWER(1024,1) AS DECIMAL(18,2)) as DataFileSizeMB,
    CAST((CAST(mflog.LogSize as decimal(18,2)) * 8) /POWER(1024,2) AS DECIMAL(18,2)) as LogFileSizeGB,
    CAST((CAST(mflog.LogSize as decimal(18,2)) * 8) /POWER(1024,1) AS DECIMAL(18,2)) as LogFileSizeMB
FROM sys.databases db
    LEFT JOIN (
SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type
  ) mfrows
  ON mfrows.database_id = db.database_id
    LEFT JOIN (
SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type
 ) mflog
 ON mflog.database_id = db.database_id
ORDER BY DB_NAME(db.database_id)

Filestats IO assessment

In this post I will show how to use sys.dm_io_virtual_file_stats to collect I/O statistics over a sertain time. There are many good information written about this DMV but I have not seen any easy one about how to collect and analyse data from the DMV over time.

I will show one way I often use. The post is written with inspiration from Paul Randals excelent article. but I have done some change to fit my needs.


First creat a table to logg the information.

CREATE TABLE [dbo].[FileStatsIO](
[database_id] [smallint] NOT NULL,
[file_id] [smallint] NOT NULL,
[num_of_reads] [bigint] NOT NULL,
[io_stall_read_ms] [bigint] NOT NULL,
[num_of_writes] [bigint] NOT NULL,
[io_stall_write_ms] [bigint] NOT NULL,
[io_stall] [bigint] NOT NULL,
[num_of_bytes_read] [bigint] NOT NULL,
[num_of_bytes_written] [bigint] NOT NULL,
[file_handle] [varbinary](8) NOT NULL,
[timestamp] [datetime] NULL
) ON [PRIMARY]


Then creat a job which run once every hour. If you want you can of course change the time to suit the timeframe you like to have.

INSERT INTO [dbo].[FileStatsIO]
([database_id]
,[file_id]
,[num_of_reads]
,[io_stall_read_ms]
,[num_of_writes]
,[io_stall_write_ms]
,[io_stall]
,[num_of_bytes_read]
,[num_of_bytes_written]
,[file_handle]
,[Timestamp])
SELECT [database_id], [file_id], [num_of_reads], [io_stall_read_ms],
[num_of_writes], [io_stall_write_ms], [io_stall],
[num_of_bytes_read], [num_of_bytes_written], [file_handle], GETDATE()
FROM sys.dm_io_virtual_file_stats (NULL, NULL);


When you have some data we come to the intressting part. Lets analyse it.

;WITH CTE1 AS
(
SELECT 
a.[timestamp],a.[database_id], a.[file_id], a.[file_handle],
rank()  OVER (PARTITION BY [database_id], [file_id] ORDER BY  [timestamp] ASC) AS Rnk,
a.[num_of_reads] AS [a_num_of_reads],
CASE WHEN [num_of_reads] = 0 THEN 0 
ELSE LAG(num_of_reads, 1) OVER(PARTITION BY [database_id], [file_id]  ORDER BY  [timestamp] ASC) 
END AS [b_num_of_reads],
a.[num_of_bytes_read] AS [a_num_of_bytes_read],
CASE WHEN num_of_bytes_read = 0 THEN 0 
ELSE LAG(num_of_bytes_read, 1) OVER(PARTITION BY [database_id], [file_id]  ORDER BY  [timestamp] ASC) 
END AS [b_num_of_bytes_read],
a.[io_stall_read_ms] AS [a_io_stall_read_ms],
CASE WHEN [io_stall_read_ms] = 0 THEN 0 
ELSE LAG(io_stall_read_ms, 1) OVER(PARTITION BY [database_id], [file_id]  ORDER BY  [timestamp] ASC) 
END AS [b_io_stall_read_ms],
a.[num_of_writes] AS [a_num_of_writes],
CASE WHEN num_of_writes = 0 THEN 0 
ELSE LAG(num_of_writes, 1) OVER(PARTITION BY [database_id], [file_id]  ORDER BY  [timestamp] ASC) 
END AS [b_num_of_writes],
a.[io_stall_write_ms] AS [a_io_stall_write_ms],
CASE WHEN io_stall_write_ms = 0 THEN 0 
ELSE LAG(io_stall_write_ms, 1) OVER(PARTITION BY [database_id], [file_id]  ORDER BY  [timestamp] ASC) 
END AS [b_io_stall_write_ms],
a.[io_stall] AS [a_io_stall],
CASE WHEN io_stall = 0
THEN 0 ELSE LAG(io_stall, 1) OVER(PARTITION BY [database_id], [file_id] ORDER BY  [timestamp] ASC) 
END AS [b_io_stall],
a.[num_of_bytes_written] AS [a_num_of_bytes_written],
CASE WHEN num_of_bytes_written = 0 THEN 0 
ELSE LAG(num_of_bytes_written, 1) OVER(PARTITION BY [database_id], [file_id] ORDER BY  [timestamp] ASC) 
END AS [b_num_of_bytes_written]
FROM FileStatsIO a
--SET FILTER HERER  IF YOU WANT--
where a.database_id = 7
)
SELECT database_id, file_id, [timestamp],
--Read stats--
(a_num_of_reads - b_num_of_reads) as reads,
[TotalByteRead] = ((ISNULL(a_num_of_bytes_read,0) - ISNULL(b_num_of_bytes_read,0))),
[AvgBytePerRead] = ((ISNULL(a_num_of_bytes_read,0) - ISNULL(b_num_of_bytes_read,0))) /
(CASE WHEN ISNULL((a_num_of_reads - b_num_of_reads),1) = 0 THEN 1 
ELSE ISNULL((a_num_of_reads - b_num_of_reads),1) END),
a_io_stall_read_ms,
b_io_stall_read_ms,
[ReadLatency(ms)] =
(ISNULL(a_io_stall_read_ms,0) - ISNULL(b_io_stall_read_ms,0)) /
(CASE WHEN ISNULL((a_num_of_reads - b_num_of_reads),1) = 0
THEN 1 ELSE ISNULL((a_num_of_reads - b_num_of_reads),1) END),
--Write stats--
(a_num_of_writes - b_num_of_writes) as Writes,
[TotalByteWrite] = ((ISNULL(a_num_of_bytes_written,0) - ISNULL(b_num_of_bytes_written,0))),
[AvgBytePerWrite] = ((ISNULL(a_num_of_bytes_written,0) - ISNULL(b_num_of_bytes_written,0))) /
(CASE WHEN ISNULL((a_num_of_writes - b_num_of_writes),1) = 0
THEN 1 ELSE ISNULL((a_num_of_writes - b_num_of_writes),1) END),
a_io_stall_write_ms,
b_io_stall_write_ms,
[WriteLatency(ms)] =
(ISNULL(a_io_stall_write_ms,0) - ISNULL(b_io_stall_write_ms,0)) /
(CASE WHEN ISNULL((a_num_of_writes - b_num_of_writes),1) = 0
THEN 1 ELSE ISNULL((a_num_of_writes - b_num_of_writes),1) END)
FROM CTE1
WHERE rnk > 1

Here are some result. Between the snapshot at 13:00 and 14:00 there has been 184705024 bytes written to datafile nr one in the database im looking at. By compare the a_io_stall_write_ms and b_io_stall_write_ms and divide it by num_of_writes we also can se the average write responsetime during the period. Same goes with reads of course.

How to know if an index is compressed

In management studio you can see if a table is compresed by just chose properties on it. But why is it not the same for index? Something that would be nice as I see it. Anyway, we can use some code to find it out.

SELECT * FROM sys.partitions a
INNER Join sys.indexes b ON b.object_id = a.object_id AND b.index_id = a.index_id
WHERE a.data_compression > 0

SQL serverjob “syspolicy_purge_history” fails with A drive with the name 'D' does not exist.

Going crazy on SQL serverjob “syspolicy_purge_history” I have setup a couple of brand new SQL 2012 clusters with al the laterst servicepack and so on but on all instances we receive the error message below.

Executed as user: DOMAIN\SQLSERVERACCOUNT. A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find drive. A drive with the name 'D' does not exist. '. Process Exit Code -1. The step failed.

After a lot of trouble shooting it seem like the service account for SQL needs to have permission on the root for D:\ I don’t know why, we have SQL binaries on D:\Progrma Files\ Anyway, it´s solved by giving the SQL account read permission to the drive.

Generate a restore script for all databases

This time I will write little about database migration. Not so fancy, often just backup/restore. For the moment I participate to a large migration project and for one SQL instance the customer had nearly 700 databases used for the same system (different kind of tests).
Anyway, they wanted to move them at same time. Not the most exciting thing to do if you do it manually. I checked some existing script to generate a restore script for this but to my surprise I did not found any that worked. After a while I did create one for this purpose. First I made a stored procedure with a inputparameter for the databasename. Then I use a coursor to run in against all databases. This will make a restore script. The only thing I needed to do afterward was to change the path to backup files and datafile destinations. This is simply done with Notepad.

First the stored procedure:

CREATE PROCEDURE GenerateRestoreScript @DatabaseName VARCHAR(100)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @MoveOption AS TABLE (Id INT IDENTITY(1,1), MoveOption VARCHAR(MAX))

PRINT 'RESTORE DATABASE ' + @DatabaseName

PRINT 'FROM DISK = ''' + @DatabaseName + '.bak''' 

PRINT 'WITH'

INSERT INTO @MoveOption ([MoveOption])

SELECT

'MOVE ''' + b.name + ''' TO ''' + b.physical_name + '''' AS [MOVE OPTION]

FROM sys.databases a

INNER JOIN sys.master_files b

ON a.database_id = b.database_id

WHERE a.name = @DatabaseName

DECLARE @LastId INT = 0, @MoveOptionText VARCHAR(MAX)

WHILE EXISTS (SELECT TOP 1 1 FROM @MoveOption WHERE Id > @LastId)

BEGIN

SELECT TOP 1 @MoveOptionText = MoveOption, @LastId = Id

FROM @MoveOption

WHERE Id > @LastId

ORDER BY Id ASC

PRINT CASE WHEN @LastId = 1 THEN '' ELSE ',' END + @MoveOptionText

END

PRINT ', RECOVERY'

PRINT 'GO'

END

GO

Then the coursor to run:

DECLARE @DatabaseName VARCHAR(256)

DECLARE @sql NVARCHAR(256)

DECLARE C1 CURSOR FOR

SELECT name

FROM sys.databases

ORDER BY name

OPEN C1

FETCH NEXT FROM C1 INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

   BEGIN

SELECT @sql = 'exec dbo.GenerateRestoreScript [' + @DatabaseName + ']'

            EXEC sp_executesql @sql

            FETCH NEXT FROM C1 INTO @DatabaseName

   END

CLOSE C1

DEALLOCATE C1

This will generate a quite nice looking restore script. And it also take all the filegroups in the database.

RESTORE DATABASE Test_Db

FROM DISK = 'Test_Db.bak'

WITH

MOVE 'Test_Db' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test_Db.mdf'

,MOVE 'Test_Db_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test_Db_log.ldf'

,MOVE 'Test_Db_SecondaryFG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\Test_Db_FG2.ndf'

,MOVE 'Test_Db_third' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test_Db_third.ndf'

,MOVE 'Test_Db_SecondaryFG2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test_Db_SecondaryFG2.ndf'

, RECOVERY

GO