I have a live server and a report server. The live server, where the application is running, has many indexes. When we create replication all the indexes are also created on the report server.

After 2 months, disk space on the report server is running out, and I have to make some space. I checked all the unused indexes on the report server and found many indexes that have not been used any time in the past two months. But they are required for the live server.

Can we delete those indexes that take unnecessary disk space on the report server as no application is pointing to report server? Will it be ok? I want to be sure before I make the changes.

  • 2
    If no app is connecting to the report server, why have a database there at all? Dropping it (after ensuring you have secured a backup somewhere, of course) will be a much more effective way to save space than pruning some indexes - dropping indexes doesn't shrink database files anyway, you'll have to additional work after that; and if you want to keep the database, we can't possibly decide for you which indexes you should keep and which you should throw away. – Aaron Bertrand Feb 9 '17 at 1:45
  • @Red Devil - How, exactly, does replication play into this? Are you replicating the application's database to your report server in order to report on it? It's a bit unclear what you're describing. – SQL_Deadwood Feb 9 '17 at 15:08
  • Yes application server is replicated to report server – Red Devil Feb 10 '17 at 0:08

Yes, you can drop the indexes on the subscriber if no one is using them. Better yet, remove them from the publication article, or create a post snapshot script to drop whatever indexes you want. Either way on next initialization, the indexes will not be created, or they will be dropped.

  • Thanx Greg Appreciated!!!!!!!! – Red Devil Feb 9 '17 at 3:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.