We help IT Professionals succeed at work.

MSSQL 2000 - 88gb "transactions" file - must shrink / reduce size.  How??

1,113 Views
Last Modified: 2012-06-27
Good morning.  I am attempting to manage a remote MSSQL 2000 Server (via remote desktop) and am noticing that there is a 88 GB "transactions" file located in the root of my \BACKUP folder.  

I've done some research on this, and all I seem to be able to find is information about how to shrink transaction log files.  I'm uncertain as to whether these transaction log files (which have .TRN extensions) have anything at all to do with the file I'm referring to (called "transactions" with no file extension - and which is friggin' gigantic by comparison), and if the same tips/instructions apply.  

Anways, I absolutely must find a way to reduce the size of this 88 GB "transactions" file somehow.  Is that at all possible, and if so, .. how?  Are there any possible negative consequences of doing this?  

Thanks,
- Yvan
Comment
Watch Question

Commented:
Transactions could be a transaction log file, or a file used by the application.

You can use enterprise manager to go through your databases and view where each one keeps it's transaction log file to check. (Right click a db->properties->transaction log).

If it is a db transaction log, you have options available for backing it up (to save it) or truncating it (to bin it). Once "emptied" you can shrink the file.

At my last job we used:

BACKUP LOG WITH TRUNCATE_ONLY

FOllowed by DBCC_SHRINKFILE

Commented:
Bear in mind truncate_only is the option to discard a transaction log - only do this if you don't need it

Otherwise those are the commands you need

Author

Commented:
>> BACKUP LOG WITH TRUNCATE_ONLY
>> FOllowed by DBCC_SHRINKFILE

You see, that's the same info I keep coming across whenever I do searches for "MSSQL transactions file". And in all the examples I've found, a specific database name is always referenced.  

Note this specific comment in my orignial inquiry:

I'm uncertain as to whether these transaction log files (which have .TRN extensions) have anything at all to do with the file I'm referring to (called "transactions" with no file extension - and which is friggin' gigantic by comparison), and if the same tips/instructions apply.  

Are you suggesting that if I go through and apply these commands to each of the MSSQL databases individually, that this will reduce the size of the 88 GB "transactions" file that's located in the root of my BACKUP folder?  Or will it only shrink the size of the the smaller, individual transaction log files in each of the backup folders (.TRN extension) ?  I've very confused about this.   Please advise.

- Yvan


 
I would suggest first finding out what kind of file this "transactions" is.  I'm not sure if its an application file or a log file.  Browse to the file through file explorer and check the properties of the file...that should give you the extension.

In regards to your question about databases, yes, the commands I provided will work for individual databases and ned to be run individually for each one.  At my last job we had a scheduled task that ran everynight and took care of this.

Author

Commented:
I'm not sure what kind of file it is either, which is why I'm asking. checking the file properties in explorer doesn't show me the extension, or offer any clues as to what the file might contain.  And I'm definitely going to double-click on it, or attempt to open it up in notepad.  

See what I mean, now?  What the hell is this "transactions" file?  
No one in the whole world seems to know what it is.  

- Yvan

Author

Commented:
I  meant NOT double-click on it or attempt to open it up in notepad, btw.  
Hi,

Here is the answer:

First a quick primer on SQL files.
.MDF files are typically the primary SQL database files
.NDF files are typically the secondary SQL database files
.LDF files are typically SQL log files
.BAK files are typically SQL database backup files
.TRN files are typically SQL transaction log backup files

Now the solution:
Based on the location you refer to above and the extension name you state, I would guess the files in question are SQL transaction log backup files. These files can be safely deleted as long you ensure you have a known good backup at the time you delete the .TRN files. IE, if the .TRN files are from Jan 1st to June 1st and you have a known good backup from June 2nd, there is no real need to keep the .TRN files from anytime prior to June 2nd.

To be safe and build in as much redundancy as possible I usually allow myself a few days or maybe even a week of overlap for .TRN and .BAK files, but nothing more than that. If you ever get a single very large .TRN you can always schedule a quick backup of the database itself and once it completes, test it, and then blow away the .TRN.

Author

Commented:
I'm not sure exactly what's getting lost in translation here.  The file I am referring to has NO FILE EXTENSION .. and is NOT located alongside BAK or TRN files. The file is located in my main BACKUP folder, and there is ONLY ONE FILE, .. which is now 94 GB in size.

What is this file named "transactions" (which - again - has NO FILE EXTENSION) and what do I need to do in order to reduce the file size? The file keeps growing more and more every day and I need to find a way to control it somehow. For starters, .. I need to somehow (and safely) reduce the size significantly.

- Yvan

Ok, if this is indeed a file that is still growing. I see that it can be one of two things.

1. SQL Transaction Log, attached to the database. You can verify this by opening Enterprise Manager and selecting your database. Right click, select properites, and review the "Transaction Log" tab. Verify whether or not it points to the location in question.

2. SQL Transaction Log Backup that is being appended to on a regular basis. The way to verify this woudl be to check your SQL jobs and Maintenance plans to see if any of them are setup to "backup" transaction logs (using append).

Please see if either of these apply. If so we can talk about how to rectify.

Author

Commented:
To be clear, . there are multiple MSSQL databases hosted on this server, ... and I already know where all the individual backup files are located (BAK and TRN files).  For that reason, .. I'm thinking that #2 (above) might be more likely.  Although -- how on earth can it be possible to have a 94 GB log file?

All of our MSSQL databases are set to have nightly backups (via maintenace plans). Some of the backup sets include TRN files, while other's don't.  I can manage these databases with full administrative privileges via Enterprise Manager, or I can remote desktop into the server.

So what is the next step that you suggest?

- Yvan
Item #2 would only be true if you have a transaction log backup set that is configured to append instead of initialize a new file. If that is the case, the easiest thing to would be to see if you can rename the file in question. If you can, it's not a SQL data or log file and you can then move it, compress it, or just delete it once you have a full system backup.

If you cant it delete it, youll need to get the file list for each file in each DB. If thats the case let me know and Ill throw you script to do this.

Author

Commented:
Okay, .. well it seems that I CAN rename the file without any problems.  I'm tempted to just delete it, .. but I'll refrain from being trigger happy until we've finished our discussion here :)

So what would you say constitutes a "full system backup"? If I have BAK and TRN files for each of my MSSQL databases for the last 7 days, .. should that suffice?  Or are there other files that I should also back up?

Assuming that it is in fact safe for me to just delete the 94 GB "transactions" file, .. will SQL server just create a new file (which I'm assuming is a LOG file) the next time the databases are backed up?  

Thanks,
 -Yvan
Hi,

That is good news! We only have a few steps to go. I'll try to list them in order, but you should be good now.

1. You should first move the file off your SQL server, perhaps to a backup server, just in case we need it later. I highly doubt it, but no harm in being safe.
2. Go ahead and take a look at the directory where you renamed the original file. I bet you see a new much smaller file with a the same name as the original and a current date and time. It may take some time, but if so that confirms it's a log backup that is appending instead of creating new files. If not, you've stumped me on this particular file.
3. Next is a full system backup. That consists of a backup of "master", "model", and "msdb". Then a backup of all your "user" databases.
4. Once you have your user database, I would hang onto the other file for about 48 hours (or at least two more regular backup cycles) so you ensure you have a good backup.
5. Finally delete the file.

Now, it's probably safe to delete the file right this minute, I'm highly confident it's a transaction log backup and once you have a database backup like in step #3 it really serves you no purpose, but step #4 is just to be safe. Finally, my confidence would increase even more if you confirmed item #2.

Hopefully I haven't rambled to much.

Author

Commented:
Nope, .. I can not confirm #2.  There are no other similarly named files inside that folder (BACKUP).  All there is is another file called "Daily" (also with no file extension) that is about 293 MB.  I see that it was last updated at the same time as the transactions file, though -- so perhaps this is what you were referring to.

transactions ... 94gb
Daily ... 293mb

- Yvan
What time where they last updated?

Author

Commented:
Midnight last night.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Ok, I've made a backup of the files, and deleted the originals.  I'll provide an update tomorrow morning.

Thanks,
- Yvan

Author

Commented:
Awesome -- the 2 files ("transactions" and "Daily") were recreated last night!

The file sizes are now much more manageable, too (2.5 gb transactions file).

Thanks for your help!  I guess what I'll need to do now is figure out how to update all of the maintenance plans for the databases so that the sizes of these files remain manageable.  Either that or I'll have to delete the files periodically.  Regardless, .. the issue is resolved for now.

Thanks!
- Yvan

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.