Database in recovery

Our server ran out of disk space during a stored procedure process. We were able to free up some space, stopped all SQL services, restarted all SQL services and now SQL Management Studio shows the database to be in recovery. Is there a way to monitor this to ensure progress is being made and to estimate when it might complete?

I don't know it can be checked programatically but you can sql error log where you recovery progress will be recorded.

Looks like you guys shutdown the servers in the middle of big transaction....

There will no other option than waiting to recover...

|||

There is a way, via T-SQL to view process progress but apparently not via GUI. The database did eventually recover and we have submitted for a new server as it ran out of disk space during a stored procedure run.

Thanks!

database File size problem

Hi,
I have a database which is 10 GB big but on disk it shows 20GB. I did
backup and shrinkdb but it doesn't shrink. I could shrink log but not
the actual db. Any hints why I cannot do this? Also weird thing is that
when I run DBCC shrinkdb I don't get any error message.
Here is the exact command I run:
DBCC SHRINKDATABASE (MYDB, 5, TRUNCATEONLY)
GO
Tnank you,
hjHi
DBCC SHRINKDATABASE will not shrink a file smaller than it's initial size,
which may be the issue in your case. Use DBCC SHRINKFILE to shrink the
individual file, but if you are will be expanding to 20GB at some time, then
you may not want to shrink it at all. Your log file should be a reasonably
constant size, if you backup the log regularly (in FULL recovery mode).
John
"Hitesh" wrote:

> Hi,
> I have a database which is 10 GB big but on disk it shows 20GB. I did
> backup and shrinkdb but it doesn't shrink. I could shrink log but not
> the actual db. Any hints why I cannot do this? Also weird thing is that
> when I run DBCC shrinkdb I don't get any error message.
> Here is the exact command I run:
> DBCC SHRINKDATABASE (MYDB, 5, TRUNCATEONLY)
> GO
> Tnank you,
> hj
>|||DBCC SHRINKDATABASE will not shrink a file smaller than its shrinkpoint.
The shrinkpoint starts out at the initial size of the file, but once you use
DBCC SHRINKFILE, that can set a new shrinkpoint, and subsequent DBCC
SHRINKDATABASE operations can shrink to that new smaller size.
Also, Hitesh, make sure you're aware what the parameters to DBCC
SHRINKDATABASE mean. The 5 parameter means to shrink so that there is 5%
free space in the file. If there is already more than 5% free space, no
shrinking will take place.
When you run DBCC SHRINKFILE, then the number is the size in MB to which you
want to shrink the file.
Why do you think you might get an error message?
--
HTH
Kalen Delaney, SQL Server MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:79C1DC8D-4BB0-4D11-BBED-A98A9BA35A2C@.microsoft.com...[vbcol=seagreen]
> Hi
> DBCC SHRINKDATABASE will not shrink a file smaller than it's initial size,
> which may be the issue in your case. Use DBCC SHRINKFILE to shrink the
> individual file, but if you are will be expanding to 20GB at some time,
> then
> you may not want to shrink it at all. Your log file should be a reasonably
> constant size, if you backup the log regularly (in FULL recovery mode).
> John
> "Hitesh" wrote:
>|||Thank you John & Kalean.
When I used DBCC SHRINKFILE on indivisual database files, it worked.
Kalean is right I might be using wrong free space percentage.
Thank you both for your help.
hj
Kalen Delaney wrote:[vbcol=seagreen]
> DBCC SHRINKDATABASE will not shrink a file smaller than its shrinkpoint.
> The shrinkpoint starts out at the initial size of the file, but once you u
se
> DBCC SHRINKFILE, that can set a new shrinkpoint, and subsequent DBCC
> SHRINKDATABASE operations can shrink to that new smaller size.
> Also, Hitesh, make sure you're aware what the parameters to DBCC
> SHRINKDATABASE mean. The 5 parameter means to shrink so that there is 5%
> free space in the file. If there is already more than 5% free space, no
> shrinking will take place.
> When you run DBCC SHRINKFILE, then the number is the size in MB to which y
ou
> want to shrink the file.
> Why do you think you might get an error message?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:79C1DC8D-4BB0-4D11-BBED-A98A9BA35A2C@.microsoft.com...

database File size problem

Hi,
I have a database which is 10 GB big but on disk it shows 20GB. I did
backup and shrinkdb but it doesn't shrink. I could shrink log but not
the actual db. Any hints why I cannot do this? Also weird thing is that
when I run DBCC shrinkdb I don't get any error message.
Here is the exact command I run:
DBCC SHRINKDATABASE (MYDB, 5, TRUNCATEONLY)
GO
Tnank you,
hjHi
DBCC SHRINKDATABASE will not shrink a file smaller than it's initial size,
which may be the issue in your case. Use DBCC SHRINKFILE to shrink the
individual file, but if you are will be expanding to 20GB at some time, then
you may not want to shrink it at all. Your log file should be a reasonably
constant size, if you backup the log regularly (in FULL recovery mode).
John
"Hitesh" wrote:
> Hi,
> I have a database which is 10 GB big but on disk it shows 20GB. I did
> backup and shrinkdb but it doesn't shrink. I could shrink log but not
> the actual db. Any hints why I cannot do this? Also weird thing is that
> when I run DBCC shrinkdb I don't get any error message.
> Here is the exact command I run:
> DBCC SHRINKDATABASE (MYDB, 5, TRUNCATEONLY)
> GO
> Tnank you,
> hj
>|||DBCC SHRINKDATABASE will not shrink a file smaller than its shrinkpoint.
The shrinkpoint starts out at the initial size of the file, but once you use
DBCC SHRINKFILE, that can set a new shrinkpoint, and subsequent DBCC
SHRINKDATABASE operations can shrink to that new smaller size.
Also, Hitesh, make sure you're aware what the parameters to DBCC
SHRINKDATABASE mean. The 5 parameter means to shrink so that there is 5%
free space in the file. If there is already more than 5% free space, no
shrinking will take place.
When you run DBCC SHRINKFILE, then the number is the size in MB to which you
want to shrink the file.
Why do you think you might get an error message?
--
HTH
Kalen Delaney, SQL Server MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:79C1DC8D-4BB0-4D11-BBED-A98A9BA35A2C@.microsoft.com...
> Hi
> DBCC SHRINKDATABASE will not shrink a file smaller than it's initial size,
> which may be the issue in your case. Use DBCC SHRINKFILE to shrink the
> individual file, but if you are will be expanding to 20GB at some time,
> then
> you may not want to shrink it at all. Your log file should be a reasonably
> constant size, if you backup the log regularly (in FULL recovery mode).
> John
> "Hitesh" wrote:
>> Hi,
>> I have a database which is 10 GB big but on disk it shows 20GB. I did
>> backup and shrinkdb but it doesn't shrink. I could shrink log but not
>> the actual db. Any hints why I cannot do this? Also weird thing is that
>> when I run DBCC shrinkdb I don't get any error message.
>> Here is the exact command I run:
>> DBCC SHRINKDATABASE (MYDB, 5, TRUNCATEONLY)
>> GO
>> Tnank you,
>> hj
>>|||Thank you John & Kalean.
When I used DBCC SHRINKFILE on indivisual database files, it worked.
Kalean is right I might be using wrong free space percentage.
Thank you both for your help.
hj
Kalen Delaney wrote:
> DBCC SHRINKDATABASE will not shrink a file smaller than its shrinkpoint.
> The shrinkpoint starts out at the initial size of the file, but once you use
> DBCC SHRINKFILE, that can set a new shrinkpoint, and subsequent DBCC
> SHRINKDATABASE operations can shrink to that new smaller size.
> Also, Hitesh, make sure you're aware what the parameters to DBCC
> SHRINKDATABASE mean. The 5 parameter means to shrink so that there is 5%
> free space in the file. If there is already more than 5% free space, no
> shrinking will take place.
> When you run DBCC SHRINKFILE, then the number is the size in MB to which you
> want to shrink the file.
> Why do you think you might get an error message?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:79C1DC8D-4BB0-4D11-BBED-A98A9BA35A2C@.microsoft.com...
> > Hi
> >
> > DBCC SHRINKDATABASE will not shrink a file smaller than it's initial size,
> > which may be the issue in your case. Use DBCC SHRINKFILE to shrink the
> > individual file, but if you are will be expanding to 20GB at some time,
> > then
> > you may not want to shrink it at all. Your log file should be a reasonably
> > constant size, if you backup the log regularly (in FULL recovery mode).
> >
> > John
> >
> > "Hitesh" wrote:
> >
> >> Hi,
> >>
> >> I have a database which is 10 GB big but on disk it shows 20GB. I did
> >> backup and shrinkdb but it doesn't shrink. I could shrink log but not
> >> the actual db. Any hints why I cannot do this? Also weird thing is that
> >> when I run DBCC shrinkdb I don't get any error message.
> >> Here is the exact command I run:
> >>
> >> DBCC SHRINKDATABASE (MYDB, 5, TRUNCATEONLY)
> >>
> >> GO
> >>
> >> Tnank you,
> >> hj
> >>
> >>

