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.
| Author |
Topic |
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
 Posted - 2009-05-07 : 02:34:01
|
| Can someone help me with a problem regarding transaction log backups please!Setup:-SQL Server 2005Windows server 2003.2 x Intel Xeon cpu – 5140 @ 2.33 GHz8.00 GB RAMBackground info - the story to date!We started having problems with our transaction log filling up and the data files for the tempdb growing to HUGE sizes almost filling our disk (whoever set up the server used the same disk for tempdb files as the other db files are on and i have read that this is wrong as tempdb files should have their own disk to grow in) the disk is 1TB in size with the log files growing beyond 70GB!The tempdb was set to autogrow with no limit set.I thought (maybe mistakenly?) that I should restrict the growth of the tempdb to prevent it filling up the disk (which could possibly crash sql server), and so after some research and reading up on the subject I created another three datafiles for the tempdb (so there would be one for each core - on the dual processors), set the max size of each one to 10GB with the log file set to a maxsize of 10GB, Autogrowth set on all at 20%, i then did a restart of sqlserver.We now get errors saying something along the lines of templog is full unable to create pages? and i have to manually shrink the log and data files of tempdb using the following:-use tempdb go dbcc shrinkfile (tempdev, 1010) go dbcc shrinkfile (tempdev2, 1010) go dbcc shrinkfile (tempdev3, 1010) go dbcc shrinkfile (tempdev4, 1010) go dbcc shrinkfile (templog, 200) goI suspect there is something wrong with the way our server was initially setup as when I right click on a database in management studio and select the 'Backup' option to look at settings, most of the databases therin (there are 24 on the server!) are set to 'Simple' recovery model. (which is probably OK) but.....If i select any of the databases to check what the other settings are i find that the options tab shows the area where you would select 'Transaction log' on all of them is grayed out!Does this mean I am not backing up the transaction logs?Does this also mean because of this they are not shrinking themselves?I can't seem to find where i could backup and auto truncate the transaction logfiles other than above?Not all of the databases are set to do a backup (some have been created by another department for temp purposes), those that are set to do a backup do so every night.Help! |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-05-07 : 13:21:34
|
| You can't backup up the transaction log of a database in Simple Recovery. In simple recovery, the log is truncated when a checkpoint operation occurs (truncated, not shrunk). The only backups you can take on simple are full and differential. That means that at best, if you need to restore the DB you can only restore it to the last full or diff backup.Re tempDB. If the error says that the database is full, ie there's no free space, why would you try to shrink, which releases free space to the OS?--Gail ShawSQL Server MVP |
 |
|
|
|
|
|
|
|
Transaction Log Backups - I'm totally confused!