June 16, 2011 at 11:20 am
Hi,
I have 2 queries/questions on DBCC SHRINKDATABASE and how it works internally.
I am trying to visualize what sql is trying to do but finding some difficulty.
Can anyone help me out on this regard?
--create a test database of 400 mb size with 2 data files in PRIMARY filegroup
USE [master]
GO
CREATE DATABASE [testdb] ON PRIMARY
( NAME = N'testdb',
FILENAME = N'C:\DATA\testdb.mdf' ,
SIZE = 307200KB , --300 mb
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB ),
( NAME = N'testdb02',
FILENAME = N'C:\DATA\testdb02.ndf' ,
SIZE = 102400KB , --100 mb
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testdb_log',
FILENAME = N'C:\DATA\testdb_log.LDF' ,
SIZE = 576KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
--query to check the free space
use testdb
go
SELECTName, Filename,
CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],
CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],
CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]
FROM dbo.sysfiles a (NOLOCK)
go
--output
Name Filename CurrentAllocatedSpace(MB) SpaceUsed(MB) AvailableSpace(MB)
testdb C:\DATA\testdb.mdf 300.00 1.31 298.69
testdb_log C:\DATA\testdb_log.LDF0.56 0.29 0.27
testdb02 C:\DATA\testdb02.ndf00.00 0.06 99.94
-- created table
use testdb
go
create table test
(c1 char(1024)
)
-- fills 200 mb data
insert into test
select 'A'
GO 204800
use testdb
go
SELECTName, Filename,
CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],
CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],
CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]
FROM dbo.sysfiles a (NOLOCK)
go
Name Filename CurrentAllocatedSpace(MB) SpaceUsed(MB) AvailableSpace(MB)
testdb C:\DATA\testdb.mdf300.00153.75146.25
testdb_log C:\DATA\testdb_log.LDF0.560.220.34
testdb02 C:\DATA\testdb02.ndf100.00 76.3123.69
-- First question, why it dont insert data in the first data file "C:\DATA\testdb.mdf" and why it is storing some data in "C:\DATA\testdb02.ndf" ?
-- shrik database to 40mb
USE [testdb]
GO
DBCC SHRINKDATABASE(N'testdb',40)
GO
/*
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
51327922881967219672
*/
-- Secondly question, why it is shriking the space from only file id "1" i.e mdf and why not .ndf??
-- I can use, dbcc shrinfile and specify the target sizein MB. Basically,i want to understand the behaviour of
-- DBCC SHRINKDATABASE. Also, in msdn documentation read the below and want to know better on this
http://msdn.microsoft.com/en-us/library/ms190488.aspx
How DBCC SHRINKDATABASE Works
DBCC SHRINKDATABASE shrinks data files on a per-file basis, but shrinks log files as if all the log files existed in one contiguous log pool. Files are always shrunk from the end.
MSDN says per-file basis, but i dont observe shrinking is taking place per file. It has picked up fileid "1" only.
use testdb
go
SELECTName, Filename,
CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],
CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],
CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]
FROM dbo.sysfiles a (NOLOCK)
go
--output
Name Filename CurrentAllocatedSpace(MB) SpaceUsed(MB) AvailableSpace(MB)
testdb C:\DATA\testdb.mdf256.19 153.75 102.44
testdb_log C:\DATA\testdb_log.LDF0.56 0.23 0.34
testdb02 C:\DATA\testdb02.ndf100.0076.31 23.69
Thanks in advance.
June 16, 2011 at 11:29 am
small correction target % is 40% and not 40M.
June 16, 2011 at 11:48 am
Read this blog series by Brent Ozar, he does a good job of explaining fragmentation: http://www.brentozar.com/archive/2009/02/index-fragmentation-findings-part-1-the-basics/ [/url]
http://www.brentozar.com/archive/2009/02/index-fragmentation-findings-part-2-size-matters/[/url]
Also check out this article by Paul Randal: http://technet.microsoft.com/en-us/magazine/2009.06.sqlqa.aspx
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
June 16, 2011 at 11:51 am
I'm not sure you have enough data in there to more accurately show, but basically you have one filegroup, so the data is split among the files. If you insert a significant %, then you should see a proportional amount in each file.
As far as Shrinkdatabase, it can be funny. I prefer SHRINKFILE if you need to lower the size of a file. The references Jorge posted are good ones.
June 16, 2011 at 11:55 am
Also, because I include this picture in my presentations... http://twitpic.com/1gdrwv
Please think of the kittens...
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
June 17, 2011 at 4:39 am
Thanks Steve.
I can go with shrinkfile but i was trying an attempt to understand the behavior and whats written in MSDN,
Can we make any changes and observe the behaviour what we expected to see that shrink is happening against all files.
Also, right now am not looking for fragementation.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply