Log Shrinking When DB Not Set To AutoShrink
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
ADVERTISEMENT
Nov 12, 2004
Hello, everyone:
If I select AutoShrink in database properties to release space to operating syetem, how does it make the perfomance to change. Thanks a lot.
ZYT
View 2 Replies
View Related
Aug 2, 2007
Howdy folks!
I've got a database that needs to run 24/7. I'm looking into maintanence options and wanted to run the following by y'all:
Ok, I've read the MSDN "Maintaining databases" article and noticed the following statement about autoshrinking: "This technique uses almost no processor time and memory". I also searched these forums and found that many users say autoshrinking heavily lags down sql transfers. So who's right? And if it does lag transfers, by how much?
Another question I have about autoshrink is fragmentation. It would seem to me that over time solely depending on autoshrink would cripple a server in terms of fragmentation; is this the case?
Also, does autoshrink (or manual shrinking or compacting) update the statistics?
Final question!!! I'm programming in native c++, is there a way for me to run commands such as "DBCC SHRINKDATABASE" in native OLE DB code?
Thanks!
View 4 Replies
View Related
Mar 1, 2001
Hi,
I had a DTS task running which was doing an insert (over 17.5 million rows) in a database for which the auto shrink option was enabled.
After a few hours I noticed that the insert was blocking the autoshrink.
Since I didn't want to kill my insert, I tried to kill the autoshrink from the query analyzer, but when trying to do so I got the message that only user processes can be killed.
Does anyone have similar experiences? Should you consider enabling the autoshrink option or is this just trouble?
Stef
View 1 Replies
View Related
May 9, 2002
Details: MSDE 1.0 / SP4 - Windows 2000 Pro
I have a database that has Truncate on Checkpoint set for the Log file. The Log file is set to AutoGrow. Is it necessary to to run dbcc shrinkdb (or the like) to get Log file to contract? Is there any harm in not contracting the Log file? I'm looking for best efficiency and least-likely-to-fail path as DB sits 'really remote' and there is little opportunity for observation.
Does anyone have any recommendations on re-indexing? I have one table that bears the most growth. It has a clustered index. What would be a suitable data point to watch? I run a SP to save DBCC SHOWCONTIG info along with the duration of a test query, but haven't seen a clear breakover point.
TIA -RC
View 2 Replies
View Related
Jun 6, 2001
Does anyone have any benchmarks for the amount of overhead caused by autoshrink of the log and having autostats enabled? We have a customer that insists that turning off these options was necessary to eliminate a performance problem they were having (Query timeouts), but we are not convinced that these two options would have generated enough overhead to have been the root cause (they also rebuilt all their indexes and made some other unspecified changes that more likely solved the problem).
We are hestitant to have them continue with these options disabled because then we need to rely on them to keep the log file shrunk and the statistics updated and because of the data changes during the day, would prefer to have stats updated automatically rather than on a fixed schedule that may not be as appropriate.
Anyway, if anyone has any feedback on overhead generated and potential performance implications of having either of these options enabled, it would be greatly appreciated.
Thanks,
Ray Rankins
View 2 Replies
View Related
Jul 21, 2004
I have a test database and I detach and reattch this database during my testing on my test system. This database is originally from my production system. I have a couple questions that came up during my testing:
1. When you detach/attach a database from a different server with the same setup, does it gather it database setup information from the model database on the current server?
2. When you switch the autoshrink database option from enable to disable, do you have to restart the server?
3. How to you change the default make of the model database?
Thanks in advance for any help.
View 1 Replies
View Related
Jul 20, 2005
Hello All,I have been encountering trouble with a SQL Server 2000 Transactionlog file, mainly with the constant growth and lack of the autoshrinkoption. Here are the details:1.)OS is Windows 2000 server sp32.)SQL Server 2000 (Build 2195) sp33.)Database Recovery Mode is set to Full4.)Maintenance Plan for "Transaction Log Backup" is set to removefiles older then 1hr.5.)The "AutoShrink" option is on for the DB properties.Also as part of the Maintenance plan the database in question isbacked up nightly. The problem is that the transaction log file hasgrown so much that it was consuming all of the free space on the harddrive so I then restricted the file growth. When all of the drivespace was consumed or the "database log file is full" message wasencountered I would run the following Query scripts to shrink the filemanually:First – Backup Log <database name> With Truncate_OnlySecond – DBCC Shrinkfile (<database name>_log, 200)Which brings the transaction log file down to 200mbs. I need toautomate this so that I don't have to keep babysitting this databaseor manually shrinking the log file.
View 3 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
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
