DBfile usage: files

Showing posts with label files. Show all posts

Thursday, March 29, 2012

dbcc shrinkfile

I have a DB with two transaction log files. I'd like have
one of them removed.
After running the following set of commands successfully...
backup log db_tdadatamart with truncate_only
dbcc shrinkfile ('datamartLog', EMPTYFILE)
...when I try to remove the second file using this
command...
alter database db_datamart
remove file datamartLog
...I get this error:
The file 'datamartLog' cannot be removed because it is not
empty.
Thanks in advance for your help.Run DBCC OPENTRAN to ensure there are no open transactions. And you might
want to make sure you have adone a log backup after that command as well.
--
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> I have a DB with two transaction log files. I'd like have
> one of them removed.
> After running the following set of commands successfully...
> backup log db_tdadatamart with truncate_only
> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> ...when I try to remove the second file using this
> command...
> alter database db_datamart
> remove file datamartLog
> ...I get this error:
> The file 'datamartLog' cannot be removed because it is not
> empty.
> Thanks in advance for your help.|||'No active open transactions' were reported... still
encoutner the same problem.
Thanks.
>--Original Message--
>Run DBCC OPENTRAN to ensure there are no open
transactions. And you might
>want to make sure you have adone a log backup after that
command as well.
>--
>Andrew J. Kelly SQL MVP
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
>> I have a DB with two transaction log files. I'd like
have
>> one of them removed.
>> After running the following set of commands
successfully...
>> backup log db_tdadatamart with truncate_only
>> dbcc shrinkfile ('datamartLog', EMPTYFILE)
>> ...when I try to remove the second file using this
>> command...
>> alter database db_datamart
>> remove file datamartLog
>> ...I get this error:
>> The file 'datamartLog' cannot be removed because it is
not
>> empty.
>> Thanks in advance for your help.
>
>.
>|||Did you do a Log backup? Any chance this is related?
http://support.microsoft.com/default.aspx?scid=kb;en-us;324432&Product=sql2k
--
Andrew J. Kelly SQL MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> >--Original Message--
> >Run DBCC OPENTRAN to ensure there are no open
> transactions. And you might
> >want to make sure you have adone a log backup after that
> command as well.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> >> I have a DB with two transaction log files. I'd like
> have
> >> one of them removed.
> >>
> >> After running the following set of commands
> successfully...
> >>
> >> backup log db_tdadatamart with truncate_only
> >> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> >>
> >> ...when I try to remove the second file using this
> >> command...
> >>
> >> alter database db_datamart
> >> remove file datamartLog
> >>
> >> ...I get this error:
> >>
> >> The file 'datamartLog' cannot be removed because it is
> not
> >> empty.
> >>
> >> Thanks in advance for your help.
> >
> >
> >.
> >|||I have some information about DBCC LOGINFO etc on my article regarding shrinking of database files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1d78101c45470$65a7c2a0$a601280a@.phx.gbl...
> 'No active open transactions' were reported... still
> encoutner the same problem.
> Thanks.
> >--Original Message--
> >Run DBCC OPENTRAN to ensure there are no open
> transactions. And you might
> >want to make sure you have adone a log backup after that
> command as well.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1df5a01c4546d$9c7f3f90$a001280a@.phx.gbl...
> >> I have a DB with two transaction log files. I'd like
> have
> >> one of them removed.
> >>
> >> After running the following set of commands
> successfully...
> >>
> >> backup log db_tdadatamart with truncate_only
> >> dbcc shrinkfile ('datamartLog', EMPTYFILE)
> >>
> >> ...when I try to remove the second file using this
> >> command...
> >>
> >> alter database db_datamart
> >> remove file datamartLog
> >>
> >> ...I get this error:
> >>
> >> The file 'datamartLog' cannot be removed because it is
> not
> >> empty.
> >>
> >> Thanks in advance for your help.
> >
> >
> >.
> >sql

dbcc shrinkfile

DBCC shrinkfile

would DBCC shrinkfile cause any blocking or any hit to an OLTP environment
while its running. Ive got a lot of extra space on some data files that I
want to shrink and was wondering if its safe to do it during our peak
hours... What does it do internally ? Any locking ,etc..Using SQL 2000Yes. It issues a lot of IO and takes short term X page locks. In internal
tests we've seen up to 20% drop in transaction throughput, depending on the
exact workload and hardware configuration. This is unavoidable due to the
operations shrink has to perform.
What proportion of the database size is free-space? Consider not doing the
shrink unless you're really desperate for the disk space or you *know* the
database size won't grow again. If you shrink, the odds are that the
database will have to grow again anyway. As always, depends on your exact
workload etc etc
It is always 'safe' to do a shrink.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uBcDfwnFEHA.2876@.TK2MSFTNGP09.phx.gbl...
> would DBCC shrinkfile cause any blocking or any hit to an OLTP environment
> while its running. Ive got a lot of extra space on some data files that I
> want to shrink and was wondering if its safe to do it during our peak
> hours... What does it do internally ? Any locking ,etc..Using SQL 2000
>

Monday, March 19, 2012

DBCC LOGINFO

I am trying to get my log files to reduce in size, but when I run DBCC
LOGINFO (MPlus) I get 491 rows with status =2 so when I go to shrink the
logfile after a backup (BACKUP LOG MPlus WITH TRUNCATE_ONLY) I get "Ensure
the Log Reader Agent is running or use sp_repldone to mark transactions as
distributed."
I would asume that when the synch runs (once an hour for me, merge) it
should update these rows as replicated, but I still have 491 no matter what.
I dont have a log reader agent, and I am hesitant to go and write off these
rows using sp_repldone.
Any suggestions?
Duncan
Duncan,
it looks as though at some time you have set up transactional replication on
articles from this database. If it was still there, then running the log
reader agent would sort things out, then you could backup and shrink. As the
publication is no longer there, then running sp_repldone will mark the
transactions as read with the same effect. This won't affect your merge
replication, as it uses a different mechanism to log the changes, namely
triggers.
Regards,
Paul Ibison
|||Can you run this in your publishing database.
sp_replicationdboption 'DatabaseName','sync with backup','false'
See if you still get this error message.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hilary: I got "The replication option 'sync with backup' of database 'Mplus'
has been set to false." What did this do? Will it help the db and backups
shrink again?
Paul, We have never had Transactional on it, not as far as I can remember. I
do have a snapshot replication running on the DB as well (as merge) to keep
a warm backup server going. Would this be why DBCC LOGINFO is returning all
these rows that have status=2?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:24DAD586-A5DF-4ECF-8834-50F8E1532E53@.microsoft.com...
> Can you run this in your publishing database.
> sp_replicationdboption 'DatabaseName','sync with backup','false'
> See if you still get this error message.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||Hilary, I ran this then BACKUP LOG MPlus WITH TRUNCATE_ONLY and still got
the same error.
What did this command do exactly?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:24DAD586-A5DF-4ECF-8834-50F8E1532E53@.microsoft.com...
> Can you run this in your publishing database.
> sp_replicationdboption 'DatabaseName','sync with backup','false'
> See if you still get this error message.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>

Sunday, March 11, 2012

DBCC IndexDefrag Or DBCC REINDEX

Hi ,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
after process ?
What is the properly method after perform IndexDefrag or REINDEX ?
Currently , I shrink the log , restart the server and perform either index
derag command. It is the right procedure to go ?
Thanks ,
TravisHI,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files after
process ?
NO
I did see any valid reason to restart the server, what I would do is
backup the database before indexing
reindex the db
backup transaction log
shrink log file
Regards
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||DBCC INDEXDEFRAG and DBREINDEX operate at page level in your database files,
the first reorganising the leaf level of your indexes (and effectively
freeing some page but NOT releasing them) and the second dropping and
rebuilding one or more indexes. Anyway those two statements will never shrink
your database or part of it.
About the transaction log shrinking, i suppose you are strongly searching
for stporage space, otherwise you are spending a lot of administrative effort
for poor results: what you are shrinking now, will grow (automatically or
manually) in the next few hours.
Gilberto Zampatti
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||In addition to the other posts, related to shrink, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||Hi Travis
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
You don't say what the recovery model is, but I would assume it is full or
bulk logged?
If your system can handle the maximum size that the transaction log reaches
then it is probably better not to shrink the file see
http://www.karaszi.com/sqlserver/info_dont_shrink.asp and the links if
references.
If you are running the defragmentation for all indexes look at the example E
for DBCC SHOWCONTIG in books online or at
http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx and reduce the
amount of work and growth of the log file size.
I am not sure why you wish to stop/start the server?
John

Thursday, March 8, 2012

dbcc emptyfile

