May 26, 2011 at 8:59 am
here is a little background, question is at the bottom....
We had this error this morning on our tlog backups.
Error:
Failed ( -1073548784) Executing the query "BACKUP LOG [My Database] TO DISK = N'..." failed with the following error: "Database 'My Database' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The log file was full.
After researching the error I found the following:
Need to check the "IsShutdown" db prop.
SELECT DATABASEPROPERTY ( 'My database' , 'IsShutDown' )
-- this command returned a 1 (yes it was shut down)
Based on the log file full 9002 error the resolution was to run
sp_add_log_file_recover_suspect_db
I successfully ran this command and it created a second log. The t-log backup then ran successfully and I was able to run a shrink and reclaimed all the log space.
Now I appear to have 2 log files and both are being used.
Are there any issues to be aware of or problems running 2 log files? Should I get rid of one of them?
Thanks,
Tom
May 26, 2011 at 9:52 am
From what I've read, I don't believe there are any issues with running more than one t-log, but there are certainly no benefits (edit: performance-wise). The log is accessed sequentially so as far as I know only one logfile will be used at a time.
I'd look to get rid of it (why have an extra log to manage?). Obviously, take full backup after and kick off the log backup chain again.
May 30, 2011 at 5:04 am
Are you using packages to take the Tlog Backups and how many drives not partition you have and how many cores of CPU you have in a machine
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 30, 2011 at 5:38 am
Syed Jahanzaib Bin hassan (5/30/2011)
how many drives not partition you have and how many cores of CPU you have in a machine
What's that got to do with the number of log files?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2011 at 5:47 am
What's that got to do with the number of log files?
If both cores access the same file at a time second core will be on wait,if you have multiple or more than 1 log file then second core will not wait to write the entries of log,multiple Files and File Groups and log file on multple cores machine will help you to increase the performance of the database guaranteed,there is an involvement of Hyper threading also but note it need drives not partitions means multple read\write heads
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 30, 2011 at 5:52 am
Syed Jahanzaib Bin hassan (5/30/2011)
If both cores access the same file at a time second core will be on wait,if you have multiple or more than 1 log file then second core will not wait to write the entries of log,multiple Files and File Groups and log file on multple cores machine will help you to increase the performance of the database guaranteed,there is an involvement of Hyper threading also but note it need drives not partitions means multple read\write heads
Not at all.
SQL uses its log files sequentially. Always. That does not depend on cores, partitions, hyperthreading or any other factor. SQL will use one log file beginning to end, then will use the second beginning to end, then will go back to the first.
That's why there's no benefit to having multiple log files. They do not increase performance in any way. Only one log file will be in use at a time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply