How to determine the version and edition of SQL Server

Question: How to determine the version and edition of SQL Server?

Answer: Connect to the instance of SQL Server, and then run the following query: 

Select @@version

An example of the output of this query is as follows:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 
10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Express 
Edition (64-bit) on Windows NT 6.1  (Build 7600: )

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Thai_CI_AS"

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Thai_CI_AS" in the equal to operation.

Cause: Temp Table that was created by your sql statement use conflict collation with default collations of database or table you querying


Resolution: I install language in Control Panel > Regional and Language Settings for Thai then restart computer. After that, it works normally.

How to transfer the logins and the passwords of SQL Server

How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008

How to transfer logins and passwords between instances of SQL Server

How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008

How to move databases between computers that are running SQL Server

How to Restore SQL Server 2005 Suspect Database

Introduction

If your project's database is in suspect mode, then no transaction will take place until and unless you repair your database. That causes a show stopper for your up and running application. Here, you will find a way to get out of this.

Background

Your Database is in Suspect Mode. I guess, you haven't experienced this problem till now. But, if it comes to you and if the database is LIVE, then it's time to read this article to get out of this tension.

Using the Code

If you find your database in Suspect mode, then please keep your nerve strong. Just proceed step by step what I am written below. I think you will get out of this trouble. SQL Server 2005 introduced a new DB Status called Emergency. This mode can change the DB from Suspect mode to Emergency mode, so that you can retrieve the data in read only mode. The steps are... After executing the script given below, you will get back your database in operational mode. Actually I have tried with two of my existing live systems and found no data loss.

Note: Obviously there are two more options available. Run REPAIR_ALLOW_DATA_LOSS to ensure the database is returned to a structurally and transitionally consistent state. Here are a few things to bear in mind about emergency mode repair: it's a one-way operation. Anything it does cannot be rolled back or undone. If this worries you (if someone ever got into that state, then surely don't have the healthy concern about data that they should have in the first place) then make a copy of the damaged database before you run emergency mode repair.
As it's a one-way operation, you cannot wrap it in an explicit user-transaction.
It's the only repair option available in emergency mode - if you try to use REPAIR_REBUILD, then it won't work.
Collapse

EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

Points of Interest

You will be happy that your database as well as the application are still in workable condition. :)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author
Suvendu Banik


Reference: http://www.codeproject.com/KB/reporting-services/SQL_2005_Suspect_Database.aspx

Changing the name of a SQL Server machine

Overview
If you change the machine name of a machine with SQL Server installed, you need to do a few things in SQL Server after the machine name change. This article outlines those steps. The article applies to SQL Server 7.0, 2000, 2005 and 2008.

Note: If the SQL Server is installed in a cluster, you should reinstall or hire an expert (who probably will tell you to reinstall). Thanks to SQL Server MVP Geoff N. Hiten for catching that.

Step 1 (7.0)
The SQL Server service will not start after the machine name change. You will get a Windows error message box: "Your SQL Server installation is either corrupt or had been tampered with (unknown package id) Please rerun setup."
All you have to do is to follow the advice. Pop in the SQL Server CD and run setup again. Setup will not reinstall anything, it will only make the adjustments needed so you can start SQL Server again.

Step 2 (all versions)
After starting SQL Server, you need to take care of the sysservers table in the master database. SQL Server stores the local machine name here and this will not match if you have renamed the machine in Windows. This is also easy to fix:

EXEC sp_dropserver 'oldservername'
GO
EXEC sp_addserver 'newservername', 'local'
GO

You need to replace above with the old machine name and with the new machine name.

Step 3 (7.0 and 2000)
In this last step, you need to handle the sysjobs table in the msdb database. There is one row in sysjobs for each SQL Server agent job. In the column originating_server, you find the name of the server where the job was created. This is to support master and target server (MSX). If a job is created on a master server, you cannot modify the job definition on the targer server; all modifications has to be done on the master server.

So, if you changed the machine name, SQL Server will think that the job originated on a master server, and you will not be able to do anything with that job definition. You can handle this in two ways:

1. Rename the machine back to the old name, script the job definitions, delete the jobs, rename the machine to the new name again and use the script to re-create the jobs. See Error 14274 Occurs When You Update a SQL Agent Job After Renaming Windows Server for more information.

2. I find it easier to modify the sysjobs table directly. Note that this isn't supported and if you don't feel confident with doing below, don't; use above steps instead.

DECLARE @srv sysname
SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname)
UPDATE sysjobs SET originating_server = @srv

If this is a target server (you have jobs sent from a master server), you have to exclude them, so you don't convert those jobs into local jobs:

WHERE originating_server = ''

You need to replace above with the old machine name.

Read this if you deploy images with SQL Server installed
The "Distributed Transaction Coordinator" service (DTC) creates a GUID in the registry when it is installed. Below is an example of a path for the key, for a SQL Server instance named "Fresh":
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\FRESH\MSSQLServer\ResourceMgrID

If you image such an installation using Ghost or some other imaging software, you will have duplicates of this GUID on your machines and your distributed transactions will fail. You can delete this key and a new key with a unique value will be created next time the SQL Server is started. Make sure that you don't have any open distributed transactions when doing this.

Reference: http://www.karaszi.com/SQLServer/info_change_server_name.asp

How to Truncate Log File in SQL Server 2005

Introduction

SQL Server 2005 is quite different from SQL Server 2000. To truncate log file is one thing which is different from SQL Server 2000. In SQL Server 2000, you just use Shrink to whatever file size you like. In SQL Server 2005, sometimes I cannot shrink the log file at all.

Here I want to describe some tricks to truncate log file for a database in SQL Server 2005. The work environment is Microsoft SQL Server Management Studio.

I. Shrink the Log File Size at the Right Time

I found out this trick:

Immediately after I use the SSIS package or Import the data to the database (highlight the database->Tasks->Import data …), or Export the data from the database (highlight the database->Tasks->Export data …), I can shrink the log file to the desired size, for example, 1MB. That is, highlight the database->Tasks->Shrink->Files , set the file size, say, 1MB.

Then, click OK and you are done.

II. Eliminate the Log File Completely

Sometimes, we just do not need the big log file. For example, I have a 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is:

1. Detach the database
2. Rename the log file
3. Attach the database without the log file
4. Delete the log file

Let’s say, the database name is testDev. In the SQL Server Management Studio,

1. Highlight the database-> Tasks->Detach..-> Click OK
2. Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf
3. Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf
4. After this is done, you can verify the contents of the attached database and then delete the log file

This way we can safely delete the log file and free up the space.

Reference Site: http://www.codeproject.com/KB/database/truncate_log_SQL_server.aspx

How to clear log and shrink database in MSSQL with transaction-sql

run this command:

backup log databasename with no_log
backup log databasename with truncate_only
dbcc shrinkdatabase(databasename,1)

How to check all database size in MSSQL

run this command in Management Studio
# exec sp_databases

Limiting views on shared SQL server installations

For shared SQL Server installations, SSMS will allow all customers to see the names of other databases. You can change that default behavior with a SQL Server 2005 configuration change.

To do so, on your SQL 2005 servers, revoke "View Any Database" from public. Then, customers of a shared server, will only see their database, provided you've made the customer the owner of the database. An easy way to do this is with the stored procedure sp_changedbowner or with Alter Database.