SQLTeam.com | Weblogs | Forums

Problems with the size of LDF File


#1

Hi Everyone.
I currently have the following situation to which I require to give immediate solution.

I have a SQL Server 2008 R2 (Microsoft SQL Server Enterprise Edition (64-bit) Version 10.50.1617.0, which is installed on a Windows 2008R2 (Microsoft Windows NT 6.1 (7601)) NT x64 server.

at the request of the CIO, replication is enabled for each transaction of the database on another server (physical) distant from the original server.

Once this feature of SQL enabled, the LDF file database grows unusually, with the feature to reach more than 250Gb stopping the SQL and generating a latency connections to the SQL server.

The manager of the database (DBA) is an external officer in the company, and it was he who set up the SQL server to perform this Copy (Mirror System or Replica).

My questions are:

1.) I can shrink the LDF file with a procedure performed by a scheduled task database, which takes place in the early morning hours when there is no connection?

2.) What risks arise with this alternative?

3.) I use the command:
CHECKPOINT;
BACKUP LOG WITH TRUNCATE_ONLY Xxxxxxxx;
DBCC SHRINKFILE (N'Xxxxxxxxx_log ', 1)

4.) How could undo a failure of this procedure on the LDF file, what if this file is corrupted or damaged? I lose my original BD of my system?

I thank all those experts on similar issues in SQLSERVER and those with experience, with some indication of the best mechanism to effect the reduction in the size of the LDF file.

I have little knowledge on this subject and I regret not being more explicit.

Thank you.


#2
  1. You can, however we wouldn't recommend it. You instead need to setup regular LOG backups, such as every 5 minutes. Do a one-time shrink after the backups are setup.

#3

Also, shrink it to 0 for the one time shrink and grow it to a reasonable size so that you don't suffer a gazillion growths which really causes the VLFs in the log file to multiply like rabbits and really slows things down especially during a restore. It should be pregrown in slices not to exceed 8GB at a time or you get too few VLFs.

Also, make sure the growth factor on the LDF file is set a reasonably large number of MB and never a percentage.