.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

Simple recovery database bloating?

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server
I have a several hundred GB simple recovery dev database bloats by 200-300 GB and does not release the free space to the OS. Sometimes drive runs out of space because of this. I had to shrink database every few months to bring back database to its size and release the space back to OS. Database has autogrow turned on.  Question is what can I do on a dailhy/weekly basis to prevent the database to grow that big so that I do not run out of space on the drive? What makes this grow and not release?

View Complete Post

More Related Resource Links

Database Switched to Simple Recovery Model


Hi there,

We came across a situation where the transaction log was full and we extended the disk that holds the transaction log. What happened after that is the log truncated and we noticed the recovery model of the database switched from FULL to SIMPLE. Does anyone come across this behaviour?

Thanks in advance.


Yong Hwee

Is a database online when it is changed from full to simple recovery model?



We have some databases that occaisally have very large ldf files and I have learned that the best way to shrink the ldf file is to change the backup mode to simple, shrink the log file, convert it back to full and do a full database backup so that the log file backups will run again. My question is during this process is the database available to the end users? When you guys do this do you feel it is nessesary to let the end users know or will they not notice what is going on?



Thanks! Kevin

execute update timedout expires , table with trigger due to recovery of database


I have table A in database A  , in table A i have trigger to insert record to database B table A, each update and insert of database A -table A, triiger fire and insert record in database B table A, when i try to update some times asp.net application gives error

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

and then error log says System.Data.SqlClient.SqlException: Database 'database B is being recovered. Waiting until recovery is finished.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean ret

Database in recovery

Our server ran out of disk space during a stored procedure process. We were able to free up some space, stopped all SQL services, restarted all SQL services and now SQL Management Studio shows the database to be in recovery.  Is there a way to monitor this to ensure progress is being made and to estimate when it might complete?

My Sql server 2005 Database in recovery mode for 3 -4 hours every day

Hi , Some of our Sql Server 2005 databases remain in recovery mode after midnight for almost 3 to 4 hours. So when my application access them error "Databse in recovery" and for same database this happens again and again every night. Please suggest why and how this can be solved?

Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential cau

Attempting to upgrade from SQL 2005 EE to SQL 2008 EE on Vmware ESX 3.5 virtual server. below is info from Detail.txt related to successful install with failures... 2010-09-02 13:50:46 Slp: Sco: Attempting to check if file 'D:\Data\MSSQL.1\MSSQL\LOG\ERRORLOG' exists 2010-09-02 13:50:46 Slp: Sco: Returning security descriptor O:BAG:SYD:AI(A;ID;FA;;;S-1-5-21-3788118842-658835432-3441893140-1006)(A;ID;FA;;;S-1-5-21-3788118842-658835432-3441893140-1007)(A;ID;FA;;;BA)(A;ID;FA;;;SY)S:AI(AU;IDFA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD) 2010-09-02 13:50:46 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC. 2010-09-02 13:50:46 Slp: Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes. 2010-09-02 13:50:46 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC. 2010-09-02 13:50:46 Slp: Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineConfigException: Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes. 2010-09-02 13:50:46 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlServerServiceBase.WaitSqlServerStart(Process processSql) 2010-09-02 13:50:46 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlServerServiceSCM.StartSqlServer(St

need a simple login page cus my hosting provider system wont allow me to have more than one database

I dont really write html code too well  but I manged to get my web site developed and deployed using the built in features such as drag n drop, master page and user account authentication that make web developing almost a codeless experience. However; my hosting provider system wont allow me to have more than one database, this causes problems becasue when I developed my web app using vb2010 web developer (asp.net v2) I used the built in web config tool to manage users for my site, now after I deployed to my domain it all worked ok with the exeption of the login page, when I tried to log in the system threw a error message saying "database not found." or a page of "gobble-dee-gook" that says the same thing When I contacted support (my hosting provider) they said i have to create a table of users in my database file via 2008 sql server, thats ok I can do that, but my real problem is I dont know how to write appropriate code to reference the table. So heres my problem:  I just want to have some kind of login page with a couple of txtboxes and a click button that maybe using the VB code behind or html checks the input variables "username" "password" against something that is hard coded into vb a string variable or a constant and then branches to Mainmenu.aspx if successfull. I just need it real simple, no fancy stuff just a login s

Recovery of Database Disabled SQL Agent, Cannot Detach Database, Etc... Please Help

I am a beginner SQL user, as the company laid off the IT guy and I am stuck with this.  So I really need some help on this one.  Any ideas would be appreciated. I was doing a merge join command to put data from one database to another.  These are 60GB databases with valuable data.  Halfway through I was told by client to just stop the query.  I did this but it left the good database with a large 80GB log file. When I rebooted the machine the recovery of this database took 5-10 minutes...  Thus the SQL Agent disabled itself waiting for recovery.  After recovery was 100% complete with no errors SQL Agent will not restart.  I can do a force turn on using a "1" statement, but the SQL Agent log file says it is still waiting for recovery. In any event, I cannot detach the database because it says 1 user connected.  I cannot backup, drop, etc...  I saw in one forum a guy said to turn service off, copy database to a new location, turn service back on, delete the database, attach copied database...  But I am a little worried about deleting. Once again - I am a SQL newbie and really need a pointer on this one.  I need to free this database and get a it back to a normal sized log file. Advice???

Simple Recovery Model Yet Transaction Log Is Full

Hi there, There's a database which has been configured with SIMPLE recovery model but yet the transaction log will grow and fill up the disk at times. Is there anything one can do to prevent the log from filling up the disk? Thanks in advance.

Database peaks CPU after recovery. Stuck in recovery mode

Rather simple problem. The database is stuck in recovery mode and I can't use it.. mssqlserver process want 100% CPU. I left it in recovery mode for almost a week with no luck. My idea was that it would repair itself. But it doesn't help and now I'm about to give up on that database. It seems quite odd that the last log entries in the ERRORLOG says: "Recovery complete..  phase 2 of 3" and then it says "Recovery 97% complete ..phase 3 of 3" After that, CPU peaks. Any ideas? Thank you! ERRORLOG: 2009-09-14 15:52:26.89 Server      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)     Nov 24 2008 13:01:59     Copyright (c) 1988-2005 Microsoft Corporation     Express Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 2009-09-14 15:52:26.89 Server      (c) 2005 Microsoft Corporation. 2009-09-14 15:52:26.89 Server      All rights reserved. 2009-09-14 15:52:26.89 Server      Server process ID is 8860. 2009-09-14 15:52:26.89 Server      Authentication mode is WINDOWS-ONLY. 2009-09-14 15:52:26.89 Server      Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. 2009-09-14 15:52:26.89 Server      This instance of SQL Server last reported using a process ID of 7996 at 9/14/2009 3:15:48 PM (local) 9/14/2009 1:15:48 PM (UTC). This is an informational message only; no user action is required. 2009-0

Need help with simple database search



Ive got a textbox and a button. When I type a name of a customer I want to perform a search in my database and display the result as a table. I cant find anything on google that shows me how to do this.

Some of you got any examples of how to do this with ADO.net?

Database in recovery


I have a database currently showing to be in recovery.  I have not been able to find a GUI method to monitor the progress (to determine when it might complete) so is there a command line T-SQL method to monitor the recovery process?


simple recovery model, backup plan best practise

any guideline on backup plan for a simple recovery model? if there is no differential backup, only has daily full backup, when the sever crashes, will it lose all info after last backup? if the data file crashes, but log file exists ok, is it possible to restore info after last backup? such as tail log backup?

SQL Server 2005 Database recovery takes long when starting


I have a large database which is consistently taking between 30 and 60 minutes to come up after restarting SQL Server.  How can I speed this up?  Please read below before responding.

I have a Winows 2003, 2 node cluster running 1 instance of SQL Server 2005 enterprise ed.    The server has 64GB of RAM, and 4 Quad core chips (16 logical processors).  The storage is on SAN, and is a mix of RAID 10 and RAID 5 volumes.  The instance has multiple DB's in it but 1 is far and away the largest @ 2TB allocated (about 1TB actual data).   The database in question is broken into many datafiles as follows:


Primary filegroup = 1 10GB datafile

Secondary filegroup = 8 x 100GB datafiles spread evenly over 4 seperate volumes for I/O performance.

Partitioning filegroups = many many files used for a partitioned table (file sizes ranging from 1.5GB to 20GB)

Again, my issue is the following.   This 2TB database takes close to an hour to startup everytime the instance of SQL Server restarts, failsover etc...  

Couple of notes:

1) We have instant file initialization configured

2) Recovery model = simple

3) Recovery interval = 0

4) There are no active large transactions running when the instance goes down


Database is "in recovery" for 3 hours



I booted a server today and it stuck in recovery state. It is SQL2005.  It is 10TB database. Error log for SQL Server doesn't show the progress. I only found "Starting up database 'MyDB'" .  How may I check what is going on.

-- this query shows percent completed 0 for all processes

select der.session_id, der.command, der.

recovery mode = simple but ldf grows


Hia all,

I set the recovery mode of my db to Simple.

I see that the log file size is increasing.

1) Why is it happening ? ( i.e. a lot of transactions have been aborted )

2) Is there any problem if I shrink the log file ?


Many thanks

Transaction log filling up in "Simple" recovery model, catch 22


I need to do the quaterly cleanup of our database involving moving historical data to our history db and delete-ing records in a our operational datastore. I've set the recovery model to "simple" as advertised in numerous fora and KB articles. Still the the transaction log goes through the roof evertime I do a

delete * from myTable
where datetime < '2006-10-01'

Where eventually it fails becouse transaction log is full....(Msg 9002)

If I do a

BACKUP LOG myDatabase
TO myBackup

sql claims that I cannot perform a transaction log backup (Msg 4208). So I 'm stuck with a large log file in the "simple" recovery model that can not be shrunk...

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