May 26, 2009 at 10:23 am
It's a backup option.
BACKUP LOG WITH TRUNCATE_ONLY
May 26, 2009 at 10:26 am
Wow. That could be a problem. The whole issue here is that there is no disk space. I don't have a place to back it up to.
May 26, 2009 at 10:30 am
middletree (5/26/2009)
Wow. That could be a problem. The whole issue here is that there is no disk space. I don't have a place to back it up to.
then you need to truncate the log using the command steve gave you (insert dbname) followed IMMEDIATELY by a full db backup
BACKUP LOG dbname WITH TRUNCATE_ONLY
---------------------------------------------------------------------
May 26, 2009 at 10:35 am
george sibbald (5/26/2009)
middletree (5/26/2009)
Wow. That could be a problem. The whole issue here is that there is no disk space. I don't have a place to back it up to.then you need to truncate the log using the command steve gave you (insert dbname) followed IMMEDIATELY by a full db backup
At this point I have to agree. If you don't have space for a backup that is what you will need to do.
1) BACKUP LOG WITH TRUNCATE_ONLY
2) BACKUP DATABASE
3) DBCC SHRINKFILE(...) -- again, shrink the log to about 1 GB for now.
4) Establish scheduled transaction log backups (every hour, 30 minutes, 15 minutes, depending on your recovery requirements)
5) Monitor your transaction log to determine if it is sized appropriately (add or remove space depending on usage).
May 26, 2009 at 10:42 am
If I am reading you correctly, this should do what I need:
USE CHEMPAXDW
GO
DBCC SHRINKFILE('CHEMPAXDW_Log', 10000)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE('CHEMPAXDW_Log', 10000)
GO
May 26, 2009 at 10:50 am
middletree (5/26/2009)
If I am reading you correctly, this should do what I need:USE CHEMPAXDW
GO
DBCC SHRINKFILE('CHEMPAXDW_Log', 10000)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE('CHEMPAXDW_Log', 10000)
GO
Not quite.
BACKUP LOG [CHEMPAXDW] WITH TRUNCATE_ONLY
BACKUP DATABASE [CHEMPAXDW]
DBCC SHRINKFILE('CHEMPAXDW_Log', 10000)
Of course you need to do a little more with regard to the syntax of the BACKUP DATABASE command.
Be sure to consult BOL for the proper syntax for all of the commands.
May 26, 2009 at 11:04 am
I know this is somewhat 'shutting the stable door after the horse has bolted', but it may help prevent such a drastic problem another time if you limit the size to which data file and log files can grow. You can do this by right-clicking on the target database in SQL Server Management Studio (I'm assuming you have that tool?), choosing 'Properties' and clicking on 'Files'.
The window will show you the logical and physical file names (which was something you asked about earlier), as well as the initial file sizes and the autogrow settings.
You can change the autogrow settings to restrict file growth to a certain size. This won't prevent the transaction log from filling up if you're not running regular transaction log backs in Full Recovery mode, but it will prevent you from filling the entire disk so you should at least be able to backup the log.
Take care when setting the autogrow values, e.g. if the Transaction Log is set to 1000MB, I wouldn't advise an autogrow setting of 1MB; it is likely that the log file will not be able to autogrow fast enough and you'll end up in the same situation. You need to have some idea of the rate of data growth for the database and the number & type of queries that are run against it in order to size the data and log files correctly.
There is a rule of thumb that says a transaction log file should be 1/4 to 1/3 the size of a data file, but that will vary based on usage as I say.
Lastly, you can set up alerts to automatically email you (or someone else ;-)) when a data file or log file reaches a threshold of your choosing, or you could have a job to automatically backup the log/truncate the log/make the database read-only/ a whole host of other things when the threshold is reached.
Cold comfort, but think of it as a (steep) learning curve and take the kudos for rescuing the situation!
May 26, 2009 at 11:13 am
Thanks. I do intend to limit the growth in the future, and I appreciate the instructions.
First things first, though. I will try to do the truncate thing now. Let's hope I don't do too much damage. 😉
May 26, 2009 at 11:29 am
Well, I had to do it different than directed. I hope I didn't screw anything up. I removed the command to backup the database.
USE CHEMPAXDW
GO
BACKUP LOG [CHEMPAXDW] WITH TRUNCATE_ONLY
DBCC SHRINKFILE('CHEMPAXDW_Log', 10000)
It brought the log down from 113GB to 10GB. I will now look into changing the backup plans.
I may be back to ask about looking to see if previous backups worked or not.
thanks for your help, everyone!
May 26, 2009 at 11:30 am
Have you run a FULL BACKUP of the database yet? If not, you need to run it now.
May 26, 2009 at 11:36 am
You should be aware of what using BACKUP LOG ... WITH TRUNCATE_ONLY is going to do. This is going to break your log chain, so you are going to have to perform a full backup again after the process to shrink the log file is completed.
That option to BACKUP LOG is deprecated in SQL Server 2005 - and no longer works in SQL Server 2008 and above. I would recommend using the following to clear the transaction log instead:
ALTER DATABASE ... SET RECOVERY SIMPLE;
Once that is done, then you should be able to shrink the log. If the log does not shrink, then issue checkpoints until the virtual log is rolled over and writing to the beginning of the file.
Then you can shrink the file using DBCC SHRINKFILE and size it appropriately. When completed:
ALTER DATABASE ... SET RECOVERY FULL;
Perform a full backup and start backing up the transaction log on a regular basis.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 26, 2009 at 11:41 am
I am doing a full backup now, using the GUI (SSMS).
May 26, 2009 at 12:11 pm
now make sure that you set up a job to run regular log backups. Otherwise this is just going to happen again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2009 at 12:43 pm
Ok, I have no idea how to do that, but will research. thanks.
May 26, 2009 at 12:45 pm
The simplest way is to setup a maintenace plan to accomplish this for you. Look it up in Books Online, it will get you started.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply