December 13, 2007 at 12:06 pm
EXPERIENCE: Very (too) little
ISSUE: (see experience)
I created a new database, "users_perm" and thought I set an "appropriate" initial size for the transaction log. I then populated this database by importing (SQL Server Management Studio, highlight database, right click, tasks, import data) many tables from two other servers. This resulted in a database of about 50GB (initial size (Files / Primary (the only one) set at 49,7xx MB). My transaction log (now) has an initial size of 155,055 GB. I would like to reset this initial value to 5GB and free up ALOT of space.
Any help, direction, suggestions would be greatly appreciated.
ENVIRONMENT:
Product: Microsoft SQL Server Enterprise Edition
O/S: Microsoft Windows NT 5.2 (3790)
Platform: NT INTEL X86
Version: 9.00.2047.00
December 13, 2007 at 12:23 pm
I haven't seen the initial size of a log change before, so I'm not sure what would cause that.
If the actual size changed, then shrinking the database (or the log file specifically), will help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2007 at 12:43 pm
Based on my inexperience I'll assume that I did not really have the initial size set as I thought. What I'm really looking for now is a way to reset it to a reasonable size so that I subsequent "shrinks" will free up the space.
Thanks in advance for any and all suggestions.
December 13, 2007 at 12:51 pm
It sounds like your database is in what is called "Full recovery" model. Meaning - all activity is logged into the transaction logged and kept there until the logs are backed up.
First - you probably want to hit Books Online and read up on what "recovery models" are, and why you have them.
First thing you need to do is to perform a backup of the transaction log which will once completed "truncate" the log, meaning it will free up the space or mark the space as unused within the file.
If you don't want to actually keep a backup of the transactions, then use the following syntax:
Backup log mydatabasename WITH TRUNCATE_ONLY
That means it will just throw those transactions away, and you will not be able to use them in a point in time recovery. But - it will free up the space.
Once you do that - it's time to shrink your log file. Syntax looks like:
DBCC SHRINKFILE(mydatabasefilename_log, 5120)
where the 5120 represents the initial size in KB's which you want to retain. It will use that size IF you've cleared up enough space. You can lookup the name of the log file by right-clicking on the DB in SSMS and looking under properties.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 13, 2007 at 3:00 pm
The target size in DBCC SHRINKFILE is actually in megabytes, so what is given above (5120) will actually give you a 5GB file size.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 13, 2007 at 3:22 pm
I keep having issues with typing the wrong unit. Scott's right of course....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply