Geek Answers Handbook

How to compress SQL Server log file with mirroring enabled?

I have several databases for my applications that use SQL Server 2005 mirroring to keep a good copy of the data elsewhere. It works like a charm, however, the log file just seems to grow and grow, each located at 15 GB for a 3 GB database.

Usually I can just compress it, but a pop-up message appears stating that this cannot be done. But it seems, in the long run, if untested will simply expand to use all the disk space.

I see that I can set the maximum file size for the log file, is this the answer here? Will the log just roll when it reaches its maximum, or will the database just stop functioning?

thanks

+5
source share
10 answers

We ran into the same problem after moving from log shipping to mirroring. You must create a task that regularly backs up the transaction log (every 15 or 30 minutes or so) to keep the log size out of control.

If it has already failed, run BACKUP LOG TO DISK = 'Nul', then run the DBCC SHRINKFILE command. Then you can customize your work.

Please note: "Nul" is not spelling, it is an old DOS trick that behaves as if you are writing a file, but actually just uploads the information to the air, so it does not take up space on the machine.

, , , . , .

EDIT: , . "nul" . , . , .

+3

http://support.microsoft.com/kb/937531

SQL Server 2005


.


1
, . DBCC SHRINKDATABASE DBCC SHRINKFILE.
[ ]

2
.

+1

40 , Db 700 . microsoft , 4%.

1-

   use master
   go
   if object_id ('sp_shrink_mirrored_database', 'P') is not null 
     drop proc sp_shrink_mirrored_database 
   go
   create procedure sp_shrink_mirrored_database @dbname sysname, @target_percent int = null
   as
   begin
     declare @filename sysname
     declare @filesize int
     declare @sql nvarchar(4000)

     if @target_percent is null
       dbcc shrinkdatabase (@dbname)
     else 
       dbcc shrinkdatabase (@dbname, @target_percent)
     declare c cursor for 
     select [name], [size] from sys.master_files where type=0 and database_id = db_id (@dbname)
     open c
     fetch next from c into @filename, @filesize
     while @@fetch_status=0
     begin
       set @filesize=(@filesize+1)*8
       set @sql='alter database [' + @dbname + '] modify file ( name=' 
         + @filename + ', size=' + cast(@filesize as nvarchar) + 'kb )'
       execute sp_executesql @sql
       fetch next from c into @filename, @filesize
     end
     close c
     deallocate c
   end
   go

2- , , , mydb, .

  EXEC sp_shrink_mirrored_database 'mydb'
+1

, ? , . , , , .

0

, ,

0

USE DBNAME
GO
DBCC SHRINKFILE(DBNAME_log, 1)
BACKUP LOG DBNAME WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBNAME_log, 1)
GO
0

Be sure to make a full backup after backing up the log using TRUNCATE_ONLY. This breaks the log backup chain.

-1
source

More articles:


All Articles