.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

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

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server

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?

View Complete Post

More Related Resource Links

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

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

Log file size question

I recently setup transaction log backups after we had a log file get near its capacity. I noticed after log files are truncated, they still use up the same amount of disk space. Is it safe to shrink a log file?  If so how do you determine what is an appropriate size to shrink it to? I ask this because some databases, like a sharepoint content database, have log files that are 130 GB in size when their data files are only 5 GB.  I assume that now that I am running hourly log backups they dont need logg files that huge. -Edit - Also most of the logg files are setup with the default settings

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)  

calculating the size required for backup of database

Hi, How to calculate the size required to take backup of a database in mssql 2000.the results returned by sp_spaceused differ too much from the actual size taken on the disk. Thanks in advance

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.

file size increase when moving from file system to sharepoint 2007

Hi, While moving files from file system to Sharepoint 2007 via windows explorer view, why do file sizes increase? thanks, Burcu  

How can I reduce the .sql file size?

The program in our office has program called eSRE. My Database server is called Wamp Server. In Wamp Server there is a MySql 5.0. MySql is my Database. Now, I already encode a lot of records in my database. When my computer encounter an error, I backed up my database and has named of lgusresystem.sql with 3,895KB. Now, after I reprogrammed my computer and reinstalled the said program, I imported such backed up database file (lgusresystem.sql) into a MySql Server but the computer prompt a message like this "No data was received to import. Either no file name was submitted, or the file size exceeded the maximum size permitted by your PHP configuration". Here in MySql Server, I can see here that the maximum file to be imported is only 2,048KiB. So, what am I going to do so that I can restore my latest records in my database? I hope you will give an appropriate immediate action on this matter. Thank you very much.   JEFFREY R. DAWATON from Philippines  

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. 

Size limit for STSADM -o backup


Hi all

Is there a size limit for the .dat backup that can be taken using STSADM -o backup

and for the .dat that can be restore using STSADM.

Thanks in Advance


check file size before uploading process in c# with asp.net


Hi All,

Thank you for Advance.

currently we are configured execution Timeout="1200" (The default is 110 seconds)  and maxRequestLength="12288"  (The default is 4096 KB)

but some times users upload files above 12MB so we are getting "Maximum request length exceeded" error.

we are checking the file size using client side javascript ActiveXObject object. but ActiveXObject only works in IE not in others like mozilla,crome,...

Jacescript Code are

function GetSize(file)
  var fso=new ActiveXObject("Scripting.FileSystemObject");
  var f=fso.getFile(file);
  return fso.getFile(file).size;


how can i check the file size before uploading process with support all the browser.

any posiblities for checking file size before server side script/object?


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”

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.

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