Hi,
(SQL 2K)
We used to have a database with 1 100Gb data file
I've now created 5 25Gb files and ran the DBCC EmptyFile command to move all
the data out of the primary file to the new ones.
Enterprise Mgr reports the primary file is 99% empty - excellent.
Problem is, it wont shrink from being 100Gb in size - no amout of DBCC
ShrinkFile, backing up logs, full backups etc will shrink it.
Can anyone suggest what I might be missing?
Thanks
NickHi
Can you post your DBCC SHRINKFILE command and the output from sp_spaceused,
sp_helpfile.
John
"Nick" wrote:
> Hi,
> (SQL 2K)
> We used to have a database with 1 100Gb data file
> I've now created 5 25Gb files and ran the DBCC EmptyFile command to move all
> the data out of the primary file to the new ones.
> Enterprise Mgr reports the primary file is 99% empty - excellent.
> Problem is, it wont shrink from being 100Gb in size - no amout of DBCC
> ShrinkFile, backing up logs, full backups etc will shrink it.
> Can anyone suggest what I might be missing?
> Thanks
> Nick
>
>|||John,
sp_spaceused:
MyDB 290343.88 MB 69625.83 MB
Reserved Data Index Unused
155007152 KB 92248808 KB 5123888 KB 57634456 KB
sp_helpfile:
name fileid filename filegroup size maxsize growth usage
Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
Unlimited 153600 KB data only
Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL 14592448 KB
Unlimited 10% log only
Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB Unlimited
10% log only
MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB Unlimited 10%
data only
MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB Unlimited
10% data only
MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB Unlimited
10% data only
MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB Unlimited
10% data only
Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB Unlimited
10% log only
Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB Unlimited
10% log only
I'm trying to shrink Merlin_Data - the primary file - by running:
DBCC SHRINKFILE (N'Merlin_Data' , 200)
and I've also tried:
DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
Any pointers really appreciated!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
> Hi
> Can you post your DBCC SHRINKFILE command and the output from
> sp_spaceused,
> sp_helpfile.
> John
> "Nick" wrote:
>> Hi,
>> (SQL 2K)
>> We used to have a database with 1 100Gb data file
>> I've now created 5 25Gb files and ran the DBCC EmptyFile command to move
>> all
>> the data out of the primary file to the new ones.
>> Enterprise Mgr reports the primary file is 99% empty - excellent.
>> Problem is, it wont shrink from being 100Gb in size - no amout of DBCC
>> ShrinkFile, backing up logs, full backups etc will shrink it.
>> Can anyone suggest what I might be missing?
>> Thanks
>> Nick
>>|||Hi
The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB) so your
shrinkfile should have worked!
Try
DBCC SHRINKFILE ( 1, 200 )
or
DBCC SHRINKFILE ( 1 )
John
"Nick" wrote:
> John,
> sp_spaceused:
> MyDB 290343.88 MB 69625.83 MB
> Reserved Data Index Unused
> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
> sp_helpfile:
> name fileid filename filegroup size maxsize growth usage
> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
> Unlimited 153600 KB data only
> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL 14592448 KB
> Unlimited 10% log only
> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB Unlimited
> 10% log only
> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB Unlimited 10%
> data only
> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB Unlimited
> 10% data only
> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB Unlimited
> 10% data only
> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB Unlimited
> 10% data only
> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB Unlimited
> 10% log only
> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB Unlimited
> 10% log only
> I'm trying to shrink Merlin_Data - the primary file - by running:
> DBCC SHRINKFILE (N'Merlin_Data' , 200)
> and I've also tried:
> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
> Any pointers really appreciated!
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
> > Hi
> >
> > Can you post your DBCC SHRINKFILE command and the output from
> > sp_spaceused,
> > sp_helpfile.
> >
> > John
> >
> > "Nick" wrote:
> >
> >> Hi,
> >>
> >> (SQL 2K)
> >>
> >> We used to have a database with 1 100Gb data file
> >>
> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command to move
> >> all
> >> the data out of the primary file to the new ones.
> >>
> >> Enterprise Mgr reports the primary file is 99% empty - excellent.
> >>
> >> Problem is, it wont shrink from being 100Gb in size - no amout of DBCC
> >> ShrinkFile, backing up logs, full backups etc will shrink it.
> >>
> >> Can anyone suggest what I might be missing?
> >>
> >> Thanks
> >>
> >> Nick
> >>
> >>
> >>
>
>|||Thats what I thought.
Even in enterprise manager, it says "Min file size 150mb" so I try shrinking
it to 200Mb and it just doesn't budge. Not an inch.
Geeze, it's frustrating as I need this file as small as possible as I've
moved all the data out of it.
Is there any way of seeing if SQL is even attempting to shrink the file? The
DBCC command returns very quickly (< 1sec) so I guess it's an off-line
process.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F2199DEB-6438-467B-B909-DBB935583740@.microsoft.com...
> Hi
> The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB) so
> your
> shrinkfile should have worked!
> Try
> DBCC SHRINKFILE ( 1, 200 )
> or
> DBCC SHRINKFILE ( 1 )
> John
> "Nick" wrote:
>> John,
>> sp_spaceused:
>> MyDB 290343.88 MB 69625.83 MB
>> Reserved Data Index Unused
>> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
>> sp_helpfile:
>> name fileid filename filegroup size maxsize growth usage
>> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
>> Unlimited 153600 KB data only
>> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL 14592448 KB
>> Unlimited 10% log only
>> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB
>> Unlimited
>> 10% log only
>> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB Unlimited
>> 10%
>> data only
>> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB
>> Unlimited
>> 10% data only
>> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB
>> Unlimited
>> 10% data only
>> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB
>> Unlimited
>> 10% data only
>> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB
>> Unlimited
>> 10% log only
>> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB
>> Unlimited
>> 10% log only
>> I'm trying to shrink Merlin_Data - the primary file - by running:
>> DBCC SHRINKFILE (N'Merlin_Data' , 200)
>> and I've also tried:
>> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
>> Any pointers really appreciated!
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
>> > Hi
>> >
>> > Can you post your DBCC SHRINKFILE command and the output from
>> > sp_spaceused,
>> > sp_helpfile.
>> >
>> > John
>> >
>> > "Nick" wrote:
>> >
>> >> Hi,
>> >>
>> >> (SQL 2K)
>> >>
>> >> We used to have a database with 1 100Gb data file
>> >>
>> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command to
>> >> move
>> >> all
>> >> the data out of the primary file to the new ones.
>> >>
>> >> Enterprise Mgr reports the primary file is 99% empty - excellent.
>> >>
>> >> Problem is, it wont shrink from being 100Gb in size - no amout of DBCC
>> >> ShrinkFile, backing up logs, full backups etc will shrink it.
>> >>
>> >> Can anyone suggest what I might be missing?
>> >>
>> >> Thanks
>> >>
>> >> Nick
>> >>
>> >>
>> >>
>>|||Hi Nick
If the command was working it should take some time, you may want to try a
larger size say 94000. What size is model?
John
"Nick" wrote:
> Thats what I thought.
> Even in enterprise manager, it says "Min file size 150mb" so I try shrinking
> it to 200Mb and it just doesn't budge. Not an inch.
> Geeze, it's frustrating as I need this file as small as possible as I've
> moved all the data out of it.
> Is there any way of seeing if SQL is even attempting to shrink the file? The
> DBCC command returns very quickly (< 1sec) so I guess it's an off-line
> process.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:F2199DEB-6438-467B-B909-DBB935583740@.microsoft.com...
> > Hi
> >
> > The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB) so
> > your
> > shrinkfile should have worked!
> >
> > Try
> >
> > DBCC SHRINKFILE ( 1, 200 )
> > or
> > DBCC SHRINKFILE ( 1 )
> >
> > John
> >
> > "Nick" wrote:
> >
> >> John,
> >>
> >> sp_spaceused:
> >> MyDB 290343.88 MB 69625.83 MB
> >>
> >> Reserved Data Index Unused
> >> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
> >>
> >> sp_helpfile:
> >>
> >> name fileid filename filegroup size maxsize growth usage
> >>
> >> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
> >> Unlimited 153600 KB data only
> >>
> >> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL 14592448 KB
> >> Unlimited 10% log only
> >>
> >> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB
> >> Unlimited
> >> 10% log only
> >>
> >> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB Unlimited
> >> 10%
> >> data only
> >>
> >> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB
> >> Unlimited
> >> 10% data only
> >>
> >> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB
> >> Unlimited
> >> 10% data only
> >>
> >> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB
> >> Unlimited
> >> 10% data only
> >>
> >> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB
> >> Unlimited
> >> 10% log only
> >>
> >> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB
> >> Unlimited
> >> 10% log only
> >>
> >> I'm trying to shrink Merlin_Data - the primary file - by running:
> >>
> >> DBCC SHRINKFILE (N'Merlin_Data' , 200)
> >> and I've also tried:
> >> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
> >>
> >> Any pointers really appreciated!
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
> >> > Hi
> >> >
> >> > Can you post your DBCC SHRINKFILE command and the output from
> >> > sp_spaceused,
> >> > sp_helpfile.
> >> >
> >> > John
> >> >
> >> > "Nick" wrote:
> >> >
> >> >> Hi,
> >> >>
> >> >> (SQL 2K)
> >> >>
> >> >> We used to have a database with 1 100Gb data file
> >> >>
> >> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command to
> >> >> move
> >> >> all
> >> >> the data out of the primary file to the new ones.
> >> >>
> >> >> Enterprise Mgr reports the primary file is 99% empty - excellent.
> >> >>
> >> >> Problem is, it wont shrink from being 100Gb in size - no amout of DBCC
> >> >> ShrinkFile, backing up logs, full backups etc will shrink it.
> >> >>
> >> >> Can anyone suggest what I might be missing?
> >> >>
> >> >> Thanks
> >> >>
> >> >> Nick
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Model - what a good idea! SQL can't shrink a database smaller than that can
it?
With much excitement, I check model's size... 1.5Mb!
Damm!!!
DBCC SHRINKFILE (N'Merlin_Data' , 94000) also comes back instantly, less
than 1 sec.
Logged in as sa.
File permissions maybe'
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FE88B764-03DC-4AD0-A6D7-5264410E1F91@.microsoft.com...
> Hi Nick
> If the command was working it should take some time, you may want to try a
> larger size say 94000. What size is model?
> John
> "Nick" wrote:
>> Thats what I thought.
>> Even in enterprise manager, it says "Min file size 150mb" so I try
>> shrinking
>> it to 200Mb and it just doesn't budge. Not an inch.
>> Geeze, it's frustrating as I need this file as small as possible as I've
>> moved all the data out of it.
>> Is there any way of seeing if SQL is even attempting to shrink the file?
>> The
>> DBCC command returns very quickly (< 1sec) so I guess it's an off-line
>> process.
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:F2199DEB-6438-467B-B909-DBB935583740@.microsoft.com...
>> > Hi
>> >
>> > The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB) so
>> > your
>> > shrinkfile should have worked!
>> >
>> > Try
>> >
>> > DBCC SHRINKFILE ( 1, 200 )
>> > or
>> > DBCC SHRINKFILE ( 1 )
>> >
>> > John
>> >
>> > "Nick" wrote:
>> >
>> >> John,
>> >>
>> >> sp_spaceused:
>> >> MyDB 290343.88 MB 69625.83 MB
>> >>
>> >> Reserved Data Index Unused
>> >> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
>> >>
>> >> sp_helpfile:
>> >>
>> >> name fileid filename filegroup size maxsize growth usage
>> >>
>> >> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
>> >> Unlimited 153600 KB data only
>> >>
>> >> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL 14592448
>> >> KB
>> >> Unlimited 10% log only
>> >>
>> >> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB
>> >> Unlimited
>> >> 10% log only
>> >>
>> >> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB
>> >> Unlimited
>> >> 10%
>> >> data only
>> >>
>> >> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB
>> >> Unlimited
>> >> 10% data only
>> >>
>> >> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB
>> >> Unlimited
>> >> 10% data only
>> >>
>> >> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB
>> >> Unlimited
>> >> 10% data only
>> >>
>> >> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB
>> >> Unlimited
>> >> 10% log only
>> >>
>> >> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB
>> >> Unlimited
>> >> 10% log only
>> >>
>> >> I'm trying to shrink Merlin_Data - the primary file - by running:
>> >>
>> >> DBCC SHRINKFILE (N'Merlin_Data' , 200)
>> >> and I've also tried:
>> >> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
>> >>
>> >> Any pointers really appreciated!
>> >>
>> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> >> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
>> >> > Hi
>> >> >
>> >> > Can you post your DBCC SHRINKFILE command and the output from
>> >> > sp_spaceused,
>> >> > sp_helpfile.
>> >> >
>> >> > John
>> >> >
>> >> > "Nick" wrote:
>> >> >
>> >> >> Hi,
>> >> >>
>> >> >> (SQL 2K)
>> >> >>
>> >> >> We used to have a database with 1 100Gb data file
>> >> >>
>> >> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command to
>> >> >> move
>> >> >> all
>> >> >> the data out of the primary file to the new ones.
>> >> >>
>> >> >> Enterprise Mgr reports the primary file is 99% empty - excellent.
>> >> >>
>> >> >> Problem is, it wont shrink from being 100Gb in size - no amout of
>> >> >> DBCC
>> >> >> ShrinkFile, backing up logs, full backups etc will shrink it.
>> >> >>
>> >> >> Can anyone suggest what I might be missing?
>> >> >>
>> >> >> Thanks
>> >> >>
>> >> >> Nick
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Hi Nick
I presume that you did a sp_helpfile again after the DBCC SHRINKFILE?
I doubt it is file permissions are a problem.
Have you tried DBCC SHRINKDATABASE?
John
"Nick" wrote:
> Model - what a good idea! SQL can't shrink a database smaller than that can
> it?
> With much excitement, I check model's size... 1.5Mb!
> Damm!!!
> DBCC SHRINKFILE (N'Merlin_Data' , 94000) also comes back instantly, less
> than 1 sec.
> Logged in as sa.
> File permissions maybe'
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FE88B764-03DC-4AD0-A6D7-5264410E1F91@.microsoft.com...
> > Hi Nick
> >
> > If the command was working it should take some time, you may want to try a
> > larger size say 94000. What size is model?
> >
> > John
> >
> > "Nick" wrote:
> >
> >> Thats what I thought.
> >>
> >> Even in enterprise manager, it says "Min file size 150mb" so I try
> >> shrinking
> >> it to 200Mb and it just doesn't budge. Not an inch.
> >>
> >> Geeze, it's frustrating as I need this file as small as possible as I've
> >> moved all the data out of it.
> >>
> >> Is there any way of seeing if SQL is even attempting to shrink the file?
> >> The
> >> DBCC command returns very quickly (< 1sec) so I guess it's an off-line
> >> process.
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:F2199DEB-6438-467B-B909-DBB935583740@.microsoft.com...
> >> > Hi
> >> >
> >> > The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB) so
> >> > your
> >> > shrinkfile should have worked!
> >> >
> >> > Try
> >> >
> >> > DBCC SHRINKFILE ( 1, 200 )
> >> > or
> >> > DBCC SHRINKFILE ( 1 )
> >> >
> >> > John
> >> >
> >> > "Nick" wrote:
> >> >
> >> >> John,
> >> >>
> >> >> sp_spaceused:
> >> >> MyDB 290343.88 MB 69625.83 MB
> >> >>
> >> >> Reserved Data Index Unused
> >> >> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
> >> >>
> >> >> sp_helpfile:
> >> >>
> >> >> name fileid filename filegroup size maxsize growth usage
> >> >>
> >> >> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
> >> >> Unlimited 153600 KB data only
> >> >>
> >> >> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL 14592448
> >> >> KB
> >> >> Unlimited 10% log only
> >> >>
> >> >> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB
> >> >> Unlimited
> >> >> 10% log only
> >> >>
> >> >> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB
> >> >> Unlimited
> >> >> 10%
> >> >> data only
> >> >>
> >> >> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB
> >> >> Unlimited
> >> >> 10% data only
> >> >>
> >> >> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB
> >> >> Unlimited
> >> >> 10% data only
> >> >>
> >> >> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB
> >> >> Unlimited
> >> >> 10% data only
> >> >>
> >> >> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB
> >> >> Unlimited
> >> >> 10% log only
> >> >>
> >> >> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB
> >> >> Unlimited
> >> >> 10% log only
> >> >>
> >> >> I'm trying to shrink Merlin_Data - the primary file - by running:
> >> >>
> >> >> DBCC SHRINKFILE (N'Merlin_Data' , 200)
> >> >> and I've also tried:
> >> >> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
> >> >>
> >> >> Any pointers really appreciated!
> >> >>
> >> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> >> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
> >> >> > Hi
> >> >> >
> >> >> > Can you post your DBCC SHRINKFILE command and the output from
> >> >> > sp_spaceused,
> >> >> > sp_helpfile.
> >> >> >
> >> >> > John
> >> >> >
> >> >> > "Nick" wrote:
> >> >> >
> >> >> >> Hi,
> >> >> >>
> >> >> >> (SQL 2K)
> >> >> >>
> >> >> >> We used to have a database with 1 100Gb data file
> >> >> >>
> >> >> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command to
> >> >> >> move
> >> >> >> all
> >> >> >> the data out of the primary file to the new ones.
> >> >> >>
> >> >> >> Enterprise Mgr reports the primary file is 99% empty - excellent.
> >> >> >>
> >> >> >> Problem is, it wont shrink from being 100Gb in size - no amout of
> >> >> >> DBCC
> >> >> >> ShrinkFile, backing up logs, full backups etc will shrink it.
> >> >> >>
> >> >> >> Can anyone suggest what I might be missing?
> >> >> >>
> >> >> >> Thanks
> >> >> >>
> >> >> >> Nick
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Well, it took a while but I've sussed it.
It turns out, that my log files were on a Windows mount-point which should
be supported on SQL2K & Windows Server 2003 Enterprise non-clustered.
I thought maybe SQL Server needed some more log space for the data file
shrink, but, whenever I tried expanding the logs SQL told me "File not
found". Very weird. So, I detached the db then moved the logs to a normal
drive and re-attached it. I could then increase the log sizes and the data
file shrank straight away.
Maybe SQL Server was unable to claim log-space for the shrink but didn't
report the error correctly as it was getting confused with the mount-points?
I'm guessing, but it works at last and many thanks for your assistance.
Nick
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:285E26C2-FF94-4539-B890-088B681AC999@.microsoft.com...
> Hi Nick
> I presume that you did a sp_helpfile again after the DBCC SHRINKFILE?
> I doubt it is file permissions are a problem.
> Have you tried DBCC SHRINKDATABASE?
> John
> "Nick" wrote:
>> Model - what a good idea! SQL can't shrink a database smaller than that
>> can
>> it?
>> With much excitement, I check model's size... 1.5Mb!
>> Damm!!!
>> DBCC SHRINKFILE (N'Merlin_Data' , 94000) also comes back instantly, less
>> than 1 sec.
>> Logged in as sa.
>> File permissions maybe'
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:FE88B764-03DC-4AD0-A6D7-5264410E1F91@.microsoft.com...
>> > Hi Nick
>> >
>> > If the command was working it should take some time, you may want to
>> > try a
>> > larger size say 94000. What size is model?
>> >
>> > John
>> >
>> > "Nick" wrote:
>> >
>> >> Thats what I thought.
>> >>
>> >> Even in enterprise manager, it says "Min file size 150mb" so I try
>> >> shrinking
>> >> it to 200Mb and it just doesn't budge. Not an inch.
>> >>
>> >> Geeze, it's frustrating as I need this file as small as possible as
>> >> I've
>> >> moved all the data out of it.
>> >>
>> >> Is there any way of seeing if SQL is even attempting to shrink the
>> >> file?
>> >> The
>> >> DBCC command returns very quickly (< 1sec) so I guess it's an off-line
>> >> process.
>> >>
>> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> >> news:F2199DEB-6438-467B-B909-DBB935583740@.microsoft.com...
>> >> > Hi
>> >> >
>> >> > The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB)
>> >> > so
>> >> > your
>> >> > shrinkfile should have worked!
>> >> >
>> >> > Try
>> >> >
>> >> > DBCC SHRINKFILE ( 1, 200 )
>> >> > or
>> >> > DBCC SHRINKFILE ( 1 )
>> >> >
>> >> > John
>> >> >
>> >> > "Nick" wrote:
>> >> >
>> >> >> John,
>> >> >>
>> >> >> sp_spaceused:
>> >> >> MyDB 290343.88 MB 69625.83 MB
>> >> >>
>> >> >> Reserved Data Index Unused
>> >> >> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
>> >> >>
>> >> >> sp_helpfile:
>> >> >>
>> >> >> name fileid filename filegroup size maxsize growth usage
>> >> >>
>> >> >> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
>> >> >> Unlimited 153600 KB data only
>> >> >>
>> >> >> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL
>> >> >> 14592448
>> >> >> KB
>> >> >> Unlimited 10% log only
>> >> >>
>> >> >> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB
>> >> >> Unlimited
>> >> >> 10% log only
>> >> >>
>> >> >> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB
>> >> >> Unlimited
>> >> >> 10%
>> >> >> data only
>> >> >>
>> >> >> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB
>> >> >> Unlimited
>> >> >> 10% data only
>> >> >>
>> >> >> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB
>> >> >> Unlimited
>> >> >> 10% data only
>> >> >>
>> >> >> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB
>> >> >> Unlimited
>> >> >> 10% data only
>> >> >>
>> >> >> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB
>> >> >> Unlimited
>> >> >> 10% log only
>> >> >>
>> >> >> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB
>> >> >> Unlimited
>> >> >> 10% log only
>> >> >>
>> >> >> I'm trying to shrink Merlin_Data - the primary file - by running:
>> >> >>
>> >> >> DBCC SHRINKFILE (N'Merlin_Data' , 200)
>> >> >> and I've also tried:
>> >> >> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
>> >> >>
>> >> >> Any pointers really appreciated!
>> >> >>
>> >> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> >> >> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
>> >> >> > Hi
>> >> >> >
>> >> >> > Can you post your DBCC SHRINKFILE command and the output from
>> >> >> > sp_spaceused,
>> >> >> > sp_helpfile.
>> >> >> >
>> >> >> > John
>> >> >> >
>> >> >> > "Nick" wrote:
>> >> >> >
>> >> >> >> Hi,
>> >> >> >>
>> >> >> >> (SQL 2K)
>> >> >> >>
>> >> >> >> We used to have a database with 1 100Gb data file
>> >> >> >>
>> >> >> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command
>> >> >> >> to
>> >> >> >> move
>> >> >> >> all
>> >> >> >> the data out of the primary file to the new ones.
>> >> >> >>
>> >> >> >> Enterprise Mgr reports the primary file is 99% empty -
>> >> >> >> excellent.
>> >> >> >>
>> >> >> >> Problem is, it wont shrink from being 100Gb in size - no amout
>> >> >> >> of
>> >> >> >> DBCC
>> >> >> >> ShrinkFile, backing up logs, full backups etc will shrink it.
>> >> >> >>
>> >> >> >> Can anyone suggest what I might be missing?
>> >> >> >>
>> >> >> >> Thanks
>> >> >> >>
>> >> >> >> Nick
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Hi Nick
I hae certainly had problems with mount points in the past so gave up using
them, they didn't allow database files larger than the host disc size to be
created. I don't know if that is still a problem though!!
John
"Nick" wrote:
> Well, it took a while but I've sussed it.
> It turns out, that my log files were on a Windows mount-point which should
> be supported on SQL2K & Windows Server 2003 Enterprise non-clustered.
> I thought maybe SQL Server needed some more log space for the data file
> shrink, but, whenever I tried expanding the logs SQL told me "File not
> found". Very weird. So, I detached the db then moved the logs to a normal
> drive and re-attached it. I could then increase the log sizes and the data
> file shrank straight away.
> Maybe SQL Server was unable to claim log-space for the shrink but didn't
> report the error correctly as it was getting confused with the mount-points?
> I'm guessing, but it works at last and many thanks for your assistance.
> Nick
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:285E26C2-FF94-4539-B890-088B681AC999@.microsoft.com...
> > Hi Nick
> >
> > I presume that you did a sp_helpfile again after the DBCC SHRINKFILE?
> >
> > I doubt it is file permissions are a problem.
> >
> > Have you tried DBCC SHRINKDATABASE?
> >
> > John
> >
> > "Nick" wrote:
> >
> >> Model - what a good idea! SQL can't shrink a database smaller than that
> >> can
> >> it?
> >> With much excitement, I check model's size... 1.5Mb!
> >> Damm!!!
> >> DBCC SHRINKFILE (N'Merlin_Data' , 94000) also comes back instantly, less
> >> than 1 sec.
> >>
> >> Logged in as sa.
> >>
> >> File permissions maybe'
> >>
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:FE88B764-03DC-4AD0-A6D7-5264410E1F91@.microsoft.com...
> >> > Hi Nick
> >> >
> >> > If the command was working it should take some time, you may want to
> >> > try a
> >> > larger size say 94000. What size is model?
> >> >
> >> > John
> >> >
> >> > "Nick" wrote:
> >> >
> >> >> Thats what I thought.
> >> >>
> >> >> Even in enterprise manager, it says "Min file size 150mb" so I try
> >> >> shrinking
> >> >> it to 200Mb and it just doesn't budge. Not an inch.
> >> >>
> >> >> Geeze, it's frustrating as I need this file as small as possible as
> >> >> I've
> >> >> moved all the data out of it.
> >> >>
> >> >> Is there any way of seeing if SQL is even attempting to shrink the
> >> >> file?
> >> >> The
> >> >> DBCC command returns very quickly (< 1sec) so I guess it's an off-line
> >> >> process.
> >> >>
> >> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> >> news:F2199DEB-6438-467B-B909-DBB935583740@.microsoft.com...
> >> >> > Hi
> >> >> >
> >> >> > The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB)
> >> >> > so
> >> >> > your
> >> >> > shrinkfile should have worked!
> >> >> >
> >> >> > Try
> >> >> >
> >> >> > DBCC SHRINKFILE ( 1, 200 )
> >> >> > or
> >> >> > DBCC SHRINKFILE ( 1 )
> >> >> >
> >> >> > John
> >> >> >
> >> >> > "Nick" wrote:
> >> >> >
> >> >> >> John,
> >> >> >>
> >> >> >> sp_spaceused:
> >> >> >> MyDB 290343.88 MB 69625.83 MB
> >> >> >>
> >> >> >> Reserved Data Index Unused
> >> >> >> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
> >> >> >>
> >> >> >> sp_helpfile:
> >> >> >>
> >> >> >> name fileid filename filegroup size maxsize growth usage
> >> >> >>
> >> >> >> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
> >> >> >> Unlimited 153600 KB data only
> >> >> >>
> >> >> >> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL
> >> >> >> 14592448
> >> >> >> KB
> >> >> >> Unlimited 10% log only
> >> >> >>
> >> >> >> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB
> >> >> >> Unlimited
> >> >> >> 10% log only
> >> >> >>
> >> >> >> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB
> >> >> >> Unlimited
> >> >> >> 10%
> >> >> >> data only
> >> >> >>
> >> >> >> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB
> >> >> >> Unlimited
> >> >> >> 10% data only
> >> >> >>
> >> >> >> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB
> >> >> >> Unlimited
> >> >> >> 10% data only
> >> >> >>
> >> >> >> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB
> >> >> >> Unlimited
> >> >> >> 10% data only
> >> >> >>
> >> >> >> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB
> >> >> >> Unlimited
> >> >> >> 10% log only
> >> >> >>
> >> >> >> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB
> >> >> >> Unlimited
> >> >> >> 10% log only
> >> >> >>
> >> >> >> I'm trying to shrink Merlin_Data - the primary file - by running:
> >> >> >>
> >> >> >> DBCC SHRINKFILE (N'Merlin_Data' , 200)
> >> >> >> and I've also tried:
> >> >> >> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
> >> >> >>
> >> >> >> Any pointers really appreciated!
> >> >> >>
> >> >> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> >> >> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
> >> >> >> > Hi
> >> >> >> >
> >> >> >> > Can you post your DBCC SHRINKFILE command and the output from
> >> >> >> > sp_spaceused,
> >> >> >> > sp_helpfile.
> >> >> >> >
> >> >> >> > John
> >> >> >> >
> >> >> >> > "Nick" wrote:
> >> >> >> >
> >> >> >> >> Hi,
> >> >> >> >>
> >> >> >> >> (SQL 2K)
> >> >> >> >>
> >> >> >> >> We used to have a database with 1 100Gb data file
> >> >> >> >>
> >> >> >> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command
> >> >> >> >> to
> >> >> >> >> move
> >> >> >> >> all
> >> >> >> >> the data out of the primary file to the new ones.
> >> >> >> >>
> >> >> >> >> Enterprise Mgr reports the primary file is 99% empty -
> >> >> >> >> excellent.
> >> >> >> >>
> >> >> >> >> Problem is, it wont shrink from being 100Gb in size - no amout
> >> >> >> >> of
> >> >> >> >> DBCC
> >> >> >> >> ShrinkFile, backing up logs, full backups etc will shrink it.
> >> >> >> >>
> >> >> >> >> Can anyone suggest what I might be missing?
> >> >> >> >>
> >> >> >> >> Thanks
> >> >> >> >>
> >> >> >> >> Nick
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Wednesday, March 7, 2012

