hi

In our Production the SQL-Server 2005. One of the Database LDF has been increased to 120GB. Which is using 75% of Drive Space.

Please kindly suggest the process to Shrink or remove or compress or Decrease its size. 

Any Documents of Steps or Tutorial to close this issue.

Thanks

Naveen

Hello Naveen,

Which recovery mode do your database have, Full or Simple. If it's full, do you run log backup and how often?

have you check log_reuse_wait_desc

select name, log_reuse_wait_desc
from sys.databases

use dbname 

go

DBCC SHRINKFILE (2,100) 

BUT, before , run DBCC LOGINFO(dbid) and show us the output

Hi Olaf

thanks for the reply.

The Database Recovery Mode is Simple.

We did not run any Log Backup.

Output:

187 worktable 2015-02-03 00:45:25.717 2 NULL 2 0 0 0 0 0
191 worktable 2015-02-03 00:45:25.717 2 NULL 2 0 0 0 0 0
193 worktable 2015-02-03 00:45:25.717 2 NULL 2 0 0 0 0 0
195 worktable 2015-02-03 00:45:25.717 2 NULL 2 0 0 0 0 0
199 worktable 2015-02-03 00:45:25.727 2 NULL 2 0 0 0 0 0
210 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
211 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
217 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
222 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
225 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
229 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
232 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
233 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
237 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
240 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
241 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
243 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
245 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
247 worktable 2015-02-03 00:45:25.790 2 NULL 2 0 0 0 0 0
5376498 SELECT 2015-02-04 13:44:25.190 2 NULL 2 0 0 0 0 0

hi

Field	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	65470464	8192	1765	2	64	0
2	65470464	65478656	1764	2	128	0
2	65470464	130949120	1766	2	64	0
2	65470464	196419584	1767	2	64	0
2	65470464	261890048	1768	2	64	0
2	65470464	327360512	1769	2	64	0
2	65470464	392830976	1770	2	64	0
2	65986560	458301440	1771	2	64	0
2	13107200	524288000	1772	2	64	1771000012602400000
2	13107200	537395200	1773	2	64	1771000012602400000
2	13107200	550502400	1774	2	64	1771000012602400000
2	13107200	563609600	1775	2	64	1771000012602400000
2	13107200	576716800	1776	2	64	1775000002437500000
2	13107200	589824000	1777	2	64	1775000002437500000
2	13107200	602931200	1778	2	64	1775000002437500000
2	13107200	616038400	1779	2	64	1775000002437500000
2	13107200	629145600	1780	2	64	1779000002433800000
2	13107200	642252800	1781	2	64	1779000002433800000
2	13107200	655360000	1782	2	64	1779000002433800000

the ahove is stating

below are the endin glines

2	,13107200	,129631518720	,11626	,2,	64	,11622000002434500000
2	13107200	129644625920	11627	2	64	11626000002348400000
2	13107200	129657733120	11628	2	64	11626000002348400000
2	13107200	129670840320	11629	2	64	11626000002348400000
2	13107200	129683947520	11630	2	64	11626000002348400000
2	13107200	129697054720	11631	2	64	11630000002245900000
2	13107200	129710161920	11632	2	64	11630000002245900000
2	13107200	129723269120	11633	2	64	11630000002245900000
2	13107200	129736376320	11634	2	64	11630000002245900000

like the above there are 9871 lines of output for the query.

thanks

Even we have executed the Shrink 

DBCC Shrink...

but it did not change anything or reduce anything on the physical file size of LDF.

Thanks

SHRINKFILE command is not advisable as your valuable log data will be lost and won't be able to restore point in time.

If your database is in full recovery mode that means you intend to recover your database to a point in time in the event of a failure.

The log file will grow until transaction log backup is taken.

You use this command only if you are in urgent situation and losing your transaction log is doesn't matter, Also shouldn't use this command as regular maintenance job

My suggestion is don't shrink the valuable log file just do the proper maintenance on your database.

Thanks.

Yep, in my opinion you have two options

1. Ser the db to FULL recovery model, backup the database + backup the log file and then issue dbcc shrinkfile

2. Run some dummy inserts on the database  and see that values under status column will be 0 then you will be able to shrink the log file

master NOTHING
tempdb NOTHING
model NOTHING
msdb NOTHING
ReportServer NOTHING
ReportServerTempDB NOTHING
PS_CRP NOTHING
PS_DEV NOTHING
PS_DV811 NOTHING
PS_PD811 NOTHING
PS_PRIST811 NOTHING
PS_PROD REPLICATION
PS_PS811 NOTHING
PS_PY811 NOTHING
PS811 NOTHING
PS_LEB811 NOTHING
PS_LEB NOTHING
DEVSAVE NOTHING
LEBDTA13 NOTHING
TEMPORARY NOTHING
imp_jde_ind NOTHING

Here it is showing as Replication.

We dont have any Replication. How to close this Replication.

Thanks

The Database Recovery Mode is Simple.

If your database is in simple recovery, and your transaction log is 120 GB, this is because you need a 120 GB log file. Shrinking it will not be of use, because it will grow again. And growing a log file will cause outages, because the space has to be zeroed out.

Possibly you could spend time on identifying the operations that requuires this much log space. Apparently you are running very large transactions, that maybe should be split up in batches.

But finding these operations and then rework them will cost manhours, and it is not unlikely that it is cheaper to get more disk space.

Hi

By default, in the simple recovery mode the inactive portion of transaction log to the oldest open transaction will be automatically truncated after each database checkpoint.  

If you run transactional replication on this database and therefore the inactive portion of transaction log will be not able to truncate due to old transactions that are marked for replication. If this is the case, please check the log reader agent whther it is running or any errors in the agent logs.

Thanks

--------------------------------------------------------------------------------------------------

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

>>SHRINKFILE command is not advisable as your valuable log data will be lost and won't be able to restore point >>>in time.

The database is set to SIMPLE recovery model.

Hi SequelMate

Thanks for the Reply.

Please kindly help me how to start this Log reader Agent.

Thanks

Hi Naveendp,

PS_PROD REPLICATION

is this database that you are working to reduce the transaction log?

Could you run this query and post the output?

select name,is_published, is_subscribed, is_merge_published,is_distributor, log_reuse_wait_desc from sys.databases where name = 'PS_PROD'

Unless replication is synchronised, you wont be able to shrink the log no matter what you do.

Check this - http://msdn.microsoft.com/en-gb/library/ms190925.aspx#FactorsThatDelayTruncati

SHRINKFILE command is not advisable as your valuable log data will be lost and won't be able to restore point in time.

Aung Mon chit

Please note that shrinking of both data and log file CAN NEVER CAUSE DATA LOSS as it just tries to cut out  free space from the file. I agree to your suggestion of not shrinking the files on regular basis.

Naveen,

You need to know your system and database. If Log is waiting on replicated transaction to be reused you need to look into that as asked by Ashwin

Hi Ashwmin

PS_PROD 0 0 0 0 REPLICATION

We got the above output.

Hi Ashwin

At present we dont have any replication going on. 

May be the old they might have done and dropped the Replication Process.

We need to clear this and then compress the Log.

Kindly guide,

Thanks

Naveen

In SSMS when you expand Replication -> Local Publications and Local Subscriptions what do you get? Do you see any records?

Do you know if replication was previously enabled and then removed?

Hi Ashwin

There is nothing under the expand of Replication.

Thanks

Hi

I feel once they tried for the replication and because of the Server running slow cancelled the process about 3 years back for 2 or 3 tables they tried for the replication.

Could you run dbcc opentran against the database?

Follow this blog

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx

Also it would be good to run the below command to make sure all the traces of replication in the database is removed.

sp_removedbreplication 'PS_PROD'

Once these are done, you should be able to shrink and tlog should not grow unnecess

DBCC OPENTRAN('PS_PROD')

Transaction information for database 'PS_PROD'.

Replicated Transaction Information:
        Oldest distributed LSN     : (1764:47897:73)
        Oldest non-distributed LSN : (1764:47909:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Please follow my earlier post, you can drop replication related information using the query I gave or follow the blog. I prefer to first follow blog and then drop replication info so that in future there wont be any issues.

Ashwin,

I thought the following steps to be done

1. EXEC sp_repldone null, null, 0,0,1 

2 . EXEC SP_removedbreplication 'PS_PROD'

3.  Shrink the Log file from GUI.

So i just to be safe side.... i would like to take the backup of the Database and Transaction Log.

Please kindly suggest me the process steps which need to be then i will go ahead.

Thanks

Yes, because the log file contains a 'active' transactions (status =2), you need to 'move' them on the top of the list.

Hi Uri Dimant

What you suggest me...

Hi Naveen

Yes the below steps seems to be fine. Yes please take a backup just to be on safe side. Step 1 should be enough normally to be able to help you shrink.

We are doing Step 2 just to ensure that we dont just leave any replication bits which should have been removed properly previously.

Hi

If you're using the simple recovery model, then just clear the transaction log by running a checkpoint.

 BACKUP LOG databasename TO devicename

2. Shrink the log to as small a size as possible (truncateonly)

DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)

NOTE: if you don't know the logical filename of your transaction log use sp_helpfile to list all of your database files.

3. Alter the database to modify the transaction log file to the appropriate size in one step

ALTER DATABASE databasename
MODIFY FILE 
( 
      NAME = transactionloglogicalfilename 
    , SIZE = newtotalsize
)

NOTE: Depending on the total size desired, you might want to break this into multiple chunks. 

  More Help Click Here

Thanks

--------------------------------------------------------------------------------------------------

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

I posted above a two options you have tom reduce the log file size 

1. Set the db to FULL recovery model, backup the database + backup the log file and then issue dbcc shrinkfile

2. Run some dummy inserts on the database  and see that values under status column will be 0 then you will be able to shrink the log file

 EXEC SP_removedbreplication 'PS_PROD'

Its giving error 

Msg 9002, Level 17, State 6, Procedure sp_MScleandbobjectsforreplication, Line 21
The transaction log for database 'PS_PROD' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

If you completed the first step, you can try to shrink the log file. Do you see space in the log file? Once the first step is done, what does the log_reuse_wait_desc show now for this database?

This step SP_removedbreplication can be also done later once everything is normal.

hi

not able to do even the first step its giving as no 

its giving below error

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.

Did you run it against the database PS_PROD?

I assume log_reuse_wait_desc still gives replication for this database?

If that is the case, you need to temporarily increase the space on the disk and increase the log file size and then run the sp_removedbreplication. Once that is done, you should be able to shrink the file.

Ashwin,

I thought the following steps to be done

1. EXEC sp_repldone null, null, 0,0,1 

2 . EXEC SP_removedbreplication 'PS_PROD'

3.  Shrink the Log file from GUI.

So i just to be safe side.... i would like to take the backup of the Database and Transaction Log.

Please kindly suggest me the process steps which need to be then i will go ahead.

Thanks

• Marked as answer by Naveen_T 1 hour 15 minutes ago

hi Ashwin

Ok we will try as you said..

Thanks, I overlooked.

Thanks for sharing.

Hi Ashwin

thanks for the support and Guidance . Our Problem got resolved. Thanks Alot.



Steps followed:

1. So i just to be safe side.... i would like to take the backup of the Database and Transaction Log.
   -> Even the Backup was not been executed to we took the MDF & LDF manually copied to other place.

2. EXEC sp_repldone null, null, 0,0,1  
    - > But this gave error as not publisher or Distributor exists.

3 . EXEC SP_removedbreplication 'PS_PROD' 
-> When we executed this still it was giving error as per your reply we created more space for execution in the Drive and moved   some folder and files and created a 25 GB free space. Then this Procedure started executing it took about 25 Mins. While this   was going on LDF file started to increase but ok it completed successfuly.
4.  select name, log_reuse_wait_desc from sys.databases 
        -> Executed the Above Query and it showed all the databases as NoTHING. 

3.  Shrink the Log file from GUI. 
        -> Then we executed Shrinking the LOG from GUI and with in seconds the total 120GB of ldf shrink into 500 MB.

Please kindly reply to my mail in your hotmail.com


Thanks 

Naveen