Aug 31, 2009

Decrypting the encrypted DTS command to readable text.

Copy and paste the DTSRUN script including DTSRUN to command line window (aka cmd), add switches /!X and /!C at the end of the DTSRUN command. Press enter.

Result has been copied to clipboard, just paste it to notepad.

/!X does not execute the command, and /!C copies the result on clipboard.

Aug 26, 2009

Moving data or log files to another location without detaching database.

There are various ways you can move the datafiles or logfiles to new location, However, I am going to share the method I used because I don't need to detach the database and reattach the database using this method. This method saves lot of maintenance time, in case if your database is replicated or it is required to be present 24/7. However, there will be performance fallback while performing any of these methods.


The only limitation using this method of moving files is that the primary data file and primary log file can not be moved while online, however the primary files can be marked as Empty File and sql server will transfer all data to new file and sql server will not put data in files marked as empty files anymore.

It has to be done in the following sequence:

  1. Add new files to database to new location.
  2. Transfer data from old file to new files and marking file as not to be used for storing any data.
  3. Remove old files (except primary data or primary log file). Though primary data or log file will not be used for storing data in future and all existing data will be moved to new file in same filegroup.

Here are the details you can follow against each step I mentioned above:-

1. Add new files to database to new location.

Assuming that you have database named TESTING and you have a file group named SECONDARY. Just make sure that you add new file to one of the existing FILEGROUPS (In my case it is SECONDARY), It is required because the existing data will be moved to existing FILEGROUPS when you mark the database as EMPTYFILE. Run the following script, edit the parameters as per your requirements.

ALTER DATABASE testing
ADD FILE ---- for adding log file just change this line to ADD LOG FILE
(NAME = 'data_5'
, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\data_5_Data.NDF'
, SIZE =5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10MB)
TO FILEGROUP secondary

2. Move data from existing file to another file and mark file as EMPTYFILE so that you could remove the file later.

DBCC SHRINKFILE(data_3, EMPTYFILE)
GO

Once you mark the file as EMPTYFILE, you may delete the file from list, or you can keep the file if the file is Primary file, however, the file now is empty (no pages) and sql will not store any data in this file.

3. This is the final step. Remove the file you just marked as EMPTYFILE

ALTER DATABASE TESTING
REMOVE FILE data_3
GO





Thanks
Swarndeep

Aug 25, 2009

SQL 2000: Database in suspect mode.

Database can go into suspect mode for a couple of reasons, including, corruption in data files, indexes, logs, bad storage, less space available on storage for the database files to grow. Following is one of the ways to fix the suspect databases. I followed this process on one of the production user databases.
Please read through before taking any action.
First you need to put your database into Emergency mode so that database can be accessed (though read only).

sp_configure 'allow updates', 1
Reconfigure with override
GO -- This will make system table editable.
--
update master..sysdatabases set status = status | 32768
where name = 'db'
GO --puts database into emergency mode
--

Once database is in emergency mode, try running the DBCC CHECKDB to check the database. If DBCC shows any error message, fix the error.

In case DBCC reports Log error, you may rebuild the log, however rebuilding the log will remove the t-log data:
DBCC REBUILD_LOG('db','c:\mssql\data\db.ldf')
--

In case of corrupted index, try creating the infected index again, you may try Create Index ... DROP_EXISTING
--

If nothing helps, you may run DBCC CHECKDB('DB',REPAIR_ALLOW_DATA_LOSS), however, this may certainly result in some data loss.

Once you have completed all the activities and database is fixed, you may run the following script to set database to normal mode.

update master..sysdatabases set status = 0 (Or the original value)
where name = 'db'
GO --puts database back into normal mode.
--

Sp_configure 'allow updates', 0
Reconfigure with override
GO -- This will make system tables un-editable.
--

Swarndeep

Optimizing Indexes with Execution Plans

Contact Me

Name

Email *

Message *