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
LVL 1
donebertAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

donebertAuthor Commented:
This database at 1 point started on SQL 2000 and now it's on SQL 2008 R2. I noticed the Compatibility version is SQL 2000. Prior to running the script I updated it to SQL 2008.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
First thing to do after migrating a database from MSSQL 2000 to 2005 or superior is to run the following command:
DBCC CHECKDB WITH DATA_PURITY 

Open in new window


After that a Rebuild Index or at least an Update Statistics should be performed as well.
0
dan_blagutCommented:
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
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

donebertAuthor Commented:
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
0
donebertAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
First, why do you need to reduce the database size?
Second, can you post here the sizes by files?
0
donebertAuthor Commented:
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.
0
donebertAuthor Commented:
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
donebertAuthor Commented:
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" ?
0
donebertAuthor Commented:
The System databases are not big. I can't find a way to rebuild a non-system database.
0
David ToddSenior DBACommented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
After a database migration and update, you should always rebuild all indexes.
The following command generate the necessary script to rebuild all indexes from your database. Just copy and paste the results of this query to a new query window and run from there. Keep in mind that this will generate locks, so run it when there's no activity in the database:
SELECT 'ALTER INDEX ALL ON ' + table_name + ' REBUILD; '
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
donebertAuthor Commented:
Ok, I will try later tonight
0
Seth SimmonsSr. Systems AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.