Solved

SQL Server 2005 Log file wont shrink

Posted on 2010-11-24
18
689 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
18 Comments
 
LVL 143

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

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 143

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 143

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 143

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 143

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 143

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 143

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Error - Query 6 50
how to restore or keep sql2000  backups useful... 2 28
SQL Server maintenance plan 8 54
partitioning database after decade growth 8 56
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

756 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