IAmTheEggman
asked on
How to shrink the log file
Greetings,
Can anyone advise me how to shrink the log file of a SQL Server database?
I have tried to follow the Microsoft article 272318 "Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE" but to no avail.
When I run the code:
BACKUP LOG Projects WITH TRUNCATE_ONLY
the system tells me that the command has been run successfully but the log file size does not change. Then if I run the code
DBCC SHRINKFILE(Projects_log,2)
I get the error message:
"Server: Msg 8985, Level 16, State 1, Line 2
Could not locate file 'Projects_log' in sysfiles."
Can anyone please help?
Can anyone advise me how to shrink the log file of a SQL Server database?
I have tried to follow the Microsoft article 272318 "Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE" but to no avail.
When I run the code:
BACKUP LOG Projects WITH TRUNCATE_ONLY
the system tells me that the command has been run successfully but the log file size does not change. Then if I run the code
DBCC SHRINKFILE(Projects_log,2)
I get the error message:
"Server: Msg 8985, Level 16, State 1, Line 2
Could not locate file 'Projects_log' in sysfiles."
Can anyone please help?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is not for u, but others who have same problem in future...
http://support.microsoft.com/default.aspx?kbid=272318
http://support.microsoft.com/kb/256650/EN-US/
itsvtk
http://support.microsoft.com/default.aspx?kbid=272318
http://support.microsoft.com/kb/256650/EN-US/
itsvtk
itsvtk seems to describe what you already did. The commands both of you used will truncate your log file ONLY if your database recovery mode is set to SIMPLE. Otherwise, any backup will only reduce the log size to the last checkpoint (MinLSN). If you want more details about this, see Books Online (BOL). Find the BACKUP LOG index entry and then select 'truncating the transaction log' topic. You can check this option in Enterprise Manager, DB > Properties > Options > Recovery Model.
see also https://www.experts-exchange.com/questions/20651271/How-do-i-truncate-a-log-file-manually-in-sql-7.html
see also https://www.experts-exchange.com/questions/20651271/How-do-i-truncate-a-log-file-manually-in-sql-7.html
Hi IAmTheEggman,
I believe you need to run the DBCC SHRINKFILE from within the database you are shrinking, this may be the problem with not finding the file (this is as itsvtk mentioned). The below should confirm the name you need to use with the dbcc command:
exec sp_helpdb Projects
I believe you need to run the DBCC SHRINKFILE from within the database you are shrinking, this may be the problem with not finding the file (this is as itsvtk mentioned). The below should confirm the name you need to use with the dbcc command:
exec sp_helpdb Projects
ASKER
Many thanks itsvtk, your solution worked just fine.
I appreciate the swift response.
I appreciate the swift response.
itsvtk