How To Shrink SQL Logs
Feb 8, 2008Just want to inquire on how to shrink my sql logs.
My disk space is running and I suspect that sql logs is the cause
of the problem..
Kindly help
ADVERTISEMENT
SQL 7.0 Transaction Logs Won't Shrink!!!
Jun 5, 2002I have seen quite a few people post this type of problem, but I am finding few solutions. Your advice and/or experiences are greatly appreciated.
Here's my scenario:
Environment: Windows NT 4.0 SP 6, SQL 7.0 (set up for Transactional Replication)
Problem: We have several remote dB machines configured for full recovery. On these machines are several dB's that are capturing aprox. 280 data points per second per unit. (Each dB represents one unit, and we have 21 units) No problem here. The problem is the transaction logs, that obviously grow profusely, will not SHRINK after, backups, dbcc commands and TSQL has been issued in failed attempts to shrink the logs. (in other words we've tried everything)
My questions are:
1. Because we are replicating, is it absolutely necessary to configure dB's for FULL recovery? How do I check in 7.0 if the dB is certainly in FULL vs. SIMPLE recovery mode?
2. I work with SQL 2000 and shrinking files is no problem. How can we shrink these log files in SQL 7.0?
It is rather urgent I find a solution as we are running out of hard drive space on our remote machines. Please help :-(
Christine
Backup And Shrink Trans Logs??
May 30, 2008Hi,
I am in the process of migrating databases from a SQL Server 2000 server to a 2005 server.
I have taken full backups and restored without recovery on the 2005 server. I now need to take transaction log backups and restore with recovery tonight to complete the operation. My question is this:
Some of the transaction logs have grown very large. Is it safe practice to perform a backup tran with truncate only and then run dbcc shrinkfile to reduce the size before I back up? This would make the process much quicker.
Thanks.
DB Engine :: Write Logs To Windows Event Logs?
Aug 6, 2015OS: 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.Â
SSIS Logs Vs Custom Logs Components
Feb 12, 2007Hi,
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
SQL Server Logs (error Logs)
Aug 28, 2001When 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
How To Shrink A Tranlog Which Won't Shrink
Jan 5, 2007If you have problems shrinking a transaction log no matter what commands you issue, here's a way to shrink the tranlog:
1. Right click on the properties of the database and go to the options tab.
2. Change the recovery mode to simple.
3. Right click again on the database - go to all tasks - shrink database.
4. Shrink the database.
5. Change the recovery mode back to what you had it as.
I found this out by trial and error as I could not find any documentation on it and no matter what I tried, I could not get the transaction log to shrink.
Shrink DB
Jun 6, 2002I have a SQL 7.0 sp3 server and I am trying to shrink a production database for use on other servers. The database/log sizes are 9601mb / 138mb with 772mb / 128mb free. I truncate a table that contains binary data. After this the database/log sizes are 9601mb / 138mb with 6634mb / 111mb free. The next step is to use EM to issue a shrink db. After this the database/log sizes are 6807mb / 378mb with 9.56mb / 365mb free.
After the use of truncate the the data in the datafile is about 2967mb.
After the shrink db the data in the datafile is about 6797mb.
The size of the data grew after the shrink db was issued. I looked at the tables and the space is largely associated with two tables that have a binary field, but the number or rows are still the same. I ran a checkdb just to make sure that there was not a problem and no problems were reported. I have used this process many times and this is the first time I have come up with these results. Any suggestions?
Thanks,
Glen
Shrink Db
Aug 17, 2001if I shrink the db will it enable me to create another db with that space released by the first db?
View 1 Replies View RelatedHow Can I Shrink My Log?
Jul 19, 2000How can i shrink my log file? It is like 900 megs and i
was it to be more like 400 megs.
Please Help
Melissa Sener
Project Manager
Square Yard
Log Not Shrink
Sep 27, 2002Hi, My name is Alexander and came´s to ask about Sql Server log.
Do you know why sometimes the log not truncate? All time
comes increasing!
I setup "Automatic Shrink" and it swims happens. If you can help me!
Thanks in advanced
Shrink Db
Jun 30, 1999Hi everybody:
Actually I have a 8.5 Gb database but the correct size that I need
to due disk space is 5 Gb. When I make a backup, the size of the dump is
4Gb, so I wonder if I create a 4- 4.5Gb my dump db will fit in it, but
not. Other way is create antoher db with 4 - 4.5 Gb and transfer via
sql-transfer or sql-bcp but I got a message that my device is too
short.
So I ask : What are the correct ways to reduce the size of a
database ?
Thanks in advance.
Why Is SQL DB So Big? Can I Shrink?
Jun 16, 2004Hello,
We just migrated our DB from one SQL Server to Another. Both DB's used to be the same size at 11GB. The new SQL Server is a whopping 33GB, and I can't seem to find out why. The new DM has fewer tables (the data is identical).
Does anyone know how I can compact or shrink my DB, know why or how it grew so much and didn't go back down to normal size, and maybe where I can search properties or change settings to find out what is going on and prevent it from happening again.
I know MS Access has a shrink utility, but don't know where it is in SQL Server.
Thanks, in advance for your help.
Rodney
Shrink Db
Jul 26, 2004Hello,
I have a 24go sqlserver 2000 database, but only 5go used, when i use the shrink command in em there is no change.
can i modify the property of the db (auto-shrink => on ) actually it's on OFF.
To Shrink Or Not To Shrink ??
Nov 27, 2006Newbie question about reclaiming some disk space on our SQL server.
We currently have a db that has the following stats:
space allocated: 34734 MB
space free: 843.66
of this the current size of .mdf is 33877 MB and the space used is 33873 MB
the drive that holds this database is running out of space, what is the best method for me to use to reclaim some disk space?
I have read books online and am a little bit confused as to whether or not using shrinkfile on the .mdf will free up space to the OS? Do I need to do anything else before or after such as reindex? Also what is the overhead as we only have about 10% free on the drive.
Shrink An LDF
May 22, 2006I am trying to shrink a log file (.LDF), but it will not work. I am typing:
BACKUP LOG "Database1" WITH TRUNCATE_ONLY
dbcc shrinkfile ("Database1_log", 50)
The microsoft site says this should work:
http://support.microsoft.com/kb/272318/
...but I can't get the file to shrink, at all? I'm sure the SQL services need to be running. I am logged in as my Administrator / sa. What could I be missing? Trying to shrink it too much? (THANKS).
Why Is Database Still Big After Shrink?
Jul 26, 2000A few of the databases I have are take up a lot of space, but when I look at the actual space that's being used, it's very small. I've tried shrinking them but they still remain at this bloated size. Is there any other way to reduce their size?
View 3 Replies View RelatedAuto Shrink Db
Oct 24, 2001what's this : "auto shrink " db option in properties ???
in which case may i use this option ?
please help
10GB Log Won't Shrink
Sep 18, 2000Txn log is nearly 10GB with only 60MB actually used. Disk has 150MB remaining. SQL 7.0 SP2.
I've truncated the log several times. I'm aware that the active portion of the log can be at the end and have added many records to cycle the active portion round to the start of the log file. How big are these log segments?
I've run dbcc shrinkfile with and without a target size.
I've checked DBCC OPENTRAN. No open txns exist.
What else can I try? I've looked through similar postings and haven't spotted anything.
Yours hopefully,
Simon
Shrink File
Mar 27, 2000I am looking for the correct procedure for shrinking a database file ,
please reply back if anybody knows the solution
Thanks
Ajay
Shrink Transaction Log
Jan 18, 2001Is it possible to shrink Transaction Log file? If yes, how? Urgent matter please.
Regards,
How To Shrink A Log File?
Oct 20, 1999On a database with a log file that has an unrestricted file growth, the file size exceeds 1 GB. Since this excessive was caused by a badly written update statement, I want to reduce the size to about 200MB.
After reading the BOL I was convinced that I only need to take two actions: truncate the log file (to create some free space in the log file)and shrink it.
These are the statements I executed:
backup log ODS with truncate_only
dbcc shrinkfile (ODS_Log, truncateonly)
After I executed these statements - BTW, there were no errors - the file size was still the same. Can somebody tell me why?
Thanks,
Stef
Shrink Error Log
Feb 17, 2000Is there a way to shrink the error log in SQL 6.5 without stoping the server?
View 1 Replies View RelatedMy SQL 7 Log File Won't Shrink
Aug 27, 1999Hi,
I have created a new database in SQL Server 7 with the auto grow options set to on. I then added a whole load of new data to the table which made the transaction log file grow to 20Mb.
I then truncated the transaction log to remove all the completed transactions. The Enterprise Manager now shows the Log to only have 3MB of data in it but the file is still 20MB.
I have tried setting the truncate log on checkpoint option, and tried running DBCC SHRINK DATABASE and DBCC SHRINK FILE commands but these seem to have no affect on the file size.
Does anyone have any idea what I might have missed/done wrong?
Yours well and truly stuck,
Martin
Shrink Logfile ?
Jun 9, 2000How do I shrink my transaction log file ? The physical file are 10Gb in size but only contains 100Mb data so I want to shrink it to save diskspace. I tried DBCC SHRINKFILE command but the file dont shrink.
Any other methods to shrink a transaction log file ? Like backup/restore ?
Help !
Thanks
Shrink SQL 6.5 Transaction Log
Mar 7, 2001Is it possible to shrink the size of a transaction log file in SQL 6.5. Normally in SQL 7.0 I use detach and reattach but this is not supported in 6.5. The log file for a 200Mb database is 749Mb with no unused space and no open transaction. Thanks
View 2 Replies View RelatedCannot Shrink Transaction Log
Sep 25, 2002Hi,
I have a t-log on one database which is 400mb when the database size is only 30mb. dbcc shrinkfile does not work and dbcc opentran shows no open transactions. When I tried to do a Backup log with truncate_only the following message displays:
The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.
The database in question is not a publisher and only receives 5 subscribed articles, none of which are changed very often. The database operates 24 x 7.
Does anyone have a solution to this dilemma?
Thank You
SHRINK Database
Nov 9, 1999I am in the process of copying database from large size to smaller size one. The actual data in the database is very less.
When I try to shrink the database, I am getting message I can't shrink the database.
Can any one suggest on 'How to copy this database?'
Thank you,
Can't Shrink The Database Log...
Jan 21, 2004Hi
I have a SQL2k db ABC with only 2mb data but around 500 log. I did the followings
----------------------------------
backup log ABC with truncate_only
dbcc shrinkdatabase(ABC, 10)
Cannot shrink log file 2 (ABC_Log) because all logical log files are in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
7 2 58768 128 58768 128
use ABC
sp_spaceused
database_name database_size unallocated space
------------ ------------------ ------------------
ABC 506.75 MB -0.51 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
2248 KB 1288 KB 544 KB 416 KB
sp_helpdb ABC
name size maxsize growth usage
-------------- ------------------ ------------------ ------ ---------
ABC_Data 1728 KB Unlimited 10% data only
ABC_Log 517184 KB Unlimited 10% log only
question:
1. what is the negative -0.51 MB for unallocated space?
2. I can't kill all the process logins, is there other way to shrink the database log side?
thanks
-D
Shrink Log Files
Jul 31, 2006Hi All,
I have a question that keeps my mind buys and I wasn't able to find answer on that.
I have a full backup recovery plan and I am backing up my Log files in every 15 minutes. At the same time My log files are shrunk in every 3 hours so Are shrinking the log files correct approach?
If somebody enlighten me on this issue I appreciate it.
Thanks
LS
:confused:
Force Shrink Log
Jan 15, 2002Id like to know how I force the transaction log to shrink in v2000?
I have sp_force_shrink_log but this only works in v7.
... Also, how do I set the log to truncate on checkpoint?
Many thanks,
Lauryn
Shrink Db Blocked
Feb 27, 2002Hi,
Issuing 'dbcc shrinkfile (log ,truncateonly)'
I get the error message below. Books online doesn't say any more.
Can anyone explain? I am the only user connected to the db at the time, no jobs are executing.
Cannot shrink log file 2 (log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
How Long To Shrink DB
Jun 25, 2004Hi,
Roughly how long would it take to shrink a 300GB db, its been running over 8 hours already (throught enterprise manager)?
Thanks in advance
aido.
Dual P4 xeon 2gb ram 350gb scsi raid array