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
Are 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_hXelpfile','truncateo nly')
(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[vbcol=seagreen]
> 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_hXelpfile','truncateo nly')
>
> (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...
excess[vbcol=seagreen]
> sysfiles"
correct.
> shrink
>
Showing posts with label dbcc. Show all posts
Showing posts with label dbcc. Show all posts
Sunday, March 25, 2012
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_helpfile','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_helpfile','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
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_helpfile','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_helpfile','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 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...
excess[vbcol=seagreen]
> sysfiles"
correct.[vbcol=seagreen]
> shrink
>
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...
excess[vbcol=seagreen]
> sysfiles"
correct.[vbcol=seagreen]
> shrink
>
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
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
Wednesday, March 7, 2012
can't rebuild msdb
Hello,
After running dbcc checkdb i'm getting:
"..54 consistency errors in database 'msdb'"
i want to rebuild the msdb database via
instmsdb.sql but i can't delete the database...
Which is the right way to do it avoiding rebuild the
master database?
Many Thank in Advance,
Galit.Galit
Do you have a last backup of msdb database?
If you do so try to restote it.
"Galit" <galitm@.ladpc.co.il> wrote in message
news:059101c3b7e1$3cd6f1b0$a301280a@.phx.gbl...
> Hello,
> After running dbcc checkdb i'm getting:
> "..54 consistency errors in database 'msdb'"
> i want to rebuild the msdb database via
> instmsdb.sql but i can't delete the database...
> Which is the right way to do it avoiding rebuild the
> master database?
> Many Thank in Advance,
> Galit.
>|||There's a section in below KB article on how you can detach the msdb database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Galit" <galitm@.ladpc.co.il> wrote in message news:059101c3b7e1$3cd6f1b0$a301280a@.phx.gbl...
> Hello,
> After running dbcc checkdb i'm getting:
> "..54 consistency errors in database 'msdb'"
> i want to rebuild the msdb database via
> instmsdb.sql but i can't delete the database...
> Which is the right way to do it avoiding rebuild the
> master database?
> Many Thank in Advance,
> Galit.
>|||Tibor Karaszi wrote:
> There's a section in below KB article on how you can detach the msdb
> database:
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071
following tibors link, after you have detached msdb, if you want to use
a blank msdb, look in the msde sp3 sql2ksp3\MSDE\Setup directory. You
will see a cabinet file sqlrun.cab. From this extract the two files
msdbdata.mdfXXXXXX and msdblog.ldfXXXXXX (both of these files are named
with a string of characters -which I think is a checksum, you can
simply rename them both to their correct respective names msdbdata.mdf
and msdblog.ldfXXXXXX)
Dont overwrite your existing msdb (just in case) rename them to *.old
for them time being till you are happy.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============|||Mark,
Wouldn't running instmsdb.sql also be an option at this stage? (Instead of attaching, as there will
be a problem if the template file doesn't have the same collation as the other system databases...)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Broadbent" <no-spam-please_mark.broadbent@.virgin.net> wrote in message
news:ecPhp5BuDHA.1680@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
> > There's a section in below KB article on how you can detach the msdb
> > database:
> >
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071
> following tibors link, after you have detached msdb, if you want to use
> a blank msdb, look in the msde sp3 sql2ksp3\MSDE\Setup directory. You
> will see a cabinet file sqlrun.cab. From this extract the two files
> msdbdata.mdfXXXXXX and msdblog.ldfXXXXXX (both of these files are named
> with a string of characters -which I think is a checksum, you can
> simply rename them both to their correct respective names msdbdata.mdf
> and msdblog.ldfXXXXXX)
> Dont overwrite your existing msdb (just in case) rename them to *.old
> for them time being till you are happy.
> --
> Br,
> Mark Broadbent
> mcdba , mcse+i
> =============|||Tibor Karaszi wrote:
> Mark,
> Wouldn't running instmsdb.sql also be an option at this stage?
> (Instead of attaching, as there will be a problem if the template
> file doesn't have the same collation as the other system databases...)
Yes very good point. Been staring at my monitor for too long #:-)
--
Br,
Mark Broadbent
mcdba , mcse+i
=============|||That's what i did.
Many many thanks,
Galit.
>--Original Message--
>Tibor Karaszi wrote:
>> Mark,
>> Wouldn't running instmsdb.sql also be an option at this
stage?
>> (Instead of attaching, as there will be a problem if
the template
>> file doesn't have the same collation as the other
system databases...)
>Yes very good point. Been staring at my monitor for too
long #:-)
>--
>Br,
>Mark Broadbent
>mcdba , mcse+i
>=============>.
>
After running dbcc checkdb i'm getting:
"..54 consistency errors in database 'msdb'"
i want to rebuild the msdb database via
instmsdb.sql but i can't delete the database...
Which is the right way to do it avoiding rebuild the
master database?
Many Thank in Advance,
Galit.Galit
Do you have a last backup of msdb database?
If you do so try to restote it.
"Galit" <galitm@.ladpc.co.il> wrote in message
news:059101c3b7e1$3cd6f1b0$a301280a@.phx.gbl...
> Hello,
> After running dbcc checkdb i'm getting:
> "..54 consistency errors in database 'msdb'"
> i want to rebuild the msdb database via
> instmsdb.sql but i can't delete the database...
> Which is the right way to do it avoiding rebuild the
> master database?
> Many Thank in Advance,
> Galit.
>|||There's a section in below KB article on how you can detach the msdb database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Galit" <galitm@.ladpc.co.il> wrote in message news:059101c3b7e1$3cd6f1b0$a301280a@.phx.gbl...
> Hello,
> After running dbcc checkdb i'm getting:
> "..54 consistency errors in database 'msdb'"
> i want to rebuild the msdb database via
> instmsdb.sql but i can't delete the database...
> Which is the right way to do it avoiding rebuild the
> master database?
> Many Thank in Advance,
> Galit.
>|||Tibor Karaszi wrote:
> There's a section in below KB article on how you can detach the msdb
> database:
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071
following tibors link, after you have detached msdb, if you want to use
a blank msdb, look in the msde sp3 sql2ksp3\MSDE\Setup directory. You
will see a cabinet file sqlrun.cab. From this extract the two files
msdbdata.mdfXXXXXX and msdblog.ldfXXXXXX (both of these files are named
with a string of characters -which I think is a checksum, you can
simply rename them both to their correct respective names msdbdata.mdf
and msdblog.ldfXXXXXX)
Dont overwrite your existing msdb (just in case) rename them to *.old
for them time being till you are happy.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============|||Mark,
Wouldn't running instmsdb.sql also be an option at this stage? (Instead of attaching, as there will
be a problem if the template file doesn't have the same collation as the other system databases...)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Broadbent" <no-spam-please_mark.broadbent@.virgin.net> wrote in message
news:ecPhp5BuDHA.1680@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
> > There's a section in below KB article on how you can detach the msdb
> > database:
> >
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071
> following tibors link, after you have detached msdb, if you want to use
> a blank msdb, look in the msde sp3 sql2ksp3\MSDE\Setup directory. You
> will see a cabinet file sqlrun.cab. From this extract the two files
> msdbdata.mdfXXXXXX and msdblog.ldfXXXXXX (both of these files are named
> with a string of characters -which I think is a checksum, you can
> simply rename them both to their correct respective names msdbdata.mdf
> and msdblog.ldfXXXXXX)
> Dont overwrite your existing msdb (just in case) rename them to *.old
> for them time being till you are happy.
> --
> Br,
> Mark Broadbent
> mcdba , mcse+i
> =============|||Tibor Karaszi wrote:
> Mark,
> Wouldn't running instmsdb.sql also be an option at this stage?
> (Instead of attaching, as there will be a problem if the template
> file doesn't have the same collation as the other system databases...)
Yes very good point. Been staring at my monitor for too long #:-)
--
Br,
Mark Broadbent
mcdba , mcse+i
=============|||That's what i did.
Many many thanks,
Galit.
>--Original Message--
>Tibor Karaszi wrote:
>> Mark,
>> Wouldn't running instmsdb.sql also be an option at this
stage?
>> (Instead of attaching, as there will be a problem if
the template
>> file doesn't have the same collation as the other
system databases...)
>Yes very good point. Been staring at my monitor for too
long #:-)
>--
>Br,
>Mark Broadbent
>mcdba , mcse+i
>=============>.
>
Subscribe to:
Posts (Atom)