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 transaction log file which is 29 GB in size. I am trying toreduce it by using dbcc shrinkfile with truncate_only but, it has noaffect on it. So, I tried to look at the logs by running dbcc loginfo(dbname). The last log is what's active (status is 2). How do I makethis status to wrap to first log so that dbcc shrinkfile would work?All other logs have status of 0, after running backup log dbnametruncate_onlyThanks.
View 4 Replies View RelatedI have a transaction log with 22mb of used space and 2,632.50MB of free space. I tried using the following statement to shrink the log and it did not do anything:
USE DB
GO
DBCC SHRINKFILE (DB_log, 50)
GO
CHECKPOINT
Can anybody help me understand what is going on here?
Gurus
My logfile for a DB grows very rapidly and grows to more than 10gb every night,i have scheduled a job to shrink this file.
Now in job activity monitor it is showing me a yellow triangle in front of the job and status as successfull and it has ran only for 1 second
It is not shrinking the logs
I have transactional replication implemented
My point is when the logfile grows more than 10 gb the job does not shrink the logs
I have got around 8 steps in thos job and this is the 7th step
Please Advice
Thanks
Nitin
I'm using SSEE as part of SharePoint 2003. The transaction logs are growing rapidly but don't appear to clear down.
As its SSEE I've been so far unable to connect using Management Studio or Enterprise Manager, so using DBCC Shrinkfile isn't an option.
Can someone please help? I'm surprised that when installing SP I didn't get the option to choose a db type or location.
I am working on deploying my production DB's to a new server and am looking for some advice. The new server is running RAID-5 (the old one is RAID-0) but it only has one controller therefore I can not create another array group. So I was wondering since the disk setup is C: and D: (Virtual Drive) if I would see any benefits from placing my log files on C: and my Data files on D:?
The other question is related to the size of my log files. Some of the DB's were not created by me and there are no constraints in log file growth causing some very large TRANS logs. For example, there is a 100 MB DB with a 1 GB trans log. I restored the DB on my new server and truncated the log file (manually, the Ent Mgr Shrink DB tool doesn't work worth a hoot) to half the size of the DB and it functions properly. I was just wondering if this would cause any problems down the road if I did all of my DB's like this?
I am restricting log file growth to approximately half the size of the DB.
There is one DB that is 1.5 GB and the log file was set restricted to 2mb and it works fine, but I feel that I should bump this up a bit.
Any recommendations?
OS: Windows 2012 Enterprise
SQL Server: 2012 Enterprise
I was wondering if there is any way all SQL Server error log entries could be automatically written to Windows Event Log.
Hi,
In my case I have to log the errors raised by any task in a package to either windows event log, text file or SQL server. Also I need to send an email notifications to a group of people telling them about the error.
Now can I use SSIS package logging for logging the errors into the required destinations. I mean right clicking on the package and selecting Logging, then adding the required log providers and enabling the events for logging into those. I think I have to upfront select the log providers to log the error, I will not have the liberty to log the error to the destination, the name of which is passed as a variable to the package. This is okay with me though.
Now what will a custom log provider help me to do in this case. Also can I somehow configure my package to call the send mail task everytime an error is raised.
Also, one more option can be developing a package that only does the error handling. It will take in the paramters or the error codes and descriptions, the destination to write to and a flag to send mail or not for that particular type of error.
What do you think? Kindly advise.
Thanks in advance for your help and time.
Regards,
$wapnil
When looking at SQL Server error logs, I noticed that the current error
log had grown to 1MB whereas most of the files are only a few KB.
QUESTION 1: Can I set the max size for an error log file? If so, how?
QUESTION 2: If not, is there a workaround?
Thanks
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.
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
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)??
Thanx
Jerry
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
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 RelatedI'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!
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
Have a 6G data file in a single filegroup.
Using dbcc shrinkfile to shrink.
Does not have any effect.
Any ideas?
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!
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
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 RelatedI 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
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
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
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
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?
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 RelatedI'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.
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 !!!
What is the best way to Shrink a Data file and Log Files.
View 1 Replies View RelatedIf 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 RelatedWhen 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.
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?