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 saying
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 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 saying
> 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 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
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:868E9C82-C2C4-4151-BBBD-40741201E676@.microsoft.com...
> 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:
>> 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 saying
>> that the file name is not in sysfiles.
>> This is what I have tried:
>> DBCC SHRINKFILE (GPJClog.ldf , 2)
>> Please help!
>> childofthe1980s|||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 query
> analyzer in SQL 2000 against the master database.
>|||Tibor Karaszi wrote:
>> 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
> 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:
> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> > analyzer in SQL 2000 against the master database.
> >
>|||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
Showing posts with label original. Show all posts
Showing posts with label original. Show all posts
Friday, March 9, 2012
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
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
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 saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980s
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:
> 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 saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>
|||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 query
> analyzer in SQL 2000 against the master database.
>
|||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:
>
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 saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980s
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:
> 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 saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s
>
|||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 query
> analyzer in SQL 2000 against the master database.
>
|||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:
>
Wednesday, March 7, 2012
Help:MS SQL 2000 SP4 dont run !
I have installed MS SQL 2000 Personal Edition on XP SP2 and It works !
I verified its version :
SQL Server 2000 Original Release 8.00.194 RTM
When I try to install SP4 after extracting locally I see the first windows for a moment and then nothing appens !
x86\setup\setupsql.exe process remain in task manager without make nothing.
Someone can help me ?Check windows event log & SQL error lgo for any information.
Also try to download the file again, make sure you're using correct SP4 file from MS.|||Thank you !
I have found only this warning in eventlog
SuperSocket info: (SpnRegister) : Error 1355.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
But I think it's because I have no SP4 installed .
When I try to install SP4 I have no message in any logs.
Process do nothing because, I suppose , It's waiting for a Dialog Window that I don't see :-(|||There was a problem on the Clustered environment previously as per KBA http://support.microsoft.com/?id=303411 , but yours it not that case. Anyway try downloading SP3a for SQL 2000 and apply, then use SP4.
BTW mention the filename of SP4 you're using to apply.|||I have the same problem with SP3.
File name is SQL2000-KB884525-SP4-x86-ENU.EXE
May be MSI ? I have 3.1
Or IIS ? I have 5.1
How can I remove IIS ?
I verified its version :
SQL Server 2000 Original Release 8.00.194 RTM
When I try to install SP4 after extracting locally I see the first windows for a moment and then nothing appens !
x86\setup\setupsql.exe process remain in task manager without make nothing.
Someone can help me ?Check windows event log & SQL error lgo for any information.
Also try to download the file again, make sure you're using correct SP4 file from MS.|||Thank you !
I have found only this warning in eventlog
SuperSocket info: (SpnRegister) : Error 1355.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
But I think it's because I have no SP4 installed .
When I try to install SP4 I have no message in any logs.
Process do nothing because, I suppose , It's waiting for a Dialog Window that I don't see :-(|||There was a problem on the Clustered environment previously as per KBA http://support.microsoft.com/?id=303411 , but yours it not that case. Anyway try downloading SP3a for SQL 2000 and apply, then use SP4.
BTW mention the filename of SP4 you're using to apply.|||I have the same problem with SP3.
File name is SQL2000-KB884525-SP4-x86-ENU.EXE
May be MSI ? I have 3.1
Or IIS ? I have 5.1
How can I remove IIS ?
Subscribe to:
Posts (Atom)