DBCC DBReindex follwed by DBCC ShrinkFile

We have a client who is running DBCC DBReindex (all tables) followed by DBCC
ShrinkFile on the log and data files with AutoShrink on their database
turned on.
We have already advised them to turn autoshrink off.
In our tests running a show contig after each operation the index
optimization is erased by running the ShrinkFile operation with many indexes
showing 100% fragmentation.
Does anyone know of documentation which reflects this circumstance?
I haven't seen any documentation per se, saying that shrink causes fragmentation. But if you read about what
shrink does and how it work, it is clear that you get fragmented data after the shrink. I recommend below two
articles:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gene Black" <geblack@.cox.net> wrote in message news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
> We have a client who is running DBCC DBReindex (all tables) followed by DBCC
> ShrinkFile on the log and data files with AutoShrink on their database
> turned on.
> We have already advised them to turn autoshrink off.
> In our tests running a show contig after each operation the index
> optimization is erased by running the ShrinkFile operation with many indexes
> showing 100% fragmentation.
> Does anyone know of documentation which reflects this circumstance?
>
>
|||It looks like shrinkdatabase does not have the same implications (this is
what the built in maintenance plan uses as viewed through profiler)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3YE8oigEHA.2916@.TK2MSFTNGP12.phx.gbl...
> I haven't seen any documentation per se, saying that shrink causes
fragmentation. But if you read about what
> shrink does and how it work, it is clear that you get fragmented data
after the shrink. I recommend below two
> articles:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gene Black" <geblack@.cox.net> wrote in message
news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
DBCC[vbcol=seagreen]
indexes
>
|||Shrinkdatabase does the same thing as shrinkfile, but it targets all the files for the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gene Black" <geblack@.hotmail.com> wrote in message news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
> It looks like shrinkdatabase does not have the same implications (this is
> what the built in maintenance plan uses as viewed through profiler)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:e3YE8oigEHA.2916@.TK2MSFTNGP12.phx.gbl...
> fragmentation. But if you read about what
> after the shrink. I recommend below two
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
> DBCC
> indexes
>
|||Evidently it doesn't since the results of showcontig don't reflect
fragmentation after the dbcc shrinkdatabase
I did
1. sp_msforeachtable 'dbcc dbreindex(''?'')'
dbcc showcontig for all tables
ADBForecasts-2 0%
ADBProjects-1 0%
ADBProjects-3 0%
ADBReference-2 0%
ADBTasks-1 0%
ADBTasks-3 14%
ADBTasks-4 0%
ADBTasks-5 0%
ADBUsers-2 0%
ADBUsers-3 0%
ADBUsers-4 0%
ADBViews-2 0%
ADBWorkSheets-2 0%
ADBWorkSheetTasks-2 0%
ADBWorkSheetTasks-3 0%
ADBWorkSheetTasks-4 0%
ADBWorkSheetTasks-5 0%
ADBWorkSheetTasks-19 0%
2. DBCC shrinkfile (2,5) then showcontig
results:
ADBForecasts-2 98%
ADBProjects-1 0%
ADBProjects-3 0%
ADBReference-2 0%
ADBTasks-1 98%
ADBTasks-3 0%
ADBTasks-4 0%
ADBTasks-5 0%
ADBUsers-2 0%
ADBUsers-3 0%
ADBUsers-4 0%
ADBViews-2 0%
ADBWorkSheets-2 0%
ADBWorkSheetTasks-2 98%
ADBWorkSheetTasks-3 92%
ADBWorkSheetTasks-4 96%
ADBWorkSheetTasks-5 96%
ADBWorkSheetTasks-19 98%
3. sp_msforeachtable 'dbcc dbreindex(''?'')' then showcontig
ADBForecasts-2 0%
ADBProjects-1 0%
ADBProjects-3 0%
ADBReference-2 0%
ADBTasks-1 0%
ADBTasks-3 14%
ADBTasks-4 0%
ADBTasks-5 0%
ADBUsers-2 0%
ADBUsers-3 0%
ADBUsers-4 0%
ADBViews-2 0%
ADBWorkSheets-2 0%
ADBWorkSheetTasks-2 0%
ADBWorkSheetTasks-3 0%
ADBWorkSheetTasks-4 0%
ADBWorkSheetTasks-5 0%
ADBWorkSheetTasks-19 0%
4. dbcc shrinkdatabase then showcontig
ADBForecasts-2 0%
ADBProjects-1 0%
ADBProjects-3 0%
ADBReference-2 0%
ADBTasks-1 0%
ADBTasks-3 14%
ADBTasks-4 0%
ADBTasks-5 0%
ADBUsers-2 0%
ADBUsers-3 0%
ADBUsers-4 0%
ADBViews-2 0%
ADBWorkSheets-2 0%
ADBWorkSheetTasks-2 0%
ADBWorkSheetTasks-3 0%
ADBWorkSheetTasks-4 0%
ADBWorkSheetTasks-5 0%
ADBWorkSheetTasks-19 0%
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OQR6P%23igEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Shrinkdatabase does the same thing as shrinkfile, but it targets all the
files for the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gene Black" <geblack@.hotmail.com> wrote in message
news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
is[vbcol=seagreen]
in[vbcol=seagreen]
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
by[vbcol=seagreen]
database
>
|||Note:
this database had autoshrink off
the shinkdatbase command was
DBCC shrinkdatabase(N'AutoADB', 10, TRUNCATEONLY )
could it be the truncateonly option which is causing the difference?
"Gene Black" <geblack@.hotmail.com> wrote in message
news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
> Evidently it doesn't since the results of showcontig don't reflect
> fragmentation after the dbcc shrinkdatabase
> I did
> 1. sp_msforeachtable 'dbcc dbreindex(''?'')'
> dbcc showcontig for all tables
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
> 2. DBCC shrinkfile (2,5) then showcontig
> results:
> ADBForecasts-2 98%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 98%
> ADBTasks-3 0%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 98%
> ADBWorkSheetTasks-3 92%
> ADBWorkSheetTasks-4 96%
> ADBWorkSheetTasks-5 96%
> ADBWorkSheetTasks-19 98%
> 3. sp_msforeachtable 'dbcc dbreindex(''?'')' then showcontig
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
>
> 4. dbcc shrinkdatabase then showcontig
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:OQR6P%23igEHA.4092@.TK2MSFTNGP10.phx.gbl...
> files for the database.
> news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
> is
wrote[vbcol=seagreen]
> in
data
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
followed[vbcol=seagreen]
> by
> database
many[vbcol=seagreen]
circumstance?
>
|||It looks like the difference is the TruncateOnly option
TRUNCATEONLY
Causes any unused space in the files to be released to the operating system
and shrinks the file to the last allocated extent, reducing the file size
without moving any data. No attempt is made to relocate rows to unallocated
pages. target_size is ignored when TRUNCATEONLY is used.
"Gene Black" <geblack@.hotmail.com> wrote in message
news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
> Evidently it doesn't since the results of showcontig don't reflect
> fragmentation after the dbcc shrinkdatabase
> I did
> 1. sp_msforeachtable 'dbcc dbreindex(''?'')'
> dbcc showcontig for all tables
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
> 2. DBCC shrinkfile (2,5) then showcontig
> results:
> ADBForecasts-2 98%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 98%
> ADBTasks-3 0%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 98%
> ADBWorkSheetTasks-3 92%
> ADBWorkSheetTasks-4 96%
> ADBWorkSheetTasks-5 96%
> ADBWorkSheetTasks-19 98%
> 3. sp_msforeachtable 'dbcc dbreindex(''?'')' then showcontig
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
>
> 4. dbcc shrinkdatabase then showcontig
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:OQR6P%23igEHA.4092@.TK2MSFTNGP10.phx.gbl...
> files for the database.
> news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
> is
wrote[vbcol=seagreen]
> in
data
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
followed[vbcol=seagreen]
> by
> database
many[vbcol=seagreen]
circumstance?
>
|||> It looks like the difference is the Truncate option
Yep, as it only cuts of the files at the end, it doesn't move any data... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gene Black" <geblack@.hotmail.com> wrote in message news:u3I6ELjgEHA.3916@.TK2MSFTNGP11.phx.gbl...
> It looks like the difference is the TruncateOnly option
> TRUNCATEONLY
> Causes any unused space in the files to be released to the operating system
> and shrinks the file to the last allocated extent, reducing the file size
> without moving any data. No attempt is made to relocate rows to unallocated
> pages. target_size is ignored when TRUNCATEONLY is used.
> "Gene Black" <geblack@.hotmail.com> wrote in message
> news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
> in
> wrote
> data
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> followed
> many
> circumstance?
>
|||Yes, truncate only does not move data. As Tibor pointed out Shrink Database
does exactly the same work as ShrinkFile with the exception that
ShrinkDatabase combines both the data and log file(s). And it does not give
you any control what so ever as to how much it shrinks one or the other.
That is why I always recommend using ShrinkFile. Srinking a file that was
just defragged will always refragment it to some degree. In most case a
great deal. DBA's who shrink their data or log files each night, especially
after a reindex should just be shot<g>.
Andrew J. Kelly SQL MVP
"Gene Black" <geblack@.hotmail.com> wrote in message
news:u3I6ELjgEHA.3916@.TK2MSFTNGP11.phx.gbl...
> It looks like the difference is the TruncateOnly option
> TRUNCATEONLY
> Causes any unused space in the files to be released to the operating
system
> and shrinks the file to the last allocated extent, reducing the file size
> without moving any data. No attempt is made to relocate rows to
unallocated[vbcol=seagreen]
> pages. target_size is ignored when TRUNCATEONLY is used.
> "Gene Black" <geblack@.hotmail.com> wrote in message
> news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
> in
the[vbcol=seagreen]
(this
> wrote
> data
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
> followed
index
> many
> circumstance?
>
|||Thanks for the help guys, even though it took a little time for me to 'see
the light'
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGyaU7kgEHA.3676@.TK2MSFTNGP12.phx.gbl...
> Yes, truncate only does not move data. As Tibor pointed out Shrink
> Database
> does exactly the same work as ShrinkFile with the exception that
> ShrinkDatabase combines both the data and log file(s). And it does not
> give
> you any control what so ever as to how much it shrinks one or the other.
> That is why I always recommend using ShrinkFile. Srinking a file that was
> just defragged will always refragment it to some degree. In most case a
> great deal. DBA's who shrink their data or log files each night,
> especially
> after a reindex should just be shot<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Gene Black" <geblack@.hotmail.com> wrote in message
> news:u3I6ELjgEHA.3916@.TK2MSFTNGP11.phx.gbl...
> system
> unallocated
> the
> (this
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> index
>

Thursday, March 29, 2012

dbcc shrinkfile

dbcc shrinkfile

dbcc shrinkfile

DBCC shrinkfile

Monday, March 19, 2012

DBCC LOGINFO

Sunday, March 11, 2012

DBCC IndexDefrag Or DBCC REINDEX

Thursday, March 8, 2012

dbcc emptyfile

dbcc emptyfile

dbcc emptyfile

Wednesday, March 7, 2012

DBCC DBReindex follwed by DBCC ShrinkFile

DBfile usage

Blog Archive

About Me