SQL 2008 Ldf files growing
I'm Running SQL 2008 R2 server 2003. A job does a full backup daily at 5.00AM then another job runs Transaction log backups every hour using this command: -
BACKUP LOG Mydb
TO Mydb_DailyBackup
WITH NOINIT
My problem is my LDF files keep growing (I have just had to change Recovery Model to simple and Shrink to stop my server crashing)
Does anyone know the syntax to shrink my db?
BACKUP LOG Mydb
TO Mydb_DailyBackup
WITH NOINIT
My problem is my LDF files keep growing (I have just had to change Recovery Model to simple and Shrink to stop my server crashing)
Does anyone know the syntax to shrink my db?
Last Comment
YZlat
http://support.microsoft.com/kb/907511
to shrink the log file changing the database from FULL Recovery to SIMPLE Changes the LSN (Log Sequence of the database).it changes the database lsn number and you cant restore the transaction log files of the database.better take a FULL Backup of the database after changing its Recovery mode.
run the belwo command in the ssms
DBCC OPENTRAN to check for any open transactions in the database
run the belwo command in the ssms
DBCC OPENTRAN to check for any open transactions in the database
ASKER
I looked at the MS support link then went here for an example: -
http://forum.katarincic.com/Default.aspx?g=posts&t=34
I added this step and it seems to be working: -
DBCC SHRINKFILE (MyDB_Log, 50 )
'MyDB_Log' found in Database properties 'Logical Name'
'50' (Mb) is the size to shrink to.
I will monitor the size over the next few days and report back
http://forum.katarincic.com/Default.aspx?g=posts&t=34
I added this step and it seems to be working: -
DBCC SHRINKFILE (MyDB_Log, 50 )
'MyDB_Log' found in Database properties 'Logical Name'
'50' (Mb) is the size to shrink to.
I will monitor the size over the next few days and report back
DBCC SHRINKFILE
I trust you are not planning on doing this on a scheduled basis. That is a very bad idea.
I trust you are not planning on doing this on a scheduled basis. That is a very bad idea.
ASKER
Hi acperkins, why is it bad?
I do an overnight full backup then a trans log every hour of the day followed by the DBCC SHRINKFILE.
This stops the trans log filling up the disk.
I do an overnight full backup then a trans log every hour of the day followed by the DBCC SHRINKFILE.
This stops the trans log filling up the disk.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for the help!
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
