SQL 2012 :: TempDB - Tlog Sizing / Capacity Planning?
Nov 18, 2014
I am in the process of formulating recommendations with respect to the purchase of additional storage for our current SQL 2012 SharePoint (2013) instance. My recommendation is to purchase separate storage (i.e, 15k disks) for the TempDB and Tlogs respectively (two sets of raid 10 disks). Currently, this server is hosting several instances, including SP, using two arrays (one for database and the other for Tlogs).
I am attempting to find information/recommendations on how to go about projecting the amount of storage for each of these while factoring in for growth.
Additional Details:
how to best formulate a reasonable estimate. Our largest content database belongs to IT and is currently ~80GB. That said, this is currently an outlier. The remaining content databases are less than 10GB (most are less than 2-3 GB). However, SharePoint will be used for digital document imaging in addition to, eventually, replacing file shares as our primary document storage medium once we roll it out.
Our current tempDB is ~400MB, but the instance was recently started a few days ago, as we had to failover to our backup server for hardware maintenance. I do not have any historical data on TempDB growth at this time. Also, I don't know how useful this would be given we have not fully deployed yet.
View 0 Replies
ADVERTISEMENT
Nov 4, 2000
hi, can any one tell me what is the meaning of capacity planning and where do I get info about it,and how does this help me in sql server admin role
thanks
Ahmed
View 1 Replies
View Related
Dec 11, 2000
What si capacity planning?
Where can i find more stuff on it?
View 1 Replies
View Related
May 28, 2001
Hi Everybody,
Can anybody explain me, what is Capacity Planning and what is Load Balancing?
tks in advance
sri
View 1 Replies
View Related
Sep 27, 2001
Does any one know where I can locate a document or template for database capacity planning on the web? Any help would be greatly appreciated. thanks.
View 1 Replies
View Related
Jul 20, 2005
HiI joined a project where 100,000 rows were added everyday. Now due toadditional customers the expectation is 2 million reocrds/day ie 10 GB worthof textfiles. We have to estimate the hard disk, memory, # of CPUs etc.Wewill have one yearworth of data in the db. Rest will be in tapes etc.We will be using WIN2000, SQL Server2000.- Any comparable server sizing willbe appreciated.1. Tohandle every day load, I thought that we will have a table for each day(pre created in the database )and have a view with union all selectingfromall these 365 tables. (This is the only way to partition in MSSQL Serverright?).2. The requirement is to populate datawarehouse tables with all the data.However there will be only inserts mostly but there can be updates too whichhappenned in the past 12 days.Hence we have to use the data from the last12 days and massage it etc and populate into datawarehouse tables.How can I do this so that I will have the datawarehouse tables with n-12days of data and I will alwys add the last 12 days data to it.Do you have any suggestions?Ragu
View 3 Replies
View Related
Aug 8, 2007
Hello,
I have been working on SQL Server Capacity Planning for a few weeks now and have gathered a lot of materials, but non of thes materials contain recommended best practices on SQL Server capacity planning and also they do not contain operational guidelines.
I would be glad if anyone can recommend a website or book that contain information on SQL Server Capacity Planning Best Practices.
Thanks,
Jide
View 3 Replies
View Related
Feb 25, 2015
I have a SQL Server 2012 DB in Full recovery mode that has never had a TLOG backup. the log is huge 200+GB.
I tried doing a transaction log backup but there is not enough space on the Disk.
How can I reclaim this log space in SQL Server 2012?
View 6 Replies
View Related
Sep 8, 2014
I need to manage the height of an horizontal range bar chart inside a SSRS 2012 report.
In my report, I've a filter to read a particular data set. So, I could select five data rows or ten data rows or twenty data rows and so on. I'd like to maintain fixed the height of the horizontal bars while the number of data rows changes.
View 1 Replies
View Related
Jul 17, 2015
I have a table with a column Capacity which is char(10) and gets populated from user files. I want to check records which have negative Capacity value. So i first checked if its numeric and then for negative.
select * from table WHERE ISNUMERIC(LTRIM(RTRIM(Capacity))) = 1 AND Capacity < 0
BUT still it checks for char fields too giving errors like - Conversion failed when converting the varchar value 'asdf ' to data type int.
View 9 Replies
View Related
Mar 30, 2014
It is possible that Data Collection can cause massive increasing MB/sec to tempdb ? I cannot find connection with tempdb and I set cash file, but on same disk.
Or it can be something different? Last two weeks what I checked was Read/Write MB/s to tempdb increasing progressively.
One time it was about 20MB/sec
After it was reseting and again 1MB/sec..
What I checked , External company which install SQL Server made one file for tempdb, next week or during breaktime(it will be possible), I would like make 8files next weekend work.
Now I saw that TempDB mdf was still increased, but using was just 8-10%
View 2 Replies
View Related
Jul 8, 2014
I have added ndf to tempdb for checking performance improvement.... Now I want to remove the ndf file... I am using below command...
USE tempdb
GO
DBCC SHRINKFILE (3, TRUNCATEONLY);
GO
use master
go
ALTER DATABASE TEMPDB Remove FILE tempdev1
Results:
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
23766476643232
Error:-
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 1
The file 'tempdev1' cannot be removed because it is not empty.
Note:
=>I restarted SQLServer from SSMS and then ran same commands mentioned above ,......and getting same error...
=> I executed above commands and restarted services...no change...
How to remove / drop ndf file...
View 7 Replies
View Related
Mar 3, 2015
I'm having an argument with our infrastructure architect who has just gone and bought lots of SSD drives to use for our tempdb data and log files, sounds great doesn't it? There is a catch though, his plan is to add the disks to the two available slots in each blade in a RAID0+1 configuration, effectively giving you one usable drive, and adding both data and log files on to one disk.
I then pointed out that SQL Server best practice is to host tempdb data and log files on two separate drive to reduce contention. The architect then basically said that because this isn't spinning disk the issue of drive, r/w contention isn't an issue I don't agree with this and wanted to get some opinions from the community, I'm still advising that two separate disks should be used but someone just went and spent £80k ($150k) on SSDs and doesn't want to back down...
View 4 Replies
View Related
Apr 10, 2015
I can get a snapshot of tables in tempDB, but I would like to track which procs are causing the load in the tempDB.
I think I can sample and record objects in the tempdb, but I would like to record the proc creating the most tempDB usage, and disk read/writes associated with those procs.
The DMV's give usage in the individual DB's, but what's a good way to correlate procs in the DB's to tempdb usage?
View 9 Replies
View Related
Dec 18, 2012
I have a server which is not running optimally and I checked the default trace. I have around 600 entries in the default trace which are all Missing Column Statistics and the database is tempdb.is_auto_create_stats_on and is_auto_update_stats_on are both 1 for tempdb.
View 2 Replies
View Related
Sep 30, 2014
Is it possible to encrypt Master database and tempdb? On executing below query result is showing temdb is encrypted.
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
View 1 Replies
View Related
May 21, 2015
So we have new servers that are going to be installed with SQL 2012 and I'm debating the wisdom of splitting tempdb with multiple files.
I know it's a myth that performance automatically improves if you split it into a number of files based on processors, but I'm debating the wisdom of putting a file on each of my data / log file drives.
For instance, I have a server with a C: drive (OS), D: drive (Data for system DBs and install of programs - 458 GB), an F: drive for user DB data files (767 GB), and a J: drive for log files (255 GB).
Obviously no files are going on C:. I'm debating on whether or not we should even leave system DBs on the D: drive given in our current 2k8 servers, we end up with Memory.dmp files over flowing the D: drives as well as .cabs and other install / update files that tend to collect on that drive over the years.
But if we leave the system DBs on D:, I'm wondering if adding a second tempdb file to F: and a third to J: will improve query performance or not.
View 9 Replies
View Related
Aug 10, 2015
While trying to move the tempdb log file to a different disk, I mistakenly executed the following and then stopped and restarted SQL Server.
USE master
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, FILENAME = 'L:MSSQL11.MSSQLSERVERMSSQLData emplog.ldf')
GO
Now I can't start SQL Server and see this error in the log files:
[code]
2015-08-10 15:28:42.55 spid7s Error: 5171, Severity: 16, State: 1.
2015-08-10 15:28:42.55 spid7s L:MSSQL11.MSSQLSERVERMSSQLData emplog.ldf is not a primary database file.
2015-08-10 15:28:42.55 spid7s Error: 1802, Severity: 16, State: 4.
2015-08-10 15:28:42.55 spid7s CREATE DATABASE failed.
Some file names listed could not be created. Check related errors.
[code]
I did not have remote connections enabled yet, so the resolutions I have found that include sqlcmd or starting in single user configuration are not working. Any way that might allow me to restore the usual tempdb settings, which I think would allow SQL to start again?
View 3 Replies
View Related
Sep 21, 2015
I am seeing select * from sys.sysprocess where blocked<>0 is always the report server
ReportServer.dbo.GetSessionData;1 is blocked by ReportServer.dbo.WriteLockSession;1
We have different reporting servers using same database.
Is it require to rebuild indexes on report server and report server temp db on daily basis?
View 0 Replies
View Related
Oct 2, 2015
I need a script to Backup & Shrink tempdb.
namesize
tempdev1024
templog64
tempdev21024
tempdev31024
tempdev41024
tempdev51024
tempdev61024
tempdev71024
tempdev81024
I can't believe how many tempdb's there are?
View 8 Replies
View Related
Aug 13, 2014
I have a tempdb split into 4 files (5 if you include the log).
Autogrowth is disabled on the mdf/ndf files so that they can be used round robin (1 file per logical CPU).
Is there a way to be alerted when there is x% of free space left?
I know hwo to check the free space via t-sql but want to be able to be alerted. I could run a sql job that reports the free space and send a database mail message if under x% but wondered if there was a built in (or better) method?
I also have SQL Sentry.
SQL 2012 Standard
View 9 Replies
View Related
Sep 11, 2014
We are seeing very high Average Disk Queue Length numbers in one of our clusters (both nodes of the cluster are Virtual, but have their own dedicated virtual environments). Our main data drive also houses TempDB, which I would like to move.
Each node in the Active/Passive cluster are running Windows Server 2012 Standard 64bit and SQL Server 2012 Enterprise 64bit. There is a separate drive for Log files and data files.
The data files also have TempDB on them as previously mentioned. I am reading that you can set up a local disk on each node of the cluster, with the same drive letter and path and then move tempdb as you would with a stand alone SQL Server.
View 4 Replies
View Related
Feb 23, 2015
I was in the process of creating additional TempDB.ndf files, and received an error saying they already exist. I checked the location and it was empty, nothing to see here. So I looked in sys.master_files and there are several tempdb files listed in various locations, all of which come up empty.
So the files are listed as online in sys.master_files, but they do not exist on the server. I restarted SQL services but it did not change anything.
View 3 Replies
View Related
May 29, 2015
Whenever SQL Server get restarted, tempdb gets recreated with its last configuration.
let me know where SQL Server store tempdb configurations? How does it know how many Tempdb files it needs to create on restart?
View 2 Replies
View Related
Aug 27, 2015
How to solve the below blocking issue
One of our production server is configured with Always on with sql cluster.
We are getting frequently blocking
Waiting Resource Key Type: Key
WaitType: LCK_M_S
WaitResourceDatabaseName : ReportServerTempDB
WaitingSessionProgramName: Report Server
BlockingSessionProgramName: Report Server
WaitCommandType: Select
WaitingCommandText: Create Procedure dbo.checksessionLoak @sessionID...
View 1 Replies
View Related
Dec 9, 2014
how to know the limitation of number of objects(Maximum no.of objects allow tempdb database) in a tempdb database?
View 2 Replies
View Related
Jul 15, 2015
We have SQL Server 2012 EE SP2 server that is getting hit by reports that time-to-time use up to 300GB of tempdb.
It's happening because our clients can use 'bad' parameters for some reports.
Is there a way to autokill sessions that overuse tempdb (for example, if a session uses more than 15Gb in tempdb)?
Killing can happen immediately or after regular check (5 min for example).
View 3 Replies
View Related
Apr 14, 2014
I receive Error: 3967, Severity: 17, State: 1. Insufficient space in tempdb to hold row versions. We have 8 data files for temp db of 10210 GB size and given 10240 GB as max size.
As MS suggest to calculate the temp db file size and growth rate we need to monitor the perform counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object.
basic formula: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction
My report disk utilizations says tempdb is full ? I thonk I need a shrink for the file .
Still I am confused in calculating the size , My perform counter gives me data as such
Free Space in tempdb (KB) 279938496
Version Generation rate (KB/s) 53681040
Version Cleanup rate (KB/s) 53422320
Version Store Size (KB) 258720
Version Store unit count 22
Version Store unit creation 774
Version Store unit truncation 752
View 4 Replies
View Related
Apr 15, 2014
I am currently investigating aa high avg write time ms issue (145ms) which seems to be only occuring on the tempdb data files.I have followed the recommended setup of TEMPDB in that
1. Data files = number of physical cores
2. Data files and logfiles are on separate partitions away from the other databases.
3. Tempdb is presized and no incremental file increases look like they are happening with frequency.
We have sharepoint 2012 setup on other sql servers and with TEMPDB setup following the same guidelines, with far more Sharepoint activity on a similary specified hardware which is why its confusing.FileIO auditing on the partitions themselves shows that the FileIO is very fast on the partitions that the tempdb data file which leads me to beleive that Sharepoint may be the culprit perhaps due to excess use of tempdb with operations taking a long time to resolve.
View 3 Replies
View Related
Jul 9, 2002
I am getting periodic failures of my Transaction log. SQL
Server 7.0 Service Pack 3 on NT 4.0. IT is not occurring
in a set pattern. I have attached the error. I am unable
to find anything on the Internet or n TechNet can someone
help me out.
Thanks;
Message is as follows:
Microsoft (R) SQLMaint Utility (Unicode),
Version [Microsoft SQL-DMO (ODBC SQLState: HYT00)]
Error 0: [Microsoft][ODBC SQL Server Driver]Timeout expired
View 1 Replies
View Related
Dec 15, 2000
Hi,
My Transaction log has 0% Log Space available. I have performed a dump transaction with no_log but to no effect. I have also extended the log size from
100MB to 200MB and this has not made any inmpression either after several dump database statements also.
The Data Device was 500MB and I extended it to 800MB and again performed a backup. Has anyone any idea how to recover from this situation ?
I can make queries on the data fine but the several backups(and Truncate log) I have made are still not creating any space for my Transaction log.
Please help !
Thanks in advance,
Alan
View 3 Replies
View Related
Jul 26, 2007
Hi all
Is there anyway to find out using TSQL for the used space VS what is available for TLOG file? Cause the GUI won't allow me [Database] > Report > Disk Usage. It said that Unable to display the report becuase the database has a compatibility level of 80 ?!?!
Thanks
View 13 Replies
View Related
Mar 21, 2007
I am considering some disaster recovery scenarios.
Lets assume my MDF is gone - the disks are dead.
The LDF is on a different disk channel. Lets assume its fine.
Can I make a "final" TLog backup from the "good" LDF file?
Maybe copy some-earlier-MDF file into place, would that enable a TLog backup from the LDF file?
'Coz if I can then I could use that as a route to getting a zero-loss recovery - make a final TLog backup, and then restore the whole lot from last FULL + All TLog backups thereafter.
Kristen
View 1 Replies
View Related
