.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Database Mirroring and shrink log files

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

Hi everybody!

I have productive database on mirror. The mirror in asynchronous mode. On principal server the log file have size 95Gb. I want to shink log files. Can i shink this log files, or a get problems if i tray do whis?


Thanks a lot!


View Complete Post

More Related Resource Links

TDE key rotation in the database mirroring situation.

I have set up database mirroring for TDE enabled database and they worked fine as far as I got all the key infrastructure identical on both Principal and mirrored SQL servers. Now it is coming to our yearly key rotation practice for compliance.  After I regenerate the DEK on principal server and also encrypt it with a newer certificate,  the mirroring is suspended and I am not able to resume it any more.  (Although I have added this newer certificate to the mirrored server too.) It is understandable because now the DEK is out of sync.  However, what are the correct steps to do the key rotation in the mirroring scenario? The bottom line is:  I DO NOT want to set up mirroring again since our backups are huge and take very long time to copy the backups from principal server to mirrored server. thanks for any input in advance.  

How to attach distribution database files

Hi  i am new to sql server. when i am exploring replication concepts, i did a small mistake i.e. i have deleted distribution database.after that i realised and try to create a new one but it still points to the old database named as "distribution" which is not available. i didnt take the backup of the database before dropping. But i have mdf and ldf files of the distribution database. so can i use the sp_attach method to add the  mdf and ldf files? or any other way to fix this issue? please help me on resolving this issue Thanks, Rock  

Shrink database log file sql server 2005

Hi, I am trying to shrink log file for a testing database.This database will be restored with production database periodically.When i tried to create backup of the log file Processed 0 pages for database 'hcbeta', file 'HC_log' on file 6. 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. BACKUP LOG successfully processed 0 pages in 0.423 seconds (0.000 MB/sec). I tried EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 Resulted with error:Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication. So tried to publish the database using EXEC sp_dboption 'hcbeta', 'Publish', 'true' resulted error mesage:Msg 15242, Level 16, State 1, Procedure sp_dboption, Line 138 Database option 'Publish' is not unique. and retrived duplicate_options as "merge Publish" ,"Publish" Plesae help me in shrinking my database log file. Thanks, Adi.

Insert multiple files to SQL Database in VB

My files are being inserted but the byte array is showing as a 0x0000... etc for every file after the first inserted file. The first inserted image is correct. The database is set up as an Image type. The problem exists in the code here. Thank you in advance! Dim uploads As HttpFileCollection uploads = HttpContext.Current.Request.FilesFor i As Integer = 0 To (uploads.Count - 1) If (uploads(i).ContentLength > 0) Then Dim c As String = System.IO.Path.GetFileName(uploads(i).FileName) 'Dim upload As HttpPostedFile = uploads(i) If Not fileUpEx.PostedFile Is Nothing And _ fileUpEx.PostedFile.ContentLength > 0 Then Try If CallingTab >= "" Then Call UploadImage(c, uploads(i).ContentLength, uploads(i).ContentType, uploads(i).InputStream, _ strUsername, intSiteID, intCompanyID, strDocumentType, CallingTab, NewVersionNumber) Else lblStatus.Text = "Document Not successfully uploaded to the database! Problem occured with the Calling Tab" End If Catch Exc As Exception Response.Write("Error: " & Exc.Message) End Try

what is the better design for location of system database data / log files and user data/log file,

is there a guideline for better performance for system database data location log location, like tempdb location etc. also better location for user databases/data and log.

adding sqlserver database files in the time of project deployeement

hi all,        how can i add sql server 2005 database files in the time of a windows application project deployment i have designed a project in Visual Studio 2005 using vb.net language and i want add sql server 2005 database file to it. i am connecting to sql server database by the connection string not using any any dataset or anything else. so how can i add database file ... please replay... 

If one database goes down in database mirroring then other will come up but because there is no Virt

In database Mirroring can I have a virtual IP... Please tell fast.. Actually we were trying to install the sql server cluster but thats not possible bcoz it is in production of Domino Server.My confusion is "If one database goes down in database mirroring then other will come up but because there is no Virtual IP type of thing in this then how my application will connect to another without manual interruption of IP change..."     "SQLSERVER DBA" "INDIA"

SQL Server 2008 Database Restore(Database Mirroring)

HI,I tried to impliment Database mirrorin in SQL Server 2008.I have 3 Sqlserver 2008 Eneterprise Edition in 3 diiferent computers with same domain.Pricnicipal server =TCP://SPHDev.sphinet.com.sg:5022.Mirror=TCP://SPHPrimary.sphinet.com.sg:5022 and witness =TCP://wsc211.sphinet.com.sg:5022.But when i click start mirroring button i got an error TITLE: Database Properties------------------------------ An error occurred while starting mirroring. ------------------------------ADDITIONAL INFORMATION: Alter failed for Database 'SPH_MSCRM'.  (Microsoft.SqlServer.Smo) ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The server network address "TCP://SPHPRIMARY.sphinet.com.sg:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418).This is the error message i got from Error Log"2009-06-03 17:01:08.89 spid27s     Database mirroring connection error 2 'DNS lookup failed with error: '11001(No such host is known.)'.' for 'TCP://SPHPRIMARY.sphinet.com.sg:5022"I restored Principal server DB  by using RESTORE WITH NORECOVERY and now this  database in mirror server is now look like as DBName(Restoring....).How i can identify

