Archive for March, 2017

How to quickly shrink/remove a database file

March 10, 2017

How to quickly shrink/remove a database file

Quickly is a relative term and this could mean turning a 2 week task into a 2 day task.

Normally what you would do if you wanted to remove a datafile is clear it of data and the remove it. You would use the DBCC SHRINKFILE (LogicalFileName, EMPTYFILE) command to do this.

You might also instead of shrinking a file, delete it and then recreate it at the desired size

Depending on the size of the file this could take days, maybe even weeks. I have witnessed the empty file moving data at a rate of 1MB a minute so if your file is several TB it will take a very long time.

However there are things we can do to speed it up

First of all put your file into read only mode you can do this using DBCC SHRINKFILE (LogicalFileName, EMPTYFILE). From books online “Emptyfile assures you that no new data will be added to the file.” which is exactly what we want

We now want to move all the tables and indexes off the file and onto other files in the filegroup we do this by rebuilding all the heaps and indexes. Use the following code to generate scripts

select concat(‘exec (”alter table ‘,quotename(d.name),’.’,quotename(object_name(a.object_id)), ‘ rebuild”)’,’
GO’) a ,reserved_page_count ,b.index_id
from sys.indexes a with(NOLOCK)
inner join sys.dm_db_partition_stats b with(NOLOCK) on a.object_id = b.object_id
inner join sys.objects c with(NOLOCK) on c.object_id = a.object_id
inner join sys.schemas d with(NOLOCK) on d.schema_id = c.schema_id
where a.type_desc = ‘Heap’ and data_space_id = 1 and a.index_id = b.index_id and c.type = ‘U’
UNION ALL
select DISTINCT concat(‘exec(”alter INDEX ALL ON ‘,quotename(d.name),’.’,quotename(object_name(a.object_id)), ‘ rebuild”)’,’
GO’) a ,reserved_page_count ,1
from sys.indexes a with(NOLOCK)
inner join sys.dm_db_partition_stats b with(NOLOCK) on a.object_id = b.object_id
inner join sys.objects c with(NOLOCK) on c.object_id = a.object_id
inner join sys.schemas d with(NOLOCK) on d.schema_id = c.schema_id
where data_space_id = 1 and a.index_id = b.index_id and c.type = ‘U’
ORDER BY 2 DESC
Before running this code you will need to disable any columnstore indexes on your tables
select concat(‘ alter index ‘, a.name , ‘ on ‘,c.name,’.’,b.name , ‘ disable’) from sys.indexes a
inner join sys.objects b on a.object_id = b.object_id
inner join sys.schemas c on b.schema_id = c.schema_id
where a.type > 5

When this has completed you can check the amount of space left using the following code

IF (SELECT OBJECT_ID(‘tempdb..#a’)) IS NOT NULL DROP TABLE #a
IF (SELECT OBJECT_ID(‘tempdb..#b’)) IS NOT NULL DROP TABLE #b
IF (SELECT OBJECT_ID(‘tempdb..#c’)) IS NOT NULL DROP TABLE #c
SELECT DB_NAME(a.database_id) db_name ,
a.name ,
CAST(( 8.0 * size ) / 1024.0 AS DEC(10, 2)) size ,
growth ,
max_size ,
is_percent_growth ,
‘USE ‘ + QUOTENAME(DB_NAME(a.database_id))
+ ‘;SELECT (8.0*FILEPROPERTY(”’ + a.name
+ ”’,”SpaceUsed”))/1024.0,”’ + DB_NAME(a.database_id) + ”’,”’
+ a.name + ”” get_file_size
INTO #a
FROM sys.master_files a
INNER JOIN sys.databases b ON b.database_id = a.database_id
WHERE b.state_desc = ‘ONLINE’ AND b.name !=’tempdb’ –master files will not give accurate data for temp log;
AND b.user_access_desc = ‘MULTI_USER’
UNION ALL
SELECT ‘tempdb’ db_name ,
a.name ,
CAST(( 8.0 * size ) / 1024.0 AS DEC(10, 2)) size ,
growth ,
max_size ,
is_percent_growth ,
‘USE [tempdb]’
+ ‘;SELECT (8.0*FILEPROPERTY(”’ + a.name
+ ”’,”SpaceUsed”))/1024.0,”’ + ‘tempdb”,”’
+ a.name + ”” get_file_size
FROM tempdb.sys.database_files a
SELECT get_file_size INTO #b FROM #a

WHILE EXISTS (SELECT 1 FROM #b)
BEGIN
DECLARE @sql VARCHAR(MAX) = (SELECT TOP 1 get_file_size FROM #b ORDER BY 1)
DECLARE @a TABLE (size DEC(10,2),name_db VARCHAR(100),name_file VARCHAR(100))

INSERT INTO @a
EXEC(@Sql)

DELETE FROM #b WHERE get_file_size = @sql
END

SELECT name_db,name_file,a.size,a.size-b.size AS free_space,CAST(100*(a.size-b.size)/a.size AS DEC(10,2)) free_space_percentage,a.growth,a.is_percent_growth,GETDATE() collection_time
INTO #c
FROM #a a
INNER JOIN @a b ON a.name = b.name_file AND a.db_name = b.name_db
SELECT * FROM #c

If there is still a significant amount of space being used in the file you can check what it by using the following

select top 1 * from sys.dm_db_database_page_allocations(7,NULL,NULL,NULL,’limited’) where allocated_page_file_id = 8 and object_id > 100
Where 7 is the database id, I use top 1 because I find the code can take more than 24 to return a complete data set

you can then rebuild the heap and tables to shift the data off the file

Advertisements

Formatting HTML for send_dbmail proc

March 2, 2017

taken from: http://stackoverflow.com/questions/7070053/convert-a-sql-query-result-table-to-an-html-table-for-email

Procedure

-- Description: Turns a query into a formatted HTML table. Useful for emails. 
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable] 
(
  @query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
  @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
  @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN   
  SET NOCOUNT ON;

  IF @orderBy IS NULL BEGIN
    SET @orderBy = ''  
  END

  SET @orderBy = REPLACE(@orderBy, '''', '''''');

  DECLARE @realQuery nvarchar(MAX) = '
    DECLARE @headerRow nvarchar(MAX);
    DECLARE @cols nvarchar(MAX);    

    SELECT * INTO #dynSql FROM (' + @query + ') sub;

    SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    

    EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT

    SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>'' 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';

    SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';    
    ';

  EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END
GO

Usage

DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = N'SELECT * FROM dbo.People', @orderBy = N'ORDER BY FirstName';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Foo',
    @recipients = 'bar@baz.com;',
    @subject = 'HTML email',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;