Friday, December 25, 2009

Move all DTS Packages between servers.

We can move all DTS packages from one server to another server through given two methods:
Method1:

If you save your dts packages under local packages, they are stored in the msdb.dbo.sysdtspackages table.
1) create a new dts package with connections to the two servers
2) create a datapump between the connection
3) because you cannot select the sysdtspackages table from the pull-down list, you need to use disconnected edit to manually modify the datapump to set the SourceTable and the DestinationTable to [msdb].[dbo].[sysdtspackages]
4) now close the disconnected edit
5) choose properties of the datapump and let dts perform the auto-mapping in the transfermations tab.
6) run it!
This will transform the rows in sysdtspackages from one server to the other, and PRESTO! All the packages have been moved, including all the version history!You could also set up a source query to select specific packages based on the name, or only the current version based on the create date.

Method 2:
Quite easy step!!!!!!1
--restore msdb db with msdbnew name from from source server to the target server then run below script:
insert into sysdtspackages ( name, id, versionid, description, categoryid, createdate, owner, packagedata, owner_sid, packagetype)
select name, id, versionid, description, categoryid, createdate, owner, packagedata, owner_sid, packagetype from msdbnew..sysdtspackages /* this is msdb from old server*/

Tuesday, December 15, 2009

Database is in transition

This issue comes when you tried to make your database offline and during this process you run the cmd sp_who2.There are some transactions in process and you try to make database offline, then you will have to sit with patience while SQL Server successfully abort/commit all transactions on backend and make it offline successfully meanwhile if you will try to access the database then get the msg, "Database is in transition", if you cannot sit with patience then just kill the thread from Activity Monitor where you see the command alter database set flat offline the other option is to restart the sql server, but in case of restart, it will not give you control of database, after restart the database will go in auto recovery mode, but after some minutes and this successful recovery you will be able to do any operation on database.

Monday, December 14, 2009

DBCC CHECKDB requires space in tempdb.

It Checks the allocation and structural integrity of all the objects in the specified database.DBCC CHECKDB is the safest repair statement because it identifies and repairs the widest possible errors. If only allocation errors are reported for a database, execute DBCC CHECKALLOC with a repair option to repair these errors. However, to ensure that all errors, including allocation errors, are properly repaired, execute DBCC CHECKDB with a repair option rather than DBCC CHECKALLOC with a repair option.
DBCC CHECKDB validates the integrity of everything in a database. There is no need to run DBCC CHECKALLOC or DBCC CHECKTABLE if DBCC CHECKDB either is currently or has been recently executed.DBCC CHECKDB performs the same checking as if both a DBCC CHECKALLOC statement and a DBCC CHECKTABLE statement were executed for each table in the database.
DBCC CHECKDB does not acquire table locks by default. Instead, it acquires schema locks that prevent meta data changes but allow changes to the data. The schema locks acquired will prevent the user from getting an exclusive table lock required to build a clustered index, drop any index, or truncate the table.
The DBCC statement collects information, and then scans the log for any additional changes made, merging the two sets of information together to produce a consistent view of the data at the end of the scan.
When the TABLOCK option is specified, DBCC CHECKDB acquires shared table locks. This allows more detailed error messages for some classes of errors and minimizes the amount of tempdb space required by avoiding the use of transaction log data. The TABLOCK option will not block the truncation of the log and will allow the command to run faster.
DBCC CHECKDB checks the linkages and sizes of text, ntext, and image pages for each table, and the allocation of all the pages in the database.
For each table in the database, DBCC CHECKDB checks that:
Index and data pages are correctly linked.
Indexes are in their proper sort order.
Pointers are consistent.
The data on each page is reasonable.
Page offsets are reasonable.
Errors indicate potential problems in the database and should be corrected immediately.
By default, DBCC CHECKDB performs parallel checking of objects. The degree of parallelism is determined automatically by the query processor. The maximum degree of parallelism is configured in the same manner as that of parallel queries. Use the sp_configure system stored procedure to restrict the maximum number of processors available for DBCC checking.