Database File and Disk Space

Hi All,
One of the drives that stores the database file is close to running out of space. The chance of me getting more space added to this drive any time soon are really low. What are other options I have?
Thanks.1. Open a new file segment on another drive
2. Shrink the existing log and or data file(s)
3. Tell TPTB that they are going to lose the ability to use the database due to lack of disk space.

database errr,URGENT

Hi
I am using mssql7 server.unfortunatily the space of the
hard disk reached below 10mb and while saving data
server has showned the disk full error.
After making enough space i restared the server,the server
has started,but one of the main database is showing an
error.msg 945 Level 14 state 2, Some of the files
could not be activated.
i am linking some of the table from this database to
another database,how can i start this database.
Please help me, urgent
Thank you
You may have better luck asking this question in
microsoft.public.sqlserver.server. The topic of this newsgroup is
Microsoft English Query.
SK
shif wrote:

>Hi
>I am using mssql7 server.unfortunatily the space of the
>hard disk reached below 10mb and while saving data
>server has showned the disk full error.
>After making enough space i restared the server,the server
>has started,but one of the main database is showing an
>error.msg 945 Level 14 state 2, Some of the files
>could not be activated.
>i am linking some of the table from this database to
>another database,how can i start this database.
>Please help me, urgent
>Thank you
>
>

database encryption

Hello!
i am aware of the ability to encrypt something or issue a certificate for a
column, etc.
does the sql server itself write to disk in clear text?
if so, is there a feature to prevent that?
can a db not be mountable to another sql server without a password?
thank you very much for your help!
-Bahman"Bahman" <Bahman@.discussions.microsoft.com> wrote in message
news:4C85593A-F3A3-467B-AD51-32A87A99143C@.microsoft.com...
> Hello!
> i am aware of the ability to encrypt something or issue a certificate for
> a
> column, etc.
> does the sql server itself write to disk in clear text?
SQL Server stores data in binary format. Their might be some plain text
that can be seen if someone were to dissect your MDF files.

> if so, is there a feature to prevent that?
There are third party software encryption tools that encrypt the actual MDF
files, or even entire hard drives.|||"Bahman" <Bahman@.discussions.microsoft.com> wrote in message
news:4C85593A-F3A3-467B-AD51-32A87A99143C@.microsoft.com...
> Hello!
> i am aware of the ability to encrypt something or issue a certificate for
> a
> column, etc.
> does the sql server itself write to disk in clear text?
SQL Server stores data in binary format. Their might be some plain text
that can be seen if someone were to dissect your MDF files.

