Hello everyone, I am very new to SQL Server world.
I am having a database with data file (*.mdf) of 1Gb of size and its log file (*.log) of 2GB !!!! Is it normal ? Is it anything I have to do from DBA point of view and how can I prevent this happening ?
Thank you in advance for your help.
Regards,
Tom
After you've run a log backup, you can use DBCC SHRINKFILE to reduce the physical size of the log file on the disk. However, unless you're really pressed for disk space, or the used log size never comes anywhere near the physical size with regular backups, I'd recommend leaving it alone. Unused space in a database file has no effect on the speed or size of backups, but frequent growing and shrinking can cause physical fragmentation in the file system.
I'm sure Google and Books Online will give you more complete information than this, but at least you'll have an idea where to look. 
David,
Excellent suggestions, thank you very much.
On a note, I am running SQL Server Express and I am using SSMSE (SQL Server Management Studion Express) tool.
Under the instance name ('(local)\SQLEXPRESS) there are five (5) folders:
1. Databases
2. Security
3. Server Objects
4. Replication
5. Management
I cannot find any option for backing up the logs.
As I said initialy, I am new to SQL Serve, I am sorry for my ignorance !!
Again, thank you very much for your help.
Best regards,
Tom
Expand the Databases folder
Right click on the database you want to backup
Point to Tasks >
then select Back Up... from the pop-up menus
There you'll have a chance to define the backup type you want to execute.
As mentioned, do a Full backup first, then do log backups at given intervals.
Hi Dale, thank you for your response.
I did a FULL database backup. Then I try to do a Differential Database Backup (is it
the same as the log-backup you mentioned ?) but it errors out as follows:
"Backup falied for Server "(local)\SQLEXPRESS". (Microsoft.SqlServer.Express.Smo)
Additional Information:
System.Data.SqlClient.SqlError: The volume on device 'C:\Backup\POS.BAK' is not part of
a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set"
Again, thanks for your help.
Regards,
Tom
BACKUP DATABASE dbname TO DISK = 'path to backup file'
BACKUP LOG dbname TO DISK = 'path to backup file'
And that's pretty much it. You can get a bit more creative with differentials, filegroups, backup devices, media sets, etc. but that's essentially the bare minimum. You can probably schedule these to run using the sqlcmd command line utility and Windows task scheduling to work around the absence of SQL Agent.
If you only have the choice of Full or Differential backups then your DB is running in SIMPLE recovery mode and you don't get to backup the log file.
You're getting the error because you're trying to do the differential backup with the full backup file still listed in the Destination list. You'll need to "Remove" that file reference and add a new one for the differential.
A Differential backup backs up all the changes in the DB since the last full backup. It's kinda like a "mini comprehensive" backup. It's something of a substitute for a series of log backups.
In Simple recovery mode the system is using the log for some administrative work and for minimally logging the activity in the DB.
If the log being so big is an issue, you're most likely in good shape to shrink it.
Now may be a good time, however, to review how the DB is being used and see if Simple recovery is appropriate or if you want to go to Full recovery and implement a backup scheme involving Full, Differential, and Log.
http://www.simple-talk.com/sql/backup-and-recovery/sql-server-2005-backups/
Hi Tom,
Check the database recovery mode(you can check database properties), if its "simple" then run the below command one by one for only one time.
If the recovery mode is "full or bulk logged" then create a job and add the following Steps into it and schedule it run daily before backup job.
Step1: Backup log databasename with truncate_only.
Step2: DBCC shrinkfile(logfilename,200)
Step1 will write all the transactions to the datafile and make a check point over there. This actually doesnt free the space to os instead it will create freespace on Log file.
Step2 will make the log file 200 free and returns the remaining freesapace to OS.
David, Dale, Sagar EXCELLENT suggestions.
Thank you very much.
I would say, the simple-talk.com SQL articles are great.
Yes, I do run the db in 'Simple' recovery model.
The solution, to make the logs as small as possible, is follows:
1. Backup log dbname with truncate_only
2. DBCC.shrinkfile(logfilename,number).
What 'number' will be like ? Sagar I see you put down 200, please advise.
Thanks again,
Tom
The number is the target size in MB.
If you specify a number smaller than what is needed to store all the data in the log then it stops at the smallest amount it can go to.
The size kinda depends on your situation...mainly how much updating is done in the DB between your backups. You don't want to make it too low, then you'll take a performance hit as it fills up and has to expand.
If the DB is infrequently modified then you can probably go fairly small. If it is more frequent then you will want to keep it at a size that will accommodate the activity.
Hope that helps.
Dale has given the necessary info... Thanks dale..
Everything depends on the frequency of db use. i have suggested to set to 200 MB for a safer side, i don't no how frequently you are going to access the db thats why i have suggested this file size.
Regards,
Sagar
