tag:blogger.com,1999:blog-44056377526621450052024-02-20T02:59:13.726-08:00DBtweetsDbtweethttp://www.blogger.com/profile/04064047065619121799noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-4405637752662145005.post-85646117021704474322012-02-25T04:09:00.000-08:002012-02-25T04:17:22.734-08:00Recover a database out from Suspect mode<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<h1>
</h1>
<h1>
</h1>
<h1>
<span style="font-size: 12pt; font-weight: normal;"></span></h1>
<div class="MsoNormal">
<span style="color: red;">What is database suspect mode?</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
Database suspect mode is situation where the database is not
available for use and a worst situation for DBA’s to get the business continue
by bringing the database online as early as possible..</div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
During this “code red” situation don’t ever panic and be
calm, the first step you need to do is to inform your customers about the
outage before they come back to you. This step will be a life saver for you as
you will not be questioned for the time you will spend to bring the database
online. </div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
Then you can refer the SQL-server error logs and find out
the root cause of the error, I recommend to use a filter while viewing the log
and use "Database name" as the filter criteria. In this way you will
only see the logs related to that particular database and then refer to the
latest log/s for the root cause.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: red;">Why database goes into suspect mode?</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
Well there are many causes which will move the database to
suspect mode, in this article we will discuss some of them.</div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify; text-indent: -0.25in;">
1)<span style="font: 7pt "Times New Roman";"> </span>Some
one deleted or misplaced a data/log file when SQL Server was offline and the
database can not start because of missing file.</div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify; text-indent: -0.25in;">
2)<span style="font: 7pt "Times New Roman";"> </span>SQL
Server could not access or place an exclusive lock on the data or log file
while coming online. Typically this may happen when SQL-sever is shared with
some other tools like antivirus etc..</div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify; text-indent: -0.25in;">
3)<span style="font: 7pt "Times New Roman";"> </span>And
the worst one is when the transactions have been corrupted. This is most common
in OLTP databases. The root cause of this issue is most likely SQL server
abruptly went down/restarted in the middle of a transaction and while coming
back, SQL server could not complete (commit/rollback) the transaction.</div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify; text-indent: -0.25in;">
4)<span style="font: 7pt "Times New Roman";"> </span>If
the data file corrupted due to Hardware or OS level failure.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: red;">How to come out of this situation?</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-align: justify;">
Firs you need to check which of your database have gone into
suspect mode, for this you can see in the object Explorer or you can query the
below scripts.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Segoe UI"; font-size: 10pt;">USE master<br />
GO<br />
<br />
SELECT</span><span style="font-family: "Segoe UI"; font-size: 10pt;"> <span style="color: blue;">NAME</span><span style="color: grey;">,</span>STATE_DESC
<span style="color: blue;">FROM</span> <span style="color: green;">SYS.DATABASES</span>
<br />
<span style="color: blue;">WHERE</span> STATE_DESC<span style="color: grey;">=</span><span style="color: red;">'SUSPECT'<br />
</span><span style="color: blue;">GO</span></span></div>
<div class="MsoNormal" style="text-align: justify;">
After this you need check the sql server error logs for the
exact reason why this has happen.</div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify; text-indent: -0.25in;">
1)<span style="font: 7pt "Times New Roman";"> </span>If
the reason was due to some deleted or misplaced data or log file the you can
simply place the missing file in proper location, the exact location you can
get in the sql-server error logs. </div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify; text-indent: -0.25in;">
2)<span style="font: 7pt "Times New Roman";"> </span>If
the reason was due to locks on data or log file while the database is coming
online, then you can use the process explorer and kill the file handler which
has placed the locks on the file. You may require the help of your system admin
for this. </div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify; text-indent: -0.25in;">
3)<span style="font: 7pt "Times New Roman";"> </span>If
the reason was due to corrupted transactions on abrupt restart of server, then
think for a while before doing any step because this it will result in data
loss .The best way to come out of this is to restore form latest backup
available. This also saves your time and downtime. </div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify;">
In the worst case if you don’t have
the option to restore then you can call Microsoft for help or you can execute
the below steps but be cautious this steps are extremely dangerous to execute
and may lead to data loss. Before executing these you may take the help of
Microsoft support help</div>
<div class="MsoNormal" style="margin-left: .5in;">
<br /></div>
<div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">EXEC</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: maroon;">sp_resetstatus</span><span style="color: blue;"> </span><span style="color: red;">'yourDBname'</span><span style="color: grey;">;</span></span></div>
<div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">ALTER</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">DATABASE</span> <span style="color: teal;">yourDBname</span> <span style="color: blue;">SET</span> <span style="color: blue;">EMERGENCY</span></span></div>
<div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">DBCC</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: teal;">checkdb</span><span style="color: grey;">(</span><span style="color: red;">'yourDBname'</span><span style="color: grey;">)</span></span></div>
<div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">ALTER</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">DATABASE</span> <span style="color: teal;">yourDBname</span> <span style="color: blue;">SET</span> <span style="color: blue;">SINGLE_USER</span> <span style="color: blue;">WITH</span> <span style="color: blue;">ROLLBACK</span> <span style="color: blue;">IMMEDIATE</span></span></div>
<div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">DBCC</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: teal;">CheckDB</span><span style="color: blue;"> </span><span style="color: grey;">(</span><span style="color: red;">'yourDBname'</span><span style="color: grey;">,</span> <span style="color: teal;">REPAIR_ALLOW_DATA_LOSS</span><span style="color: grey;">)</span></span></div>
<div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">ALTER</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">DATABASE</span> <span style="color: teal;">yourDBname</span> <span style="color: blue;">SET</span> <span style="color: blue;">MULTI_USER</span></span></div>
<div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;"> </span></div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify;">
Please note that as stated above
this is an extremely dangerous command to execute. It is a one- way command
(that is you can not rollback back this execution once you started it) which
can cause loss in data or database integrity. Technically, by executing this
command you are actually authorizing SQL Server to force transactional recovery
to run and skip the errors. If it faces errors, this operation scavenges as
much out of the transaction log as it can and then rebuilds the transaction log
from scratch. So taking this step is really the last resort you should try
after every other attempts fails.</div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify;">
<br /></div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify; text-indent: -0.25in;">
4)<span style="font: 7pt "Times New Roman";"> </span>Lastly
if the problem is due to Hardware or OS failure then you need to restore from the latest backups. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: red;">Summarizing </span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify; text-indent: -0.25in;">
1)<span style="font: 7pt "Times New Roman";"> </span>Always
ensure that you have good backup strategy </div>
<div class="MsoNormal" style="text-align: justify;">
<br /></div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify; text-indent: -0.25in;">
2)<span style="font: 7pt "Times New Roman";"> </span>Do
not detach the database while the database is in suspect mode.</div>
<div class="MsoNormal" style="margin-left: 0.5in; text-align: justify; text-indent: -0.25in;">
3)<span style="font: 7pt "Times New Roman";"> </span>Read
and understand the error logs and do not panic.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
</div>Dbtweethttp://www.blogger.com/profile/04064047065619121799noreply@blogger.com1tag:blogger.com,1999:blog-4405637752662145005.post-1763994128021683472011-10-28T06:11:00.000-07:002011-10-28T06:17:10.215-07:00Myths around Restore operations...<span style="font-weight: bold; color: rgb(153, 0, 0);">Myths around restore operations...</span><br /><br />All of them are <span style="color: rgb(153, 0, 0);">FALSE!</span><br /><br /><span style="color: rgb(153, 0, 0);">a) it is possible to do a point-in-time restore using WITH STOPAT on a full or differential backup</span><br /><br />No. The syntax looks like it allows it, but it's just a syntactical nicety to allow you to do the best practice of using WITH STOPAT on every restore operation in the point-in-time restore sequence so you don't accidentally go past it.<br /><br /><span style="color: rgb(153, 0, 0);">b) it is possible to continue with a restore sequence after having to use WITH CONTINUE_AFTER_ERROR</span><br /><br />No. If a backup is corrupt such that you must use WITH CONTINUE_AFTER_ERROR to restore it, that's restore terminates your restore sequence. If you're restoring a bunch of transaction log backups and one is corrupt, you may want to think carefully on whether you want to force it to restore or not. Forcing a corrupt log backup to restore could mean you've got inconsistent data in the database, or worst case, structural corruption. I'd most likely recommend not restoring it.<br /><br /><span style="color: rgb(153, 0, 0);">c) it is possible to restore different parts of a database to different points-in-time</span><br /><br />No. A portion of the database cannot be brought online unless it is at the same point in time as the primary filegroup. The exception, of course, is a read-only filegroup.<br /><br /><span style="color: rgb(153, 0, 0);">d) it is possible to restore filegroups from different databases together in a new database</span><br /><br />No. All the files in a database have a GUID in the fileheader page. Unless the GUID matches that of data file ID 1 in the database, it cannot be restored as part of the same database.<br /><br /><span style="color: rgb(153, 0, 0);">e) restore removes index fragmentation (or updates statistics, etc)</span><br /><br />No. What you backup is what you get when you restore.<br /><br /><span style="color: rgb(153, 0, 0);">f) it is possible to shrink a database during a restore</span><br /><br />No. This is an often-requested feature in SQL Server - be able to restore a very large, but mostly empty, database on a dev or QA server and have it only be the size of the data in the original database. But you can't.<br /><br /><span style="color: rgb(153, 0, 0);">g) you can restore a database to any downlevel version of SQL Server</span><br /><br />No. This is one of the most pervasive myths. SQL Server cannot understand databases from more recent versions (e.g. SQL Server 2005 cannot understand a SQL Server 2008 database).<br /><br /><span style="color: rgb(153, 0, 0);">h) you can always restore a database to any edition of SQL Server</span><br /><br />No. In SQL Server 2005, if there's an table/index partitioning in the database, it can only be restored on Enterprise (or Enterprise Eval or Developer) Edition. On SQL Server 2008 the list is partitioning, transparent data encryption, change data capture, and data compression.<br /><br /><span style="color: rgb(153, 0, 0);">i) using WITH STANDBY breaks the restore sequence</span><br /><br />No. The WITH STANDBY option allows you to get a read-only transactionally-consistent look at the database in the middle of the restore sequence. As far as the restore sequence is concerned, it's as if you used WITH NORECOVERY. You can stop as many times as you like using WITH STANDBY. This is what log shipping uses when you ask it to allow access to a log-shipping secondary between log backup restores. Beware though, that using WITH STANDBY might cause some seemingly-weird behavior<br /><br /><span style="color: rgb(153, 0, 0);">j) instant file initialization during a restore doesn't work if the database wasn't backed up on a server with instant file initialization enabled</span><br /><br />No. Whether instant file initialization is used is entirely dependent on whether the SQL Server instance performing the restore has it enabled. There is nothing in the backup itself that controls this.<br /><br /><span style="color: rgb(153, 0, 0);">k) restore is the best way to recover from corruption</span><br /><br />No, not necessarily. Depending on what backups you have, restore may be the best way to recover with zero or minimal data loss, but it may be way slower than running a repair and accepting some data loss, or pulling damaged/lost data back from a log shipping secondary. The best way to recover from corruption is the one that allows you to best meet your downtime and data-loss service level agreements.<br /><br /><span style="color: rgb(153, 0, 0);">l) you can take a tail-of-the-log backup after starting a restore sequence</span><br /><br />No. As soon as you start to restore over a database you lose the ability to backup the tail-of-the-log. The very first thing in a disaster recovery plan should always be to check whether a tail-of-the-log backup needs to be taken, just in case.<br /><br /><span style="color: rgb(153, 0, 0);">m) you can always do a point-in-time restore to a time covered by a log backup</span><br /><br />No. If the log backup contains a minimally-logged operation then you cannot stop at a point in time covered by that log backup. You can only restore it in its entirety. This is because a log backup following a minimally-logged operation must include the data extents that were changed by the operation, but there's nothing in the backup that says *when* the extents were changed (that would be the transaction log - that wasn't generated because the operation was minimally logged!).<br /><br /><span style="color: rgb(153, 0, 0);">n) as long as the backup completes successfully, the restore will work too</span><br /><br />No, no, no, no. A backup file is just like a data file - it sits on an I/O subsystem. And what causes most corruptions? I/O subsystems. You must periodically check that your backups are still valid otherwise you could be in for a nasty surprise when disaster strikes. See Importance of validating backups. The other thing to consider is that an out-of-band full or log backup could have been taken that breaks your restore sequence if it's not available.<br /><br /><span style="color: rgb(153, 0, 0);">o) all SQL Server page types can be single-page restored</span><br /><br />No. Various allocation bitmaps and critical metadata pages cannot be single-page restored (or fixed using automatic page repair with database mirroring in SQL Server 2008).<br /><br /><span style="color: rgb(153, 0, 0);">p) using RESTORE ... WITH VERIFYONLY validates the entire backup</span><br /><br />No. Using VERIFYONLY only validates the backup header looks like a backup header. It's only when you take the backup using WITH CHECKSUM and do RESTORE ... WITH VERIFYONLY *and* using WITH CHECKSUM that the restore does more extensive checks, including the checksum over the entire backup.<br /><br /><span style="color: rgb(153, 0, 0);">q) it is possible to restore a backup of an enrypted database without first having restored the server certificate</span><br /><br />No. That's the whole point of transparent data encryption. Lose the server certificate, lose the database.<br /><br /><span style="color: rgb(153, 0, 0);">r) a restore operation performs all REDO and UNDO operations when the restore sequence is completed</span><br /><br />No. The REDO portion of recovery is performed for each restore operation in the restore sequence. The UNDO portion is not done until the restore sequence is completed.<br /><br /><span style="color: rgb(153, 0, 0);">s) a compressed backup can only be restored using Enterprise Edition in SQL Server 2008</span><br /><br />No. All editions can restore a compressed backup. New in SQL Server 2008 R2, Standard Edition can create a compressed backup as well as Enterprise Edition.<br /><br /><span style="color: rgb(153, 0, 0);">t) the restore of a database from an earlier version of SQL Server can be made to skip the upgrade process</span><br /><br />No. It is not possible to skip any necessary upgrade or recovery during a database restore or attach.<br /><br /><span style="color: rgb(153, 0, 0);">u) a backup taken on a 32-bit instance cannot be restored on a 64-bit instance, and vice-versa</span><br /><br />No. There is not difference in the database format on different CPU architectures.<br /><br /><span style="color: rgb(153, 0, 0);">v) restoring the database is everything the application needs to continue</span><br /><br />No. Just like with a high-availability failover to a database mirror or log shipping secondary, everything in (what I call) the application ecosystem must be there for the application to work. That may include ancillary databases, logins, jobs, stored procedures etc.<br /><br /><span style="color: rgb(153, 0, 0);">w) to restore a damaged file from a multi-file filegroup you must restore the entire filegroup</span><br /><br />No. This used to be the case before SQL Server 2000, but not any more.<br /><br /><span style="color: rgb(153, 0, 0);">x) you can restore a backup to any uplevel version of SQL Server</span><br /><br />No. You can only restore a database from two versions back (i.e. you cannot directly restore a SQL Server 7.0 database to SQL Server 2008).<br /><br /><span style="color: rgb(153, 0, 0);">y) a restore operation will always take the same time as the backup operation</span><br /><br />No. There are a ton of things that can affect restore time - like whether there's a long-running transaction that need to be rolled back, or whether the database files need to be created and zero-initialized. There's no guarantee.<br /><br /><span style="color: rgb(153, 0, 0);">z) you should always drop a database before restoring</span><br /><br />No. If you drop the database then the database files need to be created and zero initalized (or at least the log file does if you have instant file initialization enabled). Also, you should *always* have a copy of the damaged database just in case the restore fails for some reason.<br /><br />This myth is one of the most common and I've come across very few people who know the truth.<br /><br /><span style="color: rgb(153, 0, 0);">a1) after breaking the log backup chain, a full database backup is required to restart it.</span><br /><br /><span style="color: rgb(153, 0, 0);">FALSE</span><br /><br />A normal transaction log backup contains all the transaction log generated since the previous log backup (or since the first ever full backup if it's the first ever log backup for the database). There are various operations that will break the log backup chain - i.e. prevent SQL Server from being able to take another log backup until the chain is restarted. The list of such operations includes:<br /><br /> Switching from the FULL or BULK_LOGGED recovery models into the SIMPLE recovery model<br /> Reverting from a database snapshot<br /> Performing a BACKUP LOG using the WITH NO_LOG or WITH TRUNCATE_ONLY (which you can't do any more in SQL Server 2008 )<br /> <br /><br />Here's an example script that shows you what I mean:<br /><br /> CREATE DATABASE LogChainTest;<br /> GO<br /> ALTER DATABASE LogChainTest SET RECOVERY FULL;<br /> GO<br /> BACKUP DATABASE LogChainTest TO DISK = 'C:\SQLskills\LogChainTest.bck' WITH INIT;<br /> GO<br /> BACKUP LOG LogChainTest TO DISK = 'C:\SQLskills\LogChainTest_log1.bck' WITH INIT;<br /> GO<br /> ALTER DATABASE LogChainTest SET RECOVERY SIMPLE;<br /> GO<br /> ALTER DATABASE LogChainTest SET RECOVERY FULL;<br /> GO<br /><br /> Processed 152 pages for database 'LogChainTest', file 'LogChainTest' on file 1.<br /> Processed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.<br /> BACKUP DATABASE successfully processed 153 pages in 0.088 seconds (14.242 MB/sec).<br /> Processed 2 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.<br /> BACKUP LOG successfully processed 2 pages in 0.033 seconds (0.341 MB/sec).<br /><br />I created a database, put it into the FULL recovery model, started the log backup chain, and then momentarily bounced it into the SIMPLE recovery model and back to FULL.<br /><br />Now if I try to take a log backup:<br /><br /> BACKUP LOG LogChainTest TO DISK = 'C:\SQLskills\LogChainTest_log2.bck' WITH INIT;<br /> GO<br /><br /> Msg 4214, Level 16, State 1, Line 1<br /> BACKUP LOG cannot be performed because there is no current database backup.<br /> Msg 3013, Level 16, State 1, Line 1<br /> BACKUP LOG is terminating abnormally.<br /><br />SQL Server knows that I performed an operation which means the next log backup will NOT contain all the log generated since the previous log backup, so it doesn't let me do it.<br /><br />The myth says that a full database backup is required to restart the log backup chain. In reality, all I need is a data backup that bridges the LSN gap. A differential backup will do:<br /><br /> BACKUP DATABASE LogChainTest TO DISK = 'C:\SQLskills\LogChainTest_Diff1.bck' WITH INIT, DIFFERENTIAL;<br /> GO<br /> BACKUP LOG LogChainTest TO DISK = 'C:\SQLskills\LogChainTest_log2.bck' WITH INIT;<br /> GO<br /><br /> Processed 40 pages for database 'LogChainTest', file 'LogChainTest' on file 1.<br /> Processed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.<br /> BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.083 seconds (4.040 MB/sec).<br /> Processed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.<br /> BACKUP LOG successfully processed 1 pages in 0.010 seconds (0.768 MB/sec).<br /><br />This is really cool because you don't need to take a (potentially very large) full database backup to be able to continue with regular log backups.<br /><br />If you have a backup strategy that involves file or filegroup backups as well as database backups, you can even restart the log backup chain after a single file differential backup! Take note, however, that to be able to restore that database, you'd need to have a data backup of each portion of it that bridges the LSN gap (i.e. a file or filegroup full or differential backup) but that's more complicated than I want to go into in this post.<br /><br /><br />Note:-This article I have took from other site for the purpose of spreading the knowledge.<br />You can download full pdf version of this article here:<span style="color: rgb(51, 102, 255);">http://www.sqlskills.com/blogs/paul/commonsqlservermyths.pdf</span>Dbtweethttp://www.blogger.com/profile/04064047065619121799noreply@blogger.com1tag:blogger.com,1999:blog-4405637752662145005.post-11577941875351281132011-10-22T03:21:00.000-07:002011-10-22T04:09:06.011-07:00Sql Server Architecture<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikLovaD5bHDTNLgpm2Nua5xv3kIa1TiS-gkQj68PtcA9jiuYuPGsAyZgEFBKIDSxthEDGH6_MDO7ixSYUMr5af_-CmIQHYRQv6AeF0TEZMtoflNhC0DYDnx9DdN9je_exddBcCRK0xtxR8/s1600/SqlServerArchiture.png"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 251px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikLovaD5bHDTNLgpm2Nua5xv3kIa1TiS-gkQj68PtcA9jiuYuPGsAyZgEFBKIDSxthEDGH6_MDO7ixSYUMr5af_-CmIQHYRQv6AeF0TEZMtoflNhC0DYDnx9DdN9je_exddBcCRK0xtxR8/s320/SqlServerArchiture.png" alt="" id="BLOGGER_PHOTO_ID_5666260539194034770" border="0" /></a><span style="font-family: arial;font-family:arial;font-size:100%;" ><br /></span><!--[if gte mso 9]><xml> <w:worddocument> <w:view>Normal</w:View> <w:zoom>0</w:Zoom> <w:punctuationkerning/> <w:validateagainstschemas/> <w:saveifxmlinvalid>false</w:SaveIfXMLInvalid> <w:ignoremixedcontent>false</w:IgnoreMixedContent> <w:alwaysshowplaceholdertext>false</w:AlwaysShowPlaceholderText> <w:compatibility> <w:breakwrappedtables/> <w:snaptogridincell/> <w:wraptextwithpunct/> <w:useasianbreakrules/> <w:dontgrowautofit/> </w:Compatibility> <w:browserlevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:latentstyles deflockedstate="false" latentstylecount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style="font-size:100%;"><b><u><span style="Lucida Console";mso-bidi-">Database Physical Structure & Internals:</span></u></b></span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">Database:</span></b></span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" ><a href="http://en.wikipedia.org/wiki/Database"><span style="text-decoration:none; text-underline:none">A Database</span></a> is a collection of data that is stored (or) organized. We can manage and access data easily. When we create a new database, by default two files will be created physically.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >We can create more than one log files and data files (NDF).</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><ul style="font-family: arial;font-family:times new roman;" type="disc"><li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; text-align:justify;mso-list:l0 level1 lfo1;tab-stops:list .5in"><span style="Lucida Console";mso-bidi-font-size:100%;" >Data file(MDF) </span></li><li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; text-align:justify;mso-list:l0 level1 lfo1;tab-stops:list .5in"><span style="Lucida Console";mso-bidi-font-size:100%;" >Log file(LDF) </span></li></ul><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > </span><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">Data File:</span></b></span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >All the data will be stored in a data file. Internally the data’s are stored in data pages. A database file has unique FileID and PageID. Log files do not have pages. They have series of log records. I will cover the Log file section in my next article.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > </span><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">Data Page:</span></b></span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Data page is a fundamental unit of data storage. Each data page has 8KB size (128 pages per MB).Each page has 96 byte header system information. All the data rows are stored in data page. Expect BLOB (text, ntext, image, xml, varchar (max), nvarchar & varbinary)</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > </span><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">Extents:</span></b></span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > Extents are fundamental units in which space is allocated to table (or) an index. Extents are 8 contiguous pages 64KB size. (16 extents per MB).</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > Two Types of extents</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><ul style="font-family: arial;font-family:times new roman;" type="disc"><li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; text-align:justify;mso-list:l1 level1 lfo2;tab-stops:list .5in"><span style="Lucida Console";mso-bidi-font-size:100%;" >Uniform/Dedicated extents</span></li><li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; text-align:justify;mso-list:l1 level1 lfo2;tab-stops:list .5in"><span style="Lucida Console";mso-bidi-font-size:100%;" >Mixed extents </span></li></ul><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > </span><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">Uniform Extents:</span></b></span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > Uniform extents are owned by a single object. All the 8-pages can be used for owning object.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > </span><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">Mixed Extents:</span></b></span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > When we create a new table (or) an index by default pages are allocated to mixed extents first.When the data grow and its reach 8 pages then its switched to uniform extents.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > How the Data Storage Size Internally For Page & Extents: </span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > </span></p><span style="font-family: arial;font-family:lucida grande;" > </span><table class="MsoNormalTable" style="width: 320.8pt; border: 1pt outset black; font-family: arial;" border="1" cellpadding="0" cellspacing="0" width="428"> <colgroup><col width="184"><col width="185"><col width="184"></colgroup> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:9.75pt"> <td style="width:138.0pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:9.75pt" valign="top" width="184"> <p class="MsoNormal" style="text-align:justify;line-height:9.75pt"><span style="font-size:100%;"><b><span style="Lucida Console";mso-bidi-">Storage Size</span></b></span></p> </td> <td style="width:138.75pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:9.75pt" valign="top" width="185"> <p class="MsoNormal" style="text-align:justify;line-height:9.75pt"><span style="font-size:100%;"><b><span style="Lucida Console";mso-bidi-">Page</span></b></span></p> </td> <td style="width:138.0pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:9.75pt" valign="top" width="184"> <p class="MsoNormal" style="text-align:justify;line-height:9.75pt"><span style="font-size:100%;"><b><span style="Lucida Console";mso-bidi-">Extents</span></b></span></p> </td> </tr> <tr style="mso-yfti-irow:1;height:11.25pt"> <td style="width:138.0pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:11.25pt" valign="top" width="184"> <p class="MsoNormal" style="text-align:justify;line-height:11.25pt"><span style="font-size:100%;"><b><span style="Lucida Console";mso-bidi-">Size per KB</span></b></span></p> </td> <td style="width:138.75pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:11.25pt" valign="top" width="185"> <p class="MsoNormal" style="text-align:justify;line-height:11.25pt"><span style="Lucida Console";mso-bidi-font-size:100%;" >8KB(1-Page)</span></p> </td> <td style="width:138.0pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:11.25pt" valign="top" width="184"> <p class="MsoNormal" style="text-align:justify;line-height:11.25pt"><span style="Lucida Console";mso-bidi-font-size:100%;" >64KB (8-Pages)</span></p> </td> </tr> <tr style="mso-yfti-irow:2;mso-yfti-lastrow:yes;height:9.75pt"> <td style="width:138.0pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:9.75pt" valign="top" width="184"> <p class="MsoNormal" style="text-align:justify;line-height:9.75pt"><span style="font-size:100%;"><b><span style="Lucida Console";mso-bidi-">Size per MB</span></b></span></p> </td> <td style="width:138.75pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:9.75pt" valign="top" width="185"> <p class="MsoNormal" style="text-align:justify;line-height:9.75pt"><span style="Lucida Console";mso-bidi-font-size:100%;" >128 Pages</span></p> </td> <td style="width:138.0pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:9.75pt" valign="top" width="184"> <p class="MsoNormal" style="text-align:justify;line-height:9.75pt"><span style="Lucida Console";mso-bidi-font-size:100%;" >16 Extents</span></p> </td> </tr> </tbody></table><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" ><br /></span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">IAM:</span></b></span><span style="Lucida Console";mso-bidi-font-size:100%;" > <b>(</b>Index Allocation Map<b>)</b></span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >IAM page tracks with which extents within the specified GAM interval belongs to a single entity (or) file. A Database file have multiple files some of the DB size more than 4 GB</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > IAM interval size is 64000 extents i.e. 4GB</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > </span><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">GAM: (</span></b></span><span style=" Lucida Console";font-size:100%;" >Global Allocation Map<b>)</b><span style="font-weight: bold;"><br /></span></span></p><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Data files split up into GAM intervals (Conceptually)</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Bit -->1 Free</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >An extent is available for allocation.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Bit --> 0 Allocated</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >An extent is already allocated.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > GAM interval size is 64000 extents i.e. 4GB</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > </span><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">SGAM: (</span></b></span><span style="Lucida Console";mso-bidi-font-size:100%;" >Shared<b> </b>Global Allocation Map<b>)</b></span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >SGAM bitmaps is exactly same as GAM.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > Bit --> 1 mixed extent one (or) more free page available</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >An extent is mixed one (or) more unallocated page available.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Bit --> 0 Either Uniform/Mixed extent No unallocated pages</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >An extent is either uniform (or) mixed all are already allocated.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > SGAM interval size is 64000 extents i.e. 4GB</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" ><br /><b>PFS: (</b>Page Free Space<b>)</b></span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >PFS has tracks with which pages have free space.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > A PFS page is the first page after the file header page in a data file (with page number 1).<br />Next comes a GAM (with page number 2) followed by an SGAM (page 3).</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >PFS interval size is 8088 pages i.e. 64MB</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > PFS does not have bitmap only have byte map.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" ><b>DCM: (</b>Differential Changed Map<b>)</b></span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >DCM tracks extents that have changed since last BACKUP DATABASE statement.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Bit --> 1 extent modified</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Extent has been modified since the last BACKUP DATABASE.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Bit --> 0 extent is not modified</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Extent has not been modified since the last BACKUP DATABASE.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">BCM: (</span></b></span><span style="Lucida Console";mso-bidi-font-size:100%;" >Bulk Changed Map<b>)</b></span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >BCM tracks extents that have been modified by bulk operations since last BACKUP LOG statement.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Bit --> 1 extent modified</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Extent has been modified by bulk logged operation after the last BACKUP LOG statement.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Bit --> 0 extent is not modified</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Extent has not been modified by bulk logged operations .Although the BCM pages appear by default in all databases.</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >How the Data Stored/Allocated Internally for the entity:</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >(Allocation Maps)</span></p><span style="font-family: arial;font-family:lucida grande;" > </span><p class="MsoNormal" style="text-align: justify; font-family: arial;font-family:times new roman;"><span style=" Lucida Console";mso-bidi-font-size:100%;" > </span></p><span style="font-family: arial;font-family:lucida grande;" > </span><table class="MsoNormalTable" style="width:320.8pt;mso-cellspacing:0in;border:outset black 1.0pt;mso-border-alt: outset black .75pt;mso-padding-alt:5.25pt 5.25pt 5.25pt 5.25pt" border="1" cellpadding="0" cellspacing="0" width="428"> <colgroup style="font-family: arial;"><col width="104"><col width="105"><col width="105"><col width="105"><col width="105"></colgroup> <tbody><tr style="height: 18.75pt; font-family: arial;font-family:times new roman;"> <td style="width:78.0pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:18.75pt" valign="top" width="104"> <p class="MsoNormal" style="text-align:justify"><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">Type of storage</span></b></span></p> <p class="MsoNormal" style="text-align:justify"><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">(Bit Type)</span></b></span></p> </td> <td style="width:78.75pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:18.75pt" valign="top" width="105"> <p class="MsoNormal" style="text-align:justify"><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">GAM</span></b></span></p> </td> <td style="width:78.75pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:18.75pt" valign="top" width="105"> <p class="MsoNormal" style="text-align:justify"><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">SGAM</span></b></span></p> </td> <td style="width:78.75pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:18.75pt" valign="top" width="105"> <p class="MsoNormal" style="text-align:justify"><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">DCM</span></b></span></p> </td> <td style="width:78.75pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:18.75pt" valign="top" width="105"> <p class="MsoNormal" style="text-align:justify"><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">BCM</span></b></span></p> </td> </tr> <tr style="height: 19.5pt; font-family: arial;font-family:times new roman;"> <td style="width:78.0pt;border:inset black 1.0pt; mso-border-alt:inset black .75pt;padding:5.25pt 5.25pt 5.25pt 5.25pt; height:19.5pt" valign="top" width="104"> <p class="MsoNormal" style="text-align:justify"><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">Bit 1</span></b></span></p> </td> <td style="width: 78.75pt; border: 1pt inset black; padding: 5.25pt; height: 19.5pt; font-family:lucida grande;" valign="top" width="105"> <p class="MsoNormal" style="text-align:justify"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Extent Free</span></p> </td> <td style="width: 78.75pt; border: 1pt inset black; padding: 5.25pt; height: 19.5pt; font-family:lucida grande;" valign="top" width="105"> <p class="MsoNormal" style="text-align:justify"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Mixed Extent at least one free pages available </span></p> </td> <td style="width: 78.75pt; border: 1pt inset black; padding: 5.25pt; height: 19.5pt; font-family:lucida grande;" valign="top" width="105"> <p class="MsoNormal" style="text-align:justify"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Extent modified</span></p> </td> <td style="width: 78.75pt; border: 1pt inset black; padding: 5.25pt; height: 19.5pt; font-family:lucida grande;" valign="top" width="105"> <p class="MsoNormal" style="text-align:justify"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Extent modified</span></p> </td> </tr> <tr style="mso-yfti-irow:2;mso-yfti-lastrow:yes;height:20.25pt"> <td style="width: 78pt; border: 1pt inset black; padding: 5.25pt; height: 20.25pt; font-family: arial;font-family:times new roman;" valign="top" width="104"> <p class="MsoNormal" style="text-align:justify"><span style="font-size:100%;"><b><span style=" Lucida Console";mso-bidi-">Bit 0</span></b></span></p> </td> <td style="width: 78.75pt; border: 1pt inset black; padding: 5.25pt; height: 20.25pt; font-family: arial;font-family:times new roman;" valign="top" width="105"> <p class="MsoNormal" style="text-align:justify"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Extent Allocated</span></p> </td> <td style="width: 78.75pt; border: 1pt inset black; padding: 5.25pt; height: 20.25pt; font-family: arial;font-family:times new roman;" valign="top" width="105"> <p class="MsoNormal" style="text-align:justify"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Either Uniform (or) Mixed No free space</span></p> </td> <td style="width: 78.75pt; border: 1pt inset black; padding: 5.25pt; height: 20.25pt; font-family: arial;font-family:arial;" valign="top" width="105"> <p class="MsoNormal" style="text-align:justify"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Extent not modified</span></p> </td> <td style="width: 78.75pt; border: 1pt inset black; padding: 5.25pt; height: 20.25pt; font-family:lucida grande;" valign="top" width="105"> <p class="MsoNormal" style="text-align: justify; font-family: arial;"><span style=" Lucida Console";mso-bidi-font-size:100%;" >Extent not modified</span></p> <p class="MsoNormal" style="margin-bottom:12.0pt;text-align:justify"><span style="Lucida Console";mso-bidi-font-family:";font-size:8.0pt;" ><span style="font-family: arial;font-size:100%;" ><br /></span><br /></span></p> </td> </tr> </tbody></table> <p class="MsoNormal" style="text-align:justify"><span style=" font-family:"Lucida Console";mso-bidi-font-family:Arial;font-size:8.0pt;" > </span></p> <p class="MsoNormal" style="text-align:justify"><span style=" font-family:"Lucida Console";mso-bidi-font-family:Arial;font-size:8.0pt;" > </span></p> <p class="MsoNormal" style="text-align:justify"><span style=" Lucida Console"font-family:";font-size:8.0pt;" > </span></p>Dbtweethttp://www.blogger.com/profile/04064047065619121799noreply@blogger.com0tag:blogger.com,1999:blog-4405637752662145005.post-43428516666685254252011-10-22T03:11:00.000-07:002011-10-22T03:16:37.817-07:00Silent SQL Server 2005 InstallationSilent SQL Server 2005 Installation is possible in two steps.<br /><br />1) Creating an .ini file<br />The SQL Server CD contains a template file called template.ini .<br />Based on that create another required .ini file which includes a single [Options] section containing multiple parameters,<br />each relating to a different feature or configuration setting.<br /><br />2) Run Setup on command prompt<br />On command prompt type following script<br /><br />setup.exe /settings <path to="" ini="" file=""><br /><br />If location of sqlinstall.ini file is at C:\SQLSetup folder.<br />The command to initiate silent installation is:<br /><br />setup.exe /settings C:SQLSetup sqlinstall.ini<br /><br />Specify the /qn switch to perform a silent installation (with no dialog boxes)<br />or the /qb switch to specify that only progress dialog boxes should be displayed.<br /></path>Dbtweethttp://www.blogger.com/profile/04064047065619121799noreply@blogger.com0