SQL 2012 :: Shrink Log In Synchronized DBs

Feb 23, 2015

I have two server A & B and I have always on high availability turned on SQL14.

I need to shrink the log on server A and it's fine if it also needs to shrink B.

My problem is that when I run my shrink command I get:

"cannot be shrunk until all secondaries have moved past the point where the log was added.."

I think I need to drop the High availability on the servers and then run this:

USE iTest2_Claims;
GO
ALTER DATABASE iTest2_Claims
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (iTest2_Claims_Log, 10);
GO
ALTER DATABASE iTest2_Claims
SET RECOVERY FULL;
GO

If the only way to shrink my log file is to drop high availibity how do I do that in script? and how risky is it that the high availaibity will not re-connect?

View 2 Replies


ADVERTISEMENT

Shrink Log File In Specific Database In Mirror / Synchronized / Restoring State

Aug 12, 2015

I wonder how do I shrink log file in a specific database which in Mirror/Synchronized/Restoring state..So that database is in Mirror server ( High availability ).

View 4 Replies View Related

SQL 2012 :: Argument Daily Transaction Log Shrink?

Jul 7, 2015

For a few days now I have a discussion with a colleague about shrinking the transaction log as a daily maintenance job on an OLTP database. The problem is I cant figure out a way to convince her she is doing something really wrong. Its not the first discussion.. Maintenance Plans.

She implemented this "solution" with a lot of customers as a solution against VLFs fragmentation and huge transaction log sizes. My thoughts about doing this is very clear and I have used the following arguments without success to convince her:

- To solve too many VLFs you have to focus on the actual size of the transaction log and the autogrowth settings in combination with regularly transaction log backups. Check the biggest transaction and modify the transaction log size based on this. Not use shrinking as a solution for solving many VLFs.

- Shrinking the transaction log file on a daily basis that is disk I/O intensive. When the transaction log file is too small for new transactions, the transaction log needs to grow and this will cause disk I/O, this can cause performance problems.

- It looks unprofessional.

These steps are used every morning at 6:00 AM and a transaction log backup is made every 30 minutes.

Step 1
DBCC SHRINKFILE (N'' , 0, TRUNCATEONLY);
go

Step 2
ALTER DATABASE
MODIFY FILE (NAME = N'', SIZE = 4098MB);
GO

My main purpose is making sure the customers have the best possible configuration and I cant accept this is being implemented. Are there any more arguments available for this issue?

View 2 Replies View Related

SQL 2012 :: Script To Backup And Shrink TempDB

Oct 2, 2015

I need a script to Backup & Shrink tempdb.

namesize
tempdev1024
templog64
tempdev21024
tempdev31024
tempdev41024
tempdev51024
tempdev61024
tempdev71024
tempdev81024

I can't believe how many tempdb's there are?

View 8 Replies View Related

SQL 2012 :: High Availability Group Transaction Log Shrink

Feb 3, 2015

I have a Customer running a database in a High Availability Group and I am not familiar with the set up... They have a transaction log that quadrupled in size during a data import and update which was generated by an external application. They have limited server space and would like to shrink the log again now as this import / update only happens once a year. The way this has always been dealt with in the past was by shrinking the DB and logs after the update.

Now however, when attempting to do a log or db shrink, an error message is generated which says that the log cannot be shrunk as the DB is in use as part of an Availability Group....

The more I search and try to read up on this subject, it looks like the DB has to be removed from the Availability Group before the log can be shrunk and then the Availability Group has to be re-created or restored in some way. Is there a simple solution to this conundrum?

View 9 Replies View Related

How To Keep Several Databases Synchronized???

Oct 20, 2000

I have a question about synchronizing databases. Let's say that I have 2 SQL Server 7 databases on 2 different computers running SQL Server 7.

The databases contain various related tables and are used to mange subscribers, contactpersonell and other data. In the databses subscribers are added, deleted and updated frequently so my problem is to keep the 2 databses that are located on different computers synchronized. If a subscriber is deleted on one computer, then it should be deleted in the other database too etc. These subscribers are identified by a subscriberID.

Is it possible in some (easy) way to do this synchronization?

And how should I specifiy where the databases are located so that my stored procedure (or whatever does this) knows where the databases and tables are located? I mean, if the databases are located for example in 2 different cities.

Thanks for all help!

Regards, Bob

View 1 Replies View Related

Synchronized Versions

Jul 23, 2005

I'm running a SQL 7 db on a LAN in city A with other users accessing it fromanother city (city B) over a WAN. Obviously the WAN users are getting muchslower access to the data. One thing I was wondering was if it was possibleto have two versions of the database, one on each server, and have themautomatically synchronize as users at each location make changes. Or, ifthat's not possible, then at least have the city B users get any changesthat the city A users get as they are made.Thanks for any input.Neil

View 5 Replies View Related

Last Time Synchronized

Feb 9, 2007

I use SQLCE2.0 and merge replication.

Is there a way to query SQLCE to find the time of the last synchronization without manually tracking the time myself.

Thank you.

View 1 Replies View Related

Recovery :: Copy Large Database To Another And Keep Them Synchronized?

Sep 20, 2015

We have a situation that requires converting all INT columns from source database to BIGINT in destination.

By ‘copying’ term, I consider any operation like insert-select, merge, asynchronous replication or any other that will get all data synchronized until some moment in time, i.e. 2015-12-31 10:00 PST when downtime is planned. Before that moment it isn’t important if data in source and destination DB are in sync.

