|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Shrinking transaction logs
Hello,
I am new to SQL server administration. I noticed that my backup system didn't backup the log files. Now it does but the log files have a lot of free space. I read a lot about this but what's the best thing I can do now to shrink them? Should the command 'DBCC shrinkfile (dbfilename_log) be enough or is their a better way? Can I only do this when no one is online? Am I right that this are only log files that you can use to restore. So whatever I do it doesn't have any consequences to the data? HDI,
Here is the standard reference: http://www.karaszi.com/SQLServer/info_dont_shrink.asp Since you are adjusting unusually large log files back to a reasonable size you should simply: DBCC SHRINKFILE(dbfilename_log, nnn) The 'nnn' stands for the appropriate size for the particular log file. You can do it with people online. If this has been this way for a while, then the log records should be at the beginning of the file and this should run swiftly in truncating the size of the log file. Doing this will not affect the integrity of your data. RLF Show quoteHide quote "HDI" <hd***@hotmail.com> wrote in message news:4e3401de-6e30-4dc0-925a-c2b66f345dd3@y7g2000yqa.googlegroups.com... > Hello, > > I am new to SQL server administration. > I noticed that my backup system didn't backup the log files. Now it > does but the log files have a lot of free space. > I read a lot about this but what's the best thing I can do now to > shrink them? > Should the command 'DBCC shrinkfile (dbfilename_log) be enough or is > their a better way? > Can I only do this when no one is online? > > Am I right that this are only log files that you can use to restore. > So whatever I do it doesn't have any consequences to the data? HDI (hd***@hotmail.com) writes:
> I am new to SQL server administration. Normally, you should not shrink files at all, but in a situation like> I noticed that my backup system didn't backup the log files. Now it > does but the log files have a lot of free space. > I read a lot about this but what's the best thing I can do now to > shrink them? > Should the command 'DBCC shrinkfile (dbfilename_log) be enough or is > their a better way? this where no one has backed up the transaction log, it is likely that it has grown excessively large. You have the command, but I think it is a good idea to set a target size. Try to find out how log you will actually fill up before the next log backup. > Can I only do this when no one is online? Yes, do you can do this online, although I would personally avoid peak hours. Or even wait until the weekend, or schedule it. > Am I right that this are only log files that you can use to restore. Right.> So whatever I do it doesn't have any consequences to the data? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  
Other interesting topics
report on indexes on foreign keys
SQL cannot connect to database for OnePoint/MOM/ForeFront all of the sudden??? using profiler to log master.sysprocesses entries ? Best Practices - using params passed to stored procedure Locks and SQLAgent - Generic refresher, Alert Engine Connection server utilizing trusted connection to other SQL Server RESTORING drop table not showing up in the transaction log Exception Access Violation in SQL SQL 2k and autonumbering |
|||||||||||||||||||||||
