MSSQL SERVER ADMINISTRATION TIPS

How to remove the each articles without dropping the entire subscription on Transactional replication

2010-06-02T06:30:00.000-07:00

How to remove the each articles without dropping the entire subscription on Transactional replication:
**************************************************

Step 1:

sp_dropsubscription 'Publication','article','subscribername','subscriptiondb'

Step 2:

DECLARE @publication AS sysname;
DECLARE @article AS sysname;
SET @publication = N'Publication';
SET @article = N'article';

-- Drop the transactional article.
USE Databasename
EXEC sp_droparticle
@publication = @publication,
@article = @article,
@force_invalidate_snapshot = 1;
GO

SQL server Flight Recorder

2009-12-22T10:27:00.000-08:00

Today i learned one new thing in sql server -SQL server Flight Recorder

How to enable sql server blackbox trace
DECLARE @traceid INT;
EXEC sp_trace_create @traceid OUTPUT, @options = 8;
SELECT @traceid AS [Trace Id];
GO

This returns the following:
traceid property value
----------- ----------- ---------------------------------------------------------------------------------
2 1 8
2 2 \\?\C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\blackbox_1.trc
2 3 5
2 4 NULL
2 5 0
The various properties are:
1: the trace options. 8 means its a black-box trace. 2: the filename of the trace file. This isn't configurable for a black-box trace. 3: the maximum size of the file in MB. This also isn't configurable for a black-box trace. 4: the stop time for the trace. NULL means the trace won't stop until its manually turned off. 5: the current trace status. 0 is off, 1 is on.Notice that the trace status is 0, which means that the trace hasn't been started - so we need to start it manually using:
EXEC sp_trace_setstatus @traceid = 2, @status = 1;
GO
Very cool. Next I wanted to see whether this trace would start up again automatically if I bounced the SQL instance I'd defined it on - so I did a net stop mssqlserver and net start mssqlserver. Just for kicks I thought I'd look in the black-box to see what it had logged when I did the net stop mssqlserver. To do that I used the following:
SELECT * FROM fn_trace_gettable (
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\blackbox_1.trc',
DEFAULT);
GO
And then ran the fn_trace_getinfo query again. Nothing. So - if you want this feature to be on all the time, you'll need to write a script that's run at instance startup that defines and enables the trace.
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/06/03/sql-server-s-black-box.aspx

How to Track License Information in SQL 2005

2009-12-01T03:57:00.000-08:00

How to Track License Information in SQL 2005
SQL 2005 no longer tracks licensing (per seat or per processor) via registry entries. SQL 2005 still reads the registry for this information, but the SQL 2005 setup doesn’t put licensing information in the registry during setup as in SQL 2000.
This is by-design. Hence, when ServerProperty(‘LicenseType’) is run on a SQL 2005 installation, ‘DISABLED’ is always returned.
This could be a problem for large companies who would like a programmatic way to track licensing rather than just having paper license tracking (which is the current method in SQL 2005).
Supported Resolution
Since SQL 2005 still queries the registry for licensing information, add the following key and values and ServerProperty(‘LicenseType’) will return license information.
Note: Licensing has always been server wide and not SQL instance specific. This setting would apply to all instances of SQL Server on the server.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\MSSQLLicenseInfo\MSSQL9.00
If you want to configure SQL Server for Per Processor then add these Registry Values under that Key adjusting for the number of processors you have a license for:
Name Type ValueMode REG_DWORD 2 ß LICENSE_MODE_PERPROCConcurrentLimit REG_DWORD 4 ß Number of Processors
If you want to configure SQL Server for Per Seat licensing then add these Registry values under the Key adjusting for the number of seat license you have purchased.
Name Type ValueMode REG_DWORD 0 ß LICENSE_MODE_PERSEATConcurrentLimit REG_DWORD 100 ß No. of client licenses registered for SQL Server in Per Seat mode.
Test in SQL Management Studio
You need to stop and restart SQL Server 2005 before the information will be available to ServerProperty() as the registry is read on start-up of SQL Server. With the above settings you would see the following when you restart SQL Server 2005.
SELECT ServerProperty(’LicenseType’) as LicenseType, ServerProperty(’NumLicenses’) as ProcessorCount
Output:LicenseType ProcessorCount
PER_PROCESSOR 4

SQL Server 2008 can store BLOBs (e.g. Images, Video, Word, Excel, PDF, MP3, etc files) in the NTFS file system rather than in a database file.

2009-11-11T05:23:00.000-08:00

SQL Server 2008 can store BLOBs (e.g. Images, Video, Word, Excel, PDF, MP3, etc files) in the NTFS file system rather than in a database file. This can be achieved by using the new FILESTREAM feature which was introduced in SQL Server
-----------------------------------------------------------------------
How to enable the filestream storage for sql server 2008 database
-----------------------------------------------------------------------

Creating a FILESTREAM Enabled Database

Creating a FILESTREAM enabled database named FileStreamDB by executing the T-SQL code below.
-----------------------------------------------------------------------------------------------

Use Master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'FileStreamDB')
DROP DATABASE FileStreamDB
GO
USE master
GO
CREATE DATABASE [FileStreamDB] ON PRIMARY
( NAME = N'FileStreamDB', FILENAME = N'D:\FileStreamDB\FileStreamDB.mdf',
SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10% )
LOG ON
( NAME = N'FileStreamDB_log', FILENAME = N'D:\FileStreamDB\FileStreamDB_log.ldf' ,
SIZE = 10MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)
GO
ALTER DATABASE [FileStreamDB]
ADD FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM
GO
ALTER DATABASE [FileStreamDB]
ADD FILE (NAME = N'FileStreamDB_FSData', FILENAME = N'E:\FileStreamDB\FileStreamData')
TO FILEGROUP FileStreamGroup
GO

----------------------------------------------------
Creating a table with FILESTREAM columns
----------------------------------------------------

Let us now create the FileStreamDataStorage table by executing the T-SQL code below. This table will be used to store FILESTREAM data:

Use FileStreamDB
GO
IF EXISTS (SELECT name FROM sys.all_objects WHERE name = N'FileStreamDataStorage')
DROP TABLE FileStreamDataStorage
GO
CREATE TABLE [FileStreamDataStorage]
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[FileStreamData] VARBINARY(MAX) FILESTREAM NULL,
[FileStreamDataGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
[DateTime] DATETIME DEFAULT GETDATE()
)
ON [PRIMARY]
FILESTREAM_ON FileStreamGroup
GO

To store a BLOB using FILESTREAM feature, you must have a column of datatype VARBINARY (MAX) along with the FILESTREAM attribute. In addition to this, the table must also have a UNIQUEIDENTIFIER column with the ROWGUIDCOL attribute.

--------------------------------------------------------------------------------

Inserting FILESTREAM Data
--------------------------------------------------------------------------------

Let us now add a row to FileStreamDataStorage table by execute the below mentioned T-SQL code.

Use FileStreamDB
GO
INSERT INTO [FileStreamDataStorage] (FileStreamData)
SELECT * FROM
OPENROWSET(BULK N'C:\SampleFiles\Image1.BMP' ,SINGLE_BLOB) AS Document
GO
INSERT INTO [FileStreamDataStorage] (FileStreamData)
SELECT * FROM
OPENROWSET(BULK N'C:\SampleFiles\Image2.BMP' ,SINGLE_BLOB) AS Document
GO
INSERT INTO [FileStreamDataStorage] (FileStreamData)
SELECT * FROM
OPENROWSET(BULK N'C:\SampleFiles\Image3.BMP' ,SINGLE_BLOB) AS Document
GO
INSERT INTO [FileStreamDataStorage] (FileStreamData)
SELECT * FROM
OPENROWSET(BULK N'C:\SampleFiles\Image4.BMP' ,SINGLE_BLOB) AS Document
GO
/*
Execute the below mentioned TSQL code to retrieve the data from
FileStreamDataStorage table.
*/
USE FileStreamDB
GO
SELECT ID
, CAST([FileStreamData] AS VARCHAR) as [FileStreamData]
, FileStreamDataGUID
, [DateTime]
FROM [FileStreamDataStorage]
GO

Courtesy from mssqltips.com
http://msdn.microsoft.com/en-us/library/cc949109.aspx

http://www.mssqltips.com/tip.asp?tip=1878&home

Microsoft Launched " Microsoft® SQL Azure™ Database is a cloud-based relational database service built on SQL Server® technologies"

2009-11-11T04:45:00.000-08:00

Microsoft Launched " Microsoft® SQL Azure™ Database is a cloud-based relational database service built on SQL Server® technologies"

Please refer below links.....
http://www.mssqltips.com/tip.asp?tip=1874&home
http://www.microsoft.com/windowsazure/sqlazure/

Thanks&Regards
Kuraliniyan S
MCTS

Microsoft Released SQL server 2005 service pack 3

2008-12-18T06:18:00.000-08:00

Microsoft Released SQL server 2005 service pack 3

https://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en#Requirements

SQL Server error logs

2008-11-26T06:36:00.000-08:00

The sqlserver bydefault keep 7 error log files.Some time we unable to investigate old issues.For Avoid those kind of issue microsoft provided good solution.

How to archive more than six SQL Server error logs

http://support.microsoft.com/kb/230590/

How to increase the number of SQL Server error logs

http://support.microsoft.com/kb/196909

SQL server 2005 SP3 Beta available....

2008-11-11T21:38:00.000-08:00

SQL server 2005 SP3 Beta available....

http://www.microsoft.com/downloads/details.aspx?FamilyId=D22317E1-BC64-4936-A14B-7A632B50A4CA&displaylang=en

Microsoft Released New Hot fixes for SQL server

2008-10-24T04:01:00.000-07:00

Microsoft Released New Hot fixes for SQL server
----------------------------------------------------------
This security update resolves four privately disclosed vulnerabilities. The more serious of the vulnerabilities could allow an attacker to run code and to take complete control of an affected system. An authenticated attacker could then install programs; view, change, or delete data; or create new accounts with full administrative rights.
http://www.microsoft.com/technet/security/bulletin/MS08-040.mspx

Vulnerability Information:

Memory Page Reuse Vulnerability - CVE-2008-0085
An information disclosure vulnerability exists in the way that SQL Server manages memory page reuse. An attacker with database operator access who successfully exploited this vulnerability could access customer data.
Convert Buffer Overrun - CVE-2008-0086
A vulnerability exists in the convert function in SQL Server that could allow an authenticated attacker to gain elevation of privilege. An attacker who successfully exploited this vulnerability could run code and take complete control of the system.
SQL Server Memory Corruption Vulnerability - CVE-2008-0107
A vulnerability exists in SQL Server that could allow an authenticated attacker to gain elevation of privilege. An attacker who successfully exploited this vulnerability could run code and take complete control of the system.
SQL Server Buffer Overrun Vulnerability - CVE-2008-0106
A vulnerability exists in SQL Server that could allow an authenticated attacker to gain elevation of privilege. An attacker who successfully exploited this vulnerability could run code and take complete control of the system.

SQL Server Agent appears with "Agent XPs disabled"

2008-08-15T02:22:00.004-07:00

Tempdb database was full.Then I restarted the sql service.while checking management studio sql server agent tab shown Agent XPs disabled.

After that I refered below steps from the msdn libraray.

Agent XPs Option
Use the Agent XPs option to enable the SQL Server Agent extended stored procedures on this server. When this option is not enabled, the SQL Server Agent node is not available in SQL Server Management Studio Object Explorer.
When you use the SQL Server Management Studio tool to start the SQL Server Agent service, these extended stored procedures are enabled automatically. For more information, see Understanding Surface Area Configuration.
Note:

Management Studio Object Explorer does not display the contents of the SQL Server Agent node unless these extended stored procedures are enabled regardless of the SQL Server Agent service state.
The possible values are:
• 0, indicating that SQL Server Agent extended stored procedures are not available (the default).
• 1, indicating that SQL Server Agent extended stored procedures are available.
The setting takes effect immediately without a server stop and restart.
Examples
The following example enables the SQL Server Agent extended stored procedures.
Copy Code
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE

Understanding SQL Server's DBCC SHOWCONTIG

2008-08-08T02:22:00.000-07:00

Understanding SQL Server's DBCC SHOWCONTIG

DBCC SHOWCONTIG is a wonderful tool which helps you to understand quite a bit more about your system than is obvious at first glance. And, frankly, the documentation doesn't use terminology that makes it very obvious either. So, this article will focus on a few of the big ideas behind the tool using SQL Server, and how you can use it to better understand what is going on inside your SQL Server box.
Probably one of the most significant performance problems found in databases is centered around table data fragmentation. One situation that may be analogous to table fragmentation might be an index at the end of a large book. A single index entry in such a book might point to several pages scattered throughout the book. You must then scan each page for the specific information you require. This differs significantly from the index of the phone book which stores its data in sorted order. A typical query for the name "Jones" might span multiple consecutive pages, but are always held in a sorted order.

In the case of a database, we start out with the data looking more like a phone book, and end with the data looking more like a history book. Therefore, we need to occasionally resort the data in an effort to recreate the phone book order. Below, you will see a graphical presentation of how SQL Server lays out the data so that we can discuss the actual findings more clearly.

A Quick SQL Server Internals Discussion
We are most familiar with the data row. The row size is set only by the definition of the table that holds it (e.g. A table of addresses require more data per row then a table of class names). In SQL Server, a table may define a row as storing as little as 4 bytes to as much as 8060.This limit is set by the size of the data page, which stores up to 8,192 bytes (8 KB). The remaining 132 bytes are used by SQL Server to track other information under the covers. Although SQL Server is designed around 8 KB pages, the smallest unit of data that SQL Server can allocate is 64 KB. This is called an extent.
To store the data in a sorted order, as in a phone book, SQL Server uses something called a clustered index. When a typical database is created, clustered indexes exist on nearly all tables. However, just because the data exists in sorted order within the page does not mean that it exists as such within an extent. The reason for this derives from situations in which there is no more room on a given page in which it can insert a row. SQL Server then removes approximately half the page and moves it to another page, which is called a Page Split (Page Splits will not occur with clustered indexes on IDENTITY based columns, but hotspotting may). In some cases, it may move that data to another extent altogether, possibly even allocating a new extent to do so. So, while we start off with names beginning with A and ending with H on one page, and names beginning with I and ending with Z on the next page, through usage, we may see that names A through C are now located on one page in one extent, D through E on another extent and S through Z back on the fifth page of the first extent, etc. It is because of the page split that there are times in which we may prefer to use tables with no clustered indexes at all. However, these tables are usually scratch tables which are highly volatile. In those situations, we desire the quicker write times at the cost of slower reads.

Calling DBCC SHOWCONTIG
Using Query Analyzer, connect to the database you wish to view. Next, you will need to get the object id of the table(s) you wish to examine. I have simplified this task to retrieve the top 10 tables by size using the following script.
SELECT TOP 10
'DBCC SHOWCONTIG(' + CAST(id AS NVARCHAR(20)) + ')'
+ CHAR(10) +
'PRINT '' ''' + CHAR(10)
FROM
sysindexes
WHERE
indid = 1 or
indid = 0
ORDER BY rows DESC
Execute this script in the database that you wish to check, and you will get an output resembling (repeated 10 times, once for each of the 10 largest tables):
DBCC SHOWCONTIG(123456789)
PRINT ''
Copy and paste the complete resultset into your query window and execute it.

The Results Explained
The results from the previous command will look something like the following:
DBCC SHOWCONTIG scanning 'MyTable1' table...
Table: 'MyTable1' (1556968673); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 18986
- Extents Scanned..............................: 2443
- Extent Switches..............................: 9238
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 25.70% [2374:9239]
- Logical Scan Fragmentation ..................: 44.58%
- Extent Scan Fragmentation ...................: 87.07%
- Avg. Bytes Free per Page.....................: 1658.7
- Avg. Page Density (full).....................: 79.51%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

DBCC SHOWCONTIG scanning 'MyTable2' table...
Table: 'MyTable2' (183984032); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 28980
- Extents Scanned..............................: 3687
- Extent Switches..............................: 22565
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 16.06% [3623:22566]
- Logical Scan Fragmentation ..................: 83.05%
- Extent Scan Fragmentation ...................: 87.44%
- Avg. Bytes Free per Page.....................: 3151.1
- Avg. Page Density (full).....................: 61.07%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
In the first table, MyTable1, we see that there were 18,986 pages examined to create the report. Those pages existed within 2,443 extents, indicating that the table consumed approximately 97% (7.8 pages per extent on average) of the extents allocated for it. We then see that while examining the pages for fragmentation, the server had to switch extent locations 9, 238 times. The Scan Density restates this by indicating the percentage of all pages within all extents were contiguous. In an ideal environment, the density displayed would be close to 100. The Logical Scan Fragmentation and Extent Scan Fragmentation are indications of how well the indexes are stored within the system when a clustered index is present (and should be ignored for tables that do not have a clustered index). In both cases, a number close to 0 is preferable. There is another anomaly being displayed here that is a little difficult to explain, but it is that SQL Server allows multiple tables to exist within a single extent, which further explains the 7.8 pages per extent (multiple tables may not however exist within a page).
The next items discuss a somewhat more mundane but important issue of page utilization. Again using the first table as the example, there are an average of 1659 bytes free per page, or that each page is 79.51% utilized. The closer that number gets to 100, the faster the database is able to read in records, since more records exist on a single page. However, this must be balanced with the cost of writing to the table. Since a page split will occur if a write is required on a page that is full, the overhead can be tremendous. This is exaggerated when using RAID 5 disk subsystems, since RAID 5 has a considerably slower write time compared to its read time. To account for this, we have the ability of telling SQL Server to leave each page a certain percentage full.
DBCC REINDEX is a related tool that will reorganize your database information in much the same way Norton Defrag will work on your hard drive (see Books Online for information on how to use DBCC REINDEX). The following report displays the differences in the data after we defragmented the data using DBCC DBREINDEX.
DBCC SHOWCONTIG scanning 'MyTable1' table...
Table: 'MyTable1' (1556968673); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 15492
- Extents Scanned..............................: 1945
- Extent Switches..............................: 2363
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 81.94% [1937:2364]
- Logical Scan Fragmentation ..................: 15.43%
- Extent Scan Fragmentation ...................: 20.15%
- Avg. Bytes Free per Page.....................: 159.8
- Avg. Page Density (full).....................: 98.03%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

DBCC SHOWCONTIG scanning 'MyTable2' table...
Table: 'MyTable2' (183984032); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 35270
- Extents Scanned..............................: 4415
- Extent Switches..............................: 4437
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.35% [4409:4438]
- Logical Scan Fragmentation ..................: 0.11%
- Extent Scan Fragmentation ...................: 0.66%
- Avg. Bytes Free per Page.....................: 3940.1
- Avg. Page Density (full).....................: 51.32%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
Here, we can see several key improvements and some examples of how proper indexing can be very important. The most glaring items for us are how well we were able to increase the scan density. Again, using the MyTable1 table as a reference, we can see that out of 1,945 extents, there were only 2363 extent switches. Notice that the number of extent switches is now a lower number than the original number of extents. This is due to the more efficient allocation of the data. And, since there is a significant reduction of the number of extent switches, searches for large quantities of contiguous data will be fulfilled much more quickly.
These reports were taken after only a small amount of processing had occurred on this system, yet already we can see that there has been a fair amount of fragmentation of the data. The table MyTable1 has already begun to show signs of performance degradation. When there is an unusually large amount of new data being inserted into the tables, these numbers will quickly begin to resemble the those that we see in the previous report.
In the table MyTable2, we see a stark difference from MyTable1.This is because of some index tuning that I had done on that table. As I said earlier, SQL Server uses the clustered indexes in order to understand how data should be ordered. To prevent page splits, I had SQL Server leave each page only 50% full. This allows for multiple inserts to occur without generating page splits, allowing our scan density to remain high for a longer period of time. But this also comes at the cost of reducing the quantity of contiguous records on each page and doubles the amount of space consumed by the table, hence the now much larger number of pages and extents scanned.

Conclusion
From examining the output of DBCC SHOWCONTIG, we were able to locate several key issues. First, we saw that our database was heavily fragmented, and required defragmentation using DBCC DBREINDEX. Next, we were able to tell what percentage of the allocated pages were actually being used by SQL Server. Finally, we saw that by modifying the fillfactor on an index, we had a tremendous affect on page splitting at the cost of more page I/O for each read.

Sql server Recovery…

2008-07-17T05:18:00.000-07:00

Sql server Recovery…

Problem:

Due to hardware failure one of the SQL database server was totally corrupted.
The sql server version is 8.00.818 .(sp3+ SQL2000-KB815495-8.00.0818-ENU.exe)
While starting the sqlserver we are not able to start the sql server service.Because there was no mdf and ldf files on the server..

Action Taken for this issue:

1.Verified Database backup files.(we have the disk backup files)
2. Uninstalled the sql server.
3. Restarted the server.
4. Installed sql server with correct configuration settings
5. Installed sp3 service pack
6. Installed SQL2000-KB815495-8.00.0818-ENU.exe
7. Stopped the sql server services
8. Started the sql server in the single user mode (c:\....\binn\>sqlservr.exe -c –m )
9. Restored the master database.
10. Restored all remaining system and user databases.
11. While checking all the databases are restored successfully.

Replication log reader agent Failure error

2008-07-11T02:02:00.000-07:00

One of the Production sql 2005 database server replication failed due to log reader agent.
Data was not moved from the publication database to distributor database.
The error message from the log reader history is below……

Code: 20011, text: 'The process could not execute 'sp_replcmds' on 'server name'.'.
The process could not execute 'sp_replcmds' on 'servername'.
Status: 2, code: 10054, text: 'TCP Provider: An existing connection was forcibly closed by the remote host.’
Status: 2, code: 10054, text: 'Communication link failure'.
Status: 2, code: 0, text: 'Query timeout expired'.
Status: 2, code: 22037, text: 'The process could not execute 'sp_replcmds' on 'server name'.'.
The agent failed with a 'Retry' status. Try to run the agent at a later time.

Action taken for this issue.

1. Restarted the log reader agent

2. Executed the sp_replflush command on publisher database.

After that issue has been resolved.

While checking logreader history and distribution history replication was working fine.

Refered Articles:

http://msdn2.microsoft.com/en-us/library/ms151190.aspx

http://technet.microsoft.com/en-us/library/ms174992.aspx

CHECKPOINT

2008-06-25T04:43:00.000-07:00

CHECKPOINT
Forces all dirty pages for the current database to be written to disk. Dirty pages are data or log pages modified after entered into the buffer cache, but the modifications have not yet been written to disk. For more information about log truncation, see Truncating the Transaction Log.
Syntax
CHECKPOINT
Remarks
The CHECKPOINT statement saves time in a subsequent recovery by creating a point at which all modifications to data and log pages are guaranteed to have been written to disk.
Checkpoints also occur:
When a database option is changed with ALTER DATABASE. A checkpoint is executed in the database in which the option is changed.
When a server is stopped, a checkpoint is executed in each database on the server. These methods of stopping Microsoft® SQL Server™ 2000 checkpoint each database:
Using SQL Server Service Manager.
Using SQL Server Enterprise Manager.
Using the SHUTDOWN statement.
Using the Windows NT command net stop mssqlserver on the command prompt.
Using the services icon in the Windows NT control panel, selecting the mssqlserver service, and clicking the stop button.
The SHUTDOWN WITH NOWAIT statement shuts down SQL Server without executing a checkpoint in each database. This may cause the subsequent restart to take a longer time than usual to recover the databases on the server.
SQL Server 2000 also automatically checkpoints any database where the lesser of these conditions occur:
The active portion of the log exceeds the size that the server could recover in the amount of time specified in the recovery interval server configuration option.
If the database is in log truncate mode and the log becomes 70 percent full.
A database is in log truncate mode when both these conditions are TRUE:
The database is using the simple recovery model.
One of these events has occurred after the last BACKUP DATABASE statement referencing the database was executed:
A BACKUP LOG statement referencing the database is executed with either the NO_LOG or TRUNCATE_ONLY clauses.
A nonlogged operation is performed in the database, such as a nonlogged bulk copy operation or a nonlogged WRITETEXT statement is executed.
An ALTER DATABASE statement that adds or deletes a file in the database is executed.
Permissions
CHECKPOINT permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles, and are not transferable.

Checkpoints and the Active Portion of the Log
Checkpoints minimize the portion of the log that must be processed during a full recovery of a database. During a full recovery, two types of actions must be performed:
The log may contain records of modifications not flushed to disk before the system stopped. These modifications must be rolled forward.
All the modifications associated with incomplete transactions (transactions for which there is no COMMIT or ROLLBACK log record) must be rolled back.
Checkpoints flush dirty data and log pages from the buffer cache of the current database, minimizing the number of modifications that have to be rolled forward during a recovery.
A SQL Server 2000 checkpoint performs these processes in the current database:
Writes to the log file a record marking the start of the checkpoint.
Stores information recorded for the checkpoint in a chain of checkpoint log records. The LSN of the start of this chain is written to the database boot page.
One piece of information recorded in the checkpoint records is the LSN of the first log image that must be present for a successful database-wide rollback. This LSN is called the Minimum Recovery LSN (MinLSN) and is the minimum of:
The LSN of the start of the checkpoint.
The LSN of the start of the oldest active transaction.
The LSN of the start of the oldest replication transaction that has not yet replicated to all subscribers.
Another piece of information recorded in the checkpoint records is a list of all outstanding, active transactions.
Deletes all log records before the new MinLSN, if the database is using the simple recovery model.
Writes to disk all dirty log and data pages.
Writes to the log file a record marking the end of the checkpoint.
The portion of the log file from the MinLSN to the last-written log record is called the active portion of the log. This is the portion of the log required to do a full recovery of the database. No part of the active log can ever be truncated. All log truncation must be done from the parts of the log before the MinLSN.
This is a simplified version of the end of a transaction log with two active transactions. Checkpoint records have been compacted to a single record.
LSN 148 is the last record in the transaction log. At the time the checkpoint recorded at LSN 147 was processed, Tran 1 had been committed and Tran 2 was the only active transaction. That makes the first log record for Tran 2 the oldest log record for a transaction active at the time of the last checkpoint. This makes LSN 142, the begin transaction record for Tran 2, the MinLSN.
Checkpoints occur:
When a CHECKPOINT statement is executed. The current database for the connection is check pointed.
When ALTER DATABASE is used to change a database option. ALTER DATABASE checkpoints the database when database options are changed.
When an instance of SQL Server is stopped by:
Executing a SHUTDOWN statement.
Using the SQL Server Service Control Manager to stop the service running an instance of the database engine.
Either of these methods checkpoints each database in the instance of SQL Server.
When an instance SQL Server periodically generates automatic checkpoints in each database to reduce the amount of time the instance would take to recover the database.
Automatic Checkpoints
SQL Server 2000 always generates automatic checkpoints. The interval between automatic checkpoints is based on the number of records in the log, not time. The time interval between automatic checkpoints can be highly variable. The time interval between automatic checkpoints is long if few modifications are made in the database. Automatic checkpoints occur frequently if a lot of data is modified.
The interval between automatic checkpoints is calculated from the recovery interval server configuration option. This option specifies the maximum time SQL Server should use to recover a database during a system restart. SQL Server estimates how many log records it can process in the recovery interval during a recovery operation. The interval between automatic checkpoints also depends on whether or not the database is using the simple recovery model.
If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.
If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:
The log becomes 70 percent full.
The number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.
Automatic checkpoints truncate the unused portion of the transaction log if the database is using the simple recovery model. The log is not truncated by automatic checkpoints if the database is using the full or bulk-logged recovery models. For more information, see Truncating the Transaction Log.
Long-Running Transactions
The active portion of the log must include every part of all uncommitted transactions. An application that starts a transaction and does not commit it or roll it back prevents SQL Server from advancing the MinLSN. This can cause two types of problems:
If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take considerably longer than the amount of time specified in the recovery interval option.
The log may grow very large because the log cannot be truncated past the MinLSN. This happens even if the database is using the simple recovery model, in which the transaction log is normally truncated on each automatic checkpoint.
Replication Transactions
The active portion of the log must also contain all transactions marked for replication, but that have not yet been replicated to a subscriber. If these transactions are not replicated in a timely manner, they can also prevent truncation of the log.

Truncating the Transaction Log
If log records were never deleted from the transaction log, the logical log would grow until it filled all the available space on the disks holding the physical log files. At some point in time, old log records no longer necessary for recovering or restoring a database must be deleted to make way for new log records. The process of deleting these log records to reduce the size of the logical log is called truncating the log.
The active portion of the transaction log can never be truncated. The active portion of the log is the part of the log needed to recover the database at any time, so must have the log images needed to roll back all incomplete transactions. It must always be present in the database in case the server fails because it will be required to recover the database when the server is restarted. The record at the start of the active portion of the log is identified by the minimum recovery log sequence number (MinLSN).
The recovery model chosen for a database determines how much of the transaction log in front of the active portion must be retained in the database. Although the log records in front of the MinLSN play no role in recovering active transactions, they are required to roll forward modifications when using log backups to restore a database to the point of failure. If you lose a database for some reason, you can recover the data by restoring the last database backup, and then restoring every log backup since the database backup. This means that the sequence of log backups must contain every log record that was written since the database backup. When you are maintaining a sequence of transaction log backups, no log record can be truncated until after it has been written to a log backup.
The log records before the MinLSN are only needed to maintain a sequence of transaction log backups.
In the simple recovery model, a sequence of transaction logs is not being maintained. All log records before the MinLSN can be truncated at any time, except while a BACKUP statement is being processed. NO_LOG and TRUNCATE_ONLY are the only BACKUP LOG options that are valid for a database that is using the simple recovery model.
Note The tempdb database always uses the simple recovery model, it cannot be switched to another recovery model. Log truncation always occurs on a checkpoint in tempdb.
In the full and bulk-logged recovery models, a sequence of transaction log backups is being maintained. The part of the logical log before the MinLSN cannot be truncated until those log records have been copied to a log backup.
Log truncation occurs at these points:
At the completion of any BACKUP LOG statement.
Every time a checkpoint is processed, provided the database is using the simple recovery model. This includes both explicit checkpoints resulting from a CHECKPOINT statement and implicit checkpoints generated by the system. The exception is that the log is not truncated if the checkpoint occurs when a BACKUP statement is still active. For more information about the interval between automatic checkpoints, see Checkpoints and the Active Portion of the Log..
Transaction logs are divided internally into sections called virtual log files. Virtual log files are the unit of truncation. When a transaction log is truncated, all log records before the start of the virtual log file containing the MinLSN are deleted. For more information about virtual log files, see Transaction Log Physical Architecture.
The size of a transaction log is therefore controlled in one of these ways:
When a log backup sequence is being maintained, schedule BACKUP LOG statements to occur at intervals that will keep the transaction log from growing past the desired size.
When a log backup sequence is not maintained, specify the simple recovery model.
This illustration shows a transaction log that has four virtual logs. The log has not been truncated after the database was created. The logical log starts at the beginning of the first virtual log and the part of virtual log 4 beyond the end of the logical file has never been used.
This illustration shows how the log looks after truncation. The rows before the start of the virtual log containing the MinLSN record have been truncated.
Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file. For information on shrinking the size of a physical log file, see Shrinking the Transaction Log.

Cumulative list of the hotfixes that are available for SQL Server 2000 स्प४

2008-06-13T04:19:00.000-07:00

Cumulative list of the hotfixes that are available for SQL Server 2000 SP4
View products that this article applies to.
function loadTOCNode(){}
Article ID
:
894905
Last Review
:
December 5, 2007
Revision
:
6.2
On This Page
INTRODUCTION
MORE INFORMATION
SQL Server 2000 build 8.00.2040
SQL Server 2000 build 8.00.2145
SQL Server 2000 build 8.00.2146
SQL Server 2000 build 8.00.2148
SQL Server 2000 build 8.00.2151
SQL Server 2000 build 8.00.2156
SQL Server 2000 build 8.00.2159
SQL Server 2000 build 8.00.2162
SQL Server 2000 build 8.00.2164
SQL Server 2000 build 8.00.2166
SQL Server 2000 build 8.00.2171
SQL Server 2000 build 8.00.2172
SQL Server 2000 build 8.00.2175
SQL Server 2000 build 8.00.2177
SQL Server 2000 build 8.00.2180
SQL Server 2000 build 8.00.2181
SQL Server 2000 build 8.00.2184
SQL Server 2000 build 8.00.2185
SQL Server 2000 build 8.00.2187
SQL Server 2000 build 8.00.2189
SQL Server 2000 build 8.00.2190
SQL Server 2000 build 8.00.2191
SQL Server 2000 build 8.00.2192
SQL Server 2000 build 8.00.2194
SQL Server 2000 build 8.00.2196
SQL Server 2000 build 8.00.2197
SQL Server 2000 build 8.00.2199
SQL Server 2000 build 8.00.2200
SQL Server 2000 build 8.00.2201
SQL Server 2000 build 8.00.2203
SQL Server 2000 build 8.00.2204
SQL Server 2000 build 8.00.2207
SQL Server 2000 build 8.00.2209
SQL Server 2000 build 8.00.2215
SQL Server 2000 build 8.00.2216
SQL Server 2000 build 8.00.2217
SQL Server 2000 build 8.00.2218
SQL Server 2000 build 8.00.2220
SQL Server 2000 build 8.00.2223
SQL Server 2000 build 8.00.2226
SQL Server 2000 build 8.00.2229
SQL Server 2000 build 8.00.2231
SQL Server 2000 build 8.00.2232
SQL Server 2000 build 8.00.2234
SQL Server 2000 build 8.00.2238
SQL Server 2000 build 8.00.2239
SQL Server 2000 build 8.00.2241
SQL Server 2000 build 8.00.2244
SQL Server 2000 build 8.00.2245
SQL Server 2000 build 8.00.2246
SQL Server 2000 build 8.00.2248
SQL Server 2000 build 8.00.2249
REFERENCES
var sectionFilter = "type != 'notice' && type != 'securedata' && type != 'querywords'";
var tocArrow = "/library/images/support/kbgraphics/public/en-us/downarrow.gif";
var depthLimit = 10;
var depth3Limit = 10;
var depth4Limit = 5;
var depth5Limit = 3;
var tocEntryMinimum = 1;

.toc{display: none;}
INTRODUCTION
loadTOCNode(1, 'summary');
This article contains a list of the Microsoft SQL Server 2000 hotfixes. You can use this list to determine which hotfixes are included in a specific hotfix build. Unless otherwise specified, all the hotfixes in earlier builds are included in later builds. The list includes all the public hotfixes that have been released since the release of Microsoft SQL Server 2000 Service Pack 4 (SP4).Important notes:
•
Before you apply any hotfix in this list, you must have SQL Server 2000 SP4 installed. To determine which version of SQL Server you have, see the following article in the Microsoft Knowledge Base:
321185 (http://support.microsoft.com/kb/321185/) How to identify your SQL Server version and edition To obtain SQL Server 2000 SP4, visit the following Microsoft Download Center Web site:
http://www.microsoft.com/downloads/details.aspx?FamilyID=8e2dfc8d-c20e-4446-99a9-b7f0213f8bc5 (http://www.microsoft.com/downloads/details.aspx?FamilyID=8e2dfc8d-c20e-4446-99a9-b7f0213f8bc5)For more information about how to download Microsoft support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591 (http://support.microsoft.com/kb/119591/) How to obtain Microsoft support files from online services Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help prevent any unauthorized changes to the file.
•
Hotfixes that are not part of a security release or a public roll-up package can only be obtained by contacting Microsoft Product Support Services. For a complete list of Microsoft Product Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/contactus/?ws=support (http://support.microsoft.com/contactus/?ws=support)Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question.
•
This list does not contain the hotfixes for Microsoft Data Access Components (MDAC) or for Microsoft SQL Server 2000 Analysis Services.
•
This article also describes a list of the SQL Server 2000 SP3-based hotfixes that are not included in SQL Server 2000 SP4. The reason is that the ship date of these hotfixes is later than the ship date of SQL Server 2000 SP4. These hotfixes are first included in this build.
Back to the top
MORE INFORMATION
loadTOCNode(1, 'moreinformation');
The following list includes all public SQL Server 2000 hotfixes that have been released since the release of SQL Server 2000 SP4.
Back to the top
SQL Server 2000 build 8.00.2040
loadTOCNode(2, 'moreinformation');
899761 (http://support.microsoft.com/kb/899761/) FIX: Not all memory is available when AWE is enabled on a computer that is running a 32-bit version of SQL Server 2000 Service Pack 4 SQL Server 2000 build 8.00.2040 includes the same hotfix that SQL Server 2000 build 8.00.2141 includes. SQL Server 2000 build 8.00.2040 is a general distribution release (GDR) update. The hotfix that is included in SQL Server 2000 build 8.00.2040 is the first hotfix that was released after SQL Server 2000 SP4.For more information about a GDR, click the following article number to view the article in the Microsoft Knowledge Base:
935897 (http://support.microsoft.com/kb/935897/) An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems
Back to the top
SQL Server 2000 build 8.00.2145
loadTOCNode(2, 'moreinformation');
836651 (http://support.microsoft.com/kb/836651/) FIX: You receive query results that were not expected when you use both ANSI joins and non-ANSI joins
898626 (http://support.microsoft.com/kb/898626/) FIX: An access violation may occur when a deadlock graph is written to the error log in SQL Server 2000
899430 (http://support.microsoft.com/kb/899430/) FIX: An access violation may occur when you run a SELECT query and the NO_BROWSETABLE option is set to ON in Microsoft SQL Server 2000
899431 (http://support.microsoft.com/kb/899431/) FIX: An access violation occurs in the Mssdi98.dll file, and SQL Server crashes when you use SQL Query Analyzer to debug a stored procedure in SQL Server 2000 Service Pack 4
900390 (http://support.microsoft.com/kb/900390/) FIX: The Mssdmn.exe process may use lots of CPU capacity when you perform a SQL Server 2000 full text search of Office Word documents
900404 (http://support.microsoft.com/kb/900404/) FIX: The results of the query may be returned much slower than you expect when you run a query that includes a GROUP BY statement in SQL Server 2000
900470 (http://support.microsoft.com/kb/900470/) FIX: You may receive an error message when you perform transactional replication in SQL Server 2000
900629 (http://support.microsoft.com/kb/900629/) FIX: You receive a "Failed to open a table" error message when you try to open a trace table by using SQL Profiler after you upgrade to SQL Server 2000 Service Pack 4
901200 (http://support.microsoft.com/kb/901200/) FIX: You may receive a “Server: Msg 7358, Level 16, State 1, Line 1” error message after you run a linked server query in SQL Server 2000 Service Pack 4
901212 (http://support.microsoft.com/kb/901212/) FIX: You receive an error message if you use the sp_addalias or sp_dropalias procedures when the IMPLICIT_TRANSACTIONS option is set to ON in SQL Server 2000 Service Pack 4
902955 (http://support.microsoft.com/kb/902955/) FIX: You receive a "Getting registry information" message when you run the Sqldiag.exe utility after you install SQL Server 2000 Service Pack 4
Back to the top
SQL Server 2000 build 8.00.2146
loadTOCNode(2, 'moreinformation');
909100 (http://support.microsoft.com/kb/909100/) FIX: The performance of the statement that raises an error differs from the performance of the statement that does not raise an error when you trace exception events in SQL Server 2000 on an IA-64 computer
Back to the top
SQL Server 2000 build 8.00.2148
loadTOCNode(2, 'moreinformation');
895123 (http://support.microsoft.com/kb/895123/) FIX: You may receive error message 701, error message 802, and error message 17803 when many hashed buffers are available in SQL Server 2000
899410 (http://support.microsoft.com/kb/899410/) FIX: You may experience slow server performance when you start a trace in an instance of SQL Server 2000 that runs on a computer that has more than four processors
902150 (http://support.microsoft.com/kb/902150/) FIX: Some 32-bit applications that use SQL-DMO and SQL-VDI APIs may stop working after you install SQL Server 2000 Service Pack 4 on an Itanium-based computer
903086 (http://support.microsoft.com/kb/903086/) FIX: The batch job stops running, and job output is truncated when you run a Transact-SQL script as a SQL Server Agent job in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2151
loadTOCNode(2, 'moreinformation');
903742 (http://support.microsoft.com/kb/903742/) FIX: You receive an “Error: 8526, Severity: 16, State: 2” error message in SQL Profiler when you use SQL Query Analyzer to start or to enlist into a distributed transaction after you have installed SQL Server 2000 Service Pack 4
905417 (http://support.microsoft.com/kb/905417/) FIX: Error message when you try to open SQL Server 2000 Data Transformation Services (DTS) packages in SQL Server Management Studio: "SQL Server 2000 DTS Designer components are required to edit DTS packages"
904244 (http://support.microsoft.com/kb/904244/) FIX: Incorrect data is inserted unexpectedly when you perform a bulk copy operation by using the DB-Library API in SQL Server 2000 Service Pack 4
Back to the top
SQL Server 2000 build 8.00.2156
loadTOCNode(2, 'moreinformation');
906790 (http://support.microsoft.com/kb/906790/) FIX: You receive an error message when you try to rebuild the master database after you have installed hotfix builds in SQL Server 2000 SP4 64-bit
906890 (http://support.microsoft.com/kb/906890/) FIX: You receive an access violation error message when a user-defined function is in a high-stress scenario in SQL Server 2000
909379 (http://support.microsoft.com/kb/909379/) FIX: You receive a 17883 error message when you run a complex query against SQL Server 2000
909380 (http://support.microsoft.com/kb/909380/) FIX: Error message 17883 may be logged in the error log when you try to manually update table statistics in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2159
loadTOCNode(2, 'moreinformation');
887700 (http://support.microsoft.com/kb/887700/) FIX: Server Network Utility may display incorrect protocol properties in SQL Server 2000
891017 (http://support.microsoft.com/kb/891017/) FIX: SQL Server 2000 may stop responding to other requests when you perform a large deallocation operation
891201 (http://support.microsoft.com/kb/891201/) FIX: Performance is significantly reduced when you set trace flag 9134 to prevent error message 601 in SQL Server 2000
891268 (http://support.microsoft.com/kb/891268/) FIX: You receive a 17883 error message and SQL Server 2000 may stop responding to other requests when you perform large in-memory sort operations
891585 (http://support.microsoft.com/kb/891585/) FIX: Database recovery does not occur, or a user database is marked as suspect in SQL Server 2000
892205 (http://support.microsoft.com/kb/892205/) FIX: You may receive a 17883 error message when SQL Server 2000 performs a very large hash operation
892392 (http://support.microsoft.com/kb/892392/) FIX: Query performance may be slower if the query contains both a GROUP BY clause and a DISTINCT keyword on the same column
892551 (http://support.microsoft.com/kb/892551/) FIX: You receive a SqlException error message when you call the sp_addmergepublication stored procedure when the Active Directory directory service is not available in SQL Server 2000
892840 (http://support.microsoft.com/kb/892840/) FIX: 17883 errors may be written to the SQL Server error log when you run a query that generates many floating point exception errors in SQL Server 2000
892923 (http://support.microsoft.com/kb/892923/) FIX: Differential database backups may not contain database changes in the Page Free Space (PFS) pages in SQL Server 2000
892935 (http://support.microsoft.com/kb/892935/) FIX: You experience slow performance when you join local partitioned views with a table in SQL Server 2000
892940 (http://support.microsoft.com/kb/892940/) FIX: You experience delays and receive a 17883 error message when SQL Server 2000 performs a large and complex query plan that uses hash operators
893172 (http://support.microsoft.com/kb/893172/) FIX: An access violation may occur when you use the READPAST lock hint and you use a non-clustered index in your query in SQL Server 2000
894257 (http://support.microsoft.com/kb/894257/) FIX: You receive an "Incorrect syntax near ')'" error message when you run a script that was generated by SQL-DMO for an Operator object in SQL Server 2000
896985 (http://support.microsoft.com/kb/896985/) FIX: The Subscriber may not be able to upload changes to the Publisher when you incrementally add an article to a publication in SQL Server 2000 Service Pack 3
897572 (http://support.microsoft.com/kb/897572/) FIX: You may receive a memory-related error message when you repeatedly create and destroy an out-of-process COM object within the same batch or stored procedure in SQL Server 2000
897578 (http://support.microsoft.com/kb/897578/) FIX: Only the master database and the tempdb database are listed when you try to create a new job in SQL Server Enterprise Manager
900625 (http://support.microsoft.com/kb/900625/) FIX: The error message that the SELECT statement returns may contain user data in SQL Server 2000
907250 (http://support.microsoft.com/kb/907250/) FIX: You may experience concurrency issues when you run the DBCC INDEXDEFRAG statement in SQL Server 2000
892310 (http://support.microsoft.com/kb/892310/) FIX: The results are different every time that you run a complex query that contains a hash outer join operator in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2162
loadTOCNode(2, 'moreinformation');
887700 (http://support.microsoft.com/kb/887700/) FIX: Server Network Utility may display incorrect protocol properties in SQL Server 2000
891017 (http://support.microsoft.com/kb/891017/) FIX: SQL Server 2000 may stop responding to other requests when you perform a large deallocation operation
891201 (http://support.microsoft.com/kb/891201/) FIX: Performance is significantly reduced when you set trace flag 9134 to prevent error message 601 in SQL Server 2000
891268 (http://support.microsoft.com/kb/891268/) FIX: You receive a 17883 error message and SQL Server 2000 may stop responding to other requests when you perform large in-memory sort operations
891585 (http://support.microsoft.com/kb/891585/) FIX: Database recovery does not occur, or a user database is marked as suspect in SQL Server 2000
891866 (http://support.microsoft.com/kb/891866/) FIX: The query runs slower than you expected when you try to parse a query in SQL Server 2000
892205 (http://support.microsoft.com/kb/892205/) FIX: You may receive a 17883 error message when SQL Server 2000 performs a very large hash operation
892310 (http://support.microsoft.com/kb/892310/) FIX: The results are different every time that you run a complex query that contains a hash outer join operator in SQL Server 2000
892392 (http://support.microsoft.com/kb/892392/) FIX: Query performance may be slower if the query contains both a GROUP BY clause and a DISTINCT keyword on the same column
892551 (http://support.microsoft.com/kb/892551/) FIX: You receive a SqlException error message when you call the sp_addmergepublication stored procedure when the Active Directory directory service is not available in SQL Server 2000
892840 (http://support.microsoft.com/kb/892840/) FIX: 17883 errors may be written to the SQL Server error log when you run a query that generates many floating point exception errors in SQL Server 2000
892923 (http://support.microsoft.com/kb/892923/) FIX: Differential database backups may not contain database changes in the Page Free Space (PFS) pages in SQL Server 2000
892935 (http://support.microsoft.com/kb/892935/) FIX: You experience slow performance when you join local partitioned views with a table in SQL Server 2000
892940 (http://support.microsoft.com/kb/892940/) FIX: You experience delays and receive a 17883 error message when SQL Server 2000 performs a large and complex query plan that uses hash operators
892985 (http://support.microsoft.com/kb/892985/) FIX: You may experience unexpected delays in query execution and unexpected time-out situations, and you may receive error message 17883 when you use the BULK INSERT command in SQL Server 2000
893172 (http://support.microsoft.com/kb/893172/) FIX: An access violation may occur when you use the READPAST lock hint and you use a non-clustered index in your query in SQL Server 2000
894254 (http://support.microsoft.com/kb/894254/) FIX: A complex query that includes a UNION ALL operator and an EXISTS clause may fail in SQL Server 2000
894257 (http://support.microsoft.com/kb/894257/) FIX: You receive an "Incorrect syntax near ')'" error message when you run a script that was generated by SQL-DMO for an Operator object in SQL Server 2000
895187 (http://support.microsoft.com/kb/895187/) FIX: You receive an error message when you try to delete records by running a Delete Transact-SQL statement in SQL Server 2000
896425 (http://support.microsoft.com/kb/896425/) FIX: The BULK INSERT statement silently skips insert attempts when the data value is NULL and the column is defined as NOT NULL for INT, SMALLINT, and BIGINT data types in SQL Server 2000
896980 (http://support.microsoft.com/kb/896980/) FIX: The query plan may take longer than expected to compile, and you may receive error message 701, error message 8623, or error message 8651 in SQL Server 2000
896985 (http://support.microsoft.com/kb/896985/) FIX: The Subscriber may not be able to upload changes to the Publisher when you incrementally add an article to a publication in SQL Server 2000 Service Pack 3
897572 (http://support.microsoft.com/kb/897572/) FIX: You may receive a memory-related error message when you repeatedly create and destroy an out-of-process COM object within the same batch or stored procedure in SQL Server 2000
897578 (http://support.microsoft.com/kb/897578/) FIX: Only the master database and the tempdb database are listed when you try to create a new job in SQL Server Enterprise Manager
898115 (http://support.microsoft.com/kb/898115/) FIX: The subquery in a CASE function is evaluated even if the condition in the WHEN expression is false in SQL Server 2000
898709 (http://support.microsoft.com/kb/898709/) FIX: Error message when you use SQL Server 2000: "Time out occurred while waiting for buffer latch type 3"
900335 (http://support.microsoft.com/kb/900335/) FIX: The SQL Server 2000 automatic database recovery operation may not succeed if an index contains a FLOAT data type or a REAL data type, and this data type contains a NaN value
900416 (http://support.microsoft.com/kb/900416/) FIX: A 17883 error may occur you run a query that uses a hash join in SQL Server 2000
900625 (http://support.microsoft.com/kb/900625/) FIX: The error message that the SELECT statement returns may contain user data in SQL Server 2000
902851 (http://support.microsoft.com/kb/902851/) FIX: SQL Server 2000 may intermittently appear to stop responding (hang) during a scheduled maintenance DBCC DBREINDEX operation
902852 (http://support.microsoft.com/kb/902852/) FIX: Error message when you run an UPDATE statement that uses two JOIN hints to update a table in SQL Server 2000: "Internal SQL Server error"
905765 (http://support.microsoft.com/kb/905765/) FIX: A view is corrupted after you run the DBCC DBREINDEX Transact-SQL statement to rebuild the clustered index on a view in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2164
loadTOCNode(2, 'moreinformation');
909089 (http://support.microsoft.com/kb/909089/) FIX: An access violation may occur when you try to run a Transact-SQL query in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2166
loadTOCNode(2, 'moreinformation');
909734 (http://support.microsoft.com/kb/909734/) FIX: An error message is logged, and new diagnostics do not capture the thread stack when the SQL Server User Mode Scheduler (UMS) experiences a nonyielding thread in SQL Server 2000 Service Pack 4
Back to the top
SQL Server 2000 build 8.00.2171
loadTOCNode(2, 'moreinformation');
908156 (http://support.microsoft.com/kb/908156/) FIX: You receive an error message when MS DTC for SQL Server 2000 reuses a server process identifier (SPID) to enlist in a distributed transaction
909369 (http://support.microsoft.com/kb/909369/) FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected
910392 (http://support.microsoft.com/kb/910392/) FIX: You may receive incorrect results when you run a Transact-SQL query in SQL Server 2000
910895 (http://support.microsoft.com/kb/910895/) FIX: You may receive an error message when you set the database collation to Thai_CI_AS and you try to run a Transact-SQL update query in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2172
loadTOCNode(2, 'moreinformation');
910707 (http://support.microsoft.com/kb/910707/) FIX: When you query a view that was created by using the VIEW_METADATA option, an access violation may occur in SQL Server 2000
910710 (http://support.microsoft.com/kb/910710/) FIX: You receive an error message when a Visual Basic application opens a dynamic cursor on a GROUP BY query in SQL Server 2000 Service Pack 4
Back to the top
SQL Server 2000 build 8.00.2175
loadTOCNode(2, 'moreinformation');
911569 (http://support.microsoft.com/kb/911569/) FIX: You may receive an access violation error message when you run a linked server query that includes a subquery
911677 (http://support.microsoft.com/kb/911677/) FIX: A query may run more slowly against SQL Server 2000 post-Service Pack 3 hotfix build 8.00.0988 than a query that you run against SQL Server 2000 post-Service Pack 3 hotfix builds that are earlier than build 8.00.0988
911678 (http://support.microsoft.com/kb/911678/) FIX: No rows may be returned, and you may receive an error message when you try to import SQL Profiler trace files into tables by using the fn_trace_gettable function in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2177
loadTOCNode(2, 'moreinformation');
912468 (http://support.microsoft.com/kb/912468/) FIX: The connection that processes the cube stops responding, and the cube is locked in SQL Server 2000 Analysis Services
913049 (http://support.microsoft.com/kb/913049/) FIX: The result set contains data for the whole relational source when you execute a query to retrieve a drillthrough result set in SQL Server 2000 Analysis Services
Back to the top
SQL Server 2000 build 8.00.2180
loadTOCNode(2, 'moreinformation');
913684 (http://support.microsoft.com/kb/913684/) FIX: You may receive error messages when you use linked servers in SQL Server 2000 on a 64-bit Itanium processor
913789 (http://support.microsoft.com/kb/913789/) FIX: The password that you specify in a BACKUP statement appears in the SQL Server Errorlog file or in the Application event log if the BACKUP statement does not run in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2181
loadTOCNode(2, 'moreinformation');
914085 (http://support.microsoft.com/kb/914085/) FIX: You may receive an error message when you try to use a Japanese user name or a Korean user name to connect to an Analysis Services server
Back to the top
SQL Server 2000 build 8.00.2184
loadTOCNode(2, 'moreinformation');
913991 (http://support.microsoft.com/kb/913991/) FIX: Hotfix files may not be copied to remote nodes when you install a 32-bit hotfix, and hotfix files may not be removed from remote nodes on a computer that is running SQL Server 2000 in an MSCS environment
914298 (http://support.microsoft.com/kb/914298/) FIX: You may receive an error message, and the SQL Server process may crash when you copy or transfer SQL Server 2000 data
914384 (http://support.microsoft.com/kb/914384/) FIX: The database status changes to Suspect when you perform a bulk copy in a transaction and then roll back the transaction in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2185
loadTOCNode(2, 'moreinformation');
914439 (http://support.microsoft.com/kb/914439/) FIX: The synchronization process may take a long time when you synchronize merge replication publications in SQL Server 2000
915340 (http://support.microsoft.com/kb/915340/) FIX: A deadlock occurs when the scheduled SQL Server Agent job that you add or that you update is running in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2187
loadTOCNode(2, 'moreinformation');
914298 (http://support.microsoft.com/kb/914298/) FIX: You may receive an error message, and the SQL Server process may crash when you copy or transfer SQL Server 2000 data
Back to the top
SQL Server 2000 build 8.00.2189
loadTOCNode(2, 'moreinformation');
913438 (http://support.microsoft.com/kb/913438/) FIX: The SQL Server process may end unexpectedly when you turn on trace flag -T1204 and a profiler trace is capturing the Lock:DeadLock Chain event in SQL Server 2000 Service Pack 4
915436 (http://support.microsoft.com/kb/915436/) FIX: Error message when you run a complex insert query or a stored procedure in SQL Server 2000: "Internal SQL Server error"
916652 (http://support.microsoft.com/kb/916652/) FIX: An access violation may occur when you run a query on a table that has a multicolumn index in SQL Server 2000
916653 (http://support.microsoft.com/kb/916653/) FIX: An access violation may occur intermittently in the CQList::Remove function when you run a query in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2190
loadTOCNode(2, 'moreinformation');
916097 (http://support.microsoft.com/kb/916097/) FIX: You may not be able to use Analysis Manager to change server properties for an instance of SQL Server 2000 Analysis Services that is running on an IA-64 server computer after you install Windows Server 2003 S916097 on a client computer
Back to the top
SQL Server 2000 build 8.00.2191
loadTOCNode(2, 'moreinformation');
916698 (http://support.microsoft.com/kb/916698/) FIX: Error message when you run SQL Server 2000: "Failed assertion = 'lockFound == TRUE'"
916950 (http://support.microsoft.com/kb/916950/) FIX: You may experience heap corruption, and SQL Server 2000 may shut down with fatal access violations when you try to browse files in SQL Server 2000 Enterprise Manager on a Windows Server 2003 x64-based computer
Back to the top
SQL Server 2000 build 8.00.2192
loadTOCNode(2, 'moreinformation');
917593 (http://support.microsoft.com/kb/917593/) FIX: The "Audit Logout" event does not appear in the trace results file when you run a profiler trace against a linked server instance in SQL Server 2000
917606 (http://support.microsoft.com/kb/917606/) FIX: You may notice a decrease in performance when you run a query that uses the UNION ALL operator in SQL Server 2000 Service Pack 4
Back to the top
SQL Server 2000 build 8.00.2194
loadTOCNode(2, 'moreinformation');
893312 (http://support.microsoft.com/kb/893312/) FIX: You may receive a "SQL Server could not spawn process_loginread thread" error message, and a memory leak may occur when you cancel a remote query in SQL Server 2000
917565 (http://support.microsoft.com/kb/917565/) FIX: Error 17883 is logged in the SQL Server error log, and the instance of SQL Server 2000 temporarily stops responding
917972 (http://support.microsoft.com/kb/917972/) FIX: You receive an access violation error message when you try to perform a read of a large binary large object column in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2196
loadTOCNode(2, 'moreinformation');
919165 (http://support.microsoft.com/kb/919165/) FIX: A memory leak occurs when you run a remote query by using a linked server in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2197
loadTOCNode(2, 'moreinformation');
919068 (http://support.microsoft.com/kb/919068/) FIX: The query may return incorrect results, and the execution plan for the query may contain a "Table Spool" operator in SQL Server 2000
919133 (http://support.microsoft.com/kb/919133/) FIX: Each query takes a long time to compile when you execute a single query or when you execute multiple concurrent queries in SQL Server 2000
919399 (http://support.microsoft.com/kb/919399/) FIX: A profiler trace in SQL Server 2000 may stop logging events unexpectedly, and you may receive the following error message: "Failed to read trace data"
Back to the top
SQL Server 2000 build 8.00.2199
loadTOCNode(2, 'moreinformation');
919221 (http://support.microsoft.com/kb/919221/) FIX: SQL Server 2000 may take a long time to complete the synchronization phase when you create a merge publication
Back to the top
SQL Server 2000 build 8.00.2200
loadTOCNode(2, 'moreinformation');
920121 (http://support.microsoft.com/kb/920121/) FIX: Error message when you add a calculated member to a cube in SQL Server 2000 Analysis Services Service Pack 4: "Unable to update the calculated member"
Back to the top
SQL Server 2000 build 8.00.2201
loadTOCNode(2, 'moreinformation');
920930 (http://support.microsoft.com/kb/920930/) FIX: Error message when you try to run a query on a linked server in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2203
loadTOCNode(2, 'moreinformation');
922040 (http://support.microsoft.com/kb/922040/) FIX: You cannot uninstall SQL Server 2000 build 2187 when SQL Server 2000 build 2187 is configured as a Microsoft Cluster Service (MSCS) node
Back to the top
SQL Server 2000 build 8.00.2204
loadTOCNode(2, 'moreinformation');
921929 (http://support.microsoft.com/kb/921929/) BUG: When you click "Build Query" in the "Execute SQL Task Properties" dialog box of the SQL Server 2000 DTS package designer, SQL Server 2005 Management Studio unexpectedly closes
Back to the top
SQL Server 2000 build 8.00.2207
loadTOCNode(2, 'moreinformation');
923344 (http://support.microsoft.com/kb/923344/) FIX: A SQL Server 2000 session may be blocked for the whole time that a Snapshot Agent job runs
Back to the top
SQL Server 2000 build 8.00.2209
loadTOCNode(2, 'moreinformation');
923327 (http://support.microsoft.com/kb/923327/) FIX: You may receive an access violation error message when you import data by using the "Bulk Insert" command in SQL Server 2000
923563 (http://support.microsoft.com/kb/923563/) FIX: Error message when you configure an immediate updating transactional replication in SQL Server 2000: "Implicit conversion from datatype 'text' to 'nvarchar' is not allowed"
923796 (http://support.microsoft.com/kb/923796/) FIX: Data in a subscriber of a merge publication in SQL Server 2000 differs from the data in the publisher
Back to the top
SQL Server 2000 build 8.00.2215
loadTOCNode(2, 'moreinformation');
924662 (http://support.microsoft.com/kb/924662/) FIX: The query performance may be slow when you query data from a view in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2216
loadTOCNode(2, 'moreinformation');
925000 (http://support.microsoft.com/kb/925000/) FIX: An access violation may occur in SQL Server 2000 Analysis Services when you try to use a 2007 Office system program to open an offline local cube
Back to the top
SQL Server 2000 build 8.00.2217
loadTOCNode(2, 'moreinformation');
924664 (http://support.microsoft.com/kb/924664/) FIX: You cannot stop the SQL Server service, or many minidump files and many log files are generated in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2218
loadTOCNode(2, 'moreinformation');
925297 (http://support.microsoft.com/kb/925297/) FIX: The result may be sorted in the wrong order when you run a query that uses the ORDER BY clause to sort a column in a table in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2220
loadTOCNode(2, 'moreinformation');
925745 (http://support.microsoft.com/kb/925745/) FIX: The members of a role that uses dynamic security cannot access the SQL Server 2000 Analysis Services database when you run a query against a cube
Back to the top
SQL Server 2000 build 8.00.2223
loadTOCNode(2, 'moreinformation');
925678 (http://support.microsoft.com/kb/925678/) FIX: Error message when you schedule a Replication Merge Agent job to run after you install SQL Server 2000 Service Pack 4: "The process could not enumerate changes at the 'Subscriber'"
925419 (http://support.microsoft.com/kb/925419/) FIX: The server stops responding, the performance is slow, and a time-out occurs in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2226
loadTOCNode(2, 'moreinformation');
925684 (http://support.microsoft.com/kb/925684/) FIX: You may experience one or more symptoms when you run a "CREATE INDEX" statement on an instance of SQL Server 2000
925732 (http://support.microsoft.com/kb/925732/) FIX: You may receive inconsistent comparison results when you compare strings by using a width sensitive collation in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2229
loadTOCNode(2, 'moreinformation');
927186 (http://support.microsoft.com/kb/927186/) FIX: Error message when you create a merge replication for tables that have computed columns in SQL Server 2000 Service Pack 4: "The process could not log conflict information"
Back to the top
SQL Server 2000 build 8.00.2231
loadTOCNode(2, 'moreinformation');
928079 (http://support.microsoft.com/kb/928079/) FIX: The Sqldumper.exe utility cannot generate a filtered SQL Server dump file when you use the Remote Desktop Connection service or Terminal Services to connect to a Windows 2000 Server-based computer in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2232
loadTOCNode(2, 'moreinformation');
928568 (http://support.microsoft.com/kb/928568/) FIX: SQL Server 2000 stops responding when you cancel a query or when a query time-out occurs, and error messages are logged in the SQL Server error log file
Back to the top
SQL Server 2000 build 8.00.2234
loadTOCNode(2, 'moreinformation');
929131 (http://support.microsoft.com/kb/929131/) FIX: In SQL Server 2000, the synchronization process is slow, and the CPU usage is high on the computer that is configured as the Distributor
929440 (http://support.microsoft.com/kb/929440/) FIX: Error messages when you try to update table rows or insert table rows into a table in SQL Server 2000: "644" or "2511"
Back to the top
SQL Server 2000 build 8.00.2238
loadTOCNode(2, 'moreinformation');
929410 (http://support.microsoft.com/kb/929410/) FIX: Error message when you run a full-text query in SQL Server 2000: "Error: 17883, Severity: 1, State: 0"
931932 (http://support.microsoft.com/kb/931932/) FIX: The merge agent fails intermittently when you use merge replication that uses a custom resolver after you install SQL Server 2000 Service Pack 4
Back to the top
SQL Server 2000 build 8.00.2239
loadTOCNode(2, 'moreinformation');
930484 (http://support.microsoft.com/kb/930484/) FIX: CPU utilization may approach 100 percent on a computer that is running SQL Server 2000 after you run the BACKUP DATABASE statement or the BACKUP LOG statement
Back to the top
SQL Server 2000 build 8.00.2241
loadTOCNode(2, 'moreinformation');
932697 (http://support.microsoft.com/kb/932697/) FIX: A calculated member that uses the DistinctCount function always returns 0 when you query a virtual cube that contains the calculated member after you install SQL Server 2000 Analysis Services Service Pack 4
Back to the top
SQL Server 2000 build 8.00.2244
loadTOCNode(2, 'moreinformation');
934203 (http://support.microsoft.com/kb/934203/) FIX: A hotfix for Microsoft SQL Server 2000 Service Pack 4 may not update all the necessary files on an x64-based computer
Back to the top
SQL Server 2000 build 8.00.2245
loadTOCNode(2, 'moreinformation');
933573 (http://support.microsoft.com/kb/933573/) FIX: You may receive an assertion or database corruption may occur when you use the bcp utility or the "Bulk Insert" Transact-SQL command to import data in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2246
loadTOCNode(2, 'moreinformation');
935465 (http://support.microsoft.com/kb/935465/) An updated version of Sqlvdi.dll is now available for SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2248
loadTOCNode(2, 'moreinformation');
935950 (http://support.microsoft.com/kb/935950/) FIX: The foreign key that you created between two tables does not work after you run the CREATE INDEX statement in SQL Server 2000
Back to the top
SQL Server 2000 build 8.00.2249
loadTOCNode(2, 'moreinformation');
936232 (http://support.microsoft.com/kb/936232/) FIX: An access violation may occur when you try to log in to an instance of SQL Server 2000
Back to the top
REFERENCES
loadTOCNode(1, 'references');
For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:
822499 (http://support.microsoft.com/kb/822499/) New naming schema for Microsoft SQL Server software update packages For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684 (http://support.microsoft.com/kb/824684/) Description of the standard terminology that is used to describe Microsoft software updates
Back to the top

APPLIES TO
•
Microsoft SQL Server 2000 Enterprise Edition 64-bit
•
Microsoft SQL Server 2000 Developer Edition
•
Microsoft SQL Server 2000 Workgroup Edition
•
Microsoft SQL Server 2000 Standard Edition
•
Microsoft SQL Server 2000 Personal Edition
•
Microsoft SQL Server 2000 Enterprise Edition
•
Microsoft SQL Server 2000 Desktop Engine (Windows)
Back to the top
Keywords:
kbbug kbfix kbqfe KB894905
Back to the top

How to manually remove a replication in SQL Server 2000 or in SQL Server 2005

2008-04-25T02:31:00.000-07:00

How to remove the transactional Publication from the server:
we should follow step by step commands to remove the Replication:
USE GOEXEC sp_dropsubscription @publication =N'', @subscriber = N'', @article = N'all', @destination_db = N''
....................................................................................................
USE GOEXEC sp_droppublication @publication = N''
USE masterGOEXEC sp_replicationdboption @dbname = N'', @optname = N'publish', @value = N'false'
.........................................................................................
USE masterGOEXEC sp_dropsubscriber @subscriber = N'', @reserved = N'drop_subscriptions'
............................................................................................USE masterGOEXEC sp_dropdistributor @no_checks = 1

Moving the tempdb database to another location

2008-04-14T03:28:00.000-07:00

Moving the tempdb database to another location

We plan to move tempdb database physical file to another drive.
We tried some different way.Atlast the below article helped to solve the issue.
Moving the tempdb database
By Bill Graziano on 05 November 2007 3 Comments Tags: Administration

--------------------------------------------------------------------------------

Moving the tempdb database in SQL Server is a simple process but does require the service to be restarted.

By default, the tempdb database is created in the same location as master, model and msdb. Tempdb is used to store temporary user created objects (e.g. temp tables), temporary internal objects (e.g. work tables for sorting) and any row version data. Each time SQL Server is started all the objects in tempdb are deleted but the file sizes are retained. People primarily want to move tempdb to increase performance or deal with file size issues.

The first step is to determine the logical file names of of the data files for tempdb. You can run the following command in SQL Server 2000 or SQL Server 2005:

USE tempdb
GO
EXEC sp_helpfile
GO

name fileid filename
--------- ------ -------------------------------
tempdev 1 c:\Data\MSSQL\data\tempdb.mdf
templog 2 c:\Data\MSSQL\data\templog.ldfI've omitted some extra columns from the result set. The logical names always seem to be tempdev and templog. Knowing this we can run the following ALTER DATABASE statement in SQL Server 2000 or SQL Server 2005 to move tempdb. Set the FILENAME parameter to the location where you'd like each file.

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempdb2005.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'C:\tempdb2005.ldf')

Output:
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.

Restart the sql server services.

GOSQL Server will return a message indicating that the old files can be deleted after restarting SQL Server or that the new files will be available after the database is restarted depending on which version of SQL Server you're using. In either case, you'll need to restart SQL Server for the change to take effect.

Sql versions list

2007-09-27T05:41:00.000-07:00

This is very important for every sql server DBA ।This version list will help disaster time.

select @@versions;
SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'))
This command will return Returns:
'RTM' = shipping version.'SPn' = service pack version'Bn', = beta version.

http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

DBA database management checklist

2007-08-21T00:31:00.001-07:00

DBA database management chekklist

Written By: Edgewood Solutions Engineers -- 7/3/2007

ProblemFor both old and new DBAs there are fundamental procedures that should be addressed and proper processes put in place to handle various areas of database management for SQL Server. Whether you are a full time DBA or this is one of many job roles that you perform the same basic steps should be implemented and adhered to in order to have some peace of mind that you are performing the correct procedures to ensure a healthy running SQL Server environment. For old DBAs these items should be a no-brainer, but often a refresher is good reminder to make sure everything is in place. For new DBAs some of these basic items are not all that apparent and often some of the most basic DBA 101 items are sometimes overlooked. So based on this, what is a good plan to implement to make sure the basic SQL Server DBA items are being addressed?
SolutionThe following is a checklist of 10 items that should be in place for all of your SQL Server database servers. Some of these items are pretty basic and easy to implement, while others require a higher level of effort to think through what is needed and then to implement the process.
These items are not necessarily written in any priority order, because not any one of these items is a complete database management plan it really requires all of these items to be thought about, addressed and implemented.
#
Item
Steps
1
Backups
This is one of the most basic items to be addressed. Everyone knows that a good solid backup plan should be in place for all databases, but time and time again I run across servers where the wrong or no backup plan is in place.
To get started you need to ask yourself a few questions such as the following:
What are you trying to recover from when a failure occurs?
How much data can be lost? A day, one hour, a week, none...
What kind of processing occurs, transaction based, batch loading, a combination?
Can the data be easily recreated if there is a failure or is this the only source of this data?
This is just a short list of questions to ask, but once these are addressed this will allow you to determine; 1) the recovery model for your database and 2) the backup processing.
Depending on your needs your backup plan may look like one of the following:
Daily full backups only
Daily full backups and transaction log backups every hour
Daily full backups, transaction log backups every 15 minutes and differential backups every 4 hours
Note: If you are unsure what to do, start with at least full backups on a daily basis. If your data is very important and you cannot easily recreate the data, change your database recovery model to FULL and implement both full and transaction log backups.
2
Run Integrity Checks
Another area that should be addressed is the integrity of your data. SQL Server offers a few options to address this such as DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, etc...
These commands check the allocation, structure and logical integrity of all objects in your database. In addition, to running these commands either through maintenance plans, maintenance jobs or via a query window you also need to analyze the data to look for any integrity issues that need to be addressed. This is another area that I see a lot of where the commands are run via maintenance jobs, but no one ever reviews the reports to see if there are any issues that need to be addressed.
For the most part these integrity issues pop up a lot less than they did with earlier versions of SQL Server, but this is still an area that should be part of your DBA procedures.
3
Maintain Indexes
Indexes are those helpful pointers that allow you quick access to your data pages in your database. When indexes are newly created the structure is nice and clean and everything works great by accessing your data via the index instead of having to scan the entire table.
Over time these helpful indexes become fragmented and take up unnecessary space and accessing your data pages is not as efficient as it was when the indexes were first created. This is where index maintenance is a critical DBA process that needs to be in place.
In SQL Server 2000 you have the ability to run index rebuilds across the board for all tables when using maintenance plans. This was an all or nothing approach. In SQL Server 2005, you also have the ability to run index rebuilds as well as index defrags. In addition, you can pick specific tables that you need to manage. Although this is not a perfect process for maintaining indexes it is definitely better than not doing anything.
To take this a step further you can manage your index maintenance table by table or index by index. Some indexes will become fragmented while others may never have an issue based on how the index was created and how data is applied to the table/index. Based on this, by doing the across the board method of index management you are spending unnecessary time addressing a problem that does not exist for some the tables. Therefore the best approach would be to use the tools that SQL Server offers such as DBCC SHOWCONTIG and sys.dm_db_index_physical_stats to identify where the real issues exist and then take steps to address these tables and indexes instead of every table and index in your database.
4
Review Error Logs
There are several areas where SQL Server logs information about processes that are occurring as well as errors that occur. The most used is probably the SQL Server Error Log. This error log gives you startup information, integrity check information, backup information, etc... as well as any SQL Server errors that occur. In addition to this log, there is also a log for SQL Server Agent and now in SQL Server 2005 Database Mail. In addition to these internal SQL Server logs you should also use the Windows Event Log to find other errors that may be occurring or possibly additional information that is not in the SQL Server logs.
Reviewing the logs should be part of your daily routine for checking the health of your system. The ideal way to handle this is to use some tool that automates the alert process when there is an error, but either way you should keep these error logs on your radar list as something to review each day.
5
Manage SQL Server Agent Jobs
SQL Server's built-in job scheduling tool is a great tool for automating your backups, index rebuilds, integrity checks, etc... But in addition to this tool giving you the flexibility to run these jobs during off hours you also need to make sure you are monitoring job success and failure. This can be automated by setting up SQL Mail (SQL 2000) or Database Mail (SQL 2005) and having failures be sent out to operators that are configured.
This is another area I see all the time where there are several jobs that fail not just once or twice but every single time they were run. Take the time on a daily basis to check out the job failures and address the issue so all of your jobs run successfully.
6
Test Backups
A SQL Server backup is only good if the restore works. No matter how many backups you take of your database if you cannot restore the file when needed there is no point in doing backups in the first place. The only way to determine if your backup/restore process will work is to periodically test on another server. This not only gives you peace of mind that the restore was successful, but this also gives you an idea of how long the entire process will take if you ever need to do this on your production server. Having this little insight and the time it will take to recover you database will go along way when you have people breathing down you neck.
In addition to testing, you should also use the RESTORE VERIFY option when creating your backups. It doesn't necessarily tell you that the restore will not have any issues, but it will at least prove that SQL Server can read the entire backup file without a problem.
7
Monitor Performance
This is one area that should be a no-brainer if you are responsible for monitoring your SQL Server environment. The database is usually the last thing people think about when they are working with an application, but when the application is slow the database is usually the first thing that is blamed for the poor performance.
The problem with performances monitoring is not that most people don't do this, it is that they are not sure how to do this. Windows and SQL Server offer built in tools such as Performance Monitor, Profiler, Execution Plans, Index Tuning Wizard, Database Engine Tuning Advisor, etc... In addition, there are a whole bunch of third party tools that allow you to trend performance issues and be alerted when there are issues. Without good data it is very difficult to say when there really is a performance issue and also without this data it is difficult to know where to spend your time fixing issues that will have a big impact versus fixing something that will not have a very big impact.
8
Document
Another thing that should be implemented is a documentation process to document procedures, priority lists, escalation lists, production changes, roll out procedures, etc... A good set of procedures should be established, so everyone that works on your SQL Servers understands the processes that you have put in place as well as to document all changes that occur, so when a problem does arise you can pinpoint when a change was made.
A simple text file could be used to track your changes or since we are all database developers/DBAs why not use SQL Server to document and track the changes. This should be one of the simplest things to implement and there is no reason you can start doing this today.
9
Create and Test Disaster Recovery Plan
Disasters don't strike all that often and because of this disaster recovery plans are usually not implemented. I am sure just about everyone has thought about this at one point in time, but thinking about a disaster recovery plan and implementing a plan are two totally different things.
As a DBA you need to take the time to determine what kind of issues may arise and how to resolve the problem when it does occur. Think about this from a server level, database level and also down to a table level. Once you have determine what you need to do and how you are going to go about resolving the issue take the time to do some tests. You don't need to test every single server in your environment, but you should try to test each type of failure that you are trying to recover from.
Another thing to put in place is a priority list for your servers and your databases. This way if there are multiple failures that occur you already have a priority list of what needs to be addressed and the order that they need to be dealt with.
10
Manage Security
Security is also another area that is the DBAs responsibility to monitor. As you probably know security levels exist at the Windows server level, SQL Server server level, database level, object level, etc... There are SQL Server server level roles, database roles and user defined roles. Take the time to analyze your permission structure and make the necessary adjustments to ensure people have the rights they need to work, but not additional rights that may cause potential problems. In addition, to securing your database servers, make sure your database backups and any external programs are also secure, so no one can gain backdoor access to your servers or your data.

Database Restore Verification

2007-08-21T00:20:00.000-07:00

ProblemI have over night processes on a few different SQL Servers in my environment that are backup and restore related. I have used the scripts on MSSQLTips.com to check if the SQL Server Agent Jobs\Job Steps have run as well as the backup verification code. Some of the backup and restore processes I have written and others I have inherited. Some of the processes seem to be very reliable and others are not. I need a way to validate the restore processes have completed properly just like the SQL Server Agent Job and backup code. Can you provide this script so I can include it in my daily verification process?
SolutionValidating daily processes such as SQL Server Agent Jobs, backups, restores, etc। should be an daily task for all DBAs to ensure the core business processes execute as expected. When thinking about restore related processes the other thought that comes to mind is log shipping. Whether you have to support daily backup and restore processes or full fledged log shipping, validating the full, differential and transaction log restores have worked properly is critical especially when these process are considered your disaster recovery or high availability solution. It is better to be safe than sorry. As such, here is code to validate the database restoration processes in the last 24 hours:

All Backups - SQL Server 2005 and 2000 Restore Verification

SELECT restore_date, destination_database_name, restore_type = CASE WHEN restore_type = 'D' THEN 'Database'WHEN restore_type = 'F' THEN 'File'WHEN restore_type = 'G' THEN 'Filegroup'WHEN restore_type = 'L' THEN 'Log'WHEN restore_type = 'I' THEN 'Differential'WHEN restore_type = 'R' THEN 'Revert'ELSE 'Unknown'ENDFROM MSDB।dbo.restorehistoryWHERE restore_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()AND restore_type <> 'V'ORDER BY restore_history_id

Full Backups - SQL Server 2005 and 2000 Restore Verification

SELECT restore_date, destination_database_nameFROM MSDB.dbo.restorehistoryWHERE restore_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()AND restore_type = 'D'ORDER BY restore_history_id
Differential Backups - SQL Server 2005 and 2000 Restore Verification
SELECT restore_date, destination_database_nameFROM MSDB।dbo.restorehistoryWHERE restore_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()AND restore_type = 'I'ORDER BY restore_history_id

Transaction Log Backups - SQL Server 2005 and 2000 Restore Verification

SELECT restore_date, destination_database_nameFROM MSDB.dbo.restorehistoryWHERE restore_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()AND restore_type = 'L'ORDER BY restore_history_id

Delete Backup History stored procedure

2007-08-20T02:47:00.000-07:00

I recently asked why his msdb database had grown so large (2GB). I found out that it was due to the sizes of the backup system tables. These tables store information used for backup and restore operations. If you've ever used the restore database wizard in Enterprise Manager (and if you have, you might want to start using Query Analyzer instead to get more familiar with backup and restore operations), you might notice that it displays a listing of previous backups to choose from for the restore. It gathers this information from these backup system tables. If you backup your transaction logs as often as we do (every 15 minutes), these tables can get quite large. Microsoft provides sp_delete_backuphistory to delete this data. But, this stored procedure is very slow as it uses a cursor to loop through the data. Using sp_delete_backuphistory took over 2 hours to delete all but 10 days worth of data. I provided to him my version of this stored procedure that uses JOINs instead of a cursor. With slight modifications, he was able to get it working for him. This version was able to delete all but 10 days worth of data in 32 seconds.

I got this stored procedure from

http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1704.aspx

Here's the stored procedure:
CREATE PROC isp_DeleteBackupHistory(@DaysToRetain int)AS
SET NOCOUNT ON
DECLARE @Err intDECLARE @rc int
BEGIN TRAN
DELETE FROM msdb..restorefile FROM msdb..restorefile rf INNER JOIN msdb..restorehistory rh ON rf.restore_history_id = rh.restore_history_id INNER JOIN msdb..backupset bs ON rh.backup_set_id = bs.backup_set_id WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain) SET @Err = @@ERROR IF @Err <> 0 GOTO Error_Exit DELETE FROM msdb..restorefilegroup FROM msdb..restorefilegroup rfg INNER JOIN msdb..restorehistory rh ON rfg.restore_history_id = rh.restore_history_id INNER JOIN msdb..backupset bs ON rh.backup_set_id = bs.backup_set_id WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain) SET @Err = @@ERROR IF @Err <> 0 GOTO Error_Exit DELETE FROM msdb..restorehistory FROM msdb..restorehistory rh INNER JOIN msdb..backupset bs ON rh.backup_set_id = bs.backup_set_id WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain) SET @Err = @@ERROR IF @Err <> 0 GOTO Error_Exit SELECT media_set_id, backup_finish_date INTO #Temp FROM msdb..backupset WHERE backup_finish_date < (GETDATE() - @DaysToRetain) SET @Err = @@ERROR IF @Err <> 0 GOTO Error_Exit DELETE FROM msdb..backupfile FROM msdb..backupfile bf INNER JOIN msdb..backupset bs ON bf.backup_set_id = bs.backup_set_id INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain) SET @Err = @@ERROR IF @Err <> 0 GOTO Error_Exit DELETE FROM msdb..backupset FROM msdb..backupset bs INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
SET @Err = @@ERROR
IF @Err <> 0 GOTO Error_Exit DELETE FROM msdb..backupmediafamily FROM msdb..backupmediafamily bmf INNER JOIN msdb..backupmediaset bms ON bmf.media_set_id = bms.media_set_id INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
SET @Err = @@ERROR
IF @Err <> 0 GOTO Error_Exit DELETE FROM msdb..backupmediaset FROM msdb..backupmediaset bms INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
SET @Err = @@ERROR
IF @Err <> 0 GOTO Error_Exit
COMMIT TRAN
SET @rc = 0
GOTO isp_DeleteBackupHistory_Exit
Error_Exit:
ROLLBACK TRAN
SET @rc = -1
isp_DeleteBackupHistory_Exit:
DROP TABLE #Temp
SET NOCOUNT OFF
RETURN @rc

GO

Replication Optimization Tips

2007-07-23T04:06:00.000-07:00

Replication Optimization Tips

QUICK TIME Database RESTORATION through QUERY ANALAYSER

2007-07-17T03:54:00.000-07:00

QUICK TIME Database RESTORATION through QUERY ANALAYSER:

This sp will give the backup set information. All the restore command we will get in single stored procedure

<<<<<<<<
create proc usp_restoration @dbname varchar(20)

as

DECLARE @databaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT

-- set database to be used
SET @databaseName = @dbName

SELECT @backup_set_id_start = MAX(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'

SELECT @backup_set_id_end = MIN(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''
+ mf.physical_device_name + ''' WITH NORECOVERY'
FROM msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id = @backup_set_id_start
UNION
SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = '''
+ mf.physical_device_name + ''' WITH NORECOVERY'
FROM msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
AND b.type = 'L'
UNION
SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
ORDER BY backup_set_id
Go

>>>>>>>>>>>>>>>>

2007-07-09T02:18:00.000-07:00

SQL - change object owner for multiple objects

sp_changeobjectowner stored procedure in the Master database to change the owner of an object one at a time, but there is a semi-automatic way to do it.

SELECT 'EXEC sp_changeobjectowner ''dbo.' + ROUTINE_NAME + ''', ''thenewuser'''FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'dbo'

Execute it, then click the top left corner of the output window, to select all the rows. Copy and paste the rows into a new query window and execute.
To change table owners, modify the query and repeat the proces:
SELECT 'EXEC sp_changeobjectowner ''dbo.' + TABLE_NAME + ''', ''thenewuser'''FROM INFORMATION_SCHEMA.TABLES

Log Shipping

2007-05-29T01:45:00.000-07:00

What is Log Shipping?
Log Shipping is one of the solutions for High Availability for business critical application. Log Shipping is first introduced in SQL Server 7.0. Log Shipping; as name defines does the shipping of Transaction Log to another server; it does exact replica of the database; it does backup of Transaction Log and restores it on a Secondary /Stand by Server which keeps database on secondary server in sync with the database on Primary Server. In case of failure work loss happens only for the time being copy of T-Log backup and restoring back to Secondary/Stand by Server. Stand by / Secondary server which can act as a primary server in case of failure. SQL Server DBA has to perform some manual tasks to bring it online for business.
Why to do Log Shipping?
There are other options like Clustering and Replication for High Availability of Business Critical Application, so why do we go for Log Shipping?
Well, there are reasons for choosing Log Shipping against Cluster and Replications:
1. Setting up: It is very easy to create a Log Shipping, only thing you need to identify is you must have two servers with SQL Server Enterprise Edition and a database(s) which is critical to your business environment.
2. Manageability: It is easy to manage Log Shipping comparing to Replication and Cluster Server, generally Log Shipping works well and you don’t feel panic with its monitoring or maintenance work.
3. Act as a Reporting Server: Stand by or Secondary Server can act as a Reporting Server in your organization, your database which is being log shipped can be used for Read Only queries or I would rather say it can act as a Reporting Server. The thing need to be consider here is it will be unavailable at the time of Restoration of Transaction Log e.g. if your Transaction Log backup takes 10 minutes of Restoration Time and you does Log Shipping every 60 minutes then your Reporting/Secondary/Stand by Server will be un-available for 10 minutes.
4. Can use multiple database(s)/server(s): You can use multiple server(s) as a Stand by or Secondary Server for different purpose, e.g. you can create two Stand by server(s) one for Reporting and another for High Availability
5. Cheaper then cluster: If you go for a Cluster Server you will have to meet software and Hardware requirement; I mean you need identical hardware to setup clustering where as in case of Log Shipping you don’t need to have exactly the same set of hardware for Primary and Secondary or Stand by server.
6. Best solution for physical dispersed location: Data Center: In your environment you are having a cluster server configured at your Data Center for high availability so that if in case of any failure or damaged you can recover your data back, imagine what if your data center itself got damaged!!!!!! In this type of scenario Log Shipping does good job you can configure Log Shipping to Dr Server which is at different physically location and if your local Data Center / Server got damaged you may still recover your data. You can do this thing using Replication too; in case of replication again you have to select Snapshot/Transactional/Snapshot Replication, now as you may use Log Shipping you would rather go for Snapshot Replication, you have to monitoring it regularly whether it is running or not!!! Where as with Log Shipping it generally runs smoothly and even if it caught an error it will tell you exact where it breaks so fixing a Log Shipping is not difficult.
Prerequisite
1. Database must be in Full or Bulk-Logged Recovery Model.It is required that Database has to be in Full or Bulk-Logged Recovery Model,One can perform T-Log backup only when Database Recovery Model is set to Full or Bulk-Logged, here is a brief on recovery model.

3. Create a share on Primary Server and Secondary Server with change and write permissions for Windows Account User which is used for SQL Server Agent.

4. You may Restore Full Database on Secondary Server manually or it can be done via Log Shipping installation itself at the time of setup process (Ensure this database on Secondary Server must be restored with NORECOVERY or STANDBY option).It is required to Restore Database with NORECOVERT STANDBY because if the database restored in recovered/write enabled mode you can not apply another T-Log.

Optional Component : Monitoring / Witness Server
Identify your Monitor Server which will look after for your Primary Server and SECONDARY Server’s Activity.It is better to use separate server which monitors the activity of Primary and Secondary Server so that you can get the alert even if server itself won’t works.
Primary Server
1. Create a Share with read/write permission for domain user which we’ll be using to startup SQLServerAgent
2. Create a Full Database backup for LogShDB
3. Backed up T-Log on the folder you have created earlier to store the log backup You can create a maintenance plan to take a T-Log backup for regular interval and then add it as a Step in job to automate this process
4. Please ensure that the duration must enough to copy the log backup file to Secondary Server.Suppose we are taking T-Log backup each 1/2 hour and if the backup file is big enough which takes more then 1/2 to copy over the network share, it will again creates a new T-Log backup which will then occupies recourse unnecessarily.
5. Copy T-Log backup file to Secondary Server’s shared folder
6. Delete the T-Log backup file after it copied to the Secondary Server’s shared folder.
7. Ensure you have selected Allow Database to assume primary role option so that if in future requirement of role reversal can be possible easily.If in case we need to change the role of primary server or secondary server to act as Primary or Secondary this option should be used while setting up Log Shipping.
8. Ensure that you have set up proper threshold values for Destination Database for Out of Sync, Load Time Delay, File Retention Period and History Retention Period option.These options are self explanatory: Out of Sync=used by witness server to send alert if the specified time has been exceeded between Last T-Log backup on primary and secondary server, Load Time Delay= Destination Database waits before it restores the T-Log, File Retention Period= Time elapse till file gets deleted and History Retention Period= Time till we retain History details
Secondary Server
1. Check if the Database is already exists on Secondary Server, if it is already their drop it.
2. Restore a Full Database Backup for LogShDB 3. Restore a T-Log for LogShDB on the secondary server which is copied on the shared folder of Secondary Server;
4. Ensure you have given STANDBY or NORECOVERY option while restoring T-Log backup for LogShDB.
Summary:
You may find log shipping is a very use full yet simple to configure, use and manage, it is cheaper and easily manageable solution for High Availability of your data. It is a good solution over clustering (because it is costly; requires identical hardware to setup cluster) and easily manageable, easy troubleshooting against replication as it clearly says where it breaks. Generally Log Shipping works very smooth once it is configured. Even if you have to re-configure it from the scratch it is easy job to do.

How to avoid deadlocking on your SQL Server

2007-05-29T01:14:00.000-07:00

Deadlock:

Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.

Some tips for reducing the deadlock:

Ensure the database design is properly normalized.
Have the application access server objects in the same order each time.
During transactions, don't allow any user input. Collect it before the transaction begins.
Avoid cursors.
Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch.
Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If you do need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there.
Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
If appropriate, use as low of an isolation level as possible for the user connection running the transaction. Consider using bound connections.

Example:

You are a database developer for a clothing retailer. The company has a database named Sales. This
database contains a table named Inventory. The Inventory table contains the list of items for sale and the
quantity available for each of those items. When sales information is inserted into the database, this table
is updated. The stored procedure that updates the Inventory table is shown in the exhibit.
CREATE PROCEDURE UpdateInventory @IntID int
AS
BEGIN
DECLARE @Count int
BEGIN TRAN
SELECT @Count = Available
FROM Inventory WITH (HOLDLOCK)
WHERE InventoryID = @IntID
IF (@Count > 0)
UPDATE Inventory SET Available = @Count – 1
WHERE InventoryID = @IntID
COMMIT TRAN
END
When this procedure executes, the database server occasionally returns the following error message:
Transaction (Process ID 53) was deadlocked on {lock} resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.

You need to prevent the error message from occurring while maintaining data integrity.
Change the table hint to UPDLOCK.
Explanation: This is a deadlock problem. We must resolve this problem. The SQL batch of this scenario
basically consists of an SELECT statement and an UPDATE statement. The SELECT statement includes a table
hint: WITH (HOLDLOCK).
This table hint is very restrictive. The rows are completely locked. We need to remove this restrictive table hint
and replace it with the table hint UPDLOCK, which is less restrictive than HOLDLOCK. It allows reads of the
rows, but no updates.
Note: table hint
A table hint specifies that a table scan, or one or more indexes, must be used by the query optimizer, or a
locking method must be used by the query optimizer with this table and for this SELECT. The query optimizer
can usually pick the best optimization method without hints being specified, therefore it is recommended that
hints only be used as a last resort by experienced developers and database administrators.

Further Refference:
http://www.sql-server-performance.com/deadlocks.asp

How to Store pdf,txt doc files in Tables MSSQL SERVER 2005

2007-05-28T01:25:00.000-07:00

Hi,

1. Create table with varbinary(max) column(s)
2. Use BCP or BULK INSERT or OPENROWSET(BULK... or create an SSIS Package to transfer the data from flat file to SQL

Example:
create table #ORStable (doclen bigint, doc varbinary(max))
insert into #ORStable
select len(bulkcolumn), *
from
openrowset(bulk 'C:\test.doc', SINGLE_BLOB)
as r

select *
from #ORStable

To use BULK INSERT you need a format file.
Unfortunately, the format file requires the exact size of the word doc

Format file definition: (worddoc.fmt)
Code Snippet 9.0 1 1 SQLBINARY 0 20480 "" 1 doc ""

create table #BItable (doc varbinary(max))
BULK INSERT #BItable
FROM 'C:\test.doc'
WITH ( FORMATFILE= 'C:\worddoc.fmt', CODEPAGE= 'RAW' )

select len(doc), *
from #BITable

To export the data back to a .doc file recreate table #ORStable (doclen bigint, doc varbinary(max))
insert into #ORStable
select len(bulkcolumn), *
from
openrowset(bulk 'C:\test.doc', SINGLE_BLOB)
as r

select *
from #ORStable

To use BULK INSERT you need a format file.
Unfortunately, the format file requires the exact size of the word doc

Format file definition: (worddoc.fmt)
Code Snippet 9.0 1 1 SQLBINARY 0 20480 "" 1 doc ""

create table #BItable (doc varbinary(max))
BULK INSERT #BItable
FROM 'C:\test.doc'
WITH ( FORMATFILE= 'C:\worddoc.fmt', CODEPAGE= 'RAW' )

select len(doc), *
from #BITable

To export the data back to a .doc file requires using BCP and a format file (again with the exact size)

Working with the .txt files and varchar(max) is a little easier, but you need varbinary to save the exact images of the PDF and DOC files.quires using BCP and a format file (again with the exact size)

Working with the .txt files and varchar(max) is a little easier, but you need varbinary to save the exact images of the PDF and DOC files.

How to tranfer the same logins and password between the sql server 2000 and sql server 2005

2007-05-17T00:07:00.000-07:00

While upgrading time I faced this problem.After that I got solution from microsoft.

steps:

1.Sql server 2000 we must create sp_help_revlogin stored procedure.

2.This stored procedure will help tranfer the login along with SID and same password.

Procedure:

This script will help to create the sp_hexadecimal and sp_help_revlogin stored procedure.

http://support.microsoft.com/kb/246133

New Features in SQL Server 2005

2007-05-08T05:13:00.000-07:00

New Features in SQL Server 2005

Management Studio

Includes All Tools
Object Explorer replaces Enterprise Manager
Query Editor Replaces Query Analyzer
New Table Designer
New Server Properties
Server Registration Export/Import
New Database Properties
Template Explorer
Solution Explorer
Projects Management
Web Browser Window
Properties Window
Assisted Editors
Source Control
XML Editor
Toolbars Selection
Dynamic Help

T-SQL Enhancements

Database Schemas
Script Storage in Project
Xquery - XML Query Language
XML Data Type
varchar(max) & nvarchar(max) Data Types
varbinary(max) Data Type
PIVOT and UNPIVOT
INTERSECT and EXCEPT
OVER(PARTITION BY...)
Recursive Query
Common Table Expression - CTE
RANK and DENSE_RANK
NTILE
ROW_NUMBER
Partitioned Table, Partition Function, & Partition Scheme
DDL Trigger
.NET Assembly support
CROSS APPLY and OUTER APPLY
OUTPUT Clause
TRY and CATCH for Exception Handling
TOP and TABLESAMPLE Clauses
EXECUTE AS Clause
MARS - Multiple Active Result Sets
Snapshot Isolation
Indexes with Included Columns

SQL Server Profiler

Deadlock Visualization
Correlate a Trace with Windows Performance Log Data
Save Trace as XML File

SQL Configuration Manager

Replaces Services Manager
Replaces Server Network Utility
Replaces Client Network Utility

SQL Server 2005 Surface Area Configuration

Surface Area Configuration for Services and Connections
Surface Area Configuration for Features

Utilities

SQLCMD utility with new scripting language
Dedicated Administrator Connection(DAC) to Server

Security
CREATE CERTIFICATE
Encryption
Decryption
Asymmetric Key

Operations

Setup Consistency Checker
Online Database Restore
Online Reindex
Mirrored Backups
Database Mirroring
Database Snapshot Database Tuning Advisor

Differnce between sql 2000 and sql 2005 with New Features

2007-05-02T00:20:00.000-07:00

Sql server 2000 vs Sqlserver 2005

Enterprise manager Sqlserver management studio(combination of EM&QA)

Query analyser visuval studio 2005(Inbuild)
Business Intelligence Development Studio (BIDS)
Analysis Services Manager (ASM)

T-sql SMO and T-sql

DTS(data transformation service) SSIs(sql server Integrated services)(advanced version of dts)

security new security features(Encryption,password expiry)

Top 10 features for administration part:
-------------------------------------------------

Database mirroring
Online Restore
Online Indexing Operations
Fast Recovery
Standards-based Information Access
SQL Server Management Studio
Dedicated Administrator Connection
Snapshot Isolation
Data Partitioning
Replication

Top 10 features for developer part:
-----------------------------------------
Hosted CommonLanguage Runtime vc#,vb,.net
Native XML Support
ADO.NET version 2.0
Security Enhancements
Transact-SQL Enhancements
Reliable Messaging for Asynchronous Applications
Visual Studio Integration
Web Services
Embedded Reports
Full-Text Search Enhancements

Top 10 Features for Business Intelligence:
-----------------------------------------------

Analysis Services
Integration Services (SSIS)
Data Mining
Reporting Services
Clustering Support
Key Performance
Scalability and Performance
Report Builder
Proactive Caching
Integration with the Microsoft Office System(SharePoint Server 2007)

SQL SERVER 2005 ENTERPRISE EDITION FEATURE:
---------------
Number of CPUs-------Nolimit
RAM………………… Operating system maximum
64-bit Support
Database Size…………Nolimit
Partitioning
Parallel Index Operations
Indexed Views

High Availability:
------------------
1.Database Mirroring
2.Failover Clustering
3.Backup Log-shipping
4.Online System Changes
5.Onlne Indexing
6.Online Restore
7.Fast Recovery

Manageability:
----------------
Auto Tuning
Profiler
SQL Server Management Studio Express
Management Studio
Database Tuning Advisor
Serviceability Enhancements
Full-text Search
SQL Agent Job Scheduling Service

Security:
-----------------
Advanced Auditing, Authentication, and Authorization
Data Encryption and Key Management
Integration with Microsoft Baseline Security Analyzer
Integration with Microsoft Update

Programmability:
-------------------------
Stored Procedures, Triggers, and Views
T-SQL Enhancements
Common Language Runtime and .NET Integration
User-defined Types
Native XML
XQuery
Notification Services
Service Broker

Integration and Interoperability:
--------------------------------------------
Import/Export
Integration Services with Basic Transforms
Integration Services Advanced Transforms
Merge Replication
Transactional Replication
Oracle Replication
Web Services (HTTP Endpoints)

Discontinued feature :

Discontinued feature
.Named pipe backup devices.
.isql utility
.'allow updates' option of sp_configure.
'open objects' option of sp_configure.
.'set working set size' option of sp_configure
.DISK INIT
.DISK RESIZE
.FOR LOAD option of CREATE DATABASE
.DBCC DBREPAIR
.DBCC NEWALLOC
DBCC PINTABLE, DBCC UNPINTABLE
DBCC ROWLOCK
DBCC TEXTALL
DBCC TEXTALLOC
Use of SRV_PWD field in the SRV_PFIELD structure when there has been an impersonation context switch from the original login.
The following protocols: NWLink IPX/SPX, AppleTalk, Banyan Vines Multiprotocol.
Rebuildm.exe
.Northwind and pubs
Remote Setup - the TARGETCOMPUTER parameter - is not supported.
SQL-DMO based WMI provider
SQL Namespace API (SQL-NS)
*= and =* outer join operators
.syslocks

SQL Server 2005 (32-bit) HARDWARE REQUIRMENTS

2007-05-01T00:14:00.000-07:00

SQL Server 2005 (32-bit)

Processor type1
Processor speed2
Memory (RAM)3
SQL Server 2005 Enterprise Edition 4
SQL Server 2005 Developer Edition
SQL Server 2005 Standard Edition
Pentium III-compatible processor or higher
Minimum: 600 MHz
Recommended: 1 GHz or higher
Minimum: 512 MB
Recommended: 1 GB or more
Maximum: Operating system maximum
SQL Server 2005 Workgroup Edition
Pentium III-compatible processor or higher
Minimum: 600 MHz
Recommended: 1 GHz or higher
Minimum: 512 MB
Recommended: 1 GB or more
Maximum: Operating system maximum
SQL Server 2005 Express Edition
Pentium III-compatible processor or higher
Minimum: 500 MHz
Recommended: 1 GHz or higher
Minimum: 192 MB
Recommended: 512 MB or more
Maximum: Operating system maximum
SQL Server 2005 Express Edition with Advanced Services
Pentium III-compatible processor or higher
Minimum: 600 MHz
Recommended: 1 GHz or higher
Minimum: 512 MB
Recommended: 1 GB or more
Maximum: Operating system maximum

SQL SERVER 2000 MAX SIZE LIMIT

2007-04-30T23:50:00.000-07:00

Object
SQL Server 2000
Batch size
65,536 * Network Packet Size1
Bytes per sort string column
8,000
Bytes per text, ntext, or image column
2 GB-2
Bytes per GROUP BY, ORDER BY
8,060
Bytes per index
9002
Bytes per foreign key
900
Bytes per primary key
900
Bytes per row
8,060
Bytes in source text of a stored procedure
Lesser of batch size or 250 MB
Clustered indexes per table
1
Columns in GROUP BY, ORDER BY
Limited only by number of bytes per GROUP BY, ORDER BY
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement

Columns per index
16
Columns per foreign key
16
Columns per primary key
16
Columns per base table
1,024
Columns per SELECT statement
4,096
Columns per INSERT statement
1,024
Connections per client
Maximum value of configured connections
Database size
1,048,516 TB3
Databases per instance of SQL Server
32,767
Filegroups per database
256
Files per database
32,767
File size (data)
32 TB
File size (log)
32 TB
Foreign key table references per table
253
Identifier length (in characters)
128
Instances per computer
16
Length of a string containing SQL statements (batch size)
65,536 * Network packet size1
Locks per connection
Max. locks per server
Locks per instance of SQL Server
2,147,483,647 (static)
Nested stored procedure levels
40% of SQL Server memory (dynamic)
Nested subqueries
32
Nested trigger levels
32
Nonclustered indexes per table
32
Objects concurrently open in an instance of SQL Server4
249
Objects in a database
2,147,483,647 (or available memory)
Parameters per stored procedure
21,474,836,474
REFERENCES per table
2,100
Rows per table
253
Tables per database
Limited by available storage
Tables per SELECT statement
Limited by number of objects in a database4
Triggers per table
256
UNIQUE indexes or constraints per table
Limited by number of objects in a database4
249 nonclustered and 1 clustered

SQL SERVER 2005MAX SIZE LIMIT

2007-04-30T23:10:00.000-07:00

SQL Server 2005 Database Engine object
Maximum sizes/numbers SQL Server 2005 (32-bit)
Batch size1
65,536 * Network Packet Size
Bytes per short string column
8,000
Bytes per GROUP BY, ORDER BY
8,060
Bytes per index key2
900
Bytes per foreign key
900
Bytes per primary key
900
Bytes per row8
8,060
Bytes per varchar(max), varbinary(max), xml, text, or image column
2^31-1
Characters per ntext or nvarchar(max) column
2^30-1
Clustered indexes per table
1
Columns in GROUP BY, ORDER BY
Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement
10
Columns per index key7
16
Columns per foreign key
16
Columns per primary key
16
Columns per base table
1,024
Columns per SELECT statement
4,096
Columns per INSERT statement
1,024
Connections per client
Maximum value of configured connections
Database size
1,048,516 terabytes
Databases per instance of SQL Server
32,767
Filegroups per database
32,767
Files per database
32,767
File size (data)
16 terabytes
File size (log)
2 terabytes
Foreign key table references per table4
253
Identifier length (in characters)
128
Instances per computer
50 instances on a stand-alone server for all SQL Server 2005 editions except for Workgroup Edition. Workgroup Edition supports a maximum of 16 instances.
Length of a string containing SQL statements (batch size)1
SQL Server 2005 supports 25 instances on a failover cluster.
Locks per connection
65,536 * Network packet size
Locks per instance of SQL Server5
Maximum locks per server
Nested stored procedure levels6
Up to 2,147,483,647
Nested subqueries
32
Nested trigger levels
32
Nonclustered indexes per table
32
Parameters per stored procedure
249
Parameters per user-defined function
2,100
REFERENCES per table
2,100
Rows per table
253
Tables per database3
Limited by available storage
Limited by number of objects in a database
1,000
2,000
256
Limited by number of objects in a database
249 nonclustered and 1 clustered
32,767
249