Transaction Log Not Shrinking Since Mirrioring Started
Oct 2, 2006
Hello all,
I am currently having a rather pestering issue with my full/transaction log backups. It seems that after running either, my logs do not truncate and the file continues to stay @ the current size and eventually fill to the brim. This issue only began after mirroring was setup. Is there any differences in log file maintainence when dealing with Mirrored databases ? I made my last attempt to run backups and waited over the weekend for some hope that a checkpoint would occur and my file would be shrunk once again for normal use.
Any insight on this is greatly apprecieated.
View 9 Replies
ADVERTISEMENT
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
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
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
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
Aug 20, 1999
I 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?
View 1 Replies
View Related
Jan 3, 2006
My transaction log blows out to more than 55 GB when I run an index rebuild on a fairly large table. It’s ok for the transaction log to get that big but I want to shrink it back down ASAP while maintaining the integrity of my transactional backups. I’m thinking of a process something like the following:
1.Full database backup
2a.BACKUP LOG database-name WITH TRUNCATE_ONLY
2b.dbcc shrinkfile(2,20,TRUNCATEONLY )
3.Full database backup
Is this the best way to shrink the T-Log while maintaining the integrity of my transactional backups?
My database is MSSQL 2000 sp4 in full recovery mode.
View 1 Replies
View Related
Jun 12, 2008
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
View 6 Replies
View Related
Nov 24, 2006
Hi All,My SQL server transaction log is getting bigger every day and my HDD ifrunning out of space.So i follow the MS KB about how to Shrinking the Transaction Log.After doing so the log is much much smaller as i can see the size of itunder enterprise manager.The problem is that the HDD still shows the same size.If i shrink the DB why the and reduce its size why the HDD does notshows it?Is there a way to clear the size from the HDD?Thanks All
View 3 Replies
View Related
Sep 27, 2007
Hallo. I need some help. Is it possible to run SQL statement outside a transaction when it is aleready started? Ok let's look at example, if I cas say so:
begin tran...{do something somehow in environment when @@trancount = 0}...commit tran
I tryed do like this
begin tran...exec('select @@trancount')...commit tran
but result was "1".
Sory about my english and thanks for help.
View 3 Replies
View Related
Mar 25, 2008
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.
View 2 Replies
View Related
Jul 23, 2005
Hi!I'm studying to have my MCSE 70-228 certification and I'm trying somethings with backing up transaction logs and shrinking it.Here's what I do:There is no activity in the database by the way.I have a transaction log of 1792 kb...I do the following command:BACKUP LOG TestDB TO TestDBBackupDBCC SHRINKFILE ('TestDB_Log',0)The transaction log is now 1280 kbI do the same command and finally my transaction log is now 1024kb...Any idea why it didn't shrink it at 1024 kb the first time?Thanks!Jeff
View 1 Replies
View Related
Oct 6, 2004
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?
View 10 Replies
View Related
Oct 12, 2002
Gurus,
I have inherited a SQL 2000 database ( (I am new to SQL DBA) and I found this when I was checking the db properites . The transaction log has grown bigger than the actual data file, I thought transaction log backups would truncate the inactive portion of the log file and shrink the transaction log, but it was not the case it seems, may be it was truncating the inactive portion of the log, but not shrinking it. This site does not have a job for truncating the data/log files periodically. What is the best method to deal this situation, how can I shrink the Transaction log quickly?,
All your suggestions are welcome.
TIA,
-Jay
View 2 Replies
View Related
Mar 31, 2008
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
Any help on this would be appreciated.
View 1 Replies
View Related
Jan 30, 2006
Environment:SQL Server 2005 Enterprise in Win2k3 Enterprise on 32(x86) bit machine
I am able to enable the Trace Flag 1400 using DBCC TRACEON (1400) GO. Only then we can able to configure for the ENDPOINTS.
I successfully configured the ENDPOINTS for both the principal server and Mirror server .And then when I started the START MIRRORING.
I got the error message. Please find the Message below.
An error occured while starting mirroring.
additional Information:
:..>Alter failed for Database 'KQDB', (Microsoft SqlServer.Smo)
:..>An exception occured while executing a Transact-SQL statement or batch
[Microsoft SqlServer.Connection.info]
:..>Database mirroring Transport is disabled in the endpoint configuration.
[Microsft SQL Server,Error: 1486]
If anyone have a solution on how to setup a database mirroring environment.Please send.
View 10 Replies
View Related
May 31, 2008
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
View 1 Replies
View Related
Feb 22, 2007
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
View 3 Replies
View Related
Feb 6, 2007
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
View 1 Replies
View Related
Dec 22, 2006
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
View 8 Replies
View Related
Jan 8, 2008
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
View 2 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
Nov 25, 2003
Dear All,
This is my first time posting in this forum, so forgive me if my post is out of place. If so, let me know.
I am trying to get started setting up a development server on Windows 2000. These are the componants I have installed so far:
- IIS 5
- MSDE Database Server
- .NET Framework 1.1
- MDAC (Microsoft Data Access Componants)
- ASP.NET Web Matrix
I have also downloaded a quickstart example, TimeTracker, to learn from.
Here is my problem: When I try to install TimeTracker, it does not detect a database connection. Also, when I try to connect to a database in Web Matrix, it says I need to have client tools installed. What are these and how to I get them?
Is there anyone that can give me a hand with this? Is it a matter of running the MSDE service? If so, how do you do this?
My apologies for not being more knowledgable, but thanks for helping me get started.
Sincerely,
Chris
View 1 Replies
View Related
Feb 2, 2003
I have just started programming using sql server. how do i get stated?
the basics are needed thanks for ur advice
View 1 Replies
View Related
Jan 2, 2008
I just installed SQL server 2005 with SP2 on Vista business.
When I open Management Studio, and try to Register a new server, I get this message:
Cannot connect to MyServer.
Additional information:
An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could nor open a connection to SQL Server) (Microsoft SQL Server, Error: 1231)
When I open SQL Server 2005 Surface Area configuration and click on Add New Administrator, there are no Available privileges. Not sure if this is relevant.
Don't know what to do, any help will be appreciated.
View 5 Replies
View Related
May 27, 2006
I have just installed SQL Server at home. Had a bit of experience at work, but at home I have to get started from scratch.
Enterprise manager says 'Connection could not be established'. In this error it talkes about 'registration properties'. I am (by default ?) a member of SQL Server group, but I have no sub items, like databases. And how do I get to where I can edit stored procedures ?
As you can see I'm quite a novice, but with a little nudge I can would a lot out myself. But not all. If you can help me now, I'll be back soon.
Thank you so much.
Regards
Robert
View 1 Replies
View Related
Nov 27, 2006
i have roughly 10-15 access databases that i am trying to run through sql server express, i installed this thing thinking it would would "just work minus a little configuration", what a misconception.
so do tell, what do i need to do. i have sql server express running, it is configured to allow remote connections. i know the path's of these access databases. how do i attach them. i saw something about the command line interface but im not interested in that because of the number of databases i will need to manage, not to mention trying to add an additional one 6 months down the road and i will be lost all over again. i thought about downloading SQL Server Management Studio thinking it would be a "gui side" to the CLI version. while it was downloading, it said i need XX program, i go to that page, it says i need XX and XX first, it is turning into a pyramid scheme to install a billion programs.
so point blank, i want to share some access databases to my local lan, i have sql server express installed and running. what do i need to finish this out. the simplest and smallest footprint solution is needed.
all this because the program im using cant do remote databases. ugh.
View 4 Replies
View Related
Apr 15, 2007
Hi,
I am trying to make a small vb.net program in visual studio 2005 edition. I would like to have a SQL database with it.
I have the Microsoft Visual Studio and SQL Server 2005 installed on my PC. I am running XP but will be moving it all to a new pc running Vista next week.
How to I get the SQL database started ?
I am not using the express edition.
View 8 Replies
View Related
Feb 23, 2007
This is a repost of my post on the VB Express board.
I would like your opinions.
I am a network guy. I learned basic years ago. I can do some html edits as needed to change sites.
Today I would like to start the long process of learning VB with .net applications. I thought I could start with Office developer xp to learn VB. I have that package.
I also would like to be able to build SQL quaries and integrations or automate and manipulate data exports and imports later down the road.
Example: Export various order and customer information from a shopping cart and import it in to Quickbooks or MS Accounting.
First off I need to be able to build web sites and I know I want to grow into VB and SQL.
What should I do?
Learn HTML from web monkey?
Start with Office xp developer Tutorials?
Start with VB Express?
Buy a book?
Can someone tell me where to start and when to move to the next language?
What is the best resource to get to it, without the bla bla bla and a commnd/syntax refference?
How do I mix Html, CSS and .Net (VB)...in the learning process?
I would like to get the fast track for the long haul.
Thanks so much.
View 1 Replies
View Related
Dec 27, 2006
I am looking to learn about reporting with SQL Server for my company
Currently we have an applicaiton running on SQL Server 2000 and have SQL Server 2000 Reporting Services installed. However, I have VB Express and Web Development Express 2005.
How do I get started here? What versions "work" with what? Do I need to either upgrade the DB to SQL 2005 or find VB.net 2003, or can I use what I have to get started?
View 1 Replies
View Related