Copying process starts early enough to allow appliance of any technique/technology – there’s enough time to find best solution.

Requirements:

1. Destination database is superset of source, having all tables and all columns plus few more columns in few tables. Additional columns have default NULL or they are computed. INT columns in source are BIGINT in destination.

2. If source and destination are not in sync at the moment downtime begins, it is allowed to have few minutes to wait for synchronization. For instance, if transaction logs from last hour or so need to be shipped, that would be ok. If large table need to be synchronized by comparing row-by-row, it’s not ok.

3. Source database performance must not be compromised by any copying if it’s done before scheduled downtime. There are couple of options I have in mind involving MERGE command but its appliance might be limited by current DB design. Additionally, based on business logic, I cannot be absolutely sure if some row, created while ago, wasn’t changed meanwhile.

For some large tables, there is no indicator at all when any row has changed – I cannot rely on IDs nor there is column like ‘[date last changed] DATETIME’

I have been reading about various kinds of replication, i.e. log shipping but I’m not clear if any of these is applicable. Key points, if there is replication involved, are copying process must start from empty destination database plus synchronization must not compromise source DB performance So, how to do this using any kind of replication?

View 17 Replies View Related

Setting Up A Web Synchronized Subscription For A Smart Client

Nov 20, 2005

So in a previous thread I discovered that in order to actually subscribe to any publication, the publisher needs to be a well-known network name, requiring DNS resolution. You can't simply point a SQLExpress instance at an ip addressinstance and have it resolve the communications.

View 5 Replies View Related

The Time Stamp Counter Of CPU On Scheduler Id 1 Is Not Synchronized With Other CPUs.

Sep 25, 2007


SQL Express 2005 with one instance on a networked dedicated stand-alone window XP€¦
We use this for custom software written in VB.net. We are not SQL experts€¦
Question 1) I am getting a event viewer warning that says
The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs.
This seems strange, since there is only one CPU on this machine. Any ideas?
Question 2) In SQL Server Management Studio Express, we do not have the actual data files attached to the instance, but the custom program does use the actual data files just fine, defined by the connection string. Is there any pro/con to having them attached in SQL Server Management Studio Express?
Thanks!
Bob

View 4 Replies View Related

The Time Stamp Counter Of CPU On Scheduler Id 1 Is Not Synchronized With Other CPUs.

Feb 23, 2008



Hi,
Get some errors in my log, does anyone knows how I can turn off all logging?

Indeed error should be investigated, but for the moment I just need to turn off all loggings.

Cheers!
Rickard

View 1 Replies View Related

The Time Stamp Counter Of CPU On Scheduler Id 5 Is Not Synchronized With Other CPUs.

Feb 17, 2007

64 bit sql 2005 - SP2 CTP is creating several of this messages in the log. Could somebody tell me what is the impact of these on the performance?

Also, could anybody from MS have any idea as to the release date of SP2 ( Non CTP)

View 24 Replies View Related

How To Shrink A Tranlog Which Won't Shrink

Jan 5, 2007

If 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.

View 4 Replies View Related

Shrink DB

Jun 6, 2002

I 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

View 2 Replies View Related

Shrink Db

Aug 17, 2001

if I shrink the db will it enable me to create another db with that space released by the first db?

View 1 Replies View Related

How Can I Shrink My Log?

Jul 19, 2000

How 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

View 5 Replies View Related

Log Not Shrink

Sep 27, 2002

Hi, 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

View 3 Replies View Related

Shrink Db

Jun 30, 1999

Hi 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.

View 1 Replies View Related

Why Is SQL DB So Big? Can I Shrink?

Jun 16, 2004

Hello,

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

View 12 Replies View Related

Shrink Db

Jul 26, 2004

Hello,
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.

View 6 Replies View Related

To Shrink Or Not To Shrink ??

Nov 27, 2006

Newbie 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.

View 4 Replies View Related

Shrink An LDF

May 22, 2006

I 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).

View 3 Replies View Related

Why Is Database Still Big After Shrink?

Jul 26, 2000

A 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 Related

Auto Shrink Db

Oct 24, 2001

what's this : "auto shrink " db option in properties ???

in which case may i use this option ?
please help

View 3 Replies View Related

10GB Log Won't Shrink

Sep 18, 2000

Txn 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

View 2 Replies View Related

Shrink File

Mar 27, 2000

I am looking for the correct procedure for shrinking a database file ,
please reply back if anybody knows the solution
Thanks
Ajay

View 1 Replies View Related

Shrink Transaction Log

Jan 18, 2001

Is it possible to shrink Transaction Log file? If yes, how? Urgent matter please.

Regards,

View 1 Replies View Related

How To Shrink A Log File?

Oct 20, 1999

On 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

View 1 Replies View Related

Shrink Error Log

Feb 17, 2000

Is there a way to shrink the error log in SQL 6.5 without stoping the server?

View 1 Replies View Related

My SQL 7 Log File Won't Shrink

Aug 27, 1999

Hi,

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

View 2 Replies View Related

Shrink Logfile ?

Jun 9, 2000

How 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

View 2 Replies View Related

Shrink SQL 6.5 Transaction Log

Mar 7, 2001

Is 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 Related







Copyrights 2005-15 www.BigResource.com, All rights reserved