> if so, is there a feature to prevent that?
There are third party software encryption tools that encrypt the actual MDF
files, or even entire hard drives.|||In SQL Server 2005 the private key associated with a certificate is always
stored encrypted. See the famous Encryption Hierarchy chart at
http://msdn2.microsoft.com/en-US/library/ms189586.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Bahman" <Bahman@.discussions.microsoft.com> wrote in message
news:4C85593A-F3A3-467B-AD51-32A87A99143C@.microsoft.com...
> Hello!
> i am aware of the ability to encrypt something or issue a certificate for
> a
> column, etc.
> does the sql server itself write to disk in clear text?
> if so, is there a feature to prevent that?
> can a db not be mountable to another sql server without a password?
> thank you very much for your help!
> -Bahman
>|||In SQL Server 2005 the private key associated with a certificate is always
stored encrypted. See the famous Encryption Hierarchy chart at
http://msdn2.microsoft.com/en-US/library/ms189586.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Bahman" <Bahman@.discussions.microsoft.com> wrote in message
news:4C85593A-F3A3-467B-AD51-32A87A99143C@.microsoft.com...
> Hello!
> i am aware of the ability to encrypt something or issue a certificate for
> a
> column, etc.
> does the sql server itself write to disk in clear text?
> if so, is there a feature to prevent that?
> can a db not be mountable to another sql server without a password?
> thank you very much for your help!
> -Bahman
>|||Can someone recommend a good highly scalable and easy to use 3rd party tool
to encrypt the contents of a disk. Keep in mind that I run a SQL Cluster. So
the disk is not local. It resides on the SAN.
TIA!
"Mike C#" <xxx@.yyy.com> wrote in message news:Na1lg.294$B95.11@.fe10.lga...
> "Bahman" <Bahman@.discussions.microsoft.com> wrote in message
> news:4C85593A-F3A3-467B-AD51-32A87A99143C@.microsoft.com...
> SQL Server stores data in binary format. Their might be some plain text
> that can be seen if someone were to dissect your MDF files.
>
> There are third party software encryption tools that encrypt the actual
> MDF files, or even entire hard drives.
>|||Can someone recommend a good highly scalable and easy to use 3rd party tool
to encrypt the contents of a disk. Keep in mind that I run a SQL Cluster. So
the disk is not local. It resides on the SAN.
TIA!
"Mike C#" <xxx@.yyy.com> wrote in message news:Na1lg.294$B95.11@.fe10.lga...
> "Bahman" <Bahman@.discussions.microsoft.com> wrote in message
> news:4C85593A-F3A3-467B-AD51-32A87A99143C@.microsoft.com...
> SQL Server stores data in binary format. Their might be some plain text
> that can be seen if someone were to dissect your MDF files.
>
> There are third party software encryption tools that encrypt the actual
> MDF files, or even entire hard drives.
>|||You can use encrypt data and file using SQL Server 2005.
In SQL Serer 2005 support many Encryption methods.
And can use EFS, NTFS .
"Bahman"?? ??? ??:

> Hello!
> i am aware of the ability to encrypt something or issue a certificate for
a
> column, etc.
> does the sql server itself write to disk in clear text?
> if so, is there a feature to prevent that?
> can a db not be mountable to another sql server without a password?
> thank you very much for your help!
> -Bahman
>|||You can use encrypt data and file using SQL Server 2005.
In SQL Serer 2005 support many Encryption methods.
And can use EFS, NTFS .
"Bahman"?? ??? ??:

> Hello!
> i am aware of the ability to encrypt something or issue a certificate for
a
> column, etc.
> does the sql server itself write to disk in clear text?
> if so, is there a feature to prevent that?
> can a db not be mountable to another sql server without a password?
> thank you very much for your help!
> -Bahman
>

Database dump size

Hi there. Running a Sql 2000 installation with latest service pack on
Windows 2000 server. My database dump to disk backup keeps increasing
by about 1 meg per day. The database itself is increasing by a small
percentage of that. In fact the dump is now about the same size as the
database. What's going on? Thanks.
What kind of backup are you doing? If you don't use the INIT option the
current backup will append to the existing ones in the dump device.
Andrew J. Kelly
SQL Server MVP
"cscott" <christopher@.uncommonlaw.com> wrote in message
news:a94f8da8.0406070533.6a59d6fb@.posting.google.c om...
> Hi there. Running a Sql 2000 installation with latest service pack on
> Windows 2000 server. My database dump to disk backup keeps increasing
> by about 1 meg per day. The database itself is increasing by a small
> percentage of that. In fact the dump is now about the same size as the
> database. What's going on? Thanks.
|||Hi ,
To add on to Andrew post, Execute the below comand from query analyzer to
check if BACKUP is executed in append mode.
restore headeronly from disk='Directory\dbname.BAK'
If this list more than 1 entry (Check the postion column in output), means
you have taken the backup in Append mode. To remove he append mode add an
OPTION
INIT along with backup database command
BACKUP DATABASE dbname to disk='c:\dbname.bak' with INIT
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uYtrikJTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> What kind of backup are you doing? If you don't use the INIT option the
> current backup will append to the existing ones in the dump device.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "cscott" <christopher@.uncommonlaw.com> wrote in message
> news:a94f8da8.0406070533.6a59d6fb@.posting.google.c om...
>
|||Used the maintenance plan wizard to set it up. It's a dump to disk.
There aren't many options to select really. It does seem like it's
appending the dump. Is there an option in Enterprise manager to disable
append or will I have to do it the hard way? Thanks.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Maint plan doesn't append. It creates a new backup device each time it performs a backup. Verify using RESTORE
HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Christopher Scott" <christopher@.uncommonlaw.com> wrote in message
news:%23cTXggVTEHA.2416@.TK2MSFTNGP12.phx.gbl...
>
> Used the maintenance plan wizard to set it up. It's a dump to disk.
> There aren't many options to select really. It does seem like it's
> appending the dump. Is there an option in Enterprise manager to disable
> append or will I have to do it the hard way? Thanks.
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Database dump size

Hi there. Running a Sql 2000 installation with latest service pack on
Windows 2000 server. My database dump to disk backup keeps increasing
by about 1 meg per day. The database itself is increasing by a small
percentage of that. In fact the dump is now about the same size as the
database. What's going on? Thanks.What kind of backup are you doing? If you don't use the INIT option the
current backup will append to the existing ones in the dump device.
Andrew J. Kelly
SQL Server MVP
"cscott" <christopher@.uncommonlaw.com> wrote in message
news:a94f8da8.0406070533.6a59d6fb@.posting.google.com...
> Hi there. Running a Sql 2000 installation with latest service pack on
> Windows 2000 server. My database dump to disk backup keeps increasing
> by about 1 meg per day. The database itself is increasing by a small
> percentage of that. In fact the dump is now about the same size as the
> database. What's going on? Thanks.|||Hi ,
To add on to Andrew post, Execute the below comand from query analyzer to
check if BACKUP is executed in append mode.
restore headeronly from disk='Directory\dbname.BAK'
If this list more than 1 entry (Check the postion column in output), means
you have taken the backup in Append mode. To remove he append mode add an
OPTION
INIT along with backup database command
BACKUP DATABASE dbname to disk='c:\dbname.bak' with INIT
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uYtrikJTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> What kind of backup are you doing? If you don't use the INIT option the
> current backup will append to the existing ones in the dump device.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "cscott" <christopher@.uncommonlaw.com> wrote in message
> news:a94f8da8.0406070533.6a59d6fb@.posting.google.com...
>|||Used the maintenance plan wizard to set it up. It's a dump to disk.
There aren't many options to select really. It does seem like it's
appending the dump. Is there an option in Enterprise manager to disable
append or will I have to do it the hard way? Thanks.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Maint plan doesn't append. It creates a new backup device each time it perfo
rms a backup. Verify using RESTORE
HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Christopher Scott" <christopher@.uncommonlaw.com> wrote in message
news:%23cTXggVTEHA.2416@.TK2MSFTNGP12.phx.gbl...
>
> Used the maintenance plan wizard to set it up. It's a dump to disk.
> There aren't many options to select really. It does seem like it's
> appending the dump. Is there an option in Enterprise manager to disable
> append or will I have to do it the hard way? Thanks.
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Database dump size

Hi there. Running a Sql 2000 installation with latest service pack on
Windows 2000 server. My database dump to disk backup keeps increasing
by about 1 meg per day. The database itself is increasing by a small
percentage of that. In fact the dump is now about the same size as the
database. What's going on? Thanks.Hello
Can you please give us details of the type of backup your
doing as well as your recovery model.
J
>--Original Message--
>Hi there. Running a Sql 2000 installation with latest
service pack on
>Windows 2000 server. My database dump to disk backup
keeps increasing
>by about 1 meg per day. The database itself is increasing
by a small
>percentage of that. In fact the dump is now about the
same size as the
>database. What's going on? Thanks.
>.
>|||What kind of backup are you doing? If you don't use the INIT option the
current backup will append to the existing ones in the dump device.
--
Andrew J. Kelly
SQL Server MVP
"cscott" <christopher@.uncommonlaw.com> wrote in message
news:a94f8da8.0406070533.6a59d6fb@.posting.google.com...
> Hi there. Running a Sql 2000 installation with latest service pack on
> Windows 2000 server. My database dump to disk backup keeps increasing
> by about 1 meg per day. The database itself is increasing by a small
> percentage of that. In fact the dump is now about the same size as the
> database. What's going on? Thanks.|||Hi ,
To add on to Andrew post, Execute the below comand from query analyzer to
check if BACKUP is executed in append mode.
restore headeronly from disk='Directory\dbname.BAK'
If this list more than 1 entry (Check the postion column in output), means
you have taken the backup in Append mode. To remove he append mode add an
OPTION
INIT along with backup database command
BACKUP DATABASE dbname to disk='c:\dbname.bak' with INIT
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uYtrikJTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> What kind of backup are you doing? If you don't use the INIT option the
> current backup will append to the existing ones in the dump device.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "cscott" <christopher@.uncommonlaw.com> wrote in message
> news:a94f8da8.0406070533.6a59d6fb@.posting.google.com...
> > Hi there. Running a Sql 2000 installation with latest service pack on
> > Windows 2000 server. My database dump to disk backup keeps increasing
> > by about 1 meg per day. The database itself is increasing by a small
> > percentage of that. In fact the dump is now about the same size as the
> > database. What's going on? Thanks.
>

Database disk partitions

I'm looking at a new, freshly configured clustered server ('a' node) and
examining the partitions with DISKPART. I don't understand what I'm seeing,
nor do I know if it is best.
First (from chkdsk) all volumes have a 4 KB AUs (blocks) and I believe that
the volumes the .*df files go on should have 64 KB AU's.
DISKPART however, is showing mostly a 32 KB offset on all drives, except for
disk 4, which is showing (none of these are DB drives):
Part # Type Size Offset
Partition 1 OEM 31 MB 32 KB
Partition 2 Dynamic (C:) 20 GB 31 MB
Partition 3 Dynamic (E:) 48 GB 20 GB
Questions:
Is 32 KB the default offset?
Is a 32 KB offset OK when using a 64 KB AU?
What's up with the 31 MB and 20 GB offsets?
In writing this post, I'm remembering (vaguely) that the offset is how far
into the disk to go before starting the partition. If so, I fail to see how
defining a 64 KB offset benefits the database server.
Thanks,
JayThese might be of interest in this area.
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
http://sqlblog.com/blogs/linchi_shea/archive/2007/02/01/performance-impact-of-disk-misalignment.aspx
http://sqlblogcasts.com/blogs/christian/archive/2007/10/09/disk-sector-alignment.aspx
http://www.emc.com/techlib/pdf/H2610_ref_arch_emc_sol_for_ms_wp_ldv.pdf
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospan@.nospam.org> wrote in message
news:uTb5RTxGIHA.5328@.TK2MSFTNGP05.phx.gbl...
> I'm looking at a new, freshly configured clustered server ('a' node) and
> examining the partitions with DISKPART. I don't understand what I'm
> seeing,
> nor do I know if it is best.
> First (from chkdsk) all volumes have a 4 KB AUs (blocks) and I believe
> that
> the volumes the .*df files go on should have 64 KB AU's.
> DISKPART however, is showing mostly a 32 KB offset on all drives, except
> for
> disk 4, which is showing (none of these are DB drives):
> Part # Type Size Offset
> Partition 1 OEM 31 MB 32 KB
> Partition 2 Dynamic (C:) 20 GB 31 MB
> Partition 3 Dynamic (E:) 48 GB 20 GB
>
> Questions:
> Is 32 KB the default offset?
> Is a 32 KB offset OK when using a 64 KB AU?
> What's up with the 31 MB and 20 GB offsets?
> In writing this post, I'm remembering (vaguely) that the offset is how far
> into the disk to go before starting the partition. If so, I fail to see
> how
> defining a 64 KB offset benefits the database server.
> Thanks,
> Jay
>
>|||Thanks Andrew, good reading.
I see my confusion on the offset. I was actually looking for the allignment.
Where can I find what the alignment a disk partition, or volume was created
with?
Where is the alignment specified? Is it in the creation of the partition, or
the creation of the disk colume?
Thanks,
jay
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eFtywJyGIHA.5544@.TK2MSFTNGP02.phx.gbl...
> These might be of interest in this area.
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
> http://sqlblog.com/blogs/linchi_shea/archive/2007/02/01/performance-impact-of-disk-misalignment.aspx
> http://sqlblogcasts.com/blogs/christian/archive/2007/10/09/disk-sector-alignment.aspx
> http://www.emc.com/techlib/pdf/H2610_ref_arch_emc_sol_for_ms_wp_ldv.pdf
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uTb5RTxGIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> I'm looking at a new, freshly configured clustered server ('a' node) and
>> examining the partitions with DISKPART. I don't understand what I'm
>> seeing,
>> nor do I know if it is best.
>> First (from chkdsk) all volumes have a 4 KB AUs (blocks) and I believe
>> that
>> the volumes the .*df files go on should have 64 KB AU's.
>> DISKPART however, is showing mostly a 32 KB offset on all drives, except
>> for
>> disk 4, which is showing (none of these are DB drives):
>> Part # Type Size Offset
>> Partition 1 OEM 31 MB 32 KB
>> Partition 2 Dynamic (C:) 20 GB 31 MB
>> Partition 3 Dynamic (E:) 48 GB 20 GB
>>
>> Questions:
>> Is 32 KB the default offset?
>> Is a 32 KB offset OK when using a 64 KB AU?
>> What's up with the 31 MB and 20 GB offsets?
>> In writing this post, I'm remembering (vaguely) that the offset is how
>> far
>> into the disk to go before starting the partition. If so, I fail to see
>> how
>> defining a 64 KB offset benefits the database server.
>> Thanks,
>> Jay
>>
>|||Hi Jay,
Alignment is only relevant to the first partition and what you're
seeing is the default 32KB alignment for Windows 2003 and prior
versions shown as the "offset" from the start of the disk. This has
nothing to do with the recommendation for 64KB AU's when you format
the partition. The alignment value should be your RAID stripe/cache
size so its only relevant to disks configure in RAID. Vista and
Longhorn automatically align to 1024KB for partitions > 4GB so you
might just want to do that if you can't get a definitive
recommendation from your storage vendor.
Regards,
Christian Bolton
http://coeo.com
http://sqlblogcasts.com/blogs/christian
On Oct 30, 11:10 pm, "Jay" <nos...@.nospam.org> wrote:
> Thanks Andrew, good reading.
> I see my confusion on the offset. I was actually looking for the allignment.
> Where can I find what the alignment a disk partition, or volume was created
> with?
> Where is the alignment specified? Is it in the creation of the partition, or
> the creation of the disk colume?
> Thanks,
> jay
> "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com> wrote in messagenews:eFtywJyGIHA.5544@.TK2MSFTNGP02.phx.gbl...
>
> > These might be of interest in this area.
> >http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliob...
> >http://sqlblog.com/blogs/linchi_shea/archive/2007/02/01/performance-i...
> >http://sqlblogcasts.com/blogs/christian/archive/2007/10/09/disk-secto...
> >http://www.emc.com/techlib/pdf/H2610_ref_arch_emc_sol_for_ms_wp_ldv.pdf
> > --
> > Andrew J. Kelly SQL MVP
> > Solid Quality Mentors
> > "Jay" <nos...@.nospam.org> wrote in message
> >news:uTb5RTxGIHA.5328@.TK2MSFTNGP05.phx.gbl...
> >> I'm looking at a new, freshly configured clustered server ('a' node) and
> >> examining the partitions with DISKPART. I don't understand what I'm
> >> seeing,
> >> nor do I know if it is best.
> >> First (from chkdsk) all volumes have a 4 KB AUs (blocks) and I believe
> >> that
> >> the volumes the .*df files go on should have 64 KB AU's.
> >> DISKPART however, is showing mostly a 32 KB offset on all drives, except
> >> for
> >> disk 4, which is showing (none of these are DB drives):
> >> Part # Type Size Offset
> >> Partition 1 OEM 31 MB 32 KB
> >> Partition 2 Dynamic (C:) 20 GB 31 MB
> >> Partition 3 Dynamic (E:) 48 GB 20 GB
> >> Questions:
> >> Is 32 KB the default offset?
> >> Is a 32 KB offset OK when using a 64 KB AU?
> >> What's up with the 31 MB and 20 GB offsets?
> >> In writing this post, I'm remembering (vaguely) that the offset is how
> >> far
> >> into the disk to go before starting the partition. If so, I fail to see
> >> how
> >> defining a 64 KB offset benefits the database server.
> >> Thanks,
> >> Jay- Hide quoted text -
> - Show quoted text -

Database corrupted?

Hi all,

Had a disk problem here, and all apps connected to our database hung. Nothing was responding, rebooted, and mssqlserver hangs on startup.

If i move the mdf and ldf files of the database in question to another folder, mssqlserver starts up ok, with the db marked as suspect.

It would seem the the mdf and ldf are corrupted.

I am restoring a backup from yesterday, but this will take hours.

Any suggestions trying to recover it as it sits?

Thanks in advance for any ideas.I had the same problem just last week. The only fix was to restore from last good backup and bring the db forward as far as you can with transaction logs.|||Odds are near certain that you aren't hung, it is just doing a truly massive recovery operation when the server restarts. The only answer I know is to let it recover, then you can go merrily on your way with your data intact.

-PatP|||Let it recover, it that complete, and run a dbcc checkdb to verify.|||Odds are near certain that you aren't hung, it is just doing a truly massive recovery operation when the server restarts. The only answer I know is to let it recover, then you can go merrily on your way with your data intact.

-PatP

The restore completed....now its time for the post-mortem.

The problem I was having was that I could not access the database using Query Analyzer, any application software, or Enterprise Manager. Any attempt to connect would result in a time-out, or just hang when trying to connect.

If the DB is not responding, how can one determine if a recovery operation is in progress?|||I use sp_who. Check for a low numbered (usually 10) spid doing a recovery in the database in question.

-PatP|||In answer to your next question "How do I run sp_who when I can't connect through Query Analyzer?", the answer is to try the OSQL command line utility, which sometimes works when all others fail.|||Good anticipation blindman :)

Thanks for the suggestions. I will file this information away for future reference.