Can't shrink transaction log

Instead of shrinking it, back it up first, this should get
rid of the active parts of the log file, then shrink it.
Peter

>--Original Message--
>Hi,
>A SQL Server2000 database we have has ended up with a
>massive Transaction Log (basically because the
maintenance
>schedule needs looking at).
>Anyway to cut a long story short, I backed up the
>transaction log which truncated it, but I can't seem to
>shrink the logfile to free up the space.
>I tried:
>DBCC SHRINKFILE (2, 200, TRUNCATEONLY)
>[where 2 is the filenumber of the logfile]
>This came back with:
>'Cannot shrink log file 2 (Main_Log) because all logical
>log files are in use.'
>Can anyone tell me how I can shrink this log file please?
>Regards,
>Vince
>.
>Check this article out
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Regards,
Gayathri Rau [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.

Can't shrink system tempdb

The shrink file dialogue box indicates the system tempdb has a current size of

1091MB, and a space used of 19MB. I’m assuming I should be able to reduce the

size to 19MB? Regardless of what shrink action I invoke it still maintains a size

of 1091MB. Is it possible to reduce the size of this system DB? Thanks to everyone

for being there to help.

SQL 2000 SP4

Hi, take a look here: http://support.microsoft.com/Default.aspx?id=307487

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks, that's exactly what I needed. I'm having difficulty finding a link

that demonstrates how to relocate system DB's, like the master DB.

Do you have a link for that also?

SQL 2000 SP4

|||Sure, --> http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

HTH;, Jens Suessmeyer.

http://www.sqlserver2005.de

Can't Shrink log File

Hi, All,
I tried to shrink a log file which was restored from production backup, but
got error. The log file is about only 10% usage according to the result of
DBCC SQLPERF ( LOGSPACE ).
When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
message:
Cannot shrink log file 2 (productionlog) because all logical log files are
in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
-- -- -- -- -- --
14 2 92888 76753 92888 76752
We do have Replication set up in Production, but I drop all replications
after restored it to Test environment. We are using SQL Server 2000.
Any help will be appreciated.
PerayuDid you backup the log first? Refer to:
http://support.microsoft.com/?id=272318
-Sue
On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
<yu.he@.state.mn.us.Remove4Replay> wrote:

>Hi, All,
>I tried to shrink a log file which was restored from production backup, but
>got error. The log file is about only 10% usage according to the result of
>DBCC SQLPERF ( LOGSPACE ).
>When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
>message:
>Cannot shrink log file 2 (productionlog) because all logical log files are
>in use.
>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>-- -- -- -- -- --
>14 2 92888 76753 92888 76752
>We do have Replication set up in Production, but I drop all replications
>after restored it to Test environment. We are using SQL Server 2000.
>Any help will be appreciated.
>Perayu
>|||I did not run Backup before run ShrintFile. But we backup log file everyday.
I just want to truncate the unused portion because the SQLPERF shows there
are only 10% space are used.
Thanks for your help.
Perayu
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.
4ax.com...
> Did you backup the log first? Refer to:
> http://support.microsoft.com/?id=272318
> -Sue
> On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
> <yu.he@.state.mn.us.Remove4Replay> wrote:
>
>|||Here's some comments on the topic: http://www.karaszi.com/SQLServer/in...i
nk.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:%23XBv5XM$FHA.3444@.TK2MSFTNGP10.phx.gbl...
>I did not run Backup before run ShrintFile. But we backup log file everyday
. I just want to
>truncate the unused portion because the SQLPERF shows there are only 10% sp
ace are used.
> Thanks for your help.
> Perayu
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.
4ax.com...
>

Can't shrink log file

I have an SQL Server 2000 database where the data file is 120 MB and the log
file is 330 MB. For some reason I am now unable to shrink the log file,
which has never been a problem before. I am doing this in Enterprise
Manager, selecting Shrink Database then selecting the log file and the
"compress pages and truncate free space" option. It says that the entire 330
MB is being used and won't shrink it. How can I fix this? Is it OK to close
down SQL Server and just delete the log file?
GlennDon't delete the file. Check out http://www.karaszi.com/SQLServer/in...ink
.asp where you
find some information about shrinking log file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message news:47616ac1$0$2353$4c368faf@.roadrunne
r.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the lo
g
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire 3
30
> MB is being used and won't shrink it. How can I fix this? Is it OK to clos
e
> down SQL Server and just delete the log file?
> Glenn
>|||You most likely have an open transaction preventing the log from reusing the
space. Run DBCC OPENTRAN() in that db to see. If not you probably have the
recovery set to FULL but are not doing log backups.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner
.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the
>log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>|||Glenn,
You can always shrink the log using t-sql as well.
First you need to backup the log but in this case its for the purpose of
reducing the size and not actually saving the log.
BACKUP LOG WITH TRUNCATE_ONLY
then you need to use the dbcc shrink file command
DBCC SHRINKFILE (LogicalFilename, size you would like to reduce it to);
http://msdn2.microsoft.com/en-us/library/ms189493.aspx
that should take care of it.
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner
.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the
>log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>|||Try to Dump Tran Databasename with no_log command then selecting Shrink
Database
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner
.com...
> I have an SQL Server 2000 database where the data file is 120 MB and the
> log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>

Can't shrink log file

I have an SQL Server 2000 database where the data file is 120 MB and the log
file is 330 MB. For some reason I am now unable to shrink the log file,
which has never been a problem before. I am doing this in Enterprise
Manager, selecting Shrink Database then selecting the log file and the
"compress pages and truncate free space" option. It says that the entire 330
MB is being used and won't shrink it. How can I fix this? Is it OK to close
down SQL Server and just delete the log file?
Glenn
You most likely have an open transaction preventing the log from reusing the
space. Run DBCC OPENTRAN() in that db to see. If not you probably have the
recovery set to FULL but are not doing log backups.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the
>log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>
|||Glenn,
You can always shrink the log using t-sql as well.
First you need to backup the log but in this case its for the purpose of
reducing the size and not actually saving the log.
BACKUP LOG WITH TRUNCATE_ONLY
then you need to use the dbcc shrink file command
DBCC SHRINKFILE (LogicalFilename, size you would like to reduce it to);
http://msdn2.microsoft.com/en-us/library/ms189493.aspx
that should take care of it.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the
>log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>
|||Try to Dump Tran Databasename with no_log command then selecting Shrink
Database
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner.com...
> I have an SQL Server 2000 database where the data file is 120 MB and the
> log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>

Can't Shrink log File

Hi, All,
I tried to shrink a log file which was restored from production backup, but
got error. The log file is about only 10% usage according to the result of
DBCC SQLPERF ( LOGSPACE ).
When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
message:
Cannot shrink log file 2 (productionlog) because all logical log files are
in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
-- -- -- -- -- --
14 2 92888 76753 92888 76752
We do have Replication set up in Production, but I drop all replications
after restored it to Test environment. We are using SQL Server 2000.
Any help will be appreciated.
Perayu
Did you backup the log first? Refer to:
http://support.microsoft.com/?id=272318
-Sue
On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
<yu.he@.state.mn.us.Remove4Replay> wrote:

>Hi, All,
>I tried to shrink a log file which was restored from production backup, but
>got error. The log file is about only 10% usage according to the result of
>DBCC SQLPERF ( LOGSPACE ).
>When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
>message:
>Cannot shrink log file 2 (productionlog) because all logical log files are
>in use.
>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>-- -- -- -- -- --
>14 2 92888 76753 92888 76752
>We do have Replication set up in Production, but I drop all replications
>after restored it to Test environment. We are using SQL Server 2000.
>Any help will be appreciated.
>Perayu
>
|||I did not run Backup before run ShrintFile. But we backup log file everyday.
I just want to truncate the unused portion because the SQLPERF shows there
are only 10% space are used.
Thanks for your help.
Perayu
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.4ax.com...
> Did you backup the log first? Refer to:
> http://support.microsoft.com/?id=272318
> -Sue
> On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
> <yu.he@.state.mn.us.Remove4Replay> wrote:
>
|||Here's some comments on the topic: 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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:%23XBv5XM$FHA.3444@.TK2MSFTNGP10.phx.gbl...
>I did not run Backup before run ShrintFile. But we backup log file everyday. I just want to
>truncate the unused portion because the SQLPERF shows there are only 10% space are used.
> Thanks for your help.
> Perayu
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.4ax.com...
>
sql

Can't shrink log file

I have an SQL Server 2000 database where the data file is 120 MB and the log
file is 330 MB. For some reason I am now unable to shrink the log file,
which has never been a problem before. I am doing this in Enterprise
Manager, selecting Shrink Database then selecting the log file and the
"compress pages and truncate free space" option. It says that the entire 330
MB is being used and won't shrink it. How can I fix this? Is it OK to close
down SQL Server and just delete the log file?
GlennDon't delete the file. Check out http://www.karaszi.com/SQLServer/info_dont_shrink.asp where you
find some information about shrinking log file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message news:47616ac1$0$2353$4c368faf@.roadrunner.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to close
> down SQL Server and just delete the log file?
> Glenn
>|||You most likely have an open transaction preventing the log from reusing the
space. Run DBCC OPENTRAN() in that db to see. If not you probably have the
recovery set to FULL but are not doing log backups.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the
>log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>|||Glenn,
You can always shrink the log using t-sql as well.
First you need to backup the log but in this case its for the purpose of
reducing the size and not actually saving the log.
BACKUP LOG WITH TRUNCATE_ONLY
then you need to use the dbcc shrink file command
DBCC SHRINKFILE (LogicalFilename, size you would like to reduce it to);
http://msdn2.microsoft.com/en-us/library/ms189493.aspx
that should take care of it.
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the
>log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>|||Try to Dump Tran Databasename with no_log command then selecting Shrink
Database
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner.com...
> I have an SQL Server 2000 database where the data file is 120 MB and the
> log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>

Can't Shrink log File

Hi, All,
I tried to shrink a log file which was restored from production backup, but
got error. The log file is about only 10% usage according to the result of
DBCC SQLPERF ( LOGSPACE ).
When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
message:
Cannot shrink log file 2 (productionlog) because all logical log files are
in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
-- -- -- -- -- --
14 2 92888 76753 92888 76752
We do have Replication set up in Production, but I drop all replications
after restored it to Test environment. We are using SQL Server 2000.
Any help will be appreciated.
PerayuDid you backup the log first? Refer to:
http://support.microsoft.com/?id=272318
-Sue
On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
<yu.he@.state.mn.us.Remove4Replay> wrote:
>Hi, All,
>I tried to shrink a log file which was restored from production backup, but
>got error. The log file is about only 10% usage according to the result of
>DBCC SQLPERF ( LOGSPACE ).
>When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
>message:
>Cannot shrink log file 2 (productionlog) because all logical log files are
>in use.
>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>-- -- -- -- -- --
>14 2 92888 76753 92888 76752
>We do have Replication set up in Production, but I drop all replications
>after restored it to Test environment. We are using SQL Server 2000.
>Any help will be appreciated.
>Perayu
>|||I did not run Backup before run ShrintFile. But we backup log file everyday.
I just want to truncate the unused portion because the SQLPERF shows there
are only 10% space are used.
Thanks for your help.
Perayu
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.4ax.com...
> Did you backup the log first? Refer to:
> http://support.microsoft.com/?id=272318
> -Sue
> On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
> <yu.he@.state.mn.us.Remove4Replay> wrote:
>>Hi, All,
>>I tried to shrink a log file which was restored from production backup,
>>but
>>got error. The log file is about only 10% usage according to the result of
>>DBCC SQLPERF ( LOGSPACE ).
>>When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
>>message:
>>Cannot shrink log file 2 (productionlog) because all logical log files are
>>in use.
>>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>>-- -- -- -- -- --
>>14 2 92888 76753 92888 76752
>>We do have Replication set up in Production, but I drop all replications
>>after restored it to Test environment. We are using SQL Server 2000.
>>Any help will be appreciated.
>>Perayu
>|||Here's some comments on the topic: 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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:%23XBv5XM$FHA.3444@.TK2MSFTNGP10.phx.gbl...
>I did not run Backup before run ShrintFile. But we backup log file everyday. I just want to
>truncate the unused portion because the SQLPERF shows there are only 10% space are used.
> Thanks for your help.
> Perayu
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:0dthp1pqj5hnpejtc0gao5hr1a5rcaju27@.4ax.com...
>> Did you backup the log first? Refer to:
>> http://support.microsoft.com/?id=272318
>> -Sue
>> On Thu, 8 Dec 2005 08:37:43 -0600, "Perayu"
>> <yu.he@.state.mn.us.Remove4Replay> wrote:
>>Hi, All,
>>I tried to shrink a log file which was restored from production backup, but
>>got error. The log file is about only 10% usage according to the result of
>>DBCC SQLPERF ( LOGSPACE ).
>>When I tried this command: DBCC ShrinkFile(productionlog, 30), I got this
>>message:
>>Cannot shrink log file 2 (productionlog) because all logical log files are
>>in use.
>>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>>-- -- -- -- -- --
>>14 2 92888 76753 92888 76752
>>We do have Replication set up in Production, but I drop all replications
>>after restored it to Test environment. We are using SQL Server 2000.
>>Any help will be appreciated.
>>Perayu
>>
>

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?
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
>

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 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
>

cant shrink data file

sql2k sp3
Before I start, let me assure you that the 5 gigs of free
space in my db is in the data file, not the log file. I
already shrunk the 7 gig log file with no problem. Now
when I run dbcc shrinkfile(db_data_file) it runs for a
long time and then barely shrinks anything. Am I missing
something here? Any ideas?
Thanks in advance.Chris,
You may need to check for fragmentation.Refer DBCC SHOWCONTIG, DBCC
INDEXDEFRAG in BooksOnLine.
Also, may be..
PRB: DBCC SHRINKFILE and SHRINKDATABASE Commands May Not Work Because of
Sparsely Populated Text, Ntext, or Image Columns
http://support.microsoft.com/default.aspx?scid=kb;en-us;324432
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"chris" <chrisr@.fingps.com> wrote in message
news:3bbe01c37626$d832d270$a301280a@.phx.gbl...
> sql2k sp3
> Before I start, let me assure you that the 5 gigs of free
> space in my db is in the data file, not the log file. I
> already shrunk the 7 gig log file with no problem. Now
> when I run dbcc shrinkfile(db_data_file) it runs for a
> long time and then barely shrinks anything. Am I missing
> something here? Any ideas?
> Thanks in advance.
>|||If you want to see where your data is allocated through the data file go to
www.sqlfe.com. You can see what is at the end of the data file.
--
Barry McAuslin
support@.sqlfe.com
http://www.sqlfe.com
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23PZlOfidDHA.828@.TK2MSFTNGP11.phx.gbl...
> Chris,
> You may need to check for fragmentation.Refer DBCC SHOWCONTIG, DBCC
> INDEXDEFRAG in BooksOnLine.
> Also, may be..
> PRB: DBCC SHRINKFILE and SHRINKDATABASE Commands May Not Work Because of
> Sparsely Populated Text, Ntext, or Image Columns
> http://support.microsoft.com/default.aspx?scid=kb;en-us;324432
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "chris" <chrisr@.fingps.com> wrote in message
> news:3bbe01c37626$d832d270$a301280a@.phx.gbl...
> > sql2k sp3
> >
> > Before I start, let me assure you that the 5 gigs of free
> > space in my db is in the data file, not the log file. I
> > already shrunk the 7 gig log file with no problem. Now
> > when I run dbcc shrinkfile(db_data_file) it runs for a
> > long time and then barely shrinks anything. Am I missing
> > something here? Any ideas?
> > Thanks in advance.
> >
> >
>

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 L
Hi 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

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

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 th
e
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 Lsql

can't shrink a log file

actually i have a db file size of 400MB and it's log file about 4.5GB, i rea
d
all about backup log and shrink the file, and the problems is every virtual
log file has an status of active, some are really old ( 2002 ), how can i
turn off the virtual files and left only one active (the last)Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramrez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> actually i have a db file size of 400MB and it's log file about 4.5GB, i
read
> all about backup log and shrink the file, and the problems is every
virtual
> log file has an status of active, some are really old ( 2002 ), how can i
> turn off the virtual files and left only one active (the last)|||this is the message result
Información de transacción para la base de datos 'SII'.
Información de transacción duplicada:
LSN distribuido más antiguo : (4796:432:1)
LSN no distribuido más antiguo : (4796:457:1)
Ejecución de DBCC completada. Si hay mensajes de error, consulte al
administrador del sistema.
transaction information for db SII (my db)
transaction information duplicated
LSN older distributed : (4796:432:1)
LSN older not distributed : (4796:457:1)
DBCC execution completed. If there are error messages, contact system
administrator.
?What's next?
"Paul S Randal [MS]" wrote:

> Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Fidel Ram_rez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
> news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> read
> virtual
>
>|||Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
OPENTRAN doesn't tell you the start time of the oldest transaction but I
suspect you've got an uncommitted transaction that's preventing the log from
being truncated. Your best bet is to contact PSS
(http://support.microsoft.com) for help and guidance.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...[vbcol=seagreen]
> this is the message result
> Informacin de transaccin para la base de datos 'SII'.
> Informacin de transaccin duplicada:
> LSN distribuido ms antiguo : (4796:432:1)
> LSN no distribuido ms antiguo : (4796:457:1)
> Ejecucin de DBCC completada. Si hay mensajes de error, consulte al
> administrador del sistema.
>
> transaction information for db SII (my db)
> transaction information duplicated
> LSN older distributed : (4796:432:1)
> LSN older not distributed : (4796:457:1)
> DBCC execution completed. If there are error messages, contact system
> administrator.
> What's next?
>
> "Paul S Randal [MS]" wrote:
>
output?[vbcol=seagreen]
rights.[vbcol=seagreen]
message[vbcol=seagreen]
i[vbcol=seagreen]
can i[vbcol=seagreen]|||thnks a lot i'll do it
"Paul S Randal [MS]" wrote:

> Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
> OPENTRAN doesn't tell you the start time of the oldest transaction but I
> suspect you've got an uncommitted transaction that's preventing the log fr
om
> being truncated. Your best bet is to contact PSS
> (http://support.microsoft.com) for help and guidance.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
> news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...
> output?
> rights.
> message
> i
> can i
>
>

can't shrink a log file

actually i have a db file size of 400MB and it's log file about 4.5GB, i read
all about backup log and shrink the file, and the problems is every virtual
log file has an status of active, some are really old ( 2002 ), how can i
turn off the virtual files and left only one active (the last)
Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramrez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> actually i have a db file size of 400MB and it's log file about 4.5GB, i
read
> all about backup log and shrink the file, and the problems is every
virtual
> log file has an status of active, some are really old ( 2002 ), how can i
> turn off the virtual files and left only one active (the last)
|||this is the message result
Información de transacción para la base de datos 'SII'.
Información de transacción duplicada:
LSN distribuido más antiguo : (4796:432:1)
LSN no distribuido más antiguo : (4796:457:1)
Ejecución de DBCC completada. Si hay mensajes de error, consulte al
administrador del sistema.
transaction information for db SII (my db)
transaction information duplicated
LSN older distributed : (4796:432:1)
LSN older not distributed : (4796:457:1)
DBCC execution completed. If there are error messages, contact system
administrator.
?What's next?
"Paul S Randal [MS]" wrote:

> Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fidel RamXrez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
> news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> read
> virtual
>
>
|||Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
OPENTRAN doesn't tell you the start time of the oldest transaction but I
suspect you've got an uncommitted transaction that's preventing the log from
being truncated. Your best bet is to contact PSS
(http://support.microsoft.com) for help and guidance.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...[vbcol=seagreen]
> this is the message result
> Informacin de transaccin para la base de datos 'SII'.
> Informacin de transaccin duplicada:
> LSN distribuido ms antiguo : (4796:432:1)
> LSN no distribuido ms antiguo : (4796:457:1)
> Ejecucin de DBCC completada. Si hay mensajes de error, consulte al
> administrador del sistema.
>
> transaction information for db SII (my db)
> transaction information duplicated
> LSN older distributed : (4796:432:1)
> LSN older not distributed : (4796:457:1)
> DBCC execution completed. If there are error messages, contact system
> administrator.
> What's next?
>
> "Paul S Randal [MS]" wrote:
output?[vbcol=seagreen]
rights.[vbcol=seagreen]
message[vbcol=seagreen]
i[vbcol=seagreen]
can i[vbcol=seagreen]
|||thnks a lot i'll do it
"Paul S Randal [MS]" wrote:

> Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
> OPENTRAN doesn't tell you the start time of the oldest transaction but I
> suspect you've got an uncommitted transaction that's preventing the log from
> being truncated. Your best bet is to contact PSS
> (http://support.microsoft.com) for help and guidance.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
> news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...
> output?
> rights.
> message
> i
> can i
>
>

can't shrink a log file

actually i have a db file size of 400MB and it's log file about 4.5GB, i read
all about backup log and shrink the file, and the problems is every virtual
log file has an status of active, some are really old ( 2002 ), how can i
turn off the virtual files and left only one active (the last)Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramírez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> actually i have a db file size of 400MB and it's log file about 4.5GB, i
read
> all about backup log and shrink the file, and the problems is every
virtual
> log file has an status of active, some are really old ( 2002 ), how can i
> turn off the virtual files and left only one active (the last)|||this is the message result
Información de transacción para la base de datos 'SII'.
Información de transacción duplicada:
LSN distribuido más antiguo : (4796:432:1)
LSN no distribuido más antiguo : (4796:457:1)
Ejecución de DBCC completada. Si hay mensajes de error, consulte al
administrador del sistema.
transaction information for db SII (my db)
transaction information duplicated
LSN older distributed : (4796:432:1)
LSN older not distributed : (4796:457:1)
DBCC execution completed. If there are error messages, contact system
administrator.
¿What's next?
"Paul S Randal [MS]" wrote:
> Do you have any uncommitted transactions? What does DBCC OPENTRAN output?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fidel Ramírez" <Fidel Ramrez@.discussions.microsoft.com> wrote in message
> news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> > actually i have a db file size of 400MB and it's log file about 4.5GB, i
> read
> > all about backup log and shrink the file, and the problems is every
> virtual
> > log file has an status of active, some are really old ( 2002 ), how can i
> > turn off the virtual files and left only one active (the last)
>
>|||Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
OPENTRAN doesn't tell you the start time of the oldest transaction but I
suspect you've got an uncommitted transaction that's preventing the log from
being truncated. Your best bet is to contact PSS
(http://support.microsoft.com) for help and guidance.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...
> this is the message result
> Información de transacción para la base de datos 'SII'.
> Información de transacción duplicada:
> LSN distribuido más antiguo : (4796:432:1)
> LSN no distribuido más antiguo : (4796:457:1)
> Ejecución de DBCC completada. Si hay mensajes de error, consulte al
> administrador del sistema.
>
> transaction information for db SII (my db)
> transaction information duplicated
> LSN older distributed : (4796:432:1)
> LSN older not distributed : (4796:457:1)
> DBCC execution completed. If there are error messages, contact system
> administrator.
> ¿What's next?
>
> "Paul S Randal [MS]" wrote:
> > Do you have any uncommitted transactions? What does DBCC OPENTRAN
output?
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Fidel Ramírez" <Fidel Ramrez@.discussions.microsoft.com> wrote in
message
> > news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> > > actually i have a db file size of 400MB and it's log file about 4.5GB,
i
> > read
> > > all about backup log and shrink the file, and the problems is every
> > virtual
> > > log file has an status of active, some are really old ( 2002 ), how
can i
> > > turn off the virtual files and left only one active (the last)
> >
> >
> >|||thnks a lot i'll do it
"Paul S Randal [MS]" wrote:
> Looks like you're running SQL Server 7.0 - is that correct? If so, DBCC
> OPENTRAN doesn't tell you the start time of the oldest transaction but I
> suspect you've got an uncommitted transaction that's preventing the log from
> being truncated. Your best bet is to contact PSS
> (http://support.microsoft.com) for help and guidance.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Fidel Ramirez" <Fidel Ramirez@.discussions.microsoft.com> wrote in message
> news:54DEF4D8-CA4B-497C-A998-FE7E0FB59346@.microsoft.com...
> > this is the message result
> >
> > Información de transacción para la base de datos 'SII'.
> >
> > Información de transacción duplicada:
> > LSN distribuido más antiguo : (4796:432:1)
> > LSN no distribuido más antiguo : (4796:457:1)
> > Ejecución de DBCC completada. Si hay mensajes de error, consulte al
> > administrador del sistema.
> >
> >
> > transaction information for db SII (my db)
> > transaction information duplicated
> > LSN older distributed : (4796:432:1)
> > LSN older not distributed : (4796:457:1)
> > DBCC execution completed. If there are error messages, contact system
> > administrator.
> >
> > ¿What's next?
> >
> >
> > "Paul S Randal [MS]" wrote:
> >
> > > Do you have any uncommitted transactions? What does DBCC OPENTRAN
> output?
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Fidel Ramírez" <Fidel Ramrez@.discussions.microsoft.com> wrote in
> message
> > > news:A2AE904A-CE8C-48F1-9852-23F0033C56AD@.microsoft.com...
> > > > actually i have a db file size of 400MB and it's log file about 4.5GB,
> i
> > > read
> > > > all about backup log and shrink the file, and the problems is every
> > > virtual
> > > > log file has an status of active, some are really old ( 2002 ), how
> can i
> > > > turn off the virtual files and left only one active (the last)
> > >
> > >
> > >
>
>

Can't see all drives in a cluster within Enterprise manager

When I am in Enterprise Manager to create a new database, I click on the location of the data and log file, I only see 1 drive where SQL Server was installed...How do I get SQL Server to display all the other drives the node owns in the cluster? I can view them under My Computer and within the Cluster Administrator. Each node owns it's own drives. But I should be able to see all drives owned by the node when browsing to create a new database so I can assign different drives for the Data and Log files.
Any ideas would be helpful
Thanks
WarrenPerhaps you have to restart SQL Server?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Warren Tyler" <anonymous@.discussions.microsoft.com> wrote in message
news:B873E1EB-030A-41F4-910A-1F15FE1A2D7B@.microsoft.com...
> When I am in Enterprise Manager to create a new database, I click on the location of the data and
log file, I only see 1 drive where SQL Server was installed...How do I get SQL Server to display all
the other drives the node owns in the cluster? I can view them under My Computer and within the
Cluster Administrator. Each node owns it's own drives. But I should be able to see all drives
owned by the node when browsing to create a new database so I can assign different drives for the
Data and Log files..
> Any ideas would be helpful,
> Thanks,
> Warren|||Dear Tibor
I will try it and let you know
Thanks
Warren|||Dear Tibor,
Same result. Still can't see all the drives?|||Sorry, that was just a wild guess...
Re-reading your original post, you say that you only see the drive where SQL Server was installed.
Do you mean the local drive? That would imply, methinks, that you didn't install the SQL Server as a
clustered SQL Server. SQL Server should not show the local drives when creating databases etc, it
should only show the "shared" drives.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Warren Tyler" <anonymous@.discussions.microsoft.com> wrote in message
news:7B544C67-CF57-4D8B-B726-3C259486A975@.microsoft.com...
> Dear Tibor,
> Same result. Still can't see all the drives?|||I found the answer. After a successful install of SQL Server on the cluster and testing fail-over. You have to take one node off-line and add dependencies (IE. the other drives) to the SQl Server , bring that node back on-line and repeat for the other node. This should be your last step then, re-test failover and moving the cluster between nodes
Whew
Thanks
Warren

Can't see all drives in a cluster within Enterprise manager

When I am in Enterprise Manager to create a new database, I click on the loc
ation of the data and log file, I only see 1 drive where SQL Server was inst
alled...How do I get SQL Server to display all the other drives the node own
s in the cluster? I can vi
ew them under My Computer and within the Cluster Administrator. Each node o
wns it's own drives. But I should be able to see all drives owned by the no
de when browsing to create a new database so I can assign different drives f
or the Data and Log files..
Any ideas would be helpful,
Thanks,
WarrenPerhaps you have to restart SQL Server?
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Warren Tyler" <anonymous@.discussions.microsoft.com> wrote in message
news:B873E1EB-030A-41F4-910A-1F15FE1A2D7B@.microsoft.com...

quote:

---

> When I am in Enterprise Manager to create a new database, I click on the location of the d
ata and

---

log file, I only see 1 drive where SQL Server was installed...How do I get S
QL Server to display all
the other drives the node owns in the cluster? I can view them under My Com
puter and within the
Cluster Administrator. Each node owns it's own drives. But I should be abl
e to see all drives
owned by the node when browsing to create a new database so I can assign dif
ferent drives for the
Data and Log files..

quote:

---

> Any ideas would be helpful,
> Thanks,
> Warren

---

|||Dear Tibor,
I will try it and let you know.
Thanks,
Warren|||Dear Tibor,
Same result. Still can't see all the drives?|||Sorry, that was just a wild guess...
Re-reading your original post, you say that you only see the drive where SQL
Server was installed.
Do you mean the local drive? That would imply, methinks, that you didn't ins
tall the SQL Server as a
clustered SQL Server. SQL Server should not show the local drives when creat
ing databases etc, it
should only show the "shared" drives.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Warren Tyler" <anonymous@.discussions.microsoft.com> wrote in message
news:7B544C67-CF57-4D8B-B726-3C259486A975@.microsoft.com...

quote:

---

> Dear Tibor,
> Same result. Still can't see all the drives?

---

|||I found the answer. After a successful install of SQL Server on the cluster
and testing fail-over. You have to take one node off-line and add dependen
cies (IE. the other drives) to the SQl Server , bring that node back on-line
and repeat for the other n
ode. This should be your last step then, re-test failover and moving the cl
uster between nodes.
Whew!
Thanks,
Warren