Managing the Size of log files (.ldf) on SQL Server
Hey Guys,
I have a SQL 2005 server which has not really been maitained by our DBA. I have now taken over the task to trying to get this SQL server is as good of a shape as possible before we eventually migrate it.
My question arises in regards to the log files and probably has several parts to it, so I will try and break it down to what I already know and whats the best way to go about finding a solution.
Problem: I have several databases, some range from 256 Megs and others are at 2 GIGS. Each of these databases however have a ridicoulously large log file. The 256 mb file is around 30 GB and the 2 GB db is around 135 GB.
Now initially before my coworker had left, he had setup the agent for SQL Server in backup exec, to first run the DB backups followed by logs to truncate them. Because of the size of the log files, these backups are taking notoriously long. Now upon investigation, at first I was very initially confused w/ the defintion of truncating and shrink. I now think i understand the difference, where truncating does not nessarily reduce file space but keeps a record of the transactions that are passed / old since the last full db backup. Shrinking physically reduces the file by removing the free space from the log file.
Now I can easily solve my problem by running the shrink command on the dbs, but before I went ahead and did that, I did a bit of research and am now aware that SHRINKING is a big no, or at least that's what a number of people say. It removes the contingous filing structure of a db, causes a need for indexes to be rebuilt and overall creates a general performance hit. Not to mention that as soon as the db is required to grow again, it will and it shall.
So my question are:
How do I shrink my .ldf files w/out using the shrink command and suffering a performance penalty?
Do you guys recommend backing up my db's with SQL Agent from backupexec or maintenance plans?
How do i guage the maximum file size growth for my DB?
Can I set alerts when my db is near the maximum file size?
How do i reindex dbs and defragment db's so they are always performing optimally?
----
Thanks for all the help. BTW this is my first dive in SQL or any form of DB's so please excuse the confusion.
I have a SQL 2005 server which has not really been maitained by our DBA. I have now taken over the task to trying to get this SQL server is as good of a shape as possible before we eventually migrate it.
My question arises in regards to the log files and probably has several parts to it, so I will try and break it down to what I already know and whats the best way to go about finding a solution.
Problem: I have several databases, some range from 256 Megs and others are at 2 GIGS. Each of these databases however have a ridicoulously large log file. The 256 mb file is around 30 GB and the 2 GB db is around 135 GB.
Now initially before my coworker had left, he had setup the agent for SQL Server in backup exec, to first run the DB backups followed by logs to truncate them. Because of the size of the log files, these backups are taking notoriously long. Now upon investigation, at first I was very initially confused w/ the defintion of truncating and shrink. I now think i understand the difference, where truncating does not nessarily reduce file space but keeps a record of the transactions that are passed / old since the last full db backup. Shrinking physically reduces the file by removing the free space from the log file.
Now I can easily solve my problem by running the shrink command on the dbs, but before I went ahead and did that, I did a bit of research and am now aware that SHRINKING is a big no, or at least that's what a number of people say. It removes the contingous filing structure of a db, causes a need for indexes to be rebuilt and overall creates a general performance hit. Not to mention that as soon as the db is required to grow again, it will and it shall.
So my question are:
How do I shrink my .ldf files w/out using the shrink command and suffering a performance penalty?
Do you guys recommend backing up my db's with SQL Agent from backupexec or maintenance plans?
How do i guage the maximum file size growth for my DB?
Can I set alerts when my db is near the maximum file size?
How do i reindex dbs and defragment db's so they are always performing optimally?
----
Thanks for all the help. BTW this is my first dive in SQL or any form of DB's so please excuse the confusion.
Last Comment
ASKER
If i set the DB to simple recovery model, I basically have a db w/out any transactions that occur after my last backup, so I lose the capabilities of a point in time recovery.
As for the DBCC shrinkfile, won't that fragment the db and affect performance?
As for the DBCC shrinkfile, won't that fragment the db and affect performance?
Yes if you change recovery model you can't do PITR so don't change it.
You could use the following code to shrink it as TRUNCATEONLY "Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. "
http://msdn.microsoft.com/en-us/library/ms189493.aspx
And yes, you must perform scheduled maintenance/backups for the log files to not ever grow.
use your_db_name
go
checkpoint
go
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
GO
DBCC SHRINKFILE (N'your_db_name_log' , 0, TRUNCATEONLY)
GO
You could use the following code to shrink it as TRUNCATEONLY "Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. "
http://msdn.microsoft.com/en-us/library/ms189493.aspx
And yes, you must perform scheduled maintenance/backups for the log files to not ever grow.
use your_db_name
go
checkpoint
go
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
GO
DBCC SHRINKFILE (N'your_db_name_log' , 0, TRUNCATEONLY)
GO
First you need to get you transaction logs back to normal.
1. Switch to Simple Recovery Model
2. Do several CHECKPOINT in a row.
3. Use DBCC SHRINKFILE("YourLogicalNam
Then once the Transaction Log is back to a normal size you have a choice:
1. Either switch back to Full Recovery Model and do frequent Transaction Logs or if you are unable to do that then
2, Leave it in Simple Recovery Model.
1. Switch to Simple Recovery Model
2. Do several CHECKPOINT in a row.
3. Use DBCC SHRINKFILE("YourLogicalNam
Then once the Transaction Log is back to a normal size you have a choice:
1. Either switch back to Full Recovery Model and do frequent Transaction Logs or if you are unable to do that then
2, Leave it in Simple Recovery Model.
ASKER
acperkins. What do you mean by: Do several CHECKPOINT in a row?
Lcohan, can you please breakdown your code for me.
The initial part is taking the size of the current DB and checking to see the extra space in the DB / Log, but why does it reference sys.database_files.
Also what variable would i use for name: (FILEPROPERTY(name, 'SpaceUsed')
Lcohan, can you please breakdown your code for me.
The initial part is taking the size of the current DB and checking to see the extra space in the DB / Log, but why does it reference sys.database_files.
Also what variable would i use for name: (FILEPROPERTY(name, 'SpaceUsed')
Do several CHECKPOINT in a row?
Execute the following command in the SSMS query window:
CHECKPOINT
This command will clear out the Transaction Log when in Simple Recovery Model.
Execute the following command in the SSMS query window:
CHECKPOINT
This command will clear out the Transaction Log when in Simple Recovery Model.
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
I get the following error, when i run the comnand:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.database_files'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.database_files'.
Are you certain you execute the command against your SQL2005 user database with sufficient rights? This new 2005 system catalog exists in each database and it replaces SQL 2000 sysfiles
http://msdn.microsoft.com/en-us/library/ms187997(v=SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms187997(v=SQL.90).aspx
ASKER
Yeah, I believe I have the approrpiate rights as I am using windows authentication and am part of the admin group for windows which is also part of the admin group for sql.
Can you run the following command in your SQL against one of the user databases?
select * from sys.database_files
If not then in my opinion you are not running against SQL 2005 or higher but lower version
OR you just "...believe I have the approrpiate rights" but you don't.
select * from sys.database_files
If not then in my opinion you are not running against SQL 2005 or higher but lower version
OR you just "...believe I have the approrpiate rights" but you don't.
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
You can use
DBCC SHRINKFILE
To manage the size of the log file, you can
1. Set the database recovery model to SIMPLE
2. Schudle regular backups
For optimal growth size, you need to observe the database for a while and take note of much it gows in a specific time interval, get the average growth and from that you can determine what size to need to set.