HELP--still cannot shrink the transaction log--PLEASE HELP

Hello:
This is an update to my other posting. I know that it is unorthodox to post
twice but I don't know what else to do.
Below is my original posting. I had already done the items posted by the
other gentleman such as reivewing the sysfiles. I cannot get the logical
file name from anywhere either.
Again, please help! Should I just not shrink the file. The client only has
1.82 GB of free space left and there is nothing else that can be deleted.
Below is my message:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message sayin
g
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sNevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"childofthe1980s" wrote:

> Hello:
> This is an update to my other posting. I know that it is unorthodox to po
st
> twice but I don't know what else to do.
> Below is my original posting. I had already done the items posted by the
> other gentleman such as reivewing the sysfiles. I cannot get the logical
> file name from anywhere either.
> Again, please help! Should I just not shrink the file. The client only h
as
> 1.82 GB of free space left and there is nothing else that can be deleted.
> Below is my message:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in que
ry
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message say
ing
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>|||> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
The documentation clearly states that you can use *either* the logical filen
ame or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the
logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *
logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical file
name. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usag
e of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles W
HERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles W
HERE groupid = 0
USE master
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:868E9C82-C2C4-4151-BBBD-40741201E676@.microsoft.com...[vbcol=seagreen]
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
> "childofthe1980s" wrote:
>|||you were getting that error because you were running it in the master
database, not the databse you wanted to shrink the log for. the
documentation's correct.
"childofthe1980s" wrote:

> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in que
ry
> analyzer in SQL 2000 against the master database.
>|||Tibor Karaszi wrote:
> The documentation clearly states that you can use *either* the logical
> filename or the file id. The documentation is correct. The command you
> posted had 'GPSJCLog.ldf' for the logical filename, and I guess that you
> by mistake put the *physical* filename there instead of the *logical*
> filename. If you show ut the output from below, we can say for sure:
> SELECT name, filename FROM sysfiles
> I have shrunk database files occasionally and I always used the logical
> filename. In addition, here's a script (executed on my SQL Server 2000)
> which clearly show the usage of the *logical* filename:
> USE master
> CREATE DATABASE myTestShrink ON PRIMARY
> (NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
> ,SIZE = 1024KB , FILEGROWTH = 10%)
> LOG ON
> (NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
> , SIZE = 1024KB , FILEGROWTH = 10%)
> GO
> --Increase log file size
> BACKUP DATABASE myTestShrink TO DISK = 'nul'
> CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
> INSERT INTO myTestShrink..test(c2)
> SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
> GO
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> --Empty the log
> BACKUP LOG myTestShrink WITH NO_LOG
> --Check the logical and physical files names
> SELECT name, filename FROM myTestShrink..sysfiles
> --Perform the shrink, ***based on logical filename***
> USE myTestShrink
> GO
> DBCC SHRINKFILE('myTestShrink_log', 2)
> --Check log file size
> SELECT name, size*8192/(1024*1024) AS SizeInMb FROM
> myTestShrink..sysfiles WHERE groupid = 0
> USE master
>
...just to add to Tibor's response. You can retreive the logical
filename by running sp_helpdb 'YourDatabaseName'.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||No, I did all different combinations--with ldf, without ldf, against the
master, against the database, and nothing was working until I placed the
database fileID of "2" in the suntax.
So, yes the documentation needs updating!
Thank you to all who helped!
childofthe1980s
"thomarse" wrote:

> you were getting that error because you were running it in the master
> database, not the databse you wanted to shrink the log for. the
> documentation's correct.
> "childofthe1980s" wrote:
>
>|||childofthe1980s wrote:
> No, I did all different combinations--with ldf, without ldf, against the
> master, against the database, and nothing was working until I placed the
> database fileID of "2" in the suntax.
> So, yes the documentation needs updating!
>
I think you need to show up the documentation that's wrong. If you look
in BOL, the syntax is :
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
where file_name and file_id is explained as
"file_name
Is the logical name of the file shrunk. File names must conform to the
rules for identifiers. For more information, see Using Identifiers. "
"file_id
Is the identification (ID) number of the file to be shrunk. To obtain a
file ID, use the FILE_ID function or search sysfiles in the current
database."
I only see that it says you can use the LOGICAL filename or the fileID.
I don't see it mentions the physical filename anywhere?
Regards
Steen Schlüter Persson
Database Administrator / System Administrator