What Shrinks When Shrinking
Jul 20, 2005
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.
View 1 Replies
ADVERTISEMENT
Feb 20, 2006
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
View 6 Replies
View Related
Jul 20, 2005
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.
View 1 Replies
View Related
May 24, 2006
Hi all,
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 !?
Thanks in advance!
View 6 Replies
View Related
Jul 15, 2015
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.
Attached the screenshots for reference.
SSRS View:
Export to Excel(Where data is shrinking):
View 6 Replies
View Related
Feb 20, 2001
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 ?
Thanks for your help.
View 1 Replies
View Related
Dec 14, 2007
I am new to SQL server.
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
Thanks
View 3 Replies
View Related
Apr 13, 2008
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
View 5 Replies
View Related
Feb 1, 2005
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)??
Thanx
Jerry
View 6 Replies
View Related
Mar 4, 2002
HiRunning SQL 7 sp3 on NT 4
I have a database that has the auto shrink option turned OFF. However, the log file seems to auto shrink after the user
runs bulk insert.
The log file is not setup to auto grow either.Any ideas.
Thanks,
Tariq
View 2 Replies
View Related
Apr 25, 2002
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.
View 2 Replies
View Related
May 3, 2001
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:
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
6 2 640 128 640 128
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
However my transaction log remains the same size. Any advise would be appreciated!
View 2 Replies
View Related
Aug 28, 2001
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.
Any assistance is appreciated...
Many thanks,
Michelle
View 8 Replies
View Related
Sep 18, 2001
Have a 6G data file in a single filegroup.
Using dbcc shrinkfile to shrink.
Does not have any effect.
Any ideas?
View 1 Replies
View Related
Oct 18, 2001
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.
Thanks!
View 2 Replies
View Related
Jul 27, 2000
Hello all,
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.
Thanks in advance for any help on this issue
-Matt
View 5 Replies
View Related
Sep 15, 2000
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?
View 1 Replies
View Related
Jan 8, 2001
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
View 2 Replies
View Related
Jan 11, 2001
Hello,
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
View 4 Replies
View Related
Feb 1, 2000
Hi again
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.
Michal Pajkowski
View 3 Replies
View Related
Oct 9, 2000
HI-
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.
-Ryan
View 2 Replies
View Related
Dec 4, 2000
I have a database that is 9 Gig in size and I am no longer needing the information that was stored in this database.
So I deleted the User tables that I created and deleted the User stored procedures.
I performed the Shrinking database feature and it only shrunk my database to 2 Gig.
How come it did not shrink the database to at least 10 MB since I no longer have data stored in the database?
View 3 Replies
View Related
Dec 30, 1999
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
View 4 Replies
View Related
Nov 29, 2000
I'm trying to shrink the log file. I have used the suggestions that I have seen posted previously. This is the script I've been using.
DECLARE @DB VARCHAR(40)
SELECT @DB = 'PBDSSTEST'
CHECKPOINT
EXEC('DBCC SHRINKFILE ('+@DB+'_log, 1, NOTRUNCATE)')
EXEC('DBCC SHRINKFILE ('+@DB+'_log, 1, TRUNCATEONLY)')
CREATE TABLE t2 (char1 char(4000))
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.
View 4 Replies
View Related
Feb 20, 2001
Ideally it seems to me that it would be best to only shrink databases when they
are not in use.
However, it seems this may not be practical, therefore can a Database
be shrunk while it is actively being used?
Thanks !!!
View 1 Replies
View Related
Apr 15, 2005
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."
View 5 Replies
View Related
Nov 3, 2006
What is the best way to Shrink a Data file and Log Files.
View 1 Replies
View Related
Jan 18, 2002
If you shrink a production db on server 1 which is using log shipping to restore to server 2. Does the db on server 2 also shrink?
View 1 Replies
View Related
Oct 12, 2004
When I try to shrink the log file for my databases I am getting following message.
Cannot shrink log file 2 (cm_archive_mp_Log) because all logical log files are in use.
what should I do to successfully shrink the log file.
Thanks.
View 1 Replies
View Related
Dec 30, 2004
OK here's my dilema:
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?
View 2 Replies
View Related
Jan 12, 2005
file?
I know how to shrink the log file but I am not sure if I can shrink the MDF file
and how. I am using SQL 2000.
I don;t think my database is that big but the complete backup file is up
to 6G.
this is what is displayed in Taskpad
MDF-------------- 5941M Used 589M Free
Log -------------- 3.58 M Used 12.5M Free
Any suggestion is greatly appreciated.
Thank you
View 4 Replies
View Related
Nov 29, 2005
Hi All,
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?
View 2 Replies
View Related
Nov 8, 2006
how can we shrink the log file to the required size without affecting the live database .....
Thanks in advance
View 2 Replies
View Related