Saturday, July 13, 2013

[MS SQL Server] Run DBCC DBREINDEX on all tables and 50 are 10% fragmented and 3 are 100%

[MS SQL Server] Run DBCC DBREINDEX on all tables and 50 are 10% fragmented and 3 are 100%


Run DBCC DBREINDEX on all tables and 50 are 10% fragmented and 3 are 100%

Posted: 12 Jul 2013 06:40 AM PDT

I ran DBCC DBREINDEX on all tables and 50 are 10% or more fragmented and 3 are 100%. Most of the 50 are in the 60% to 90% range.[code="sql"]USE DataWarehouseGOEXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"GO[/code]I know that DBREINDEX is be deprecated.So for the top 10 tables I ran the following:[code="sql"]ALTER INDEX ALL ON Schema.TableNameREBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON); [/code]What could be preventing the tables from dropping down to 0%?Some of the tables are Heap. I could see why that would be a problem.Edit: typo

Data Base File Swap

Posted: 12 Jul 2013 11:42 PM PDT

Hi All,We have a huge load scheduled every day during that time we have no access / data for end user to access.We need to plan this to remove the dependency between Load and usage.The plan is to always Load data to a Load DB and Once Load is done, we need to swap out the Loaded to Current.Please need your help to understand the steps and also the limitations on with this approach.Sudhir Nune

Can we Create sql server instance through script?

Posted: 12 Jul 2013 04:19 PM PDT

Hi, I am using SQL server 2008 standard edition. Is there a way to create a new server Instance by a script except the default instance.? Thanks.

Question on uninstalling sql 2008 r2 sp2 CU3

Posted: 12 Jul 2013 11:00 AM PDT

Hi All,I had to uninstall CU3(KB2754552) and the build number before uninstall was 10.50.4266.0..I was thinking the build number would change to 10.50.4000.0 after the uninstall, does anyone know if that is not the case. I still see the same build number as before, I reboot sql service as well, still same.This is sql server 2008r2 64 bit.Http://support.microsoft.com/KB/2754552Regards,SueTons.

Alert When Data Files Have Less Potential Growth than Autogrowth

Posted: 12 Jul 2013 08:04 AM PDT

I have a production database that grows at a rate of approximately 5 GB per week and is 1.2 TB in total size currently. We have a weekly task to grow the files at a rate equal to our expected growth, thus avoiding autogrowth events. But occasionally there is unusual growth and an autogrowth event does occur. We also have the data files configured with maximum size limits to avoid filling storage devices and also the performance impact of jumbo files. Our current max is 250GB / file. I know that's too big but it's better than no limit. :-DOur production DBAs routinely monitor the database to make sure the file has not hit that bad place of Potential Growth = 0 MB. This condition was discovered today.My question is, how can I automatically monitor and alert for the condition [color=#0000FF][b][i]Potential Growth <= 2 * Autogrowth[/i][/b][/color]?I am using Idera SQL Diagnostic Manager, which provides reasonable monitoring, but i have not been able to find a way to alert on this condition in the tool.Thanks in advance,Greg

No comments:

Post a Comment

Search This Blog