Tag Info

New answers tagged

0

A few notes here: You definetely have to separate the files logically using different partitions (I recommend separate partitions for: system, data files (mdf/ndf), transaction log files (ldf) and tempdb files (mdf/ndf). Where you put the tempdb transaction log file is up to you (tempdb drive or log drive), I'd go with log drive. Backup files should not be ...


1

You can go directly to the tempdb properties and change the makeup of the database like you would any other database. The difference here is that the changes you make will reset the initial creation size when SQL Server starts up. It will also affect the current tempdb database that is live. So if you add more files in properties, it will actually add the ...


2

Sure, you can do it with little risk, but it depends on the state of your current tempdb. If tempdb has grown quite large, you might need to shrink it first before you have enough space for perhaps 4 good size tempdb files. Because of how active tempdb is and how hard it is to shrink that you might need to schedule a little downtime to make the clean ...


3

Now if we commit the first transactions that starts at 12:00, no row is being removed from the version store (even pass a minute). The question is WHY ? Minimal condition for a version row to be garbage collected is when SQL Server determines, based on the transactional states, that this version is no longer needed. Also, referring to my SQL ...


3

As stated in Paul's recommendations and in KB 2154845 use 8 files, and adjust as needed. As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number ...


1

Shrinking a database is not a good idea. There are specific cases, but they require a DBA to carefully evaluate the situation. Sql Server cannot have an idea if a query in, say, next five minutes is going to hit tempdb heavily. Without such a knowledge, shrinking and expanding tempdb is going to be unnecessary and, to be honest, stupid too. TempDB is ...


2

Question 1: Though I will do in non peek hours of business but are there any chances that making these changes can corrupt my database or log file? No this cannot damage your Log file. Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it? Yes database compression affects I/O and as far as my experience ...



Top 50 recent answers are included