http://www.sqlmag.com/authors/author/author/5613129/rsscomment/5613129en-USFri, 25 May 2012 08:17:00 GMTFri, 25 May 2012 08:17:00 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/curious-case-missing-sos_scheduler_yield-waits-143100#commentsAnchorThu, 17 May 2012 00:50:20 GMT
Really Superb. Previously I had tried to understand this wit type but I did not get such good description. Many thnks for sharing this.]]>Chinatk ChhapiaThu, 17 May 2012 00:50:20 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/curious-case-missing-sos_scheduler_yield-waits-143100#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/master-database-corrupt-142934#commentsAnchorMon, 30 Apr 2012 11:29:38 GMT
Thanks Paul. Would you recommend that CHECKDB be run on all databases besides master on a daily basis on a separate server?]]>influentMon, 30 Apr 2012 11:29:38 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/master-database-corrupt-142934#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/prevent-table-scans-searching-lob-data-142845#commentsAnchorFri, 27 Apr 2012 13:23:24 GMT
Could a fulltext index work here?]]>Tom PfadenhauerFri, 27 Apr 2012 13:23:24 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/prevent-table-scans-searching-lob-data-142845#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/prevent-table-scans-searching-lob-data-142845#commentsAnchorFri, 27 Apr 2012 10:37:16 GMT
That's a good technique, provided (a) you're using a new enough SQL Server version so that you can have a computed column based on a LOB column; (b) you can figure out what value might be useful for searching within the LOB. Of course, there is going to have to be a table scan done (but only once) to read all values from the LOB column in order to build the index. I'd love to see you discuss a pattern that I've tried to use a few times -- not, however, specifically involving LOB data. You can use a combination of "top" and a where clause based on the clustered index column(s) to do the work (a glorified table scan) in "chunks", each as its own transaction, so that the locks are held for less time. If you definitely need the a single transaction to cover all the changes (so you don't partially update the table), and you're hopefully not updating a high percentage of rows, you can have each "chunk" be read-only against the "target" table, and write the needed data (primary key and new values for column[s] to change) to a temporary table. Then you update the target table from the temporary table in one shot. (Of course if the data could be changing out from under you, some additional work might be required to avoid problems.) Is this a standardized pattern? Is there something wrong with it?]]>JamesMerrillFri, 27 Apr 2012 10:37:16 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/prevent-table-scans-searching-lob-data-142845#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/enable-backup-compression-default-142809#commentsAnchorThu, 12 Apr 2012 11:10:12 GMT
Choosing whether to enable backup compression at the instance level is important if your backup is using the Full, Differential, and Log modes paradigm. If, however, you're using an online backup service for your SQL server that uses sub-file level technology to scan the database byte-for-byte to find database pages that have changed, and only transmits those pages to the offsite backup location, then you don't have to worry about compression at all. Read more about doing SQL server backups online here: http://info.zetta.net/2-totally-ways-sql-server-backup-recovery/]]>NickZettaThu, 12 Apr 2012 11:10:12 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/enable-backup-compression-default-142809#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/setting-permissions-database-mirror-snapshot-142561#commentsAnchorTue, 13 Mar 2012 19:27:04 GMT
One caveat to remember is that if they have access to the principal server through another login, they will get the permissions of that user in the database even though they are accessing via a different login. For example, if user Domain\Sally is a member of both Domain\SalesUsers and Domain\Managers. SalesUsers are granted read access to mirrored database DB1 and then the login is removed, but Managers are granted access to the server and have read access to database DB2, Sally will be able to login as a member of the Managers group and will have read access to both DB1 and DB2.]]>Robert L DavisTue, 13 Mar 2012 19:27:04 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/setting-permissions-database-mirror-snapshot-142561#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/setting-permissions-database-mirror-snapshot-142561#commentsAnchorTue, 13 Mar 2012 19:25:49 GMT
One caveat to remember is that if they have access to the principal server through another login, they will get the permissions of that user in the database even though they are accessing via a different login. For example, if user Domain\Sally is a member of both Domain\SalesUsers and Domain\Managers. SalesUsers are granted read access to mirrored database DB1 and then the login is removed, but Managers are granted access to the server and have read access to database DB2, Sally will be able to login as a member of the Managers group and will have read access to both DB1 and DB2.]]>Robert L DavisTue, 13 Mar 2012 19:25:49 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/setting-permissions-database-mirror-snapshot-142561#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/transaction-log-corruption-backups-141816#commentsAnchorSun, 11 Mar 2012 09:27:12 GMT
I ran into this issue with one database out of 24 all residing on the same volume. Taking the database offline and bringing it back online fixed the backups until it happened again a day or two later. It turned out that this one database had auto close enabled. After disabling auto close, the problem never resurfaced. There were no signs of hardware issues or any other issues with this particular server.]]>jchamiakSun, 11 Mar 2012 09:27:12 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/transaction-log-corruption-backups-141816#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/transaction-log-corruption-backups-141816#commentsAnchorMon, 30 Jan 2012 16:44:28 GMT
If the transaction log file still exist and we are getting error while backup, is there a way to backup as much log data as we could other than the corrupted log data ? In production we do not simply want to sacrifice the log file. e.g. one of the technent article and your own old articles links below, http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx http://technet.microsoft.com/en-us/magazine/dd450974.aspx]]>pc3500Mon, 30 Jan 2012 16:44:28 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/transaction-log-corruption-backups-141816#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/curious-case-failed-database-mirroring-failover-141694#commentsAnchorMon, 09 Jan 2012 11:48:23 GMT
Thanks Kim and Paul for that insightful tip. Unfortunately this is only applicable when you are the owner of the client-side product development... Let's take the example of a large ERP system that relies on SQL database connectivity and where the client only connects once at the start-up... In such cases, the DB fail-over will be useful only as a quick DR situation, because the client would never be able to reconnect to the new server instance, even if the name remains the same. I think the only way that would work would be in conjunction with VMWare's HA solution where the server would be instantly replaced by a new VM on the fly (that was synchronizing and waiting standby in the background). Have a great year 2012 with the new Denali. @GP_Beat]]>Beat BucherMon, 09 Jan 2012 11:48:23 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/curious-case-failed-database-mirroring-failover-141694#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/run-bigint-values-141541#commentsAnchorFri, 23 Dec 2011 10:38:57 GMT
As a mathematician might say, your answer is almost correct, but not sufficient. The calculation you show only yields the number of hours, not the number of years, which is, happily, close to 2.925 million. To get there, however, you must also diver by 24, then by 365.25, which equals 2,922,710 years and change. Your answer is close enough for government work. However, the conditions you assume are a bit optimistic given today's computational capabilities. First, there is no reason you would have to store the integers - records could conceivable be deleted as you go, saving only interesting ones. So your storage space argument is moot. Second, the rate of 100,000 transactions per second is too low. For example, in a Beowolf cluster with a distributed database I could easily process a billion records per second of, sensor values from, say, the Hubble space telescope. That brings us down to only 2.92 years. Calculated data sets, auch as those produced by protein folding computations, can proceed at even faster rates. So it's not inconceivable, even today, that you could experience big int exhaustion. The risk depends on your application. ]]>melbeckmanFri, 23 Dec 2011 10:38:57 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/run-bigint-values-141541#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/run-bigint-values-141541#commentsAnchorFri, 23 Dec 2011 10:35:41 GMT
As a mathematician might say, your answer is almost correct, but not sufficient. The calculation you show only yields the number of hours, not the number of years, which is, happily, close to 2.925 million. To get there, however, you must also diver by 24, then by 365.25, which equals 2,922,710 years and change. Your answer is close enough for government work. However, the conditions you assume are a bit optimistic given today's computational capabilities. First, there is no reason you would have to store the integers - records could conceivable be deleted as you go, saving only interesting ones. So your storage space argument is moot. Second, the rate of 100,000 transactions per second is too low. For example, in a Beowolf cluster with a distributed database I could easily process a billion records per second of, sensor values from, say, the Hubble space telescope. That brings us down to only 2.92 years. Calculated data sets, auch as those produced by protein folding computations, can proceed at even faster rates. So it's not inconceivable, even today, that you could experience big int exhaustion. The risk depends on your application. ]]>melbeckmanFri, 23 Dec 2011 10:35:41 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/run-bigint-values-141541#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/scheduled-maintenance-jobs-collidehellip-141632#commentsAnchorFri, 23 Dec 2011 08:55:06 GMT
I absolutely LOVE the free SQLJOBVIS tool for showing SQL Agent jobs in a gantt-style layout so you can not only see completion status but much more importantly visually see job execution overlaps!]]>TheSQLGuruFri, 23 Dec 2011 08:55:06 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/scheduled-maintenance-jobs-collidehellip-141632#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorMon, 12 Dec 2011 17:11:27 GMT
Paul/Kimberly, I'm gonna have to take you on your offer of help. I'm currently going back and forth with two developers working as contractor at my workplace that are making a push for some regular maintenance involving shrinking log files on SQL databases. This is not the first time they've pushed for this, it seems to come up every time they see a large log file and in their own opinion, 'no log file should be over 1Gig ever'. I've tried my best to explain why this is a bad idea. I've sent them the link to this blog post and countless others and now the argument is boiling down to them saying, 'but they are talking about not shrinking the database file, not the log file'. I'm tired of the same argument but it doesn't seem my word is carrying any weight so if you could help me to better put this to rest I would appreciate it. Maybe I could send you the email thread and you could point if I'm not communicating this properly?]]>Jose HernandezMon, 12 Dec 2011 17:11:27 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorFri, 09 Dec 2011 16:44:05 GMT
--- A developer had created a table to log error messages for his app without letting me know (going forward He'll write these errors to the windows app log.) --- Rather than writing the logs to tables or windows application logs, write them to a text file. Windows Application Event Log won't fill generally fill up, but it will generally reach a maximum size, then start removing older entries. It's likely that your developer's logs will wipe out any information in the Windows Application Event Log. See my blog at marcjellinek.wordpress.com for a SQLCLR function to write to a text file. --- Given more time, could I have backed up the database(s), perforn the shrink, taken the SQL service down and performed a disk defrag on the mdf and ldf arrays? --- That's probably your best bet. You should also rebuild or reorganize any fragmented indexes. --- Alternatively, could I have shrunk the db, backed it up, dropped the database, recreate the DB and restore over it with the backup? --- That might take care of the majority of your file fragmentation, but it would leave the indexes fragmented. Given the size of your database (2-20GB isn't all that large), you should be fine.]]>Marc JellinekFri, 09 Dec 2011 16:44:05 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorFri, 09 Dec 2011 16:24:29 GMT
I recently had to (very very relunctantly) shrink a production database. A developer had created a table to log error messages for his app without letting me know (going forward He'll write these errors to the windows app log.) This table caused the database to fill up all available hard disk space until SQL stopped allowing transactions. I truncated the table but still had this huge mdf. The shrink operation reduced the size of the DB from 20GB to 2 GB. Given more time, could I have backed up the database(s), perforn the shrink, taken the SQL service down and performed a disk defrag on the mdf and ldf arrays? Alternatively, could I have shrunk the db, backed it up, dropped the database, recreate the DB and restore over it with the backup? Are these viable solutions? Thanks, Serg]]>spachecoFri, 09 Dec 2011 16:24:29 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorFri, 09 Dec 2011 15:30:08 GMT
CRM 2011 has a shrinkdatabase that runs as a system scheduled job about every 3-4hours - should I contact the SQL team or the Dynamics CRM team on this? I want to gut out the stored procedures that runs it but I probably wont be supported if I do that. We upgraded in place from CRM4 so hoping THAT is reason this is going on. We dont see anything in the system jobs - looks to be setup that way by default CRM 2011 install.]]>SQLTIMEFri, 09 Dec 2011 15:30:08 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorFri, 09 Dec 2011 14:06:50 GMT
One last comment: Not only does SHRINKDATAFILE or SHRINKDB cause index fragmentation, it causes file system fragmentation. I've never been able to get a handle on the real impact of file system fragmentation on performance with SQL Server, especially where data and transaction log files are stored on SANs or RAID arrays. One would assume that log files (which are read and written sequentially and thus should be stored on RAID1 or RAID 10 media) that having the read/write heads go in one smooth arc across the platters would be beneficial, but you can't assume that on a RAID or SAN environment. Any input on the impact of filesystem fragmentation of data and transaction log files?]]>Marc JellinekFri, 09 Dec 2011 14:06:50 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorFri, 09 Dec 2011 13:57:20 GMT
Here's my reply to the vendor arguments: "Indexes are fragmented already, so it doesn't make a difference" - regular database maintenance should include index defragmentation. "No one has ever complained about performance" - refer them to the thousands of public comments on a variety of websites (if a major application like BackupExec or Blackberry Enterprise Server) "Customers want their disk space back" - but then you are only going to consume it again during the next processing cycle. Shrinking the data files give the customer the impression that they have more disk space available than they actually have. If you shrink the data files, then use the "reclaimed" disk space for something else, then the application causes the data files to grow (and consume all available disk space)... SQL Server falls over (if we're talking about tempdb) or the database is unable to process additional transactions. This advice makes the database less performant and less reliable. As a professional, this is not advice I can follow. Better to accurately tell your customers how much disk space is going to be required so they can plan and budget to have it available. "We have to shrink tempdb because the operations we do cause it to grow continually" It sounds like you are using cursors and temp tables, then not releasing them. Space within tempdb will be reclaimed and reused (just like any other SIMPLE recovery model database) once the transactions that pertain to the data are committed. Shrinking the database files will NOT recover space from transactions that are still "in-flight", so this advice doesn't make sense to me. It's advice like this that leads me to believe the majority of products that do this are written for companies that do not have a professional DBA on staff. If that's the case, I strongly encourage those companies to evaluated cloud-based alternatives for their applications. They will have a DBA on staff.]]>Marc JellinekFri, 09 Dec 2011 13:57:20 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/efficient-index-maintenance-database-mirroring-140758#commentsAnchorSat, 03 Dec 2011 15:58:38 GMT
We use the approach of rebuilding indexes over 30% fragmentation, otherwise reorganize. But it isn't reindexing that's the big problem for us, its updating statistics. We're currently updating stats at 90% scan rate for 12 hours each weekend on our primary client database -- its about 1.3TB and 45 million records currently and we ran in full recovery for DR, not for mirroring.]]>rpettySat, 03 Dec 2011 15:58:38 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/efficient-index-maintenance-database-mirroring-140758#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorFri, 02 Dec 2011 16:34:54 GMT
I am currently working for a company on contract and the incumbent DBA has the same line - all databases need to be shrunk after backup. When I tried to explain why this should not happen (using articles written by Paul et al) I was told that nobody had every question it before and that is the way it is staying!]]>Dormer10Fri, 02 Dec 2011 16:34:54 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorFri, 02 Dec 2011 08:38:39 GMT
This advice makes sense if the database/application is transactional but what happens in the case where the vendor's application does its work, spits out the results and those results dont change. They re essentially static; used for reporting only. The next time the application runs, it spits the results out to a new database. Would you recommend using DBCC SHRINKDATABASE (for both log and database files) to reclaim disk space in this scenario? (This is the situation Im in now. The analysis software used by our team creates huge results databases, sometimes hundreds of gigabytes in size. The vendor recommends shrinking periodically to regain storage space both from logs and the database. Recovery mode is set to SIMPLE for all results db. TempDB is set to a static size and never changed unless deemed to be too small. Autoshrink is frowned upon as it might tie up server resources at critical times such as when another analysis is running. Periodic targeted shrinking allows us to control storage use). ]]>Lonster777Fri, 02 Dec 2011 08:38:39 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorThu, 01 Dec 2011 22:20:34 GMT
Super Article...and Many thanks Paul. I will surely assure to add you this time in my nasty :) mail converstaions about illogical explanations of sql server. Thanks, Aditya]]>Aditya BadramrajuThu, 01 Dec 2011 22:20:34 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorThu, 01 Dec 2011 15:38:02 GMT
If you need a rescuer, Paul will have your back astonishingly. He helped me deal with some hard-headed and stubborn vendors. Many Thanks Paul!]]>DBAThu, 01 Dec 2011 15:38:02 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorThu, 01 Dec 2011 11:03:00 GMT
One of my [least] favorite arguments is, "I read on a blog post that it's not that bad." This was actually in regards to using clustered nonsequential GUIDs on all tables, but I still think it's relevant.]]>Robert L DavisThu, 01 Dec 2011 11:03:00 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorThu, 01 Dec 2011 09:57:36 GMT
I have my own horror story related to this. I was actually forced to find another job many moons ago because it, in fact. At the company I was with at the time, some management turnover occurred and a new system administrator came on-board who had a lot of IT friends. Over the course of 3-4 months, most of the existing IT/helpdesk staff was replaced with personal friends of his. Apparently my turn came up and he convinced the director to bring in a consultant (yes, one of his buddies) to review things. Shortly after his review, I was brought before him, the sysadmin, the director and the programming manager. I was interrogated about why databases were not being shrunk regularly or automatically. Despite every effort, they were not going to be convinced that this (highly paid) consultant didn't know what he was talking about. I gave notice, promptly got walked out (for security reasons) and landed the best job I've ever had two weeks later. ]]>lordbeazleyThu, 01 Dec 2011 09:57:36 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/vendormandated-regular-database-shrinking-141477#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-does-page-life-expectancy-mean-137153#commentsAnchorTue, 18 Oct 2011 11:55:10 GMT
Pretty outstanding explanation.... as usual, you guys make all the technical stuff seem pretty easy to understand! Kudos to both of you!]]>thehappydbaTue, 18 Oct 2011 11:55:10 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-does-page-life-expectancy-mean-137153#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/efficient-index-maintenance-database-mirroring-140758#commentsAnchorMon, 10 Oct 2011 11:40:20 GMT
Thank you, Paul. Very good.]]>Roberson FerreiraMon, 10 Oct 2011 11:40:20 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/efficient-index-maintenance-database-mirroring-140758#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/efficient-index-maintenance-database-mirroring-140758#commentsAnchorMon, 10 Oct 2011 10:18:43 GMT
Do you suggest a threshold for REORGANIZE versus REBUILD? Thank you]]>Dana JonesMon, 10 Oct 2011 10:18:43 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/efficient-index-maintenance-database-mirroring-140758#commentsAnchorhttp://www.sqlmag.com/article/sql-server/Using-Database-Repair-for-Disaster-Recovery-125597#commentsAnchorThu, 29 Sep 2011 04:04:43 GMT
Hello Paul, Great article! One question: Is it possibe to detect corruption in database that you have broken with making backup with checksum? I tried to make a backup of this database with checksum option but it went without errors, also I was able to restore database with cheksum option without any errors, then only checkdb detected an error. ]]>Przemek DuszaThu, 29 Sep 2011 04:04:43 GMThttp://www.sqlmag.com/article/sql-server/Using-Database-Repair-for-Disaster-Recovery-125597#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/curious-case-empty-heap-table-139573#commentsAnchorWed, 28 Sep 2011 20:17:32 GMT
I was pointed to this entry from http://www.sqlservercentral.com/Forums/Topic1182140-392-1.aspx Paul, you say 'If that space isnt going to be reused', why might it not be reused? In the situation I have it appears that the new data is just being appended to the end of the table, and the free space is never getting freed up. There appears to be no command to recover the space, so once the problem is there the options are to create (and then drop) a clustered index, or copy the data 'somewhere' truncate and replace. I think there is a missing DBCC command, or an enhancement to one of the background processes required. pcd]]>pcdWed, 28 Sep 2011 20:17:32 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/curious-case-empty-heap-table-139573#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/inconsistent-analysis-clustered-indexes-140494#commentsAnchorTue, 20 Sep 2011 14:47:54 GMT
This seems like another reason to like read committed *snapshot*. It wouldn't block in the first place, and since it sees only rows committed at the start of the select statement, I can't think of a way that it would return a count that was just plain wrong, as happened in your example.]]>Quentin KingTue, 20 Sep 2011 14:47:54 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/inconsistent-analysis-clustered-indexes-140494#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/inconsistent-analysis-clustered-indexes-140494#commentsAnchorMon, 19 Sep 2011 12:39:18 GMT
Nice example and explanation! There is a tiny typo: after updating in connection 3, you would commit in connection 1, not connection 2 (which is blocked and waiting on conn 1 to commit).]]>David WallsMon, 19 Sep 2011 12:39:18 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/inconsistent-analysis-clustered-indexes-140494#commentsAnchorhttp://www.sqlmag.com/article/sql-server/Using-Database-Repair-for-Disaster-Recovery-125597#commentsAnchorFri, 09 Sep 2011 05:41:41 GMT
Hello Sir, First of all, thank you very much for sharing this information. In some circumstances all the free methods are fail to repair corrupt sql server database like DBCC CHECKDB, backup or do not recover complete database like repair_allow_data_loss. At this scenarios third party Stellar sql server recovery software may help to recover as much as possible.]]>Addision PhilipFri, 09 Sep 2011 05:41:41 GMThttp://www.sqlmag.com/article/sql-server/Using-Database-Repair-for-Disaster-Recovery-125597#commentsAnchorhttp://www.sqlmag.com/article/project-plan-backup-sql-server-2008-r2-83/database-backup-and-recovery/advanced-backup-and-restore-options-129834#commentsAnchorFri, 19 Aug 2011 07:55:14 GMT
Great article. Might we suggest the additional check be done against your log files (if you have database recovery set to 'full'. Review the header in your log set using RESTORE HEADERONLY from [name of logfile] to confirm that the Flag field does not contain any non-zero values (in SQL-2000) or the bit flag (in 2005 and beyond) is not set for 'minimally logged transactions'. This will "invalidate" the log set for restoring back to a point in time (...STOPAT Date, Time) with the error: Msg 4327, Level 16, State 1, Line 1 The log in this backup set contains minimally logged changes. Point-in-time recovery is inhibited. RESTORE will roll forward to end of logs without recovering the database. This happened to us during a critical restore! We could ONLY restore from the previous (full) backup with NO logs due to this error. As it turns out, we determined that 'minimally logged transactions' were getting into our 'FULL' recovery database during our nightly maintenance processes (which occured after a full nightly backup). The LogInit statement at the end of the maintenance managed to capture some minimally logged processes during the maintenance processing! The solution was to do the full backup AFTER the nightly processing following by a LOGINIT statement which resolved our problem. Hope this helps]]>tlamplFri, 19 Aug 2011 07:55:14 GMThttp://www.sqlmag.com/article/project-plan-backup-sql-server-2008-r2-83/database-backup-and-recovery/advanced-backup-and-restore-options-129834#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/rebalancing-data-files-filegroup-140068#commentsAnchorFri, 05 Aug 2011 07:50:55 GMT
I had some mild success with this by doing a reindex without the drop_existing. I recently had to add files to existing filegroups on a vendor app as part of an upgrade. But no data would go to the new files. I reindexed and like I said, had some mild success. But it seems like the root is the free space weighting. If I had file_1 at 35GB and 10GB free and added file_2 at 5MB with 5MB free, file_1 still has the most free space and lower wieght and will get filled first. So it seems your new files need to be created with the same amount of free space and the other members of the group, evening out the weighting. ]]>RandyDFri, 05 Aug 2011 07:50:55 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/rebalancing-data-files-filegroup-140068#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/rebalancing-data-files-filegroup-140068#commentsAnchorFri, 05 Aug 2011 05:52:34 GMT
I'm pretty sure I tried this method recently - it sounded like it'd work to me too! However, it appears you can't "emptyfile" the PRIMARY filegroup, so whilst some of the data moves over to your second temporary filegroup, not all of it does. So then you've got the same problem on the way back of having one file significantly larger than the others, and data's not striped across all files nicely. Unless any else has some bright ideas (and I'd seriously appreciate a magic solution to this), all I can see is that you need to generate a new schema for your whole database on another server (or the same server if there's space) and use DTS/SSIS to migrate all the data, table by table, to the new schema which has 4 files in the primary filegroup instead of 2 (or however many you're dealing with). There is the added benefit that when you're doing this, if you're on the right version/edition of SQL, you can create your new schema with some wisely chosen PAGE or ROW level compression. Then as the data comes in, it's stored compressed which will improve your storage utilisation and performance as well as the multiple-data-files benefit. The difficulty here is working out how to import an entire set of tables to a new database as quickly as possible by doing things like turning clustered tables to heaps and disabling triggers during import, then putting these things back again afterwards.]]>timjohnstoneFri, 05 Aug 2011 05:52:34 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/rebalancing-data-files-filegroup-140068#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/rebalancing-data-files-filegroup-140068#commentsAnchorThu, 04 Aug 2011 22:07:55 GMT
So if I have a vendor app that wants me to stay in the same (primary) filegroup... can i do the following to rebalance? 1) create a new file as large or larger than current file (like tempfile.ndf, logical name temp) 2) dbcc shrinkfile w/ emptyfile parameter on original file (testdb.mdf, logical name testdata) 3) make new files the size I want in primary (testdb2.ndf , testdb3.ndf etc) 4) make testdb.mdf same size as files created in step 3 5) dbcc shrinkfile w/ emptyfile on tempfile.ndf 6) remove tempfile.ndf from filegroup ? I thought this might allow me to re-balance across the new files since I'd empty the original into another temp file and then empty it back into the other files. I know this would require a significant amount of space, (let alone time) but if it is available is there some caveat to prevent this? -redi311]]>redi311Thu, 04 Aug 2011 22:07:55 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/rebalancing-data-files-filegroup-140068#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/size-of-data-operations-when-adding-and-removing-columns-137144#commentsAnchorThu, 07 Jul 2011 16:50:01 GMT
The query in this article will also add column with NULL value for existing records ALTER TABLE PaulsTable ADD QuantityColumn INT DEFAULT 0; so it should be ALTER TABLE PaulsTable ADD QuantityColumn INT NOT NULL DEFAULT 0; to add column with default value for existing records.]]>PankajThu, 07 Jul 2011 16:50:01 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/size-of-data-operations-when-adding-and-removing-columns-137144#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/when-do-checkpoints-occur-for-tempdb-137143#commentsAnchorThu, 07 Jul 2011 11:08:22 GMT
Good Article]]>deedee9Thu, 07 Jul 2011 11:08:22 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/when-do-checkpoints-occur-for-tempdb-137143#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/curious-case-empty-heap-table-139573#commentsAnchorWed, 29 Jun 2011 12:02:01 GMT
Would DBCC FORCEGHOSTCLEANUP help in this situation?]]>Robert Davis aka Wed, 29 Jun 2011 12:02:01 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/curious-case-empty-heap-table-139573#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/the-curious-case-of-the-disappearing-corruptions-137151#commentsAnchorFri, 27 May 2011 07:11:15 GMT
Yes - if you’re going to do it as part of the same maintenance job.]]>Paul S. RandalFri, 27 May 2011 07:11:15 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/the-curious-case-of-the-disappearing-corruptions-137151#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/the-curious-case-of-the-disappearing-corruptions-137151#commentsAnchorFri, 27 May 2011 04:35:22 GMT
Thank you very much Paul... So hving DBCC Checkdb after Re indexing helps us right???

-Aditya]]>Aditya BadramrajuFri, 27 May 2011 04:35:22 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/the-curious-case-of-the-disappearing-corruptions-137151#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorWed, 18 May 2011 05:04:19 GMT
No - he’s accurate but is missing the part about a manual failover not being covered by the mirroring partner timeout, because the Windows Server is still alive to respond to the ping request - hence my article (and your testing). Thanks.]]>Paul S. RandalWed, 18 May 2011 05:04:19 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorWed, 18 May 2011 02:18:22 GMT
...the plot thickens...well, curdles.

In the ’High availability with database mirroring’ chapter of Rod Colledge’s excellent book ’SQL Server 2008 Administration in Action’, he says:

"If a mirroring principal is set up as a clustered instance, consider adjusting the mirroring session timeout value to greater than the default 10 seconds. Doing so will prevent mirroring failover during a clustering failover. Typical cluster failovers take up to 90 seconds to complete, so adjusting the mirroring timeout to at least this value is recommended. Use ALTER DATABASE x SET PARTNER TIMEOUT 90 or something similar."

This goes against my testing and, unless I’ve misunderstood, your article, too.

Is what Rod is saying inaccurate?]]>PhineasGageWed, 18 May 2011 02:18:22 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorFri, 13 May 2011 08:43:38 GMT
Perfect - Thanks, Paul.]]>PhineasGageFri, 13 May 2011 08:43:38 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorFri, 13 May 2011 08:12:55 GMT
http://msdn.microsoft.com/en-us/library/ms191519.aspx shows the easiest way - just remove the witness before the controlled failover and put it back again afterwards.]]>Paul S. RandalFri, 13 May 2011 08:12:55 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorFri, 13 May 2011 07:40:18 GMT
I breathed a sigh of relief when I found this article as I have asked myself the same question over the last few days.

Your comment "the mirroring partner timeout only comes into play if the mirror server doesn’t get a response from the principal server at all" explains a lot, including why our mirror database was failing over during our manual cluster failovers, despite the PARTNER TIMEOUT value being several minutes.

I don’t think this important point is emphasised in many other articles on this topic.

What would be the recommended way "to temporarily remove the witness server from the mirroring configuration"? Perhaps stop the SQL Service on the mirror server, or could we simply "Pause" mirroring during the cluster failover and start it again afterwards?]]>PhineasGageFri, 13 May 2011 07:40:18 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-can-a-database-snapshot-run-out-of-space-137145#commentsAnchorMon, 11 Apr 2011 07:16:06 GMT
FYI: there’s a dmv exposing this info: (actual size vs os file size)
/*
DMV Database File Size on Disk.sql

*/
Select IVFS.database_id
, DB_NAME(IVFS.database_id) as DBName
, IVFS.file_id
, IVFS.size_on_disk_bytes /* Number of bytes used on the disk for this file.
For sparse files, this number is the actual number of bytes on the disk
that are used for database snapshots. */
, IVFS.size_on_disk_bytes / 1048576 as Effective_size_on_disk_MB
, MF.name as DbFileName
, MF.type_desc
, MF.[physical_name]
, MF.size * 8 / 1024 as FileSizeMB /*Current file size, in 8-KB pages. For a database snapshot, size reflects the maximum space that the snapshot can use for the file. */

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
left join sys.master_files MF
on MF.database_id = IVFS.database_id
and MF.file_id = IVFS.file_id
order by DBName
, DbFileName
;
]]>alzdbaMon, 11 Apr 2011 07:16:06 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-can-a-database-snapshot-run-out-of-space-137145#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-doesnt-new-hardware-enhance-sql-server-performance-137135#commentsAnchorSat, 01 Jan 2011 10:50:38 GMT
Quite often a database system is in tune with the delays to transfer data to/from the disks. With really faster processors, the system may want to write to disk sooner and has to wait for the rotation of the disk(s) to be in the right place. That waiting may cause queuing of the I/O request.

Do the disks have RPS (rotational position sensing) capabilities? Do they have other features such as larger cache and NCQ (Command queuing). Can NCQ be turned off, so that I/O is done FIFO?

Just my thoughts that suggest the I/O system needs looking at.

Leslie in Montreal

]]>lsatensteinSat, 01 Jan 2011 10:50:38 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-doesnt-new-hardware-enhance-sql-server-performance-137135#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/a-safe-method-for-moving-a-database-to-a-new-location-137129#commentsAnchorThu, 04 Nov 2010 18:32:33 GMT
@Kevin I don’t like that approach because if the database is damaged in some way, it may be very hard to get it attached again. Following my procedure doesn’t go through the attach process so even if the database is damaged, it will still be attached.

@Gary I’m afraid you’ve been reading the wrong magazeines again :-) Take a look at the benchmarking blog post where I show (for an example workload) that having multiple data files on the same RAID-10 array can lead to a perf gain: http://www.sqlskills.com/BLOGS/PAUL/post/Benchmarking-do-multiple-data-files-make-a-difference.aspx]]>Paul S. RandalThu, 04 Nov 2010 18:32:33 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/a-safe-method-for-moving-a-database-to-a-new-location-137129#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/a-safe-method-for-moving-a-database-to-a-new-location-137129#commentsAnchorThu, 04 Nov 2010 12:11:18 GMT
I agree 100% getting the database off the same drive as the operating system and page file is critical. However, I have a question about creating additional file groups. If one is using RAID 5 or RAID 10 for the data files, isn’t adding a new file group on the same RAID drives sort of limiting. I mean you really gain nothing since in essence the new file group is on the same drives and the original data files.
Is this correct or have I been reading the wrong magazines again?
Thanks
Gary]]>Gary892Thu, 04 Nov 2010 12:11:18 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/a-safe-method-for-moving-a-database-to-a-new-location-137129#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/a-safe-method-for-moving-a-database-to-a-new-location-137129#commentsAnchorThu, 04 Nov 2010 11:59:15 GMT
You state that "the safest procedure for this is not to detach/attach the database". Can you expand on why that would not be safe to a) detach the database, b) copy (not move) the files to the new location & c) re-attach them?]]>kevinhartmanThu, 04 Nov 2010 11:59:15 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/a-safe-method-for-moving-a-database-to-a-new-location-137129#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/a-safe-method-for-moving-a-database-to-a-new-location-137129#commentsAnchorTue, 02 Nov 2010 10:44:28 GMT
Yes - unfortunately there’s no good way to move LOB data except extracting out into a new table. I wish this would be fixed but I’m not holding my breath.]]>Paul S. RandalTue, 02 Nov 2010 10:44:28 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/a-safe-method-for-moving-a-database-to-a-new-location-137129#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/a-safe-method-for-moving-a-database-to-a-new-location-137129#commentsAnchorTue, 02 Nov 2010 07:10:31 GMT
As I understand it this method will NOT move LOB data. The LOB data (image,text,etc) will stay in the original file group when you do the CREATE INDEX … WITH (DROP_EXISTING=ON). Is there any good way to move the LOB data to a new Filegroup?

Please correct me if I’m wrong.

thanks
Mike ]]>mlmooreTue, 02 Nov 2010 07:10:31 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/a-safe-method-for-moving-a-database-to-a-new-location-137129#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-how-long-it-takes-for-dbcc-checkdb-to-run-137119#commentsAnchorSun, 17 Oct 2010 02:28:20 GMT
Can you believe , it takes 1 day and 15 hours and it was still running (DB size : 23 GB, RAM : 4 GB, Dual core processor), then My my Boss decide to stop it and find another solution.
then we stopped it , we did the following steps:
- We detach the DB from SAN Storage
- Copying DB from SAN to C Drive
- Reatach DB from C , it is up and running
- During the week end we moved it back to SAN.

(By the way, it was marked as suspect befor DBCC)

]]>KaramSun, 17 Oct 2010 02:28:20 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-how-long-it-takes-for-dbcc-checkdb-to-run-137119#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-choose-a-good-index-fill-factor-137126#commentsAnchorSun, 03 Oct 2010 08:30:34 GMT
Yes = and that’s part of the trade off you need to make for your particular situation. There’s no right answer - experiment.]]>PaulRandalSun, 03 Oct 2010 08:30:34 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-choose-a-good-index-fill-factor-137126#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-choose-a-good-index-fill-factor-137126#commentsAnchorSat, 02 Oct 2010 00:00:30 GMT
Does Fill Factor impact read / write operation. I think when we set Fill factor to 50% or so it will slow down read operation. similarly if we set it to 100% it will slow down the write operatoin. How to get optimal performance through fill factor both for read / write operation.

Thank you.
]]>Tahir Sat, 02 Oct 2010 00:00:30 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-choose-a-good-index-fill-factor-137126#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-choose-a-good-index-fill-factor-137126#commentsAnchorFri, 01 Oct 2010 16:10:38 GMT
Yes, of course - no-one does this manually. At the lowest end, the maintenance plan wizard can help you with this, or you can download a script to customize for yourself. Google Ola Holangren and get his latest script.]]>PaulRandalFri, 01 Oct 2010 16:10:38 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-choose-a-good-index-fill-factor-137126#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-choose-a-good-index-fill-factor-137126#commentsAnchorFri, 01 Oct 2010 10:20:52 GMT
Looks like this is a endless task, since this depends on the data beeing stored and the data can change everyday, in some cases more often then that, is it possible to implement some job to automatically respond to fragmentation problems?]]>MarcosGalvaniFri, 01 Oct 2010 10:20:52 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-choose-a-good-index-fill-factor-137126#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-choose-a-good-index-fill-factor-137126#commentsAnchorFri, 01 Oct 2010 06:49:49 GMT
If you don’t specify a fill factor, the previously used fill factor for that index (or the default, if this is the first index operation) will be used. If you specify 0 (or 100) that says to leave zero free space in the index leaf-level pages.]]>PaulRandalFri, 01 Oct 2010 06:49:49 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-choose-a-good-index-fill-factor-137126#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-choose-a-good-index-fill-factor-137126#commentsAnchorThu, 30 Sep 2010 21:07:21 GMT
what does fillfactor 0 mean? does it maintain your previous settings or do you need to know what your current setting is before you reindex?]]>markThu, 30 Sep 2010 21:07:21 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-choose-a-good-index-fill-factor-137126#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-how-long-it-takes-for-dbcc-checkdb-to-run-137119#commentsAnchorMon, 30 Aug 2010 16:14:23 GMT
Now... if only one of these hundreds of websites would tell you how to ’actually’ execute this!
Not one site says ’Now type this command into...’.
Nope, just says: ’Just Run This!’

Execute through SSH, MySQL what?!?! If going through MySQL and fails, then do....

Too many vague instructions from ’professional’ programmers :)]]>NeotropicMon, 30 Aug 2010 16:14:23 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-how-long-it-takes-for-dbcc-checkdb-to-run-137119#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorThu, 22 Jul 2010 11:12:25 GMT
The Windows server was shutdown or just the SQL Server? If the latter, the mirroring failover will occur as you describe.]]>Paul S. RandalThu, 22 Jul 2010 11:12:25 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorWed, 21 Jul 2010 21:59:10 GMT
Though the principal server has been completely shutdown, its not waiting for the partner timeout value that has been set. During the time resources failover to the other node of cluster, databases failover to the mirror server. Resources came up on other node in 30 secs and Partner time out was set to 120 secs. Please suggest.]]>sapWed, 21 Jul 2010 21:59:10 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-isnt-the-mirroring-partner-timeout-working-137113#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/can-i-restore-my-enterprise-edition-database-on-all-editions-of-sql-server-137118#commentsAnchorThu, 01 Jul 2010 16:19:28 GMT
Actually, licensing for DR boxes is not necessary if the DR is in standby.
So you can use Enterprise edition without having to purchase additional licenses.]]>GThu, 01 Jul 2010 16:19:28 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/can-i-restore-my-enterprise-edition-database-on-all-editions-of-sql-server-137118#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/can-i-restore-my-enterprise-edition-database-on-all-editions-of-sql-server-137118#commentsAnchorWed, 30 Jun 2010 04:39:22 GMT
Thanks Paul, This information should come in real handy.]]>mumbaiguy_3Wed, 30 Jun 2010 04:39:22 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/can-i-restore-my-enterprise-edition-database-on-all-editions-of-sql-server-137118#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/is-it-a-bad-idea-to-rebuild-all-indexes-every-night-137116#commentsAnchorMon, 14 Jun 2010 11:57:51 GMT
Thank you Paul. Nice info. I wasn’t really aware, one could calculate the required log file size in advance.
If I could, I’d add two additional things to keep in mind:
1) An index REBUILD will also rebuild the statistics that is related to the index – which is good and awkward. Good, because this creates up- to-date statistics. Additionally, these statistics are created with fullscan, so this rebuild will also create high quality statistics. Nice and lovely for the optimizer. Awkward, because this will invalidate any cached execution plans that make use of these updated statistics which in turn will lead to necessary re-compilations of these plans. So, the radical approach of rebuilding all indexes will very likely lead to otherwise unnecessary re-compilations.
Any index REORGANIZE will NOT update any statistics on the other hand.
2) Deciding on the returned fragmentation from sys.dm_db_index_physical_stats alone which action to perform may not be sufficient in all cases. I’ve discovered this problem recently and this article encouraged me to blog about it. You may read the regarding blog post here: http://sqlserver-online.blogspot.com/2010/06/do-you-rebuild-your-indexes.html.]]>Holger SchmelingMon, 14 Jun 2010 11:57:51 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/is-it-a-bad-idea-to-rebuild-all-indexes-every-night-137116#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/using-partial-database-availability-for-targeted-restores-137112#commentsAnchorFri, 30 Apr 2010 16:34:34 GMT
One thing to add. When doing a partial restore, you can still query a partitioned table with only some of the its filegroups online. You will recieve a warning if your query covers those partitions that have not been attatched.

