Solved

SQL Server 2005 Log file wont shrink

Posted on 2010-11-24
18
685 Views
Last Modified: 2012-08-14
Hi,

We have a log file that was apparently always around 1gb in size, after an update to the system that uses the db the log file shot up to 17gb and now shrink.

Ive ran

DBCC OPENTRAN (RE7)  - (where RE7 is the name of DB)

and i get the following results.

Transaction information for database 'RE7'.

Replicated Transaction Information:
        Oldest distributed LSN     : (0:0:0)
        Oldest non-distributed LSN : (83521:309:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Does anyone have troubleshooting tips i can use to find out what is locking at this size? im guessings its a transaction that never completed?

Thanks

S
0
Comment
Question by:stebennettsjb
  • 9
  • 8
18 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34206450
>im guessings its a transaction that never completed?

exactly. see output of sp_who2 to eventually find the culprit
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 34206457
you've got a trans that hasn't went through your transactional replication.  Check your replication status.
0
 

Author Comment

by:stebennettsjb
ID: 34213489
Hi champ,

We dont run any replication, just to make things a bit weirder for me..

AngelIII

I ran sp_who2 and it only showed SPIDs from after the shutdown we had to do on this server a few weeks ago (I take in a perfect world where the machine had never been turned off since the problem i would see a SPID for the problem?) Or am I on completely the wrong track?

Thanks for the help

S

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34215560
what is the output of:
select * from ::fn_dblog(null,null) 

Open in new window

0
 

Author Comment

by:stebennettsjb
ID: 34229431
Hi AngelIII

I get a list of 295 results..

Ive heard of sp_who2 and looked at results of that but never heard of this one.. what does it do? google results for  a select statement arent very good :(

thanks for the help

s
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34229471
do you have some with "old" begin time
select * from ::fn_dblog(null,null) 
where [begin time] is not null
order by [begin time] asc

Open in new window

0
 

Author Comment

by:stebennettsjb
ID: 34229863
Hi AngelIII,

down to 59 results now...

All Begin Time are for today and yesterday. Database name is always NULL same with File status, physical name, logical name

thanks for the help

S
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34229906
can you cross-match that info with the output of dbcc opentran:

        Oldest non-distributed LSN : (83521:309:1)

A+
0
 

Author Comment

by:stebennettsjb
ID: 34230163
well helps if i run the query against the right database.

I used the script on a few dbs to see results, all of the queries gave me results in less than 1 second. Im currently at the 20min mark for the database with the problem..

Is that normal? as i said its a 17-18gb file the rest where 10gb at there largest.

S
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34230200
>well helps if i run the query against the right database.
:) happens to all of us


so, what is the output for both statements for the right database?
20 minute for the oldest transaction?

possibly you have some really large transaction in there, which could be:
* bad trigger
* bad sql
* injection sql
* ...

that blow up your transaction log ...
0
 

Author Comment

by:stebennettsjb
ID: 34230233
The query is still executing 25mins and no results.. on the other dbs i ran it against they all resolved and gave me answer in less than 1 second and 1 had 4k results.

There a point where i should stop it from running? or just leave it and eventually it finish.

thanks for all the help

s
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34230260
>or just leave it and eventually it finish.

seems like there is really an issue..
you should try to let it finish.

unless you want to take the database offline and terminating all connections to it, to see if that resolves the issue.
before that, I would however try to do a test backup/restore of the db to see if that works before playing around ...
0
 

Author Comment

by:stebennettsjb
ID: 34230650
Thanks Angel..

Well 1 hour 15mins still no results..

I moved the log file last week due to us having no room on the log drive any more (I wasn't aware that this file has shot up in size) i used the following to do it, This would have killed all connections to it wouldnt it?

Should i be looking into how to create a new log file for a database?

-- Find File locations and online status
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'RE7');

-- Use ALTER DATABASE COMMAND TO MOVE DATABASE FILES
-----------------------------------------------------
--ALTER DATABASE database_name SET OFFLINE (Stops all connections to the DB) SINGLE_USER WITH ROLLBACK  MMEDIATE
ALTER DATABASE RE7 SET OFFLINE

-- Move the file/s to there new location

-- ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' ) (User for each file that has been moved)
ALTER DATABASE RE7 MODIFY FILE ( NAME = DB_1_log, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RE7\RE7_log.ldf' )

-- Check move has completed

-- ALTER DATABASE TO ONLINE when move is completed
ALTER DATABASE RE7 SET ONLINE



0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34230665
yes, that seems correct ...
0
 

Author Comment

by:stebennettsjb
ID: 34230756
So 1hour 30 mins no results...

Do you think its worth deleting the Tlog and re-creating it? or any more tests you think i can do?

I think ill leave this running if its not completed by the time i get in tomorrow morning then its off to the delete?

Thanks again for all the help Angel.

S


0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34230784
>Do you think its worth deleting the Tlog and re-creating it? or any more tests you think i can do?
yes. you could detach the db, delete (rename) the ldf file, and reattach the db with only the data files...
0
 

Author Comment

by:stebennettsjb
ID: 34230830
Thanks Angel,

I'll give it till tomorrow like i said if its still not completed ill close the call and create a new log file.

Thanks again

S
0
 

Author Comment

by:stebennettsjb
ID: 34234867
well.....

it finished just short of 4 hours!

There are 3018437 records!

Not sure where to start on that.. apart from well thats clearly not good.......

I cant see a row with anything close to the Oldest non-distributed LSN : (83521:309:1)

Current LSN are all 00014844:0001xxxxx:xxxx
Transacion ID are all : 0000:0243xxxx
Previous LSN is always 0000000:0000000:0000
Oldest replicated begin LSN, Next replicated end lsn, last distrubuted backup end lsn, last dustrubuted end lsn are all NULL for ever record.

Not sure where to go from here and how bad it is to have over 3mill reecords from this, this is one of the smaller DBs we have, the largeest most used db had the 300 records...

Any ideas!?

Thanks as always

s
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question