our logfile size is full what can i do?

Question

thanks

---

Thanks, Purna

Answer 1

Set the database to SIMPLE recovery model and run DBCC SHRINKFILE(log_file,100) to reduce physical size.

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 2

or add extra log file to the DB

http://msdn.microsoft.com/en-us/library/ms189253.aspx

vt

EDIT.

Adding 2nd log file is a temporary fix only. You need to find out what causing the log file to grow.. see the links

http://msdn.microsoft.com/en-us/library/ms175495.aspx

http://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 3

check my blog for best practice  to shrink a log file

http://sqlservr.blog.com/2012/06/26/best-practice-to-shrink-in-logldf-file-in-sql-server/

run this command in management studio

select name,recovery_model_desc from sys.databases

if your database is in SIMPLE Recovery mode then

dbcc shrinkfile('dbname_log',0)

if your database is in FULL Recovery mode and  Log Shipping, is implemented , then check my blog

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 4

No benefit adding secondary log file...

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 5

No benefit adding secondary log file...

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

correct.. but way the other party sounded was.. he was having a disaster..edited the post..

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 6

http://www.karaszi.com/SQLServer/info_large_transaction_log_file.asp

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 7

We need to take log back of the database then shrink the log file, after shrinking the log file free space will be released to the disk.

 

Example:

BACKUP LOG <databasename>  TO DISK = ‘path’

BACKUP LOG SALES TO DISK = ‘D:\SALES_LOG.TRN’

 

USE <dbname>

USE SALES

sp_helpfile

DBCC SHRINKFILE(‘<logfilename>’,<targetsizein MB>)

DBCC SHRINKFILE(‘sales_log’,100)

---

pradeep kumar reddy

Answer 8

Hi,

If its a User database and your db recovery model is full then schedule a hourly or half hourly based on ur business needs transcation log backup.So your log file size will be maintained to least.

Shrinking is not the best option for production database's....in addition to change recovery model to simple you wont be able to restore in point in time.So full recovery is best option.

Hope it helps you.

---

Regards, Ashish

------------------------------------------------------------

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 9

We need to take log back of the database then shrink the log file, after shrinking the log file free space will be released to the disk.

Example:

BACKUP LOG <databasename>  TO DISK = ‘path’

BACKUP LOG SALES TO DISK = ‘D:\SALES_LOG.TRN’

USE <dbname>

USE SALES

sp_helpfile

DBCC SHRINKFILE(‘<logfilename>’,<targetsizein MB>)

DBCC SHRINKFILE(‘sales_log’,100)

---

pradeep kumar reddy

Hey,

Shrinking is purely not advisebly....

pls check out the below blog....

http://aashishthakkar.blogspot.in/2012/09/not-to-run-shrinkdatabase.html

In addition some ppl say occasionally shrink is sometimes necessary but i agn

I don’t agree with that.. unless it is absolutely sure that the db is NOT going grow ever again then it make sense.. else leave the free space there.. Think about a situation, you shrunk the db and there is only 5 MB free space left and one fine morning  one your developer transfer a 10MB size wroth data into it. The data file has to grow to accommodate the extra 5MB ..right..??. This auto growth typically hurt the system performance ..

If you then also want to then shirk db only if it’s in readonly mode..

Nw its upto you if you want to hurt Performance then procced with shrinkng process...

---

---

---

Regards, Ashish

------------------------------------------------------------

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 10

thanks for sharing this valuable information :)