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

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

Transaction log backup creates big log backup file

Posted By:      Posted Date: October 18, 2010    Points: 0   Category :Sql Server

HI All,

One of my customer having problem with the transaction log backup .There is one maintenance job that will take Full backup on daily basis.and transaction log backup in 4 hrs.On Sunday there is one rebuild index job .After this rebuild index job the transaction log is taking 17 minutes and it creates 12 gb transaction log backup (only for the first time). Usually the transaction  took 6/8 seconds and the transaction file will be in less than 100 mb.

Pleas help me to resolve this issue


Thanks in advance 


View Complete Post

More Related Resource Links

Multiple databases backup/restore in a transaction, if possible, into only one file


Hi dear,

I have three databases named XDW, XOLTP, XOLTPSchema. All of these are for only one application. In the application, user should do backup/restore using GUI.


1. How I should do backup/restore in one transaction? i.e. all of three actions should be success elsewhere all of them should fail.

2. Is it possible to backup them into only one file e.g. X.bak?

3. Or, What is the standard solution in this condition?

Thanks in advance.

How To: Joining multiple DataTable using “LINQ to DataSet”

Transaction Log Backup & Transaction Log file size



Database is in Full Recovery Model.  Backup is done as follows

1)  Full Back up done every Sunday 00:00

2)  Differential Backup done Done Every Day at 00:00

3)  Transaction Log is Backed Up Every 15minutes.


I have a problem Transaction Log Size suddenly gone to 22GB (Data File is only 100mb) and Transaction Log Backup ruinning for a long time but it is not backing up. 


Why is this problem occuring?  How do I solve this? 


Toolbox: Easy File Backup, Exploring Files And Folders Inside Visual Studio, Multiple Monitor Softwa


If the responsibility for creating, managing, and executing routine backups is yours, these tools will make it easier. Also see how you can browse folders and files from inside Visual Studio.

Scott Mitchell

MSDN Magazine May 2009

SQL Server backup failing on one file

Hi I have a server which is not backing up a db each day. When I look at the event log it displays the following...   SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\########\########_File_CS_DefaultData.ndf] in database [########] (5). The OS file handle is 0x000006A0. The offset of the latest long I/O is: 0x00000008e00000 2010-08-16 16:07:38.19 Backup Error: 3203, Severity: 16, State: 1. 2010-08-16 16:07:38.19 Backup Read on "D:\########\########_File_CS_DefaultData.ndf" failed: 1117(failed to retrieve text for this error. Reason: 15100) 2010-08-16 16:07:38.20 Backup Error: 3041, Severity: 16, State: 1. 2010-08-16 16:07:38.20 Backup BACKUP failed to complete the command BACKUP DATABASE ########. Check the backup application log for detailed messages. 2010-08-16 16:12:14.32 spid5s SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\########\########_File_CS_DefaultData.ndf] in database [########] (5). The OS file handle is 0x000006A0. The offset of the latest long I/O is: 0x00000009900000 2010-08-16 16:13:07.02 Backup Error: 3203, Severity: 16, State: 1. 2010-08-16 16:13:07.02 Backup Read on "D:\########\########_File_CS_DefaultData.ndf" failed: 1117(failed to retrieve text for this error. Reason: 15105) 2010-

Can't restore database backup file in my database ? using sql server 2005. please help

VERY IMPORTANT i am trying to restore database.bak in sql server 2005 (i know the database.bak was also generated in sql 2005 server) i am trying to restore back up database .bak into the new database i just created in sql server 2005 i have saved my database .bak into c drive and when i select database .bak "From Device", it doesn't get populated in the list below and i see nothing and it keeps on prompting a message "You must select a restore source" Here's the screen shot:   PLEASE HELP..it's really important (i tried restoring database in sql server 2008 and it was sucessful but i am facing this problem in sql server 2005 only)  

Why doesn't SQL Server 2005 Enterprise always shrink a log file after a backup?

I have several databases that, despite being backed up regularly, do not release space to the operating system.  In one extreme case, I have a log file that is less than 1% utilized (and has been for months.) I know how to manually force a shrink, but I should not need to do that on any sort of regular basis.  In fact, it is against best practices to manually shrink a log file (or any database file for that matter) because of the very high risk of an unrecoverable error. It is my understanding that SQL Server will eventually handle these things on its own.  However, it does not appear to be doing so.  Any thoughts as to why? Thanks, Dave

TDE disabled and service restarted but still can't restore a new backup file

Hello, I disabled TDE on my dev database because we determined no personally identifiable info will be stored.  Disabling was done like so:Then I created a backup of MyDB database and tried to restore on a different server instance on my same machine (using SSMS) and I get  the following error: ALTER DATABASE MyDB SET ENCRYPTION OFF GO use MyDB go drop database encryption key go use master go DROP certificate DevCert GO DROP MASTER KEY GO Afterwards, I ran the following query to ensure there are no encrypted databases, and it returns zero rows: SELECT DB_NAME(database_id) AS dbname, encryption_state, case encryption_state   WHEN 0 THEN 'Unencrypted (no database encryption key present)'   WHEN 1 THEN 'Unencrypted'   WHEN 2 THEN 'Encryption in Progress'   WHEN 3 THEN 'Encrypted'   WHEN 4 THEN 'Key Change in Progress'   WHEN 5 THEN 'Decryption in Progress'   ELSE CAST(encryption_state AS varchar(20)) END AS encryption_state, key_algorithm, key_length FROM sys.dm_database_encryption_keys Then I created a backup of MyDB database and tried to restore on a different server instance on my same machine (using the SSMS) and I get the following fatal error: The media family on device 'c:\Program Files\...\Backup\TDE_removed_from_db.bak' is incorrectly formed.  SQL Server cannot process this media family. 

How can I shrink the size of LDF file after BACKUP LOG?


Ive just done BACKUP LOG against particular database and found no changes in size for the appropriate LDF file. It is shrunk only after I issue:







But this approach breaks all logs chain. Is there any way to shrink the LDF file for FULL backup model without destroying the whole sequece of log backups?

How to retrieve the mysql backup file (backup.sql) into sql


-- Database: `ebhcemr`
USE ebhcemr;
-- --------------------------------------------------------

-- Table structure for table `addresses`

CREATE TABLE  addresses (
  id int(11) NOT NULL default '0',
  line1 varchar(255) default NULL,
  line2 varchar(255) default NULL,
  city varchar(255) default NULL,
  [state] varchar(35) default NULL,
  zip varchar(10) default NULL,
  plus_four varchar(4) default NULL,
  country varchar(255) default NULL,
  foreign_id int(11) default NULL,
  PRIMARY KEY  (id),
  KEY foreign_id (foreign_id)

How to make a backup file (.bak) from a database (.mdf)


I need to make a backup file(.bak) of a database(.mdf)

I learned I need to use MS SQL Server management studio Express to do so, but I can't figure it out how.

I don't know how to connect to the database using this program. In the "Connect to Server" dialogue window, I am supposed to choose a server name from the dropdown list, but nothing comes up. What am i doing wrong? Pls see the screet shot. Please help!

In Log-shipping what is load delay period on secondary server - Skipping log backup file since load


During logshipping, job on secondary server is ran successfully BUT give this information

"Skipping log backup file since load delay period has not expired ...."

What is this "Load delay period" ? Can we configure this somehow, somewhere ?

NOTE : The value on "Restore Transasction Log tab", Delay Restoring backups at least = Default (zero minutes)


Think BIG but Positive, may be GLOBAL better UNIVERSAL.

Problem in restoring backup file


at System.DBNull.System.IConvertible.ToBoolean(IFormatProvider provider)
   at System.Convert.ToBoolean(Object value, IFormatProvider provider)
   at Microsoft.SqlServer.Management.SqlManagerUI.BakDevMediaContents.InitBackupSets(Restore sqlRestore)
   at Microsoft.SqlServer.Management.SqlManagerUI.BakDevMediaContents.InitProp()
   at Microsoft.SqlServer.Management.SqlManagerUI.BakDevMediaContents.InitializeOffline(CDataContainer dataContainer)
   at Microsoft.SqlServer.Management.SqlManagerUI.BackupDeviceContents..ctor(CDataContainer dataContainer, IServiceProvider sp)
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreSelectBackupSource.buttonContents_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)

returning backup file to application


Hi all. I want send backup statement from my c# code to sql server then sql server return same back up file to my application in varbinary(max). what code is necessary for that?

CHeck to see if file exists on Server before backup



  I want to check first to see ifthe xyz.bak file exsits before I perform a backup.



File system permission error during BACKUP service master key



I'm having a strange issue with file system permissions.

A backup database to file location e:\sqlbackup works fine.
A BACKUP SERVICE MASTER KEY or BACKUP MASTER KEY fails with the error ´MSG 15240, Level 16, State 1 Cannot Write into file ´e:\sqlbackup\file.bak' Verify that you have write permissions, etc...'

What am I missing?


returning backup file

Hi all. I want send backup statement from my c# code to sql server then sql server return same back up file to my application in varbinary(max). what code is necessary for that? I use windows application by c#.
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