Data File Does Not Shrink! (SQL2K)

Hi,
I have a database that its data file is almost 15GB. When I go to shrink
file page (EM), the page displays that the minimum file size can be 8GB but
after shrink, the size doesn't change at all! I tried both EM and DBCC
SHRINKFILE and the result is the same(the process takes 10 minutes)! It
might help if I say that the complete backup is about 9GB, but after restore
it's 15GB.
Any help would be greatly appreciated.
Leilahave you tried shrinking the files individually? To do this go to
shrink database, then click on the files... button and select the data
file you want to shrink.
Leila wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila|||Thanks,
I explained it exactly in my previous post!
"Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...
> have you tried shrinking the files individually? To do this go to
> shrink database, then click on the files... button and select the data
> file you want to shrink.
> Leila wrote:
>> Hi,
>> I have a database that its data file is almost 15GB. When I go to shrink
>> file page (EM), the page displays that the minimum file size can be 8GB
>> but
>> after shrink, the size doesn't change at all! I tried both EM and DBCC
>> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
>> might help if I say that the complete backup is about 9GB, but after
>> restore
>> it's 15GB.
>> Any help would be greatly appreciated.
>> Leila
>|||you were less exact with the line "any help would be greatly
appreciated"
anyway, I was just trying to check the basics, people often confuse the
shrink database option and shrink file option.
things you could try (I don't know if they make a difference, but I
tend to clutch at straws when this happens to me and it usually works
in the end) would be clearing all connections to your db (not always an
option), checking to see what locks are held, if it's an option you
could even try detaching and re-attaching.
I believe the backup will restore with the file sizes it originally was
backed up with, so if you are restoring from a backup that had a lot of
free space when backed up, it will claim that space again upon restore.
Leila wrote:
> Thanks,
> I explained it exactly in my previous post!
>
> "Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
> news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...
> > have you tried shrinking the files individually? To do this go to
> > shrink database, then click on the files... button and select the data
> > file you want to shrink.
> >
> > Leila wrote:
> >> Hi,
> >> I have a database that its data file is almost 15GB. When I go to shrink
> >> file page (EM), the page displays that the minimum file size can be 8GB
> >> but
> >> after shrink, the size doesn't change at all! I tried both EM and DBCC
> >> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> >> might help if I say that the complete backup is about 9GB, but after
> >> restore
> >> it's 15GB.
> >> Any help would be greatly appreciated.
> >> Leila
> >|||Try:
dbcc shrinkfile(1,9000)
... explicitly specify the size (in MB)
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila,
The situation you are reporting, is relacionated with the DB Transaction Log.
If you are not doing any log shipping, or transaction log backups, run the
Following:
Backup Log DatabaseName with truncate_only
This will truncate the inactive part of the Trn Log.
Best Regards,
Paulo Condeça
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila
Let's start with classic question , why would you want to shrink the file at
all?
Have you read this article?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
DBCC SHRINKFILE does not shrink a file past the size needed to store the
data. If you have 50 percent of the pagaes in a 10 MB data file are used ,a
DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only
5GB/MB not 6GB/MB
"Leila" <Leilas@.hotpop.com> wrote in message
news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB
> but after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after
> restore it's 15GB.
> Any help would be greatly appreciated.
> Leila
>|||Thanks,
I exactly tried it, but no change occured!
"F_clef" <Fclef@.discussions.microsoft.com> wrote in message
news:A2C58C00-D254-496F-ABFC-B1C7DB76EBDF@.microsoft.com...
> Try:
> dbcc shrinkfile(1,9000)
> ... explicitly specify the size (in MB)
> "Leila" wrote:
>> Hi,
>> I have a database that its data file is almost 15GB. When I go to shrink
>> file page (EM), the page displays that the minimum file size can be 8GB
>> but
>> after shrink, the size doesn't change at all! I tried both EM and DBCC
>> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
>> might help if I say that the complete backup is about 9GB, but after
>> restore
>> it's 15GB.
>> Any help would be greatly appreciated.
>> Leila
>>|||Thanks,
The log file almost always is 30MB! (Simple recovery model is being used)
"Paulo Condeça" <PauloCondea@.discussions.microsoft.com> wrote in message
news:B2BF958E-CE71-4DD6-8570-DA5EB3EF47F0@.microsoft.com...
> Leila,
> The situation you are reporting, is relacionated with the DB Transaction
> Log.
> If you are not doing any log shipping, or transaction log backups, run the
> Following:
> Backup Log DatabaseName with truncate_only
> This will truncate the inactive part of the Trn Log.
> Best Regards,
> Paulo Condeça
> "Leila" wrote:
>> Hi,
>> I have a database that its data file is almost 15GB. When I go to shrink
>> file page (EM), the page displays that the minimum file size can be 8GB
>> but
>> after shrink, the size doesn't change at all! I tried both EM and DBCC
>> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
>> might help if I say that the complete backup is about 9GB, but after
>> restore
>> it's 15GB.
>> Any help would be greatly appreciated.
>> Leila
>>|||Thanks Uri,
Actually there was a big table in db (about 5GB). At the end of year, the
data of this table was moved to other db (kind of archive). The admin
dropped and recreated this table in first db. After some time, this table
became large again. sp_SpaceUsed shows that this table has 3GB reserved
space, 500MB is data and almost 2.5GB is unused! I'm curious to free this
2.5GB space.
I must mention that DBCC CHECKDB returns error for 4 pages near eachother.
But the ObjectID the it shows does not exists in sysobjects and Object_Name
function returns NULL for it!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edCoPQ$BHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Leila
> Let's start with classic question , why would you want to shrink the file
> at all?
> Have you read this article?
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> DBCC SHRINKFILE does not shrink a file past the size needed to store the
> data. If you have 50 percent of the pagaes in a 10 MB data file are used
> ,a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to
> only 5GB/MB not 6GB/MB
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have a database that its data file is almost 15GB. When I go to shrink
>> file page (EM), the page displays that the minimum file size can be 8GB
>> but after shrink, the size doesn't change at all! I tried both EM and
>> DBCC SHRINKFILE and the result is the same(the process takes 10 minutes)!
>> It might help if I say that the complete backup is about 9GB, but after
>> restore it's 15GB.
>> Any help would be greatly appreciated.
>> Leila
>sql

Data File Does Not Shrink! (SQL2K)

Hi,
I have a database that its data file is almost 15GB. When I go to shrink
file page (EM), the page displays that the minimum file size can be 8GB but
after shrink, the size doesn't change at all! I tried both EM and DBCC
SHRINKFILE and the result is the same(the process takes 10 minutes)! It
might help if I say that the complete backup is about 9GB, but after restore
it's 15GB.
Any help would be greatly appreciated.
Leila
have you tried shrinking the files individually? To do this go to
shrink database, then click on the files... button and select the data
file you want to shrink.
Leila wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
|||Thanks,
I explained it exactly in my previous post!
"Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
news:1163509708.111473.245080@.b28g2000cwb.googlegr oups.com...
> have you tried shrinking the files individually? To do this go to
> shrink database, then click on the files... button and select the data
> file you want to shrink.
> Leila wrote:
>
|||you were less exact with the line "any help would be greatly
appreciated"
anyway, I was just trying to check the basics, people often confuse the
shrink database option and shrink file option.
things you could try (I don't know if they make a difference, but I
tend to clutch at straws when this happens to me and it usually works
in the end) would be clearing all connections to your db (not always an
option), checking to see what locks are held, if it's an option you
could even try detaching and re-attaching.
I believe the backup will restore with the file sizes it originally was
backed up with, so if you are restoring from a backup that had a lot of
free space when backed up, it will claim that space again upon restore.
Leila wrote:[vbcol=seagreen]
> Thanks,
> I explained it exactly in my previous post!
>
> "Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
> news:1163509708.111473.245080@.b28g2000cwb.googlegr oups.com...
|||Try:
dbcc shrinkfile(1,9000)
... explicitly specify the size (in MB)
"Leila" wrote:

> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>
|||Leila,
The situation you are reporting, is relacionated with the DB Transaction Log.
If you are not doing any log shipping, or transaction log backups, run the
Following:
Backup Log DatabaseName with truncate_only
This will truncate the inactive part of the Trn Log.
Best Regards,
Paulo Conde?a
"Leila" wrote:

> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>
|||Leila
Let's start with classic question , why would you want to shrink the file at
all?
Have you read this article?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
DBCC SHRINKFILE does not shrink a file past the size needed to store the
data. If you have 50 percent of the pagaes in a 10 MB data file are used ,a
DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only
5GB/MB not 6GB/MB
"Leila" <Leilas@.hotpop.com> wrote in message
news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB
> but after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after
> restore it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
|||Thanks,
I exactly tried it, but no change occured!
"F_clef" <Fclef@.discussions.microsoft.com> wrote in message
news:A2C58C00-D254-496F-ABFC-B1C7DB76EBDF@.microsoft.com...[vbcol=seagreen]
> Try:
> dbcc shrinkfile(1,9000)
> ... explicitly specify the size (in MB)
> "Leila" wrote:
|||Thanks,
The log file almost always is 30MB! (Simple recovery model is being used)
"Paulo Condea" <PauloCondea@.discussions.microsoft.com> wrote in message
news:B2BF958E-CE71-4DD6-8570-DA5EB3EF47F0@.microsoft.com...[vbcol=seagreen]
> Leila,
> The situation you are reporting, is relacionated with the DB Transaction
> Log.
> If you are not doing any log shipping, or transaction log backups, run the
> Following:
> Backup Log DatabaseName with truncate_only
> This will truncate the inactive part of the Trn Log.
> Best Regards,
> Paulo Condea
> "Leila" wrote:
|||Thanks Uri,
Actually there was a big table in db (about 5GB). At the end of year, the
data of this table was moved to other db (kind of archive). The admin
dropped and recreated this table in first db. After some time, this table
became large again. sp_SpaceUsed shows that this table has 3GB reserved
space, 500MB is data and almost 2.5GB is unused! I'm curious to free this
2.5GB space.
I must mention that DBCC CHECKDB returns error for 4 pages near eachother.
But the ObjectID the it shows does not exists in sysobjects and Object_Name
function returns NULL for it!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edCoPQ$BHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Leila
> Let's start with classic question , why would you want to shrink the file
> at all?
> Have you read this article?
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> DBCC SHRINKFILE does not shrink a file past the size needed to store the
> data. If you have 50 percent of the pagaes in a 10 MB data file are used
> ,a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to
> only 5GB/MB not 6GB/MB
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
>

Data File Does Not Shrink! (SQL2K)

Hi,
I have a database that its data file is almost 15GB. When I go to shrink
file page (EM), the page displays that the minimum file size can be 8GB but
after shrink, the size doesn't change at all! I tried both EM and DBCC
SHRINKFILE and the result is the same(the process takes 10 minutes)! It
might help if I say that the complete backup is about 9GB, but after restore
it's 15GB.
Any help would be greatly appreciated.
Leilahave you tried shrinking the files individually? To do this go to
shrink database, then click on the files... button and select the data
file you want to shrink.
Leila wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB bu
t
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after resto
re
> it's 15GB.
> Any help would be greatly appreciated.
> Leila|||Thanks,
I explained it exactly in my previous post!
"Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...
> have you tried shrinking the files individually? To do this go to
> shrink database, then click on the files... button and select the data
> file you want to shrink.
> Leila wrote:
>|||you were less exact with the line "any help would be greatly
appreciated"
anyway, I was just trying to check the basics, people often confuse the
shrink database option and shrink file option.
things you could try (I don't know if they make a difference, but I
tend to clutch at straws when this happens to me and it usually works
in the end) would be clearing all connections to your db (not always an
option), checking to see what locks are held, if it's an option you
could even try detaching and re-attaching.
I believe the backup will restore with the file sizes it originally was
backed up with, so if you are restoring from a backup that had a lot of
free space when backed up, it will claim that space again upon restore.
Leila wrote:[vbcol=seagreen]
> Thanks,
> I explained it exactly in my previous post!
>
> "Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
> news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...|||Try:
dbcc shrinkfile(1,9000)
... explicitly specify the size (in MB)
"Leila" wrote:

> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB bu
t
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after resto
re
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila,
The situation you are reporting, is relacionated with the DB Transaction Log
.
If you are not doing any log shipping, or transaction log backups, run the
Following:
Backup Log DatabaseName with truncate_only
This will truncate the inactive part of the Trn Log.
Best Regards,
Paulo Conde?a
"Leila" wrote:

> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB bu
t
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after resto
re
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila
Let's start with classic question , why would you want to shrink the file at
all?
Have you read this article?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
DBCC SHRINKFILE does not shrink a file past the size needed to store the
data. If you have 50 percent of the pagaes in a 10 MB data file are used ,a
DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only
5GB/MB not 6GB/MB
"Leila" <Leilas@.hotpop.com> wrote in message
news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB
> but after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after
> restore it's 15GB.
> Any help would be greatly appreciated.
> Leila
>|||Thanks,
I exactly tried it, but no change occured!
"F_clef" <Fclef@.discussions.microsoft.com> wrote in message
news:A2C58C00-D254-496F-ABFC-B1C7DB76EBDF@.microsoft.com...[vbcol=seagreen]
> Try:
> dbcc shrinkfile(1,9000)
> ... explicitly specify the size (in MB)
> "Leila" wrote:
>|||Thanks,
The log file almost always is 30MB! (Simple recovery model is being used)
"Paulo Condea" <PauloCondea@.discussions.microsoft.com> wrote in message
news:B2BF958E-CE71-4DD6-8570-DA5EB3EF47F0@.microsoft.com...[vbcol=seagreen]
> Leila,
> The situation you are reporting, is relacionated with the DB Transaction
> Log.
> If you are not doing any log shipping, or transaction log backups, run the
> Following:
> Backup Log DatabaseName with truncate_only
> This will truncate the inactive part of the Trn Log.
> Best Regards,
> Paulo Condea
> "Leila" wrote:
>|||Thanks Uri,
Actually there was a big table in db (about 5GB). At the end of year, the
data of this table was moved to other db (kind of archive). The admin
dropped and recreated this table in first db. After some time, this table
became large again. sp_SpaceUsed shows that this table has 3GB reserved
space, 500MB is data and almost 2.5GB is unused! I'm curious to free this
2.5GB space.
I must mention that DBCC CHECKDB returns error for 4 pages near eachother.
But the ObjectID the it shows does not exists in sysobjects and Object_Name
function returns NULL for it!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edCoPQ$BHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Leila
> Let's start with classic question , why would you want to shrink the file
> at all?
> Have you read this article?
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> DBCC SHRINKFILE does not shrink a file past the size needed to store the
> data. If you have 50 percent of the pagaes in a 10 MB data file are used
> ,a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to
> only 5GB/MB not 6GB/MB
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
>

Data encryption in SQL Server 2000

