[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1484
  • Last Modified:

SQL 2000 Transaction Log and "DBCC SHRINKFILE"

Hi,

I've got a problem with an SQL Server in that the transaction log has grown too large and filled the disk.

As such, I performed a backup of the transaction logs, and then tried to use the DBCC SHRINKFILE T-SQL command to shrink the file.

However, when I run the command it immidiately returns "The command(s) completed successfully" and nothing happens to the file.

I tried to run it using SQL Server Management Studio 2005 from another machine in the domain, and the graphical interface here confirms that the log file is 97% free space, but again when I run it it says "Executing" for a few seconds, and completes without error, again without changing the size of the log file.

There is nothing in the EventLog that suggests an error either.

This is the T-SQL command I ran (though I have tried several other permutations): -
DBCC SHRINKFILE (N'MyDB_LOG.LDF', 0, TRUNCATEONLY)

What am I doing wrong???

Thanks in advance.
0
Safeserve
Asked:
Safeserve
1 Solution
 
Cedric_DCommented:
run CHECKPOINT stetement before shrinking.
then run DBCC SHRINKFILE (N'pacs_oltp_log1' , 1).

but these measures will not effect when you initially created too large logfile. In that case you should move log to new file and delete old file.

also, I can advice to change recovery model from full to simple to decrease Logfile growth.

0
 
SafeserveAuthor Commented:
Hi, thanks for the help.

No luck with CHECKPOINT either, but once I changed it to simple it shrank correctly.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now