Disk space / log file size problem
-
Worried newbie needs advice.
Inherited a 2000 SQL server that was configured by the "experts" whose application runs on it.
Discovered today that 47 of 50 available Gig on the D: partition are gone. Most of that is consumed by two transaction log files, that don't appear to have EVER been truncated. The DBs are set to Full recovery mode, with no limit on the transaction log file size. Oh, and there is no maintenance plan in place. (Shame on me for assuming there was.)
My first thought is to use Enterprise Manager to create a backup, which I believe truncates log files as it runs. However, I'm not sure there is enough free disk space for a backup to succeed.
Is there some way that (just this once) I can truncate the log files to create free disk space, after which I can set up a normal maintenance plan? I believe DBCC Shrink File run thru Query Analyzer does something similar, but I'm too green at this to know -- and I don't want to guess wrong!
Thanks for any advice! Best, Christine
-
You will need to take a backup first regardless - if there is no space on the local disk use a UNC Share to backup to another drive on the network. Once you have taken a backup and the inactive enteries have been removed from the trans log - go ahead and shrink the log files. Then you will have a acceptable size trans log and so then you can plan to back up your trans log on on a regular basis
-
Try this, if it works you will lose any recoverablity to this point.
Change Recovery mode to Simple
Stop / Start the DB
Shrink the Log
Return the Recovery Mode.
Backup
Make Maint Plan !!!
KlK -
First find out a place to backup the full DB, then make a backup from the transaction log only, then use EM menu and shrink the log file, you will have to repeat this procedure many times until tou get the right size.
-
Some progress. As Vikramnat suggested, I backed up to another server (by UNC name) that had enough space. That was a great success.
I ran a DBCC Shrinkfile command, aiming to get the log down to about 4 Meg. That peeled off about a Gig, so now the log is about 20 Gig. Some progress, but not enough.
Now I'm running the following:
DBCC Shrinkfile (logname, 128, truncateonly)
Have run it a dozen times now and nothing more seems to be happening. Should I keep at it? Suggestions welcome.
--Christine
-
Hi Christine,
Another route you can try is by using detach and then when you do the attach, attach the MDF and not the LDF. SO basically what you will do is use the model DB and take the DB offline, then detach the DB, rename the log file just incase, kinda like a backup it it. THen Attach the DB specifying only the MDF. A new log file will be created with the smallest allocated space. Once you verify everything you can delete the old trans log that you renamed.
-
Not sure if I'm ready to try that. (Yep, I'm a newbie -- and a cautious one at that.)
-
Hi Christine,
I have done it numerous times and it works great. Up to you, ofcourse if you feel uncomfortable doing it - thats understandable
. You are probably like 'DELETING A TRANSACTION LOG - YICKS - NO WAY, what is this dude talking about But here are the steps in detail:
1. Backup the DB ( I guess everyone knows this has to be the first step
2. Take the DB offline - sp_dboption 'db_name','offline', 'True'
3. Detach the DB
sp_detach_db db_name or EXEC sp_detach_db @dbname = 'db_name'
4. Go into the dir where your trn log is and rename the log file for the above Database to *.backup
5. Attach the DB (Only specify MDF - log file it will create itself with the smallest allocated space)
EXEC sp_attach_db @dbname = 'db_name',
@filename1 = N'd:\sql files\data\db_name.mdf'
6. Verify the change by viewing properties of the DB in EM and after verification remove the old transaction log that you renamed to *.backup. Also make sure that the new trn log is created in the dir.
Ofcourse do this late at night sometime.
-
Vikramnat, thank you very much for the detailed explanation. When you put it that way, it does not look so bad at all!
Perhaps I'll give that a try today, on the "test environment" database. Did I mention that I have two databases with these crazy-gigantic log files, one for Production data and the other containing Test data? The Test database is just a copy of the Production db. So if I kill it, it's not the end of the world. But I'd like to understand what I'm doing -- and why the familiar steps to shrink the log file are not working. I once used the combination of backup DB, backup log, and DBCC SHRINKFILE to bring a 10 Gig log down to about 2 Meg, where it belonged. I don't understand why it won't work now, unless the Full Recovery Mode is the difference. The time I fixed this problem before, the DB's recovery mode was Simple.
-
Before you go through the whole detaching the db situation try running
BACKUP LOG <dbname> WITH NO_LOG
GO
Then run your dbcc shrinkfile.
-
Stacenic, where will that put the backup that is created?
-
Or, do as KKnudson suggested and just go to properties and set the database's recovery to simple mode - that will wipe it just like he said, then shrink the log and datafiles of any unused space.
The bottom line, if it was 47 gigs out of 50, no maintenance plan, no idea what was going on kind of sloppiness then you will not be asked to restore it to 'last thursday around 10am' - so no sense going through the hurdles of being able (maybe) to.
Quick, simple, direct. Then set it up to be able to restore to 'last thursday around 10am' via a maintenance plan and you will be the new corporate hero(ine).
Chuck
Thanks, and don't forget to Chuckle -
It does not actually create a physical backup file, it's a way of clearing data from the transaction log.
-
Stacenic, thank you, that did it!!
I ran that string exactly as you suggested, then ran my dbcc shrinkfile command and set a target log size of 5 Meg. <Dramatic music> I suddenly have TONS of room on my D: partition!
I'm going to hit my books now to look up what the heck I just did, and see if it's something I ought to do to my Production database, too. Stacenic, would you mind explaining why that command was necessary? Is it an extra necessary step to shrink a log file when the database's recovery mode is Full? (Please pardon my ignorant questions ....)
--Christine
-
From BOL...
Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.
Basically this clears the log of old transactions. This is worthwhile doing immediately after a full database backup on your production server, and then instituing transaction log dumps to disk to prevent the logs from growing again while maintaning recoverability.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply