MDF and LDF files - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MDF and LDF files

Author

Topic

sqldbpro
Starting Member

11 Posts

Posted - 2013-05-30 : 03:16:55

OS: Windows Server 2008 SP2
SQL: SQL Server 2008 R2
Current .mdf file size : 700 MB

Question 1. (File sizes)

I would like to know what can be the maximum file size of
(a) .mdf file
(b) .ldf file

Question 2 (Backup)
I am wondering if it would be a good idea to copy the .mdf and .ldf files of a database for the purpose of backup rather than using a third party utility or the SQL 2008 management Studio GUI (Tasks-Backup).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 03:43:56

Maximum size of mdf doesnt have any recommended sizes. It really depends on your disk space how much it can hold and also your business decision on how much data they would be interested to retain.ldfs can grow enormosuly depending in size and amount of transactions taking place, recovery model etc

The easiest way is to take backup. No need of copying the files etc for that. .bak file will have sufficient details in itself to restore database to the stage when you took the backup.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sqldbpro
Starting Member

11 Posts

Posted - 2013-05-30 : 04:04:47

quote:
Originally posted by visakh16

Maximum size of mdf doesnt have any recommended sizes. It really depends on your disk space how much it can hold and also your business decision on how much data they would be interested to retain.ldfs can grow enormosuly depending in size and amount of transactions taking place, recovery model etc

The easiest way is to take backup. No need of copying the files etc for that. .bak file will have sufficient details in itself to restore database to the stage when you took the backup.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks mate.

The Hard Disc Drive capacity is 500 GB and 350 GB is the current free space. No other data apart from SQL data goes into this 350 GB. There is another 500 GB HDD attached to the Server as well. Disk space is not an issue for me.

So is it that the .mdf file size can be as big as 350 GB in this case. ?

I take it that you are advising to take Backup from within the SQL server 2008 Management Studio. Does the backup file (.bak) has both .mdf and .ldf files.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 04:25:15

it can...Though you would have constant backup strategy to backup and keep the mdf growth in check.
Backups are of different types-full,differential,log etc. They dont store data as mdf,ldf files. they store details on data of database as well as the transactions. For rebuiding a database you need to have details on data as well as transactions which is what you get from log files (ldf)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sqldbpro
Starting Member

11 Posts

Posted - 2013-05-30 : 08:32:09

Thank you Visakh

I have been taking a daily manual backup using the SQL server 2008 management studio GUI before application users start accessing the data. As it is just about 700 MB it takes less than 5 minutes to do that. Backup happens smoothly and I have verified the .bak file by attaching it on other standalone SQL test server.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-05-30 : 08:38:55

Also perform Database consistency checks, just to make sure your databases is good.

mohammad.javeed.ahmed@gmail.com

sqldbpro
Starting Member

11 Posts

Posted - 2013-05-30 : 11:40:34

Thank you Ahmed.

I have researched for a while on the database consistency aspect as you bought it up. Checking database for integrity is indeed a very good consideration that i missed before.

Therefore, I have created a test maintenance plan for a database using the maintenance plan wizard. The two tasks are:
(1)Check data consistency/integrity
(2)Daily backup at scheduled time.

The log files were set for this maintenance plan. The results are as follows:

===========================================================
Log file contents for Database integrity subplan is as follows
Microsoft(R) Server Maintenance Utility (Unicode) Version 10.50.1600
Report was generated on "SERVER".
Maintenance Plan: Adventure Works maintenance plan
Duration: 00:00:26
Status: Succeeded.
Details:
Check Database Integrity (SYSTEM)
Check Database integrity on Local server connection
Databases: AdventureWorks2008R2
Include indexes
Task start: 2013-05-30T20:50:06.
Task end: 2013-05-30T20:50:31.
Success
Command:USE [AdventureWorks2008R2]
GO
DBCC CHECKDB(N''AdventureWorks2008R2'') WITH NO_INFOMSGS

GO

===========================================================

The log file contents for database backup subplan is as follows;
Microsoft(R) Server Maintenance Utility (Unicode) Version 10.50.1600
Report was generated on "Server".
Maintenance Plan: Adventure Works maintenance plan
Duration: 00:00:17
Status: Succeeded.
Details:
Back Up Database (Full) (SYSTEM)
Backup Database on Local server connection
Databases: AdventureWorks2008R2
Type: Full
Append existing
Task start: 2013-05-30T20:50:06.
Task end: 2013-05-30T20:50:22.
Success
Command:BACKUP DATABASE [AdventureWorks2008R2] TO DISK = N''H:\SQL\Database Backups\Maintenance Plans backup\Adventure works MP\Adventure Works'' WITH NOFORMAT, NOINIT, NAME = N''AdventureWorks2008R2_backup_2013_05_30_205006_0516757'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''AdventureWorks2008R2'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''AdventureWorks2008R2'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''AdventureWorks2008R2'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''H:\SQL\Database Backups\Maintenance Plans backup\Adventure works MP\Adventure Works'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO

=======================================
Question 1
The observation while setting up the maintenance plan was that the Move Up and Move Down buttons in the dialog box were not highlighted. It would have been needed if i had set database backup first followed by database integrity check. Has anyone else faced this issue?

Question 2
A test backup has been scheduled for 8:50 P.M daily. Suppose the users update data between 9 A.M to 11 A.M. on next day. Unfortunately, the database gets corrupt.

I would only have the full backup of the database at end of previous day work hours. The data added in two hours on next day will be lost. What is the best method to tackle this.

Question 3
Do i need to backup log files separately as well apart from the full database backup. If yes, how does it helps. Is it a solution to the issue mentioned in Question 2.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-05-30 : 12:56:34

Yes backing up the logs will help you to perform point in time recovery.

mohammad.javeed.ahmed@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-31 : 02:03:21

quote:
Originally posted by sqldbpro

Thank you Visakh

I have been taking a daily manual backup using the SQL server 2008 management studio GUI before application users start accessing the data. As it is just about 700 MB it takes less than 5 minutes to do that. Backup happens smoothly and I have verified the .bak file by attaching it on other standalone SQL test server.

I assume its a full backup.
Keep in mind that whilst this will help you to build database to start of the day , in case of any intermediate failure you wont be able to restore database to point in time unless you do log backups.

ie suppose you'd a db failure at 03:00 PM Using your backup you can only get database to stage when it was at start of the day (12 midnight). You will not be able to recover any transactions that happened b/w 12 midnight to 3 PM. So based on criticality of your application,data volatility etc you may have to consider using a corresponding recovery model like full and add a log backup step at desired frequency

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sqldbpro
Starting Member

11 Posts

Posted - 2013-05-31 : 12:22:40

quote:
Originally posted by visakh16

quote:
Originally posted by sqldbpro

Thank you Visakh

I have been taking a daily manual backup using the SQL server 2008 management studio GUI before application users start accessing the data. As it is just about 700 MB it takes less than 5 minutes to do that. Backup happens smoothly and I have verified the .bak file by attaching it on other standalone SQL test server.

I assume its a full backup.
Keep in mind that whilst this will help you to build database to start of the day , in case of any intermediate failure you wont be able to restore database to point in time unless you do log backups.

ie suppose you'd a db failure at 03:00 PM Using your backup you can only get database to stage when it was at start of the day (12 midnight). You will not be able to recover any transactions that happened b/w 12 midnight to 3 PM. So based on criticality of your application,data volatility etc you may have to consider using a corresponding recovery model like full and add a log backup step at desired frequency

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Yes, it's a full database backup. From the readings on this thread, i am now certain that i would need to take transaction log backups as well.

However, the issue is that the database mode is simple. The full mode is not highlighted. What setting needs to be done to change it from "Simple" to "Full".

d3goldnews
Starting Member

3 Posts

Posted - 2013-05-31 : 22:01:59

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources