Solved

SQL Server 2005 Log file wont shrink

Posted on 2010-11-24
18
679 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now