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 complete. Show all posts
Showing posts with label complete. 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
>
Friday, February 10, 2012
can't get the complete list of tables owned by the dbo
Hi, SQL Server name Maps to this equivalent SQL-92 name Database catalog Owner schema Object object user-defined data type domain
i am using sp_tables to get the list of the tables in the database owned by dbo
The database i am trying to query would let me query all the tables present in the database.there are almost a 1000 of them(owned by dbo) .I can query them and i get the records.
But the execution of the stored procedure "sp_tables" would return only 10 or so tables.I was puzzled by this.
can someone clarify this inconsistency.
thank youI'm not sure what is causing the behavior you see, but you can get a list of tables owned by dbo by doing this:
select * from sysobjects where type = 'U' and uid=1
This query will work correctly when ported to SQL 2005.|||Is dbo always 1?
isn't it safer to do:
select o.name from sysobjects o inner join sysusers u on o.uid = u.uid
where o.xtype = 'u' and u.name = 'dbo'|||
Hi,
As a purely practical matter, it is hard for me to imagine MSFT changing the dbo==1 relationship. However, you make an excellent point - AFAIK the BOL does not make an explicit guarantee, and it is best not to rely on hardcoding ID#s into your scripts.
Your query won't work on a case-sensitive server. I recommend this as the simplest
select * from sysobjects where type = 'U' and uid=user_id('dbo')
Notice that I am looking for user-created tables (type = 'U'). The list you get back in SQL 2005 is different than the list in SQL 2000.
Moreover, if you look for MSFT-created system objects (type='S')
select * from sysobjects where type = 'S' and uid=user_id('dbo')
You get back an empty set in SQL 2005 because all the system tables are now owned by the uid=user_id('sys'). Moreover, the system tables themselves are different in SQL 2005 than they are in SQL 2000. In fact, they are not even directly SELECT-able anymore. You must use the catalog views to query the catalog.
Hope this helps
|||
Would the best advice be to use the INFORMATION_SCHEMA.tables view?
select *
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'dbo'
Most people shouldn't be looking up system tables like this anyhow, and this syntax works in 2000 and 2005 (7.0? can't remember) and should work in the next version, and the next, etc...
Moreover, the system tables themselves are different in SQL 2005 than they are in SQL 2000. In fact, they are not even directly SELECT-able anymore. You must use the catalog views to query the catalog.
I just wonder how stable these views will be in the future? I don't plan to use them in production code for anything. (other than monitoring, of course) The information_schema is supposed to be a stable/never subtracted from set of tables, right?
|||USE pubs
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA ='dbo'
AND TABLE_TYPE ='BASE TABLE' --If you leave this out you will also get VIEWS
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Views are table too :) But yeah, I should have mentioned that too.|||What is INFORMATION_SCHEMA ? Is it like a class ? As we are putting INFORMATION_SCHEMA.Table,etc.....
Can anyone explain ?
Thanks.
|||It is a special schema (in 2000 owner) used to contain metadata that is standard across platforms. It is kind of like DBO, but it is "special".|||From BOL:
Information Schema Views
These views provide an internal, system table-independent view of the SQL Server meta data. Information schema views allow applications to work properly even though significant changes have been made to the system tables. The information schema views included in SQL Server conform to the SQL-92 Standard definition for the INFORMATION_SCHEMA.
SQL Server supports a three-part naming convention when referring to the current server. The SQL-92 standard also supports a three-part naming convention. However, the names used in both naming conventions are different. These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database. Each INFORMATION_SCHEMA view contains meta data for all data objects stored in that particular database
This table describes the relationships between the SQL Server names and the SQL-92-standard names.
This naming convention mapping applies to these SQL Server SQL-92-compatible views. These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database. Each INFORMATION_SCHEMA view contains meta data for all data objects stored in that particular database.
Denis the SQL Menace
http://sqlservercode.blogspot.com/
can't get the complete list of tables owned by the dbo
Hi, SQL Server name Maps to this equivalent SQL-92 name Database catalog Owner schema Object object user-defined data type domain
i am using sp_tables to get the list of the tables in the database owned by dbo
The database i am trying to query would let me query all the tables present in the database.there are almost a 1000 of them(owned by dbo) .I can query them and i get the records.
But the execution of the stored procedure "sp_tables" would return only 10 or so tables.I was puzzled by this.
can someone clarify this inconsistency.
thank youI'm not sure what is causing the behavior you see, but you can get a list of tables owned by dbo by doing this:
select * from sysobjects where type = 'U' and uid=1
This query will work correctly when ported to SQL 2005.|||Is dbo always 1?
isn't it safer to do:
select o.name from sysobjects o inner join sysusers u on o.uid = u.uid
where o.xtype = 'u' and u.name = 'dbo'|||
Hi,
As a purely practical matter, it is hard for me to imagine MSFT changing the dbo==1 relationship. However, you make an excellent point - AFAIK the BOL does not make an explicit guarantee, and it is best not to rely on hardcoding ID#s into your scripts.
Your query won't work on a case-sensitive server. I recommend this as the simplest
select * from sysobjects where type = 'U' and uid=user_id('dbo')
Notice that I am looking for user-created tables (type = 'U'). The list you get back in SQL 2005 is different than the list in SQL 2000.
Moreover, if you look for MSFT-created system objects (type='S')
select * from sysobjects where type = 'S' and uid=user_id('dbo')
You get back an empty set in SQL 2005 because all the system tables are now owned by the uid=user_id('sys'). Moreover, the system tables themselves are different in SQL 2005 than they are in SQL 2000. In fact, they are not even directly SELECT-able anymore. You must use the catalog views to query the catalog.
Hope this helps
|||
Would the best advice be to use the INFORMATION_SCHEMA.tables view?
select *
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'dbo'
Most people shouldn't be looking up system tables like this anyhow, and this syntax works in 2000 and 2005 (7.0? can't remember) and should work in the next version, and the next, etc...
Moreover, the system tables themselves are different in SQL 2005 than they are in SQL 2000. In fact, they are not even directly SELECT-able anymore. You must use the catalog views to query the catalog.
I just wonder how stable these views will be in the future? I don't plan to use them in production code for anything. (other than monitoring, of course) The information_schema is supposed to be a stable/never subtracted from set of tables, right?
|||USE pubs
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA ='dbo'
AND TABLE_TYPE ='BASE TABLE' --If you leave this out you will also get VIEWS
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Views are table too :) But yeah, I should have mentioned that too.|||What is INFORMATION_SCHEMA ? Is it like a class ? As we are putting INFORMATION_SCHEMA.Table,etc.....
Can anyone explain ?
Thanks.
|||It is a special schema (in 2000 owner) used to contain metadata that is standard across platforms. It is kind of like DBO, but it is "special".|||From BOL:
Information Schema Views
These views provide an internal, system table-independent view of the SQL Server meta data. Information schema views allow applications to work properly even though significant changes have been made to the system tables. The information schema views included in SQL Server conform to the SQL-92 Standard definition for the INFORMATION_SCHEMA.
SQL Server supports a three-part naming convention when referring to the current server. The SQL-92 standard also supports a three-part naming convention. However, the names used in both naming conventions are different. These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database. Each INFORMATION_SCHEMA view contains meta data for all data objects stored in that particular database
This table describes the relationships between the SQL Server names and the SQL-92-standard names.
This naming convention mapping applies to these SQL Server SQL-92-compatible views. These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database. Each INFORMATION_SCHEMA view contains meta data for all data objects stored in that particular database.
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Subscribe to:
Posts (Atom)