I have to do it manually or is there any other way...?to transfer the users in database mirroring

One issue in database mirroring: In Primary server every week 2-3 users will additionally added.How will these users gone to Mirror Server database?I have to do it manually or is there any other way...? One more thing is that Primary server is llve  and 24 hours users connected, If I want to migrate users on other server then I have to make mirror server as primary server and then run SP_rev_login , it is automatic failover then  the newly created users will not be at the mirror server??then problem"SQLSERVER DBA" "INDIA"

In database Mirroring:

In database Mirroring: I have found that there is a "mirror Server" option in System DSN ,Sql Native Client configuration but one confusion is here that : Should I have to create the same DSN configuration on Mirror server without giving "mirror Server" name in this case??"SQLSERVER DBA" "INDIA"

Multiple Data Files Per Database Not Allocating

We recently read that I/O performance can be increased by creating multiple data files for a database: "As a general guideline, create one data file for each CPU" So went ahead and did this, and restarted the SQL engine, but still it is writing all data to the first file "Production_data_1". We would assume after we restarted it would reallocate the data across all four data files. Currently, the other three data files have 1GB of space allocated, but the engine is not writing data to the other three files. How can we distribute the existing data across all four data files? See a screenshot we took at: http://www.ftoperations.net/screenshot.png

Database Mirroring: How To Set Up Servers?

  Hi, I am currently reading into Database Mirroring... A few questions regarding the transaction log came up my mind when trying to find a setup/disaster recovery strategy:   Imagine this situation: I am already running a SQL Server database... Now I want to add synchronized database mirroring by adding a mirror database to the setup. Which prerequisites are required on the mirror database? Is it sufficient to use an empty database? Or do I need to restore a backup of the principal database first?   Same for disaster recovery: Imagine the primary database goes offline and the Witness designates the mirror database as the principal database. After re-installing Windows/SQL Server on the former principal machine, which prerequisites are required to let it become the mirror of the new principal database? Do I need to create a current backup of the new principal database? Or will an old backup of the old principal database do? Or will even an empty database on the new machine do the job?   Finally, this is a more general question I have, regarding the transaction log: The mirror database is fed from the principal database's transaction log... How can I make sure that all necessary transaction log records are still available in the transaction log? I couldn't find information on how records are deleted from the transaction log file... Does the transaction

How to shrink the sharepoint database log file



I have a SharePoint content database with the size 70GB but he log file is 450GB. i wanted to shrink the log file. Can anyone tell me  how to do that ? It would be great if anyone can provide me the complete flow.

  1. Do I have to shink the log file when the database is offline?
  2. what if we just take the backup and create a new log file ?
  3. How do we restrict the size of the log file by not growing big but just auto shrink by it self.

This is all on the production server, i need to fix this asap. Any help would be appreciated.

Thanks in Advance.

How to shrink the sharepoint database log file



I have a SharePoint content database with the size 70GB but he log file is 450GB. i wanted to shrink the log file. Can anyone tell me  how to do that ? It would be great if anyone can provide me the complete flow.

  1. Do I have to shink the log file when the database is offline?
  2. what if we just take the backup and create a new log file ?
  3. How do we restrict the size of the log file by not growing big but just auto shrink by it self.

This is all on the production server, i need to fix this asap. Any help would be appreciated.

Thanks in Advance.


Database snapshots do not work well with databases having multiple log files


It appears there is a bug in the implementation of database snapshots.

If you create a snapshot on a database with multiple log files:

Then restore from the snapshot:

then all of you log files, but one will disappear. As a result the backup command will fail with the following message:
Msg 3636, Level 16, State 2, Line 1
An error occurred while processing 'BackupMetadata' metadata for database id 12 file id 10.
Msg 3046, Level 16, State 2, Line 1
Inconsistent metadata has been encountered. The on

SQL Server 2005 - Differences between "Autoshrink" database option and manual "shrink database / log


Hello All !

I want to know if there are differences between "Autoshrink" database option and the SQL DBCC shrink (database/file) command.

I have, in production environment, a MS application (MIIS) with a SQL 2005 DB. This application takes very large db files (logs and datas). It's absolutely normal (several millions of transactions by day)

However, I had this morning a problem with disk space.

If I run manual dbcc shrink (db / file): I recover 4 GB

If I set "Autoshrink" option: I recover 50 GB

Microsoft doesn't recommand to set the "autoshrink" option on the majority of databases (for performances problems)

Are there links about these differences ?

Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2

set up database with .bak files in SAL server management studio



I need to connect to 3 remote databases from my local machine and  restore 3 .bak files, one for each database.

I am new to asp and sql server and am finding SQL Server Management studio quite confusing.  I cannot find any options to connect remotely or restore a database from .bak.

Many thanks

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend