February 3, 2011 at 11:38 am
Hello:
Our compnay wants me to
1.) make a back up of the production database ( The database is 150 GB ).
2.) restore the backup in a different server.
3.) Remove data except for 10 customers.
4.) Make a new backup using the database in step #3.
5.) Restore the database (The one that has only 10 customers ) to another server.
I have completed steps 1 through 4.
However the size of the backup obtained in step #4 is still large in size. I expected it
to be about a tenth of the original database or even less.
what am I doing wrong here.
Is there a command to force SQL server to reduce the size of the backup file ?
February 3, 2011 at 11:45 am
The problem is that removing the data from the database does not cause the size of the database to go down. Then when you back it up you are still backing up the full size of the database. After you remove everything but the 10 customer records you will need to shrink the database prior to backing it up. Once you have done this your backup will be a reasonable size.
Regards,
Jason P. Burnett
Senior DBA
February 3, 2011 at 12:05 pm
try to rebuild indexes.
February 3, 2011 at 12:43 pm
How do I shrink the size of the database.
Is there a command that I can use ?
February 3, 2011 at 12:45 pm
RE: Rebuild Indexes ?
Is there one single command that will start to rebuild all the indexes ?
(We have about 2000 tables. Some of the tables have upto 20 indexes. )
February 3, 2011 at 12:53 pm
mw112009 (2/3/2011)
RE: Rebuild Indexes ?Is there one single command that will start to rebuild all the indexes ?
(We have about 2000 tables. Some of the tables have upto 20 indexes. )
try this:
-------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_ALTER_INDEX
--
-- AUTHOR : Tara Kizer
--
-- DATE : February 27, 2007
--
-- INPUTS : @dbName - name of the database
-- @statsMode - LIMITED, SAMPLED or DETAILED
-- @defragType - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)
-- @minFragPercent - minimum fragmentation level
-- @maxFragPercent - maximum fragmentation level
-- @minRowCount - minimum row count
--
-- OUTPUTS : None
--
-- DEPENDENCIES : sys.dm_db_index_physical_stats, sys.objects,
-- sys.schemas, sys.indexes, sys.partitions
--
-- DESCRIPTION : Defragments indexes
/*
EXEC isp_ALTER_INDEX
@dbName = 'DatabaseName',
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000
*/
-- 09/03/2008 - http://weblogs.sqlteam.com/tarad/archive/2008/09/03/Defragmenting-Indexes-in-SQL-Server-2005.aspx
-------------------------------------------------------------------------------------------
ALTER PROC [dbo].[isp_ALTER_INDEX]
(
@dbName sysname,
@statsMode varchar(8) = 'SAMPLED',
@defragType varchar(10) = 'REORGANIZE',
@minFragPercent int = 25,
@maxFragPercent int = 100,
@minRowCount int = 0
)
AS
SET NOCOUNT ON
IF @statsMode NOT IN ('LIMITED', 'SAMPLED', 'DETAILED')
BEGIN
RAISERROR('@statsMode must be LIMITED, SAMPLED or DETAILED', 16, 1)
RETURN
END
IF @defragType NOT IN ('REORGANIZE', 'REBUILD')
BEGIN
RAISERROR('@defragType must be REORGANIZE or REBUILD', 16, 1)
RETURN
END
DECLARE
@i int, @objectId int, @objectName sysname, @indexId int, @indexName sysname,
@schemaName sysname, @partitionNumber int, @partitionCount int,
@sql nvarchar(4000), @edition int, @parmDef nvarchar(500), @allocUnitType nvarchar(60),
@indexType nvarchar(60), @online bit, @disabled bit, @dataType nvarchar(128),
@charMaxLen int, @allowPageLocks bit, @lobData bit
SELECT @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))
SELECT
IDENTITY(int, 1, 1) AS FragIndexId,
[object_id] AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragPercent,
record_count AS RecordCount,
partition_number AS PartitionNumber,
index_type_desc AS IndexType,
alloc_unit_type_desc AS AllocUnitType
INTO #FragIndex
FROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)
WHERE
avg_fragmentation_in_percent > @minFragPercent AND
avg_fragmentation_in_percent < @maxFragPercent AND
index_id > 0
ORDER BY ObjectId
-- LIMITED does not include data for record_count
IF @statsMode IN ('SAMPLED', 'DETAILED')
DELETE FROM #FragIndex
WHERE RecordCount < @minRowCount
SELECT @i = MIN(FragIndexId)
FROM #FragIndex
SELECT
@objectId = ObjectId,
@indexId = IndexId,
@partitionNumber = PartitionNumber,
@indexType = IndexType,
@allocUnitType = AllocUnitType
FROM #FragIndex
WHERE FragIndexId = @i
WHILE @@ROWCOUNT <> 0
BEGIN
SET @sql = '
SELECT @objectName = o.[name], @schemaName = s.[name]
FROM [' + @dbName + '].sys.objects o
JOIN [' + @dbName + '].sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.[object_id] = @objectId'
SET @parmDef = N'@objectId int, @objectName sysname OUTPUT, @schemaName sysname OUTPUT'
EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId,
@objectName = @objectName OUTPUT, @schemaName = @schemaName OUTPUT
SET @sql = '
SELECT @indexName = [name], @disabled = is_disabled, @allowPageLocks = allow_page_locks
FROM [' + @dbName + '].sys.indexes
WHERE [object_id] = @objectId AND index_id = @indexId'
SET @parmDef = N'
@objectId int, @indexId int, @indexName sysname OUTPUT,
@disabled bit OUTPUT, @allowPageLocks bit OUTPUT'
EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@indexName = @indexName OUTPUT, @disabled = @disabled OUTPUT,
@allowPageLocks = @allowPageLocks OUTPUT
IF @indexType = 'CLUSTERED INDEX'
BEGIN
-- CHARACTER_MAXIMUM_LENGTH column will equal -1 for max size or xml
SET @sql = '
SELECT @lobData = 1
FROM [' + @dbName + '].INFORMATION_SCHEMA.COLUMNS c
WHERETABLE_NAME = @objectName AND
(DATA_TYPE IN (''text'', ''ntext'', ''image'') OR
CHARACTER_MAXIMUM_LENGTH = -1)'
SET @parmDef = N'@objectName sysname, @lobData bit OUTPUT'
EXEC sp_executesql
@sql, @parmDef, @objectName = @objectName, @lobData = @lobData OUTPUT
END
SET @sql = '
SELECT @partitionCount = COUNT(*)
FROM [' + @dbName + '].sys.partitions
WHERE [object_id] = @objectId AND index_id = @indexId'
SET @parmDef = N'@objectId int, @indexId int, @partitionCount int OUTPUT'
EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@partitionCount = @partitionCount OUTPUT
-- Developer and Enterprise have the ONLINE = ON option for REBUILD.
-- Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions:
-- disabled indexes, XML indexes, indexes on local temp tables, partitioned indexes,
-- clustered indexes if the underlying table contains LOB data types (text, ntext, image, varchar(max),
-- nvarchar(max), varbinary(max) or xml), and
-- nonclustered indexes that are defined with LOB data type columns.
-- When reoganizing and page locks is disabled for the index, we'll switch to rebuild later on,
-- so we need to get setup with the proper online option.
IF @edition = 3 AND (@defragType = 'REBUILD' OR (@defragType = 'REORGANIZE' AND @allowPageLocks = 0))
BEGIN
SET @online =
CASE
WHEN @indexType = 'XML INDEX' THEN 0
WHEN @indexType = 'NONCLUSTERED INDEX' AND @allocUnitType = 'LOB_DATA' THEN 0
WHEN @lobData = 1 THEN 0
WHEN @disabled = 1 THEN 0
WHEN @partitionCount > 1 THEN 0
ELSE 1
END
END
ELSE
SET @online = 0
SET @sql = 'ALTER INDEX [' + @indexName + '] ON [' + @dbName + '].[' +
@schemaName + '].[' + @objectName + '] ' +
CASE
WHEN @defragType = 'REORGANIZE' AND @allowPageLocks = 0 THEN 'REBUILD'
ELSE @defragType
END
IF @online = 1
SET @sql = @sql + ' WITH (ONLINE = ON)'
IF @partitionCount > 1 AND @disabled = 0 AND @indexType <> 'XML INDEX'
SET @sql = @sql + ' PARTITION = ' + CAST(@partitionNumber AS varchar(10))
PRINT @SQL
--EXEC (@SQL)
SELECT @i = MIN(FragIndexId)
FROM #FragIndex
WHERE FragIndexId > @i
SELECT
@objectId = ObjectId,
@indexId = IndexId,
@partitionNumber = PartitionNumber,
@indexType = IndexType,
@allocUnitType = AllocUnitType
FROM #FragIndex
WHERE FragIndexId = @i
END
February 3, 2011 at 1:23 pm
The .BAK backup file should get smaller as the data in the database gets smaller (free space increases), even if the overall size of the .MDF remains the same.
How much free space do the different databases have ?
How large are the different .LDF files ?
February 3, 2011 at 2:23 pm
Can somebody help me with a command syntax here please ?
I know there is a command where you just specify only a table name and then SQL server
will rebuild all the indexes for that particular table ?
I think the syntax looks like the following:
ALTER INDEX ALL ON patient_encounter REBUILD
Please confirm
February 3, 2011 at 2:34 pm
This is from Books OnLine 'ALTER INDEX ':
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
How will this help your backups ?
February 3, 2011 at 2:51 pm
Good question
See the steps below
1.) I am going to delete most of the data from the tables and have only 10 customers
This will cut the size of data in the database.
2.) When I run the "ALTER INDEX ...... REBUILD" commad on each table that will cut the
size of the index ( Rebuillding an index will have to get rid of data that was deleted )
So this means we made the indexes small.
After completing steps 1 and 2 you can create a new backup file.
So then you can expect the size of the BAK file to be small ?
Am I right ?
February 3, 2011 at 3:07 pm
What recovery model is your database ? How big is the log file ?
What is the result of this ?
USE MyDatabase
GO
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;
February 3, 2011 at 3:11 pm
These are the stats I got from the real prod database ( I did not do any shrinking yet on this one )
NGProdNextGen_System_Data308.5000007.937500
NGProdNextGen_Log 11962.06250011923.859375
NGProdNextGen_Index_1 60492.31250017741.125000
NGProdNextGen_Core_Data_183719.18750012532.687500
February 3, 2011 at 3:18 pm
Let me ask you a question here.
Why are you dividing by 128 ?
I thougt you divide by 1024 to convert to megabytes
( or you can divide it by 1024*1024 to get the answer in gigabytes )
Please explain
February 3, 2011 at 3:30 pm
if your table is a HEAP, the space would not be released automatically.
to release the space, I guess you can create a clustered index, which will re-organise all the data and thus remove all the holes that were generated when you deleted the data. Then if you want to, you can delete the clustered index
February 3, 2011 at 3:39 pm
So your Prod backup should be about 114 G
".. Why are you dividing by 128 ?.."
BOL: size, int, Current size of the file, in 8-KB pages.
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply