Someone has set the compress drive flag on a SQL 2005 Windows 2003 disk
drive. Our vendor thinks this may be the reason users are experiencing
performance issues with the application.

What can we expect if we remove the flag?

I have just reindexed the database. Is there another maintenance task I
should run to compact or compress SQL database?

Thank you!

> Someone has set the compress drive flag on a SQL 2005 Windows 2003 disk
> drive.

That shouldn't work. SQL Server should refuse to start the database if any of the database files
(except for files belonging to read-only filegroups) are compressed. I suggest you check this again.
If someone managed to hack this, then I suggest that you immediately remove compressions. It isn't
supported and it shouldn't work.


> I have just reindexed the database. Is there another maintenance task I
> should run to compact or compress SQL database?

Reindex is not the same as compact/compress. Compaction/compression is something you want to stay
away from since it tend to cause bad performance and *cause* fragmentation
(see http://www.karaszi.com/SQLServer/info_dont_shrink.asp for more info). Reindexing is one of the
things that can be a good thing to do on a periodic basis. Updating statistics as well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Marie" <Marie@discussions.microsoft.com> wrote in message
news:8F40CF1C-CC1C-4787-BC69-7EA693837BC4@microsoft.com...
> Someone has set the compress drive flag on a SQL 2005 Windows 2003 disk
> drive. Our vendor thinks this may be the reason users are experiencing
> performance issues with the application.
>
> What can we expect if we remove the flag?
>
> I have just reindexed the database. Is there another maintenance task I
> should run to compact or compress SQL database?
>
> Thank you!
>
>

"Marie" <Marie@discussions.microsoft.com> wrote in message
news:8F40CF1C-CC1C-4787-BC69-7EA693837BC4@microsoft.com...
> Someone has set the compress drive flag on a SQL 2005 Windows 2003 disk
> drive. Our vendor thinks this may be the reason users are experiencing
> performance issues with the application.
>
> What can we expect if we remove the flag?
>
> I have just reindexed the database. Is there another maintenance task I
> should run to compact or compress SQL database?
>
> Thank you!
>

http://blogs.msdn.com/sqlblog/archiv...d-volumes.aspx

Even if it did work, a compressed drive would slow down I/O significantly,
indexing would probably not improve the situation a great deal.

John

Thank you!
Three more questions.....

How do I run update stats?

Can I schedule reindex and update stats to run periodically, or are these
jobs best done manually?

Finally...any ideas what I can expect when I remove the compress flag?

Thanks again!

"Marie" wrote:

> Someone has set the compress drive flag on a SQL 2005 Windows 2003 disk
> drive. Our vendor thinks this may be the reason users are experiencing
> performance issues with the application.
>
> What can we expect if we remove the flag?
>
> I have just reindexed the database. Is there another maintenance task I
> should run to compact or compress SQL database?
>
> Thank you!
>
>

"Marie" <Marie@discussions.microsoft.com> wrote in message
news:BC037643-B4DB-40A8-9446-84F93E6F3540@microsoft.com...
> Thank you!
> Three more questions.....
>
> How do I run update stats?
>
You can call sp_updatestats or run UPDATE STATISTICS for each table
yourself.

> Can I schedule reindex and update stats to run periodically, or are these
> jobs best done manually?
These can be put in a job with steps you have created yourself ( see
sys.dm_db_index_physical_stats in books online for an example of re-indexing
depending on the level of fragmentation ). Alternatively you can create a
maintenance plan and schedule that (again see books online)
>
> Finally...any ideas what I can expect when I remove the compress flag?
>
Have you changed windows explorer to display compressed files in a different
colour to check that database files are compressed? If they are, then
stopping SQL Server before you change it, otherwise the file will be open
and you can't change it.

> Thanks again!
>
> "Marie" wrote:
>
John

Thank you for all your !

"John Bell" wrote:

>
> "Marie" <Marie@discussions.microsoft.com> wrote in message
> news:8F40CF1C-CC1C-4787-BC69-7EA693837BC4@microsoft.com...
> > Someone has set the compress drive flag on a SQL 2005 Windows 2003 disk
> > drive. Our vendor thinks this may be the reason users are experiencing
> > performance issues with the application.
> >
> > What can we expect if we remove the flag?
> >
> > I have just reindexed the database. Is there another maintenance task I
> > should run to compact or compress SQL database?
> >
> > Thank you!
> >
>
> http://blogs.msdn.com/sqlblog/archiv...d-volumes.aspx
>
> Even if it did work, a compressed drive would slow down I/O significantly,
> indexing would probably not improve the situation a great deal.
>
> John
>

"Marie" <Marie@discussions.microsoft.com> wrote in message
news:A5C961EC-1AF3-4B73-86D0-CB970EC3747B@microsoft.com...
> Thank you for all your !
>
> "John Bell" wrote:
>
>>
>> "Marie" <Marie@discussions.microsoft.com> wrote in message
>> news:8F40CF1C-CC1C-4787-BC69-7EA693837BC4@microsoft.com...
>> > Someone has set the compress drive flag on a SQL 2005 Windows 2003 disk
>> > drive. Our vendor thinks this may be the reason users are experiencing
>> > performance issues with the application.
>> >
>> > What can we expect if we remove the flag?
>> >
>> > I have just reindexed the database. Is there another maintenance task
>> > I
>> > should run to compact or compress SQL database?
>> >
>> > Thank you!
>> >
>>
>> http://blogs.msdn.com/sqlblog/archiv...d-volumes.aspx
>>
>> Even if it did work, a compressed drive would slow down I/O
>> significantly,
>> indexing would probably not improve the situation a great deal.
>>
>> John
>>

On the subject of compression, you should look at the compression availble
in SQL 2008!

John