** Trans log won't truncate **

[Jason H]

SQL7:

I have a 300MB db, and a transaction log near 1.3GB. Upon notification, I backed up the db log with truncate_only - no luck getting it smaller. Later, tried backup with no_log ( assuming the o/s was full - no diff)

I tried shinkfile (logfile,truncateonly) and no luck.

I tried dbcc opentrans to see if any pending trans. The db looks fine with dbcc checkdb. I managed to free up a mere 50MB. I checked the permissions on the db, and added backup db, and backup log in the db permissions for the user logged in (also tried this with sa)

I am unable to free up the space to the os. Can I somehow rid the log file and start off with a fresh log file? I need this space. As a patch I moved the log to a larger filesystem as a temporarily fix.

start/stop SQL- nothing? reboot -nothing? I played with waiting game. This log does not want to release space. The log grew from data loads.

Question1: Suggestions how to truncate this log? The contents are not really impt, but the space is.

Question2: Can I add another logfile, then use EMPTYFILE to transfer the contents to the newly added log file, then REMOVE The original logfile? In theory does this make sense?

Thanks,
Jason

[Brian Davies]

Jason ...

The problem is the way that SQL manages the log. It breaks it up into smaller pieces called virtual logs. SQL Server marks some of the VLs as active. Shrinking operations only reduce the size to the last active VL. Use DBCC LOGINFO('your database&#39. Note the status column. Any with a value of 2 are active. Also note the sequence number. Shrinking will only reduce the size to the highest numbered one with a status of 2.

Use the procedure sp_detach_db to disassociate the db from its physical file (see SQL Books).

Change the name of the physical file that was being used for the transaction log to something else.

Use sp_attach_single_file_db to reattach the physical file to the DB (see SQL Books). The reattach operation will rebuild the transaction log at a minimum size.

... Brian


------------
Jason H at 10/16/00 9:54:00 AM


SQL7:

I have a 300MB db, and a transaction log near 1.3GB. Upon notification, I backed up the db log with truncate_only - no luck getting it smaller. Later, tried backup with no_log ( assuming the o/s was full - no diff)

I tried shinkfile (logfile,truncateonly) and no luck.

I tried dbcc opentrans to see if any pending trans. The db looks fine with dbcc checkdb. I managed to free up a mere 50MB. I checked the permissions on the db, and added backup db, and backup log in the db permissions for the user logged in (also tried this with sa)

I am unable to free up the space to the os. Can I somehow rid the log file and start off with a fresh log file? I need this space. As a patch I moved the log to a larger filesystem as a temporarily fix.

start/stop SQL- nothing? reboot -nothing? I played with waiting game. This log does not want to release space. The log grew from data loads.

Question1: Suggestions how to truncate this log? The contents are not really impt, but the space is.

Question2: Can I add another logfile, then use EMPTYFILE to transfer the contents to the newly added log file, then REMOVE The original logfile? In theory does this make sense?

Thanks,
Jason

[Craig]

It has been posted numerous times in this forum to detach the database and then re-attach it. You will get a new transaction log. Then, set a size maximum on it to prevent this from occurring.


------------
Jason H at 10/16/00 9:54:00 AM


SQL7:

I have a 300MB db, and a transaction log near 1.3GB. Upon notification, I backed up the db log with truncate_only - no luck getting it smaller. Later, tried backup with no_log ( assuming the o/s was full - no diff)

I tried shinkfile (logfile,truncateonly) and no luck.

I tried dbcc opentrans to see if any pending trans. The db looks fine with dbcc checkdb. I managed to free up a mere 50MB. I checked the permissions on the db, and added backup db, and backup log in the db permissions for the user logged in (also tried this with sa)

I am unable to free up the space to the os. Can I somehow rid the log file and start off with a fresh log file? I need this space. As a patch I moved the log to a larger filesystem as a temporarily fix.

start/stop SQL- nothing? reboot -nothing? I played with waiting game. This log does not want to release space. The log grew from data loads.

Question1: Suggestions how to truncate this log? The contents are not really impt, but the space is.

Question2: Can I add another logfile, then use EMPTYFILE to transfer the contents to the newly added log file, then REMOVE The original logfile? In theory does this make sense?

Thanks,
Jason

[Jason]

Sorry, if it is a repeat posting, I am new to the forum (and SQL7, use Oracle..). I checked the archives, no relevant info.

thanks and have a nice day

[Tom Bartos]

If you are trying to shrink the logfile for a particular Database,
you can use these steps, I have used successfully many times,
when our logfiles grow too large for comfort.

I will use the pubs DB as an example. start the query analizer

Use PUbs
go

select * from sysfiles

fileid groupid size maxsize growth status perf name filename
------ ------- ----------- ----------- ----------- ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 176 -1 10 1048579 0 pubs C:MSSQL7DATApubs.mdf
2 0 63 -1 10 1097794 0 pubs_log c:mssql7DATApubs_log.ldf

(2 row(s) affected)



--- in this example the pubs_log is the name of the log file

then run:

USE PUBS
GO

DBCC SHRINKFILE (pubs_log, 7) -- 5 is the size in mb of the log file after shrinking
GO


--that shoud do it. For more info look in books online for dbcc shrinkfile


------------
Jason H at 10/16/00 9:54:00 AM


SQL7:

I have a 300MB db, and a transaction log near 1.3GB. Upon notification, I backed up the db log with truncate_only - no luck getting it smaller. Later, tried backup with no_log ( assuming the o/s was full - no diff)

I tried shinkfile (logfile,truncateonly) and no luck.

I tried dbcc opentrans to see if any pending trans. The db looks fine with dbcc checkdb. I managed to free up a mere 50MB. I checked the permissions on the db, and added backup db, and backup log in the db permissions for the user logged in (also tried this with sa)

I am unable to free up the space to the os. Can I somehow rid the log file and start off with a fresh log file? I need this space. As a patch I moved the log to a larger filesystem as a temporarily fix.

start/stop SQL- nothing? reboot -nothing? I played with waiting game. This log does not want to release space. The log grew from data loads.

Question1: Suggestions how to truncate this log? The contents are not really impt, but the space is.

Question2: Can I add another logfile, then use EMPTYFILE to transfer the contents to the newly added log file, then REMOVE The original logfile? In theory does this make sense?

Thanks,
Jason

[BL]

This is definitely not an MS sanctioned solution, but in a pinch, you can checkpoint the DB, delete the log file, and restart the SQLService. It will create a new 2MB log file. USE AT YOUR OWN RISK!

You may want to search on Neil Pike, who's written articles on this.


------------
Jason H at 10/16/00 9:54:00 AM


SQL7:

I have a 300MB db, and a transaction log near 1.3GB. Upon notification, I backed up the db log with truncate_only - no luck getting it smaller. Later, tried backup with no_log ( assuming the o/s was full - no diff)

I tried shinkfile (logfile,truncateonly) and no luck.

I tried dbcc opentrans to see if any pending trans. The db looks fine with dbcc checkdb. I managed to free up a mere 50MB. I checked the permissions on the db, and added backup db, and backup log in the db permissions for the user logged in (also tried this with sa)

I am unable to free up the space to the os. Can I somehow rid the log file and start off with a fresh log file? I need this space. As a patch I moved the log to a larger filesystem as a temporarily fix.

start/stop SQL- nothing? reboot -nothing? I played with waiting game. This log does not want to release space. The log grew from data loads.

Question1: Suggestions how to truncate this log? The contents are not really impt, but the space is.

Question2: Can I add another logfile, then use EMPTYFILE to transfer the contents to the newly added log file, then REMOVE The original logfile? In theory does this make sense?

Thanks,
Jason