The interesting thing is if you limit the query to the available partition (like your current year example) no message is returned.
]]>JohnFri, 30 Apr 2010 16:34:34 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/using-partial-database-availability-for-targeted-restores-137112#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/sizing-your-transaction-log-137107#commentsAnchorThu, 15 Apr 2010 16:06:05 GMT
I don’t mention index reorganize in the article as it works as a series of small transactions and so does not count as a large, long-running transaction (I wrote it specifically to do that). As long as correct log management is taking place, an index reorg should not cause the log to grow.]]>Paul RandalThu, 15 Apr 2010 16:06:05 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/sizing-your-transaction-log-137107#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/sizing-your-transaction-log-137107#commentsAnchorThu, 15 Apr 2010 16:00:02 GMT
Article does not mention about Index Reorg which also uses Transaction log.]]>SamThu, 15 Apr 2010 16:00:02 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/sizing-your-transaction-log-137107#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-you-back-up-is-what-you-restore-137104#commentsAnchorThu, 25 Mar 2010 08:32:04 GMT
It is always a pleasure to hear from this couple. Thanks a lot for all your posts!

Take Care!]]>MarcosGalvaniThu, 25 Mar 2010 08:32:04 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-you-back-up-is-what-you-restore-137104#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2005/are-you-taking-the-right-backups-#commentsAnchorMon, 19 Oct 2009 04:53:24 GMT
Is there some useful information here? Looks like it is just advertise. Take Care!]]>MarcosMon, 19 Oct 2009 04:53:24 GMThttp://www.sqlmag.com/article/sql-server-2005/are-you-taking-the-right-backups-#commentsAnchor