A huge (and never used) database log was taking up about 4 GB of HD
space. We want the data for historical capacity, however, don't care
about the transactions log.
After a bit of research I ran the script on:
http://support.microsoft.com/defaul...&NoWebContent=1
(which works just fine on Sql Server 2k)
And then
DBCC SHRINKFILE(RamdomDataData_Log, 2)
This shrank the log file from 4ish gigs to 2 MB.
Of course my boss did backflips and wanted me to do it to *all* the
databases. I told him that it was probably a bad idea since we do want
the transaction logs incase something crashes, we can recreate the DB
from (for example) a week ago's DB backup.
So my question is this: When I shrink it to 2mb (or 200 MB as I am
suggesting) what are we actually "losing" and "keeping" does it keep
the most recent transactions (in which case I need to figure how much
we add each day) or earliest records, or random ones, or are they all
just "compressed?"
I don't weant to lost the transaction logs for the last week or two,
but now that this shrinking has become the holy grail I need to show
there will be bad things happening if I just make these logs all reset
to the size of a floppy disk on a regular basis.
Hi,I have a DB where I would like to maintain a fixed size and control itby myslef.I do not have the options: "Auto-Grow" and "Auto-Shrink" enabled.[color=blue]>From time to time, without a notice, or any logging, the database files[/color]gets shrinker and this causes a database full error.As I wrote, I would like to maintain the size of the database by myselfand not automatioc by the DB server.Please help me find out what can cause this problem and how to solvethis issue.Thnaks,- Ze'ev
I want to replicate a database to a subscriber that will be used as a readonly copy. The data has to be replicated as close to instantly as possible.To do this I set up a database export of objects and data to populate thesubscriber, then I set up transactional replication. To verify thatreplication is working successfully, I count the rows in each table, thereare 3 tables in total. For one of the tables, the replication completes butalmost immediately afterward, the table starts to shrink, and after severalhours the record count is zero. This isn't happening to the other twotables, and I can't figure out why.If you have no idea what might be causing this, perhaps you can suggestsome places to start looking. This is Win2k SP4 with SQL 2000 SP3.Thanks much.
I'm using a free barcode font so i can create scannable tickets via reporting services 2k5. When I print the tickets, everything seems fine. But when i export to PDF though, it looks like the barcode font shrunk. All the lines are pulled together, making scanning impossible. Is there a certain setting or so that i can use to ensure the font's width ?
-Update- The weird thing is: when i export it inside visual studio, the barcode is shown as it is supposed to in the pdf !?
I have a SSRS 2012 report which have few columns with long text. They appear good when viewed in browser. However, when I export it to excel data is shrinking. How can I avoid the data shrinking in excel.
1) Does shrinking a db have any side affects ? Or this is pretty much a normal operation ? 2) Also in db options, is it recommended to have auto shrink checked ?
We have many databases for which log files have grown much bigger. The one I need to Shrink immediately has 16 MB .mdf file and 12 GB of .ldf file.
I will very much appreciate, if somebody can help me with step by step process to shrink the database/log file (some way). We are in a crunch situation
Hi all,I've deleted a lot of albums but the size of personal.mdf isn't shrinking - how do i go about acheiving this? I've tried to shrink it in sql management express but the file is read only.. please help! thanks
I have a webhost where it seems my control of my database is fairly restricted. I cannot backup the database because I don't have the necessary permissions. I cannot perform a DBCC SHRINKFILE (permissions) - and many other DBCC commands. I ran into a problem where my log file filled up during the middle of the day and impacted my operations - data was lost.
I found some TSQL for shrinking the log file, but the statement: SET @TruncLog = 'BACKUP LOG [' + db_name() + '] WITH TRUNCATE_ONLY'; EXEC (@TruncLog)
will not execute because I cannot execute the BACKUP LOG command (permissions)...
Is there anything else I can do, or am I to far up the creek (w/o my paddle)??
I have a 13 Gig Log File with only 121 Mgs of space used. I have run the DBCCSHRINKFILE command and it has shrunk it by about 100 Mgs. Why can't I get it to shrink to a reasonable size.
I've been trying to shrink my SQL 7 Transaction log after it had grown to 30+GB's. After running the command dbcc shrinkfile 'filename' and the new size, I'm getting a result set of:
I'm having a problem shrinking my transaction log. I have a 1GB database with a 500MB transaction log. The transaction logs are backed up every 10 minutes but it has grown to 2.2GB. I've tried backing the transaction log with TRUNCATE_ONLY and then tried doing a DBCC SHRINKFILE but it doesn't seem to work. I've checked if there were any old, long running queries but there is none. What else can I do to reduce the transaction log size?
I've had a thought to create a secondary log file and then delete the primary but that isn't allowed. Is there anyway I can make the secondary log file the primary and vice-versa? This way I should be able to delete the secondary log file to reclaim space.
We have a database that was created with a 50 MB transaction log, that is set to autogrow. Due to activity the log periodically grows to over 200 MB. So we expanded it to 200 MB. But everytime the log is backed up, it shrinks to it's original size of 50 MB. The autoshrink option is not set, and no one is manually shrinking the log. Any idea what could be causing this? We are using SQL 7, SP 3.
I am having a problem truncating a transaction log. The truncate and database shrink commands execute successfully, but do not reduce the size of the transaction log.
Hi, is there any other way to shrink the log file other than truncating every time it gets bigger... I have a 800MGs database and the log is getting as big as 11Gigs... any suggestions other than truncating it? Is there a way to set it off?
I have a 1.2 gig db which has truncate checkpoint set and over 850 meg of free space. WHen I shrink it through either DBCC or EM I see no change. ANy ideas? Thanks
Ran the Andrew Zanevsky script to shrink the Tran log, this works great, but say the log gets shrinked to 30MB, I then expand it to 40MB, then sometime within a 24 hour period it magically gets reset to 30MB. The database is a standalone DB, and is on SQL7 SP1 the script I ran can be found @ http://www.pinnaclepublishing.com/sq/SQMag.nsf/WebIndexByIssue/F36C151BD80FAD8F852568D0007799BE?open. Can anyone please give some insight into this or maybe a flag that got reset somewhere? Thanks in advance Pat
Damn, people, how to shrink log file. I've tried several times with DBCC SHRINKDATABASE ( database_name , TRUNCATEONLY) and DBCC SHRINKFILE (log_name, final_MB, TRUNCATEONLY)
and the log still is _untouched_
I have got big table, about 190 mln records (15 GB) and after simple update the log is very big but unnessesery.
I've got a development server where I periodically have to allow the tempdb to grow to over 2GB. However, I'm at a loss on how to shrink the tempdb back down to a manageable size. I need the drive space on the server for other tasks. This seems like such a simple thing but I can't find information anywhere on it.
i havae a database size of 1000mb with used as 507.50 and unused as 492.50. i tried with commands like dbcc shrink file and dbcc shrink database but i could not get the result as what i need. I need to shrink in such a way that it should have 25% of the used space as free space. could any one help
DECLARE @i int SELECT @i = 0 WHILE (@i < 300) begin INSERT t2 values ('a') SELECT @i = @i +1 END DROP TABLE t2
BACKUP LOG PBDSSTEST TO PBDSSTESTLOGBACKUP
Before I ran this the used portion of the log was 1795.12 MB, the free was 1570.50 MB for a total of 3365.62 MB.
After completing this the used portion was 25.62 MB, the free space was 3263.50 for a total of 3289.12 MB. It only srhunk about 26 MB. Why won't it shrink it anymore. There was plenty of data in the used at the time of the shrinkfile command so I would have thought it would have shrunk it a lot more. Please help. I don't understand why it won't shrink it anymore than it did.
Hi, my log has grown out of proportion after performing an indexdefrag in bulk-logged mode! Now I cant shrink the damn thing coz I have forgotton how to shrink it when the logical log files are still in use.
Can somebody enlighten me please. thankyou.
error msg: " Cannot shrink log file 2 (MyDatabase_Log) because all logical log files are in use."
I have a SQL Server database (in 2000) which is 3 gig in size. I am using bulk-logged as the recovery model (I've tried simple.)
We import 300,000+ records on a monthly basis. This increases the tranlog with each import (and often times records need to be deleted and re-imported due to bad fields on the import files) and it ends up growing the tranlog to over 1 gig by the end of the day.
I've tried and tried but I can't shrink the tranlog! I've used every shrink type command and option (with truncate_only, emptyfile, etc.) but I still can't shrink the tranlog.
The database is set on a once a month backup schedule (which includes backing up the tranlog.)
Any help would be great! I was able to shrink it once after issuing several commands (i.e. dbcc commands + shrinkfile, shrinkdatabase, etc.) but I can't remember the combination of commands I used and I can't get it to shrink last time now no matter what I do!
I cannot use BCP or DTS or any other type of SQL Server importing process! I need to use the Access front end to do the importing (linked tables) because there are simply too many checks it needs to do which BCP or DTS can't handle. I do however use some stored procedures to help with the importing process speed.
Again, Any help would be great as we have 300,000+ records to import today and having a 1 gig tranlog slows things down. How can I shrink a stubborn tranlog which doesn't want to shrink and what's the best type of configuration for this database?
I have steps that I take to shrink transaction log. One of the steps is to delete the transaction log after I detach the database. Am I losing any data by doing that?
