Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
The tempdB of MS SQL server
Posted on 2013-01-01
Dear all,
right now we consider to place Tempdb in SSD, but the size the SSD is not always large enought.
If we place mulitple tempdB file cross different type of hard disk, like SSD + SAS, can MS SQL still operational if only the tempdB in SSD failed but not SAS?
DBA100.
right now we consider to place Tempdb in SSD, but the size the SSD is not always large enought.
If we place mulitple tempdB file cross different type of hard disk, like SSD + SAS, can MS SQL still operational if only the tempdB in SSD failed but not SAS?
DBA100.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
6 Comments
Hi marrowyung,
You can split tempdb to multiple files. So yes, you can put one file on SSD, and another on SAS. But both files should be available. If one of them is inaccessible, you will not be able to start your server.
You can monitor tempdb file size, but this database is recreated every time server restarts. If your server is running long enough, just take a look at file size, this should give you general understanding.
You can split tempdb to multiple files. So yes, you can put one file on SSD, and another on SAS. But both files should be available. If one of them is inaccessible, you will not be able to start your server.
You can monitor tempdb file size, but this database is recreated every time server restarts. If your server is running long enough, just take a look at file size, this should give you general understanding.
Active today
Rimvis,
"If one of them is inaccessible, you will not be able to start your server."
Exactly what I am thinking, I didn't do that before as I don't have a good reason to do this. But if SQL server alwasy like to use file in a round robin manner, and if one of the file is missing and MS SQL is accessing it, MS SQL should return error.
"You can monitor tempdb file size, but this database is recreated every time server restarts. If your server is running long enough, just take a look at file size, this should give you general understanding. "
Exactly what I told my staff and we should reserve around 40% of buffer in total for the aim of safty.
DBA100.
"If one of them is inaccessible, you will not be able to start your server."
Exactly what I am thinking, I didn't do that before as I don't have a good reason to do this. But if SQL server alwasy like to use file in a round robin manner, and if one of the file is missing and MS SQL is accessing it, MS SQL should return error.
"You can monitor tempdb file size, but this database is recreated every time server restarts. If your server is running long enough, just take a look at file size, this should give you general understanding. "
Exactly what I told my staff and we should reserve around 40% of buffer in total for the aim of safty.
DBA100.
Active 3 days ago
Short answer is "NO". If your tempdb failed on SSD, the only way it would keep working if there was some kind of hardware (RAID) mirroring and it was a hardware failure.
So, having multiple tempdb's does not mean that any one of them can fail and automatically locked out.
Just how big does your tempdb get ? You can setup SSD's in a mirror (might lose out on the TRIM functionality, but that doesnt really impact all that much in the scheme of things).
For size you can run a script or simple SQL,
So, having multiple tempdb's does not mean that any one of them can fail and automatically locked out.
Just how big does your tempdb get ? You can setup SSD's in a mirror (might lose out on the TRIM functionality, but that doesnt really impact all that much in the scheme of things).
For size you can run a script or simple SQL,
USE TEMPDB
go
SELECT name
,type_desc
,physical_name
,size /128.0 size_allocated_MB
,fileproperty(name,'SpaceUsed') / 128.0 size_being_used_MB
,(size-fileproperty(name,'SpaceUsed'))/128.0 size_available_MB
,(fileproperty(name,'SpaceUsed')*1.0 / size) * 100.0 percent_used_MB
,((size - fileproperty(name,'SpaceUsed'))*1.0/size)*100 percent_available_MB
FROM sys.database_files
go
Active today
mark_wills,
yeah, we will use the montioring tools to check the peak size of the tempdB file.
But did you frequently see the size change dynamically? except the user/progamming do the 'select * From xxxx', which can kill the tempdB volume as the tempdB keep expanding to the unlimited size.
In 'http://technet.microsoft.com/en-US/library/ms345368(v=sql.100).aspx', what is 'space-use values'? I can't see how I can use this information to project the real figure.
"Short answer is "NO". If your tempdb failed on SSD, the only way it would keep working if there was some kind of hardware (RAID) mirroring and it was a hardware failure.
So, having multiple tempdb's does not mean that any one of them can fail and automatically locked out."
so this means you agree that setup muitiple tempDB file don't help but hardware mirror on SSD volume can ?
DBA100.
yeah, we will use the montioring tools to check the peak size of the tempdB file.
But did you frequently see the size change dynamically? except the user/progamming do the 'select * From xxxx', which can kill the tempdB volume as the tempdB keep expanding to the unlimited size.
In 'http://technet.microsoft.com/en-US/library/ms345368(v=sql.100).aspx', what is 'space-use values'? I can't see how I can use this information to project the real figure.
"Short answer is "NO". If your tempdb failed on SSD, the only way it would keep working if there was some kind of hardware (RAID) mirroring and it was a hardware failure.
So, having multiple tempdb's does not mean that any one of them can fail and automatically locked out."
so this means you agree that setup muitiple tempDB file don't help but hardware mirror on SSD volume can ?
DBA100.
Active 3 days ago
multiple tempdb can help, but not in terms of automatic failover or redundancy. It can help in size and performance. Hardware mirroring does offer that automatic failover, so, any of the RAID versions offering high redundancy is what you want.
yes, you can see tempdb changing size - normally growth (on disk) and any deleted space (within the db) quickly gets reused once released, so dont always see erratic shrinkage (unless you have autoshrink on - not advised).
Probably the "space-use values" you mention is looking at the different consumers / uses for tempdb and mentions the different aspects in both the capacity planning and that troubleshooting links, that troubleshooting link in possibly not the best discussion on tempdb, but, it does show how to identify the different use (albeit not explaining what that is).
There is also : http://technet.microsoft.com/en-US/library/ms175527(v=sql.100).aspx which talks about optimising tempdb. I think all three together should give you the best possible views of tempdb.
If plain size is a problem, then run the query to show the size and available space, and monitor that. If the size is really getting out of hand, then you need to start looking at what is "wasting" the tempdb resource and thats when you need some of the queries from the troubleshooting link.
.
yes, you can see tempdb changing size - normally growth (on disk) and any deleted space (within the db) quickly gets reused once released, so dont always see erratic shrinkage (unless you have autoshrink on - not advised).
Probably the "space-use values" you mention is looking at the different consumers / uses for tempdb and mentions the different aspects in both the capacity planning and that troubleshooting links, that troubleshooting link in possibly not the best discussion on tempdb, but, it does show how to identify the different use (albeit not explaining what that is).
There is also : http://technet.microsoft.com/en-US/library/ms175527(v=sql.100).aspx which talks about optimising tempdb. I think all three together should give you the best possible views of tempdb.
If plain size is a problem, then run the query to show the size and available space, and monitor that. If the size is really getting out of hand, then you need to start looking at what is "wasting" the tempdb resource and thats when you need some of the queries from the troubleshooting link.
.
Featured Post
PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
INTRODUCTION:
While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted? Many believe this is the case.
Let us try to examine for ourselves with an example.
To get started, use the following script, wh…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 9 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
635 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.