donebert
asked on
SQL 2008 R2 Database never had maintenance done and is 500 Gig
Currently our Database is 501G and will only grow. Maintenance was never done as far as I can see. I need to free up drive space and instead of shrinking, I would like to rebuild or reorganize the database. I don't know a lot about SQL except for the basics. I ran this index table script shown in the .docx file and in the .xlsx file is the index table readout I received after running the script. I am having a hard time reading the data. I read if the percent defragmented is 30 or less I should rebuild the database. I'm not sure exactly what I should do and looking for guidance. I went thru most of the tables manually and none of them really had a lot of space. Let me know your thougts
SELECT-object.docx
SQL-Database-Index-of-Tables.xlsx
SELECT-object.docx
SQL-Database-Index-of-Tables.xlsx
First thing to do after migrating a database from MSSQL 2000 to 2005 or superior is to run the following command:
After that a Rebuild Index or at least an Update Statistics should be performed as well.
DBCC CHECKDB WITH DATA_PURITY After that a Rebuild Index or at least an Update Statistics should be performed as well.
Hello
Here you can find a nice article about reducing the size of a database
http://aboutsqlserver.com/2014/12/02/size-does-matter-10-ways-to-reduce-the-database-size-and-improve-performance-in-sql-server/
You also can find some interesting scripts, but don't forget the backup before runing this scripts.
Dan
Here you can find a nice article about reducing the size of a database
http://aboutsqlserver.com/2014/12/02/size-does-matter-10-ways-to-reduce-the-database-size-and-improve-performance-in-sql-server/
You also can find some interesting scripts, but don't forget the backup before runing this scripts.
Dan
ASKER
OK, I'm currently running DBCC CHECKDB WITH DATA_PURITY. I maybe did not make my self clear about the compatibility change. Where I changed it was by going to "Options" via the properties on that database and went to "Options" I saw the setting was at SQL 2000 (80). This is were I changed it to SQL 2008 (100) prior to running the scripts. Hope that makes sense. I will check out the link provided as well. I will let you know the results. Thanks
ASKER
The results just came back and here were the results. I figure there were no issue's found.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'OLDREPUBLIC50'.
What would be my next step to reduce the database? I read what dan_blagut web link and not sure what to do next.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'OLDREPUBLIC50'.
What would be my next step to reduce the database? I read what dan_blagut web link and not sure what to do next.
First, why do you need to reduce the database size?
Second, can you post here the sizes by files?
Second, can you post here the sizes by files?
ASKER
The database currently is 501 Gig and had never had any maintenance done with it. When we backup the database prior to upgrading our third party program it takes awhile. This SQL server is on a VM and we have been adding additional space as it filled up the storage in the past. From manually looking at all the table sizes, I don't believe space is even close to 250G. I believe the third party program was written poorly and they have no answers for me.
I would like to see if I can do anything to have everything run faster.
I would like to see if I can do anything to have everything run faster.
ASKER
What you send is total size of the database (SUM of all files). Can you send the report with the size by files?
Anyway, there's only 8GB that you can free up. Did you run a Full Rebuild on the database?
Anyway, there's only 8GB that you can free up. Did you run a Full Rebuild on the database?
ASKER
No I did not run a full rebuild on the database ever. Are you talking about a report I ran "Disk Usage by Top Tables" ?
ASKER
The System databases are not big. I can't find a way to rebuild a non-system database.
Hi,
Be warned that rebuilding a clustered index (rather than reorganizing that index) can require an amount of free space.
This isn't an Access database where the maintenance (compaction) really will reduce the file size.
I'd use Ola Hallengren's free script, but set the Fragmentationlevel2 fairly high (say 90% or higher) and set the FragmentationMedium to Null. Maybe set the TimeLimit as well. That way you only touch the most fragmented indexes for a first run. And then check the results in the CommandLog table.
https://ola.hallengren.com/
HTH
David
PS I know the argument that overdoing the maintenance can be counter productive, but am happy with once the database has a complete run with normal parameters, with doing this daily, if it can be fitted in your maintenance window each day.
Be warned that rebuilding a clustered index (rather than reorganizing that index) can require an amount of free space.
This isn't an Access database where the maintenance (compaction) really will reduce the file size.
I'd use Ola Hallengren's free script, but set the Fragmentationlevel2 fairly high (say 90% or higher) and set the FragmentationMedium to Null. Maybe set the TimeLimit as well. That way you only touch the most fragmented indexes for a first run. And then check the results in the CommandLog table.
https://ola.hallengren.com/
HTH
David
PS I know the argument that overdoing the maintenance can be counter productive, but am happy with once the database has a complete run with normal parameters, with doing this daily, if it can be fitted in your maintenance window each day.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, I will try later tonight
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER