Transaction log same size as database

Question

Transaction log same size as database

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Ray Mond SSCertifiable Points: 7159 More actions · Answer 1

You no longer need to create dummy records to move the location of the active transaction. See here for details (applies to SQL Server 2005 and 2008), and here for a worked example.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 2

Thanks Ray, very informational. The second link didn't work for me.

Ray Mond SSCertifiable Points: 7159 More actions · Answer 3

My bad. .com went missing. Thanks for pointing that out.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 4

Since my db is in Simple Recovery mode, I cannot backup the trans log. I guess I'll substitue db backups...but I wonder if it will have the same affect?

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 5

Ok, so I ran the following:

--DBCC LOGINFO ( )

--BACKUP LOG WITH TRUNCATE_ONLY

--DBCC LOGINFO ( )

--DBCC SHRINKFILE( , 8192)

--DBCC LOGINFO ( )

All times the loginfo remained the same. I'm attaching.....

(I was going to run a b/u of the db, but that runs every night and hasn't changed anything)

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 6

Ok, think I found the problem (or lack there of!)

This just wasn't making sense why the file would not reduce! There is the C: drive, a separate data drive, and a swap drive. On the data drive there are folders for sql backups/data/logs/reports. I was just assuming that since there was a log file present in that logs folder, that was the live log. Well, all this made me go to the properties of the database to be sure where the file resides. To my astonishment, it was set to C:\programfiles\MS Sql Server...etc!!! Apparently it was changed over a year ago and the old log was never removed. Maybe if I wouldn't looked at the modified date, that would have been a dead give-away.

So my log is actually 586MB currently. I am very happy with that! I will be removing the old log now, don't believe it is needed any longer!

Thanks to all for your help. I did learn a lot about trans logs!