I know there is no native encryption in SQL2K, but what 3rd party encryption tools would other forum members recommend from experience? I am required to encrypt data for PCI compliance.
Thanks
LempsterSQL Server Magazine (http://www.sqlmag.com/articles/index.cfm?articleid=44550&) gives you a few good hints, even if you don't subscribe. Apart from that, I do not know what kind of requirements you have, but keep in mind that NTFS supports encryption of files, which is transparent to the applications, so you can encrypt the data files so that only the SQL Server Service Account can read the file. Hope this was of some help.

data corruption question

sql2k sp3
Based on recent experience Ive got a few questions about data corruption:
1> How does it happen?
2> What can be done to prevent it?
3> If it does happen, what are the proper steps to try to fix it without
restoring?
4> If I do need to restore, how do I know how far back to restore? I wouldnt
want to go through a lengthy restore process and then find out I still have
the corruption. Is there a way to know the day/time it first started?
TIA, ChrisRHi
99.999% of all corruption is caused by hardware.
Get good and reputable hardware and don't cut corners when installing it.
Have a UPS to keep the server up during power interruptions. Have battery
backed up RAID controller cards.
Make very regular backups, including transaction logs and get them off to
tape ASAP. Run regular DBCC's to check the consistency of the databases, and
have 'Torn page detection' on for each database. This will tell you very
quickly that some corruption has occurred. How often? Well, depending on
your uptime requirements, DBCC's daily does not sound like a bad idea.
Some times, corruption is not detected until weeks after it occurred so you
loose a lot. Daily, at worst case you loose a day's data.
Run the latest BIOS and driver versions, keep up to date with Windows SP's
and hotfixes and SQL SP's.
Don't plug you server and the kettle into the same wall plug, and keep the
Managers away from the servers. LOL.
Most of it is just common sense.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A640B78-E981-4F57-B8A9-AF324C0ECE10@.microsoft.com...
> sql2k sp3
> Based on recent experience Ive got a few questions about data corruption:
> 1> How does it happen?
> 2> What can be done to prevent it?
> 3> If it does happen, what are the proper steps to try to fix it without
> restoring?
> 4> If I do need to restore, how do I know how far back to restore? I
wouldnt
> want to go through a lengthy restore process and then find out I still
have
> the corruption. Is there a way to know the day/time it first started?
> TIA, ChrisR

data corruption question

sql2k sp3
Based on recent experience Ive got a few questions about data corruption:
1> How does it happen?
2> What can be done to prevent it?
3> If it does happen, what are the proper steps to try to fix it without
restoring?
4> If I do need to restore, how do I know how far back to restore? I wouldnt
want to go through a lengthy restore process and then find out I still have
the corruption. Is there a way to know the day/time it first started?
TIA, ChrisR
Hi
99.999% of all corruption is caused by hardware.
Get good and reputable hardware and don't cut corners when installing it.
Have a UPS to keep the server up during power interruptions. Have battery
backed up RAID controller cards.
Make very regular backups, including transaction logs and get them off to
tape ASAP. Run regular DBCC's to check the consistency of the databases, and
have 'Torn page detection' on for each database. This will tell you very
quickly that some corruption has occurred. How often? Well, depending on
your uptime requirements, DBCC's daily does not sound like a bad idea.
Some times, corruption is not detected until weeks after it occurred so you
loose a lot. Daily, at worst case you loose a day's data.
Run the latest BIOS and driver versions, keep up to date with Windows SP's
and hotfixes and SQL SP's.
Don't plug you server and the kettle into the same wall plug, and keep the
Managers away from the servers. LOL.
Most of it is just common sense.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A640B78-E981-4F57-B8A9-AF324C0ECE10@.microsoft.com...
> sql2k sp3
> Based on recent experience Ive got a few questions about data corruption:
> 1> How does it happen?
> 2> What can be done to prevent it?
> 3> If it does happen, what are the proper steps to try to fix it without
> restoring?
> 4> If I do need to restore, how do I know how far back to restore? I
wouldnt
> want to go through a lengthy restore process and then find out I still
have
> the corruption. Is there a way to know the day/time it first started?
> TIA, ChrisR

data corruption question

sql2k sp3
Based on recent experience Ive got a few questions about data corruption:
1> How does it happen?
2> What can be done to prevent it?
3> If it does happen, what are the proper steps to try to fix it without
restoring?
4> If I do need to restore, how do I know how far back to restore? I wouldnt
want to go through a lengthy restore process and then find out I still have
the corruption. Is there a way to know the day/time it first started?
TIA, ChrisRHi
99.999% of all corruption is caused by hardware.
Get good and reputable hardware and don't cut corners when installing it.
Have a UPS to keep the server up during power interruptions. Have battery
backed up RAID controller cards.
Make very regular backups, including transaction logs and get them off to
tape ASAP. Run regular DBCC's to check the consistency of the databases, and
have 'Torn page detection' on for each database. This will tell you very
quickly that some corruption has occurred. How often? Well, depending on
your uptime requirements, DBCC's daily does not sound like a bad idea.
Some times, corruption is not detected until weeks after it occurred so you
loose a lot. Daily, at worst case you loose a day's data.
Run the latest BIOS and driver versions, keep up to date with Windows SP's
and hotfixes and SQL SP's.
Don't plug you server and the kettle into the same wall plug, and keep the
Managers away from the servers. LOL.
Most of it is just common sense.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A640B78-E981-4F57-B8A9-AF324C0ECE10@.microsoft.com...
> sql2k sp3
> Based on recent experience Ive got a few questions about data corruption:
> 1> How does it happen?
> 2> What can be done to prevent it?
> 3> If it does happen, what are the proper steps to try to fix it without
> restoring?
> 4> If I do need to restore, how do I know how far back to restore? I
wouldnt
> want to go through a lengthy restore process and then find out I still
have
> the corruption. Is there a way to know the day/time it first started?
> TIA, ChrisR

data "movement" the culprit?

sql2k sp3
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, no
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its taking
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what else
could cause this.
TIA, ChrisR
It's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR
|||I havent looked, but will. Does this number represent how long it take SQL to
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:

> It's unlikely that is the cause. A page split if it occurs should only take
> ms not minutes. And you would see blocking. My guess is you have a bad query
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>
>
|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...[vbcol=seagreen]
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:

data "movement" the culprit?

sql2k sp3
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, no
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its taking
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what else
could cause this.
TIA, ChrisRIt's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR|||I havent looked, but will. Does this number represent how long it take SQL to
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:
> It's unlikely that is the cause. A page split if it occurs should only take
> ms not minutes. And you would see blocking. My guess is you have a bad query
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> > sql2k sp3
> >
> > I have a report query that sometimes runs in 3 seconds, other times I kill
> > it after 10 minutes. When its taking a long time, there is no no blocking,
> > no
> > extremely high cpu/ memory usage, no huge amount of activity. Something I
> > just noticed though is that someone put the Clustered Index on a date
> > filed
> > even though this is an OLTP DB. Is there any possibility that when its
> > taking
> > a long time to return results, whats happening is that data was recently
> > inserted that would cause the data in the table to move because of where
> > the
> > clustering is? That SQL is searching for a moving target based on the data
> > range of the SP, which is in the middle of being rearranged because of
> > it's
> > clustering setup? If this were the case, would it cause actual
> > blocking(dont
> > forget Im not seeing any.)? I know I'm grasping here, but dont know what
> > else
> > could cause this.
> >
> > TIA, ChrisR
>
>|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:
>> It's unlikely that is the cause. A page split if it occurs should only
>> take
>> ms not minutes. And you would see blocking. My guess is you have a bad
>> query
>> plan and disk bottlenecks. But the only way to be sure is to monitor the
>> server from all aspects to see what is the bottleneck. What about wait's
>> during that time?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>> > sql2k sp3
>> >
>> > I have a report query that sometimes runs in 3 seconds, other times I
>> > kill
>> > it after 10 minutes. When its taking a long time, there is no no
>> > blocking,
>> > no
>> > extremely high cpu/ memory usage, no huge amount of activity. Something
>> > I
>> > just noticed though is that someone put the Clustered Index on a date
>> > filed
>> > even though this is an OLTP DB. Is there any possibility that when its
>> > taking
>> > a long time to return results, whats happening is that data was
>> > recently
>> > inserted that would cause the data in the table to move because of
>> > where
>> > the
>> > clustering is? That SQL is searching for a moving target based on the
>> > data
>> > range of the SP, which is in the middle of being rearranged because of
>> > it's
>> > clustering setup? If this were the case, would it cause actual
>> > blocking(dont
>> > forget Im not seeing any.)? I know I'm grasping here, but dont know
>> > what
>> > else
>> > could cause this.
>> >
>> > TIA, ChrisR
>>

data "movement" the culprit?

sql2k sp3
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, n
o
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its takin
g
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what els
e
could cause this.
TIA, ChrisRIt's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR|||I havent looked, but will. Does this number represent how long it take SQL t
o
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:

> It's unlikely that is the cause. A page split if it occurs should only tak
e
> ms not minutes. And you would see blocking. My guess is you have a bad que
ry
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>
>|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...[vbcol=seagreen]
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:
>