|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2011-06-16 : 13:21:25
|
| 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 filegroupUSE [master]GOCREATE DATABASE [testdb] ON PRIMARY ( NAME = N'testdb', FILENAME = N'C:\DATA\testdb.mdf' , SIZE = 307200KB , --300 mbMAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), ( NAME = N'testdb02', FILENAME = N'C:\DATA\testdb02.ndf' , SIZE = 102400KB , --100 mbMAXSIZE = 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 testdbgoSELECT Name, 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--outputName Filename CurrentAllocatedSpace(MB) SpaceUsed(MB) AvailableSpace(MB)testdb C:\DATA\testdb.mdf 300.00 1.31 298.69testdb_log C:\DATA\testdb_log.LDF 0.56 0.29 0.27testdb02 C:\DATA\testdb02.ndf 00.00 0.06 99.94-- created table use testdbgo create table test(c1 char(1024)) -- fills 200 mb data insert into test select 'A' GO 204800 use testdbgoSELECT Name, 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)goName Filename CurrentAllocatedSpace(MB) SpaceUsed(MB) AvailableSpace(MB)testdb C:\DATA\testdb.mdf 300.00 153.75 146.25testdb_log C:\DATA\testdb_log.LDF 0.56 0.22 0.34testdb02 C:\DATA\testdb02.ndf 100.00 76.31 23.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 40mbUSE [testdb]GODBCC SHRINKDATABASE(N'testdb',40)GO/*DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages5 1 32792 288 19672 19672*/-- 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 thishttp://msdn.microsoft.com/en-us/library/ms190488.aspxHow DBCC SHRINKDATABASE WorksDBCC 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 testdbgoSELECT Name, 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 256.19 153.75 102.44testdb_log C:\DATA\testdb_log.LDF 0.56 0.23 0.34testdb02 C:\DATA\testdb02.ndf 100.00 76.31 23.69Thanks in advance. |
|
shrinkdatabase