Can't shrink database or files

I can't shrink my data file using either DBCC SHRINKDATABASE or DBCC
SHRINKFILE.
SHRINKDATABASE appears to complete okay but there is still a lot of excess
space in the database (more than half).
SHRINKFILE gives the error "Could not locate file '<filename>' in sysfiles"
but the filename is the correct logical file name of my data file and I
checked in sysfiles that it exists and the data file location is correct.
Can anyone tell me what the problem might be? Is there another way to shrink
the database?
ThanksAre you in the right database when you issue the DBCC SHRINKFILE command?
DBCC SHRINKDATABASE takes the db name as a parameter, but SHRINKFILE does
not. It works on the current database.
If you are in the right database, can you show us the output of sp_helpfile,
and show us the exact command you are trying to run?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:C065C200-4C54-4AF9-91AB-6EF1C320D66E@.microsoft.com...
>I can't shrink my data file using either DBCC SHRINKDATABASE or DBCC
> SHRINKFILE.
> SHRINKDATABASE appears to complete okay but there is still a lot of excess
> space in the database (more than half).
> SHRINKFILE gives the error "Could not locate file '<filename>' in
> sysfiles"
> but the filename is the correct logical file name of my data file and I
> checked in sysfiles that it exists and the data file location is correct.
> Can anyone tell me what the problem might be? Is there another way to
> shrink
> the database?
> Thanks|||Hi,
SHRINKDATABASE command will not shrink the file if there is any active open
transactions. Execute the command DBCC OPENTRAN(DBNAME)
to identify if there is any actiove open tran. Wait for those tranasctions
to close or kill the SPID which is open.
If you still face issues in shrinking then set the database to Single user
and shrink:-
Steps you can do to shrink the transaction log file:-
1. Backup the transaction log using the below command.
backup log <dbname> to disk='c:\backup\dbname.trn'
2. Bring the database to single user
Alter database <dbname> set single_user with rollback immediate
3. Shrink the database log file
dbcc shrinkdatabase(DBNAME) -- See books online for usage
4. Bring the database to multi user
alter database <dbname> set multi_user
----
WyHY SHRINK FILE is giving error:-
As Kalen pointed, you are not in the right database
Steps:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
dbcc shrinkfile('name_given_by_sp_h­elpfile','truncateonly')
(Execute the DBCC SQLPERF(LOGSPACE) to identify that the log is shrink)
Thanks
Hari
SQL Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:C065C200-4C54-4AF9-91AB-6EF1C320D66E@.microsoft.com...
> I can't shrink my data file using either DBCC SHRINKDATABASE or DBCC
> SHRINKFILE.
> SHRINKDATABASE appears to complete okay but there is still a lot of excess
> space in the database (more than half).
> SHRINKFILE gives the error "Could not locate file '<filename>' in
sysfiles"
> but the filename is the correct logical file name of my data file and I
> checked in sysfiles that it exists and the data file location is correct.
> Can anyone tell me what the problem might be? Is there another way to
shrink
> the database?
> Thanks|||Nonsense. Shrink will happily wait for any locks it requires and then
continue - that's the whole point of it being online.
Do you have LOB columns in the database (select * from sysindexes where
indid=255)? If you've deleted a bunch of LOB data, this cannot be reclaimed
without bcp out/in or using select/into. This is fixed in SQL Server 2005.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OLwbMHhVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hi,
> SHRINKDATABASE command will not shrink the file if there is any active
open
> transactions. Execute the command DBCC OPENTRAN(DBNAME)
> to identify if there is any actiove open tran. Wait for those tranasctions
> to close or kill the SPID which is open.
> If you still face issues in shrinking then set the database to Single user
> and shrink:-
> Steps you can do to shrink the transaction log file:-
>
> 1. Backup the transaction log using the below command.
> backup log <dbname> to disk='c:\backup\dbname.trn'
> 2. Bring the database to single user
> Alter database <dbname> set single_user with rollback immediate
> 3. Shrink the database log file
> dbcc shrinkdatabase(DBNAME) -- See books online for usage
> 4. Bring the database to multi user
> alter database <dbname> set multi_user
> ----
> WyHY SHRINK FILE is giving error:-
> As Kalen pointed, you are not in the right database
> Steps:-
> use <dbname>
> go
> sp_helpfile
> Based on the name column for the transaction log file execute the dbcc
> shrinkfile
>
> dbcc shrinkfile('name_given_by_sp_h­elpfile','truncateonly')
>
> (Execute the DBCC SQLPERF(LOGSPACE) to identify that the log is shrink)
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:C065C200-4C54-4AF9-91AB-6EF1C320D66E@.microsoft.com...
> > I can't shrink my data file using either DBCC SHRINKDATABASE or DBCC
> > SHRINKFILE.
> >
> > SHRINKDATABASE appears to complete okay but there is still a lot of
excess
> > space in the database (more than half).
> >
> > SHRINKFILE gives the error "Could not locate file '<filename>' in
> sysfiles"
> > but the filename is the correct logical file name of my data file and I
> > checked in sysfiles that it exists and the data file location is
correct.
> >
> > Can anyone tell me what the problem might be? Is there another way to
> shrink
> > the database?
> >
> > Thanks
>sql

Can't shrink a Transaction Log File

I backed up my log file and attempted to shrink it with DBCC
SHRINKFILE. The command executes but leaves the transaction log at
it's current size. I know that an uncommited tansaction can cause this
to occur.
a) How do I test for this?
b)How do I get rid of it?
Regards
John LHi Gemel,
Try to backup your transaction log again and then try to shrink the file.
If its possible to loose youre trans log info(i guess you dont) change the
recovery model to simple(you'll loose your log info), shrink your tlog to the
desired size, then change recovery model to full and finally backup youre
database.
Hope that this can help you.
Regards
"gemel" wrote:
> I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L
>|||If you have a long running open transaction that could prevent the log from
shrinking..
DBCC opentran will show you the oldest transaction still in transaction
state.
You might also try
DBCC SQLPERF(logspace) to see how much space in the log is used
also
DBCC loginfo ( google on this to find more details.)
hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gemel" <jonel@.glenavon1.demon.co.uk> wrote in message
news:gdhm31tl8rs5t2804ibhc9onv36lee5evb@.4ax.